1: # 2003 April 4
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library. The
12: # focus of this script is testing the sqlite3_set_authorizer() API
13: # and related functionality.
14: #
15: # $Id: auth.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16: #
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: # disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
22: # defined during compilation.
23: if {[catch {db auth {}} msg]} {
24: finish_test
25: return
26: }
27:
28: rename proc proc_real
29: proc_real proc {name arguments script} {
30: proc_real $name $arguments $script
31: if {$name=="auth"} {
32: db authorizer ::auth
33: }
34: }
35:
36: do_test auth-1.1.1 {
37: db close
38: set ::DB [sqlite3 db test.db]
39: proc auth {code arg1 arg2 arg3 arg4} {
40: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
41: return SQLITE_DENY
42: }
43: return SQLITE_OK
44: }
45: db authorizer ::auth
46: catchsql {CREATE TABLE t1(a,b,c)}
47: } {1 {not authorized}}
48: do_test auth-1.1.2 {
49: db errorcode
50: } {23}
51: do_test auth-1.1.3 {
52: db authorizer
53: } {::auth}
54: do_test auth-1.1.4 {
55: # Ticket #896.
56: catchsql {
57: SELECT x;
58: }
59: } {1 {no such column: x}}
60: do_test auth-1.2 {
61: execsql {SELECT name FROM sqlite_master}
62: } {}
63: do_test auth-1.3.1 {
64: proc auth {code arg1 arg2 arg3 arg4} {
65: if {$code=="SQLITE_CREATE_TABLE"} {
66: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
67: return SQLITE_DENY
68: }
69: return SQLITE_OK
70: }
71: catchsql {CREATE TABLE t1(a,b,c)}
72: } {1 {not authorized}}
73: do_test auth-1.3.2 {
74: db errorcode
75: } {23}
76: do_test auth-1.3.3 {
77: set ::authargs
78: } {t1 {} main {}}
79: do_test auth-1.4 {
80: execsql {SELECT name FROM sqlite_master}
81: } {}
82:
83: ifcapable tempdb {
84: do_test auth-1.5 {
85: proc auth {code arg1 arg2 arg3 arg4} {
86: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
87: return SQLITE_DENY
88: }
89: return SQLITE_OK
90: }
91: catchsql {CREATE TEMP TABLE t1(a,b,c)}
92: } {1 {not authorized}}
93: do_test auth-1.6 {
94: execsql {SELECT name FROM sqlite_temp_master}
95: } {}
96: do_test auth-1.7.1 {
97: proc auth {code arg1 arg2 arg3 arg4} {
98: if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
99: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
100: return SQLITE_DENY
101: }
102: return SQLITE_OK
103: }
104: catchsql {CREATE TEMP TABLE t1(a,b,c)}
105: } {1 {not authorized}}
106: do_test auth-1.7.2 {
107: set ::authargs
108: } {t1 {} temp {}}
109: do_test auth-1.8 {
110: execsql {SELECT name FROM sqlite_temp_master}
111: } {}
112: }
113:
114: do_test auth-1.9 {
115: proc auth {code arg1 arg2 arg3 arg4} {
116: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
117: return SQLITE_IGNORE
118: }
119: return SQLITE_OK
120: }
121: catchsql {CREATE TABLE t1(a,b,c)}
122: } {0 {}}
123: do_test auth-1.10 {
124: execsql {SELECT name FROM sqlite_master}
125: } {}
126: do_test auth-1.11 {
127: proc auth {code arg1 arg2 arg3 arg4} {
128: if {$code=="SQLITE_CREATE_TABLE"} {
129: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
130: return SQLITE_IGNORE
131: }
132: return SQLITE_OK
133: }
134: catchsql {CREATE TABLE t1(a,b,c)}
135: } {0 {}}
136: do_test auth-1.12 {
137: execsql {SELECT name FROM sqlite_master}
138: } {}
139:
140: ifcapable tempdb {
141: do_test auth-1.13 {
142: proc auth {code arg1 arg2 arg3 arg4} {
143: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
144: return SQLITE_IGNORE
145: }
146: return SQLITE_OK
147: }
148: catchsql {CREATE TEMP TABLE t1(a,b,c)}
149: } {0 {}}
150: do_test auth-1.14 {
151: execsql {SELECT name FROM sqlite_temp_master}
152: } {}
153: do_test auth-1.15 {
154: proc auth {code arg1 arg2 arg3 arg4} {
155: if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
156: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
157: return SQLITE_IGNORE
158: }
159: return SQLITE_OK
160: }
161: catchsql {CREATE TEMP TABLE t1(a,b,c)}
162: } {0 {}}
163: do_test auth-1.16 {
164: execsql {SELECT name FROM sqlite_temp_master}
165: } {}
166:
167: do_test auth-1.17 {
168: proc auth {code arg1 arg2 arg3 arg4} {
169: if {$code=="SQLITE_CREATE_TABLE"} {
170: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
171: return SQLITE_DENY
172: }
173: return SQLITE_OK
174: }
175: catchsql {CREATE TEMP TABLE t1(a,b,c)}
176: } {0 {}}
177: do_test auth-1.18 {
178: execsql {SELECT name FROM sqlite_temp_master}
179: } {t1}
180: }
181:
182: do_test auth-1.19.1 {
183: set ::authargs {}
184: proc auth {code arg1 arg2 arg3 arg4} {
185: if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
186: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
187: return SQLITE_DENY
188: }
189: return SQLITE_OK
190: }
191: catchsql {CREATE TABLE t2(a,b,c)}
192: } {0 {}}
193: do_test auth-1.19.2 {
194: set ::authargs
195: } {}
196: do_test auth-1.20 {
197: execsql {SELECT name FROM sqlite_master}
198: } {t2}
199:
200: do_test auth-1.21.1 {
201: proc auth {code arg1 arg2 arg3 arg4} {
202: if {$code=="SQLITE_DROP_TABLE"} {
203: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
204: return SQLITE_DENY
205: }
206: return SQLITE_OK
207: }
208: catchsql {DROP TABLE t2}
209: } {1 {not authorized}}
210: do_test auth-1.21.2 {
211: set ::authargs
212: } {t2 {} main {}}
213: do_test auth-1.22 {
214: execsql {SELECT name FROM sqlite_master}
215: } {t2}
216: do_test auth-1.23.1 {
217: proc auth {code arg1 arg2 arg3 arg4} {
218: if {$code=="SQLITE_DROP_TABLE"} {
219: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
220: return SQLITE_IGNORE
221: }
222: return SQLITE_OK
223: }
224: catchsql {DROP TABLE t2}
225: } {0 {}}
226: do_test auth-1.23.2 {
227: set ::authargs
228: } {t2 {} main {}}
229: do_test auth-1.24 {
230: execsql {SELECT name FROM sqlite_master}
231: } {t2}
232:
233: ifcapable tempdb {
234: do_test auth-1.25 {
235: proc auth {code arg1 arg2 arg3 arg4} {
236: if {$code=="SQLITE_DROP_TEMP_TABLE"} {
237: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
238: return SQLITE_DENY
239: }
240: return SQLITE_OK
241: }
242: catchsql {DROP TABLE t1}
243: } {1 {not authorized}}
244: do_test auth-1.26 {
245: execsql {SELECT name FROM sqlite_temp_master}
246: } {t1}
247: do_test auth-1.27 {
248: proc auth {code arg1 arg2 arg3 arg4} {
249: if {$code=="SQLITE_DROP_TEMP_TABLE"} {
250: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
251: return SQLITE_IGNORE
252: }
253: return SQLITE_OK
254: }
255: catchsql {DROP TABLE t1}
256: } {0 {}}
257: do_test auth-1.28 {
258: execsql {SELECT name FROM sqlite_temp_master}
259: } {t1}
260: }
261:
262: do_test auth-1.29 {
263: proc auth {code arg1 arg2 arg3 arg4} {
264: if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
265: return SQLITE_DENY
266: }
267: return SQLITE_OK
268: }
269: catchsql {INSERT INTO t2 VALUES(1,2,3)}
270: } {1 {not authorized}}
271: do_test auth-1.30 {
272: execsql {SELECT * FROM t2}
273: } {}
274: do_test auth-1.31 {
275: proc auth {code arg1 arg2 arg3 arg4} {
276: if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
277: return SQLITE_IGNORE
278: }
279: return SQLITE_OK
280: }
281: catchsql {INSERT INTO t2 VALUES(1,2,3)}
282: } {0 {}}
283: do_test auth-1.32 {
284: execsql {SELECT * FROM t2}
285: } {}
286: do_test auth-1.33 {
287: proc auth {code arg1 arg2 arg3 arg4} {
288: if {$code=="SQLITE_INSERT" && $arg1=="t1"} {
289: return SQLITE_IGNORE
290: }
291: return SQLITE_OK
292: }
293: catchsql {INSERT INTO t2 VALUES(1,2,3)}
294: } {0 {}}
295: do_test auth-1.34 {
296: execsql {SELECT * FROM t2}
297: } {1 2 3}
298:
299: do_test auth-1.35.1 {
300: proc auth {code arg1 arg2 arg3 arg4} {
301: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
302: return SQLITE_DENY
303: }
304: return SQLITE_OK
305: }
306: catchsql {SELECT * FROM t2}
307: } {1 {access to t2.b is prohibited}}
308: ifcapable attach {
309: do_test auth-1.35.2 {
310: execsql {ATTACH DATABASE 'test.db' AS two}
311: catchsql {SELECT * FROM two.t2}
312: } {1 {access to two.t2.b is prohibited}}
313: execsql {DETACH DATABASE two}
314: }
315: do_test auth-1.36 {
316: proc auth {code arg1 arg2 arg3 arg4} {
317: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
318: return SQLITE_IGNORE
319: }
320: return SQLITE_OK
321: }
322: catchsql {SELECT * FROM t2}
323: } {0 {1 {} 3}}
324: do_test auth-1.37 {
325: proc auth {code arg1 arg2 arg3 arg4} {
326: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
327: return SQLITE_IGNORE
328: }
329: return SQLITE_OK
330: }
331: catchsql {SELECT * FROM t2 WHERE b=2}
332: } {0 {}}
333: do_test auth-1.38 {
334: proc auth {code arg1 arg2 arg3 arg4} {
335: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} {
336: return SQLITE_IGNORE
337: }
338: return SQLITE_OK
339: }
340: catchsql {SELECT * FROM t2 WHERE b=2}
341: } {0 {{} 2 3}}
342: do_test auth-1.39 {
343: proc auth {code arg1 arg2 arg3 arg4} {
344: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
345: return SQLITE_IGNORE
346: }
347: return SQLITE_OK
348: }
349: catchsql {SELECT * FROM t2 WHERE b IS NULL}
350: } {0 {1 {} 3}}
351: do_test auth-1.40 {
352: proc auth {code arg1 arg2 arg3 arg4} {
353: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
354: return SQLITE_DENY
355: }
356: return SQLITE_OK
357: }
358: catchsql {SELECT a,c FROM t2 WHERE b IS NULL}
359: } {1 {access to t2.b is prohibited}}
360:
361: do_test auth-1.41 {
362: proc auth {code arg1 arg2 arg3 arg4} {
363: if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
364: return SQLITE_DENY
365: }
366: return SQLITE_OK
367: }
368: catchsql {UPDATE t2 SET a=11}
369: } {0 {}}
370: do_test auth-1.42 {
371: execsql {SELECT * FROM t2}
372: } {11 2 3}
373: do_test auth-1.43 {
374: proc auth {code arg1 arg2 arg3 arg4} {
375: if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
376: return SQLITE_DENY
377: }
378: return SQLITE_OK
379: }
380: catchsql {UPDATE t2 SET b=22, c=33}
381: } {1 {not authorized}}
382: do_test auth-1.44 {
383: execsql {SELECT * FROM t2}
384: } {11 2 3}
385: do_test auth-1.45 {
386: proc auth {code arg1 arg2 arg3 arg4} {
387: if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
388: return SQLITE_IGNORE
389: }
390: return SQLITE_OK
391: }
392: catchsql {UPDATE t2 SET b=22, c=33}
393: } {0 {}}
394: do_test auth-1.46 {
395: execsql {SELECT * FROM t2}
396: } {11 2 33}
397:
398: do_test auth-1.47 {
399: proc auth {code arg1 arg2 arg3 arg4} {
400: if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
401: return SQLITE_DENY
402: }
403: return SQLITE_OK
404: }
405: catchsql {DELETE FROM t2 WHERE a=11}
406: } {1 {not authorized}}
407: do_test auth-1.48 {
408: execsql {SELECT * FROM t2}
409: } {11 2 33}
410: do_test auth-1.49 {
411: proc auth {code arg1 arg2 arg3 arg4} {
412: if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
413: return SQLITE_IGNORE
414: }
415: return SQLITE_OK
416: }
417: catchsql {DELETE FROM t2 WHERE a=11}
418: } {0 {}}
419: do_test auth-1.50 {
420: execsql {SELECT * FROM t2}
421: } {}
422: do_test auth-1.50.2 {
423: execsql {INSERT INTO t2 VALUES(11, 2, 33)}
424: } {}
425:
426: do_test auth-1.51 {
427: proc auth {code arg1 arg2 arg3 arg4} {
428: if {$code=="SQLITE_SELECT"} {
429: return SQLITE_DENY
430: }
431: return SQLITE_OK
432: }
433: catchsql {SELECT * FROM t2}
434: } {1 {not authorized}}
435: do_test auth-1.52 {
436: proc auth {code arg1 arg2 arg3 arg4} {
437: if {$code=="SQLITE_SELECT"} {
438: return SQLITE_IGNORE
439: }
440: return SQLITE_OK
441: }
442: catchsql {SELECT * FROM t2}
443: } {0 {}}
444: do_test auth-1.53 {
445: proc auth {code arg1 arg2 arg3 arg4} {
446: if {$code=="SQLITE_SELECT"} {
447: return SQLITE_OK
448: }
449: return SQLITE_OK
450: }
451: catchsql {SELECT * FROM t2}
452: } {0 {11 2 33}}
453:
454: # Update for version 3: There used to be a handful of test here that
455: # tested the authorisation callback with the COPY command. The following
456: # test makes the same database modifications as they used to.
457: do_test auth-1.54 {
458: execsql {INSERT INTO t2 VALUES(7, 8, 9);}
459: } {}
460: do_test auth-1.55 {
461: execsql {SELECT * FROM t2}
462: } {11 2 33 7 8 9}
463:
464: do_test auth-1.63 {
465: proc auth {code arg1 arg2 arg3 arg4} {
466: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
467: return SQLITE_DENY
468: }
469: return SQLITE_OK
470: }
471: catchsql {DROP TABLE t2}
472: } {1 {not authorized}}
473: do_test auth-1.64 {
474: execsql {SELECT name FROM sqlite_master}
475: } {t2}
476: do_test auth-1.65 {
477: proc auth {code arg1 arg2 arg3 arg4} {
478: if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
479: return SQLITE_DENY
480: }
481: return SQLITE_OK
482: }
483: catchsql {DROP TABLE t2}
484: } {1 {not authorized}}
485: do_test auth-1.66 {
486: execsql {SELECT name FROM sqlite_master}
487: } {t2}
488:
489: ifcapable tempdb {
490: do_test auth-1.67 {
491: proc auth {code arg1 arg2 arg3 arg4} {
492: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
493: return SQLITE_DENY
494: }
495: return SQLITE_OK
496: }
497: catchsql {DROP TABLE t1}
498: } {1 {not authorized}}
499: do_test auth-1.68 {
500: execsql {SELECT name FROM sqlite_temp_master}
501: } {t1}
502: do_test auth-1.69 {
503: proc auth {code arg1 arg2 arg3 arg4} {
504: if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
505: return SQLITE_DENY
506: }
507: return SQLITE_OK
508: }
509: catchsql {DROP TABLE t1}
510: } {1 {not authorized}}
511: do_test auth-1.70 {
512: execsql {SELECT name FROM sqlite_temp_master}
513: } {t1}
514: }
515:
516: do_test auth-1.71 {
517: proc auth {code arg1 arg2 arg3 arg4} {
518: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
519: return SQLITE_IGNORE
520: }
521: return SQLITE_OK
522: }
523: catchsql {DROP TABLE t2}
524: } {0 {}}
525: do_test auth-1.72 {
526: execsql {SELECT name FROM sqlite_master}
527: } {t2}
528: do_test auth-1.73 {
529: proc auth {code arg1 arg2 arg3 arg4} {
530: if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
531: return SQLITE_IGNORE
532: }
533: return SQLITE_OK
534: }
535: catchsql {DROP TABLE t2}
536: } {0 {}}
537: do_test auth-1.74 {
538: execsql {SELECT name FROM sqlite_master}
539: } {t2}
540:
541: ifcapable tempdb {
542: do_test auth-1.75 {
543: proc auth {code arg1 arg2 arg3 arg4} {
544: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
545: return SQLITE_IGNORE
546: }
547: return SQLITE_OK
548: }
549: catchsql {DROP TABLE t1}
550: } {0 {}}
551: do_test auth-1.76 {
552: execsql {SELECT name FROM sqlite_temp_master}
553: } {t1}
554: do_test auth-1.77 {
555: proc auth {code arg1 arg2 arg3 arg4} {
556: if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
557: return SQLITE_IGNORE
558: }
559: return SQLITE_OK
560: }
561: catchsql {DROP TABLE t1}
562: } {0 {}}
563: do_test auth-1.78 {
564: execsql {SELECT name FROM sqlite_temp_master}
565: } {t1}
566: }
567:
568: # Test cases auth-1.79 to auth-1.124 test creating and dropping views.
569: # Omit these if the library was compiled with views omitted.
570: ifcapable view {
571: do_test auth-1.79 {
572: proc auth {code arg1 arg2 arg3 arg4} {
573: if {$code=="SQLITE_CREATE_VIEW"} {
574: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
575: return SQLITE_DENY
576: }
577: return SQLITE_OK
578: }
579: catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
580: } {1 {not authorized}}
581: do_test auth-1.80 {
582: set ::authargs
583: } {v1 {} main {}}
584: do_test auth-1.81 {
585: execsql {SELECT name FROM sqlite_master}
586: } {t2}
587: do_test auth-1.82 {
588: proc auth {code arg1 arg2 arg3 arg4} {
589: if {$code=="SQLITE_CREATE_VIEW"} {
590: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
591: return SQLITE_IGNORE
592: }
593: return SQLITE_OK
594: }
595: catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
596: } {0 {}}
597: do_test auth-1.83 {
598: set ::authargs
599: } {v1 {} main {}}
600: do_test auth-1.84 {
601: execsql {SELECT name FROM sqlite_master}
602: } {t2}
603:
604: ifcapable tempdb {
605: do_test auth-1.85 {
606: proc auth {code arg1 arg2 arg3 arg4} {
607: if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
608: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
609: return SQLITE_DENY
610: }
611: return SQLITE_OK
612: }
613: catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
614: } {1 {not authorized}}
615: do_test auth-1.86 {
616: set ::authargs
617: } {v1 {} temp {}}
618: do_test auth-1.87 {
619: execsql {SELECT name FROM sqlite_temp_master}
620: } {t1}
621: do_test auth-1.88 {
622: proc auth {code arg1 arg2 arg3 arg4} {
623: if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
624: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
625: return SQLITE_IGNORE
626: }
627: return SQLITE_OK
628: }
629: catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
630: } {0 {}}
631: do_test auth-1.89 {
632: set ::authargs
633: } {v1 {} temp {}}
634: do_test auth-1.90 {
635: execsql {SELECT name FROM sqlite_temp_master}
636: } {t1}
637: }
638:
639: do_test auth-1.91 {
640: proc auth {code arg1 arg2 arg3 arg4} {
641: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
642: return SQLITE_DENY
643: }
644: return SQLITE_OK
645: }
646: catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
647: } {1 {not authorized}}
648: do_test auth-1.92 {
649: execsql {SELECT name FROM sqlite_master}
650: } {t2}
651: do_test auth-1.93 {
652: proc auth {code arg1 arg2 arg3 arg4} {
653: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
654: return SQLITE_IGNORE
655: }
656: return SQLITE_OK
657: }
658: catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
659: } {0 {}}
660: do_test auth-1.94 {
661: execsql {SELECT name FROM sqlite_master}
662: } {t2}
663:
664: ifcapable tempdb {
665: do_test auth-1.95 {
666: proc auth {code arg1 arg2 arg3 arg4} {
667: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
668: return SQLITE_DENY
669: }
670: return SQLITE_OK
671: }
672: catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
673: } {1 {not authorized}}
674: do_test auth-1.96 {
675: execsql {SELECT name FROM sqlite_temp_master}
676: } {t1}
677: do_test auth-1.97 {
678: proc auth {code arg1 arg2 arg3 arg4} {
679: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
680: return SQLITE_IGNORE
681: }
682: return SQLITE_OK
683: }
684: catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
685: } {0 {}}
686: do_test auth-1.98 {
687: execsql {SELECT name FROM sqlite_temp_master}
688: } {t1}
689: }
690:
691: do_test auth-1.99 {
692: proc auth {code arg1 arg2 arg3 arg4} {
693: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
694: return SQLITE_DENY
695: }
696: return SQLITE_OK
697: }
698: catchsql {
699: CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2;
700: DROP VIEW v2
701: }
702: } {1 {not authorized}}
703: do_test auth-1.100 {
704: execsql {SELECT name FROM sqlite_master}
705: } {t2 v2}
706: do_test auth-1.101 {
707: proc auth {code arg1 arg2 arg3 arg4} {
708: if {$code=="SQLITE_DROP_VIEW"} {
709: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
710: return SQLITE_DENY
711: }
712: return SQLITE_OK
713: }
714: catchsql {DROP VIEW v2}
715: } {1 {not authorized}}
716: do_test auth-1.102 {
717: set ::authargs
718: } {v2 {} main {}}
719: do_test auth-1.103 {
720: execsql {SELECT name FROM sqlite_master}
721: } {t2 v2}
722: do_test auth-1.104 {
723: proc auth {code arg1 arg2 arg3 arg4} {
724: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
725: return SQLITE_IGNORE
726: }
727: return SQLITE_OK
728: }
729: catchsql {DROP VIEW v2}
730: } {0 {}}
731: do_test auth-1.105 {
732: execsql {SELECT name FROM sqlite_master}
733: } {t2 v2}
734: do_test auth-1.106 {
735: proc auth {code arg1 arg2 arg3 arg4} {
736: if {$code=="SQLITE_DROP_VIEW"} {
737: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
738: return SQLITE_IGNORE
739: }
740: return SQLITE_OK
741: }
742: catchsql {DROP VIEW v2}
743: } {0 {}}
744: do_test auth-1.107 {
745: set ::authargs
746: } {v2 {} main {}}
747: do_test auth-1.108 {
748: execsql {SELECT name FROM sqlite_master}
749: } {t2 v2}
750: do_test auth-1.109 {
751: proc auth {code arg1 arg2 arg3 arg4} {
752: if {$code=="SQLITE_DROP_VIEW"} {
753: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
754: return SQLITE_OK
755: }
756: return SQLITE_OK
757: }
758: catchsql {DROP VIEW v2}
759: } {0 {}}
760: do_test auth-1.110 {
761: set ::authargs
762: } {v2 {} main {}}
763: do_test auth-1.111 {
764: execsql {SELECT name FROM sqlite_master}
765: } {t2}
766:
767:
768: ifcapable tempdb {
769: do_test auth-1.112 {
770: proc auth {code arg1 arg2 arg3 arg4} {
771: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
772: return SQLITE_DENY
773: }
774: return SQLITE_OK
775: }
776: catchsql {
777: CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1;
778: DROP VIEW v1
779: }
780: } {1 {not authorized}}
781: do_test auth-1.113 {
782: execsql {SELECT name FROM sqlite_temp_master}
783: } {t1 v1}
784: do_test auth-1.114 {
785: proc auth {code arg1 arg2 arg3 arg4} {
786: if {$code=="SQLITE_DROP_TEMP_VIEW"} {
787: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
788: return SQLITE_DENY
789: }
790: return SQLITE_OK
791: }
792: catchsql {DROP VIEW v1}
793: } {1 {not authorized}}
794: do_test auth-1.115 {
795: set ::authargs
796: } {v1 {} temp {}}
797: do_test auth-1.116 {
798: execsql {SELECT name FROM sqlite_temp_master}
799: } {t1 v1}
800: do_test auth-1.117 {
801: proc auth {code arg1 arg2 arg3 arg4} {
802: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
803: return SQLITE_IGNORE
804: }
805: return SQLITE_OK
806: }
807: catchsql {DROP VIEW v1}
808: } {0 {}}
809: do_test auth-1.118 {
810: execsql {SELECT name FROM sqlite_temp_master}
811: } {t1 v1}
812: do_test auth-1.119 {
813: proc auth {code arg1 arg2 arg3 arg4} {
814: if {$code=="SQLITE_DROP_TEMP_VIEW"} {
815: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
816: return SQLITE_IGNORE
817: }
818: return SQLITE_OK
819: }
820: catchsql {DROP VIEW v1}
821: } {0 {}}
822: do_test auth-1.120 {
823: set ::authargs
824: } {v1 {} temp {}}
825: do_test auth-1.121 {
826: execsql {SELECT name FROM sqlite_temp_master}
827: } {t1 v1}
828: do_test auth-1.122 {
829: proc auth {code arg1 arg2 arg3 arg4} {
830: if {$code=="SQLITE_DROP_TEMP_VIEW"} {
831: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
832: return SQLITE_OK
833: }
834: return SQLITE_OK
835: }
836: catchsql {DROP VIEW v1}
837: } {0 {}}
838: do_test auth-1.123 {
839: set ::authargs
840: } {v1 {} temp {}}
841: do_test auth-1.124 {
842: execsql {SELECT name FROM sqlite_temp_master}
843: } {t1}
844: }
845: } ;# ifcapable view
846:
847: # Test cases auth-1.125 to auth-1.176 test creating and dropping triggers.
848: # Omit these if the library was compiled with triggers omitted.
849: #
850: ifcapable trigger&&tempdb {
851: do_test auth-1.125 {
852: proc auth {code arg1 arg2 arg3 arg4} {
853: if {$code=="SQLITE_CREATE_TRIGGER"} {
854: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
855: return SQLITE_DENY
856: }
857: return SQLITE_OK
858: }
859: catchsql {
860: CREATE TRIGGER r2 DELETE on t2 BEGIN
861: SELECT NULL;
862: END;
863: }
864: } {1 {not authorized}}
865: do_test auth-1.126 {
866: set ::authargs
867: } {r2 t2 main {}}
868: do_test auth-1.127 {
869: execsql {SELECT name FROM sqlite_master}
870: } {t2}
871: do_test auth-1.128 {
872: proc auth {code arg1 arg2 arg3 arg4} {
873: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
874: return SQLITE_DENY
875: }
876: return SQLITE_OK
877: }
878: catchsql {
879: CREATE TRIGGER r2 DELETE on t2 BEGIN
880: SELECT NULL;
881: END;
882: }
883: } {1 {not authorized}}
884: do_test auth-1.129 {
885: execsql {SELECT name FROM sqlite_master}
886: } {t2}
887: do_test auth-1.130 {
888: proc auth {code arg1 arg2 arg3 arg4} {
889: if {$code=="SQLITE_CREATE_TRIGGER"} {
890: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
891: return SQLITE_IGNORE
892: }
893: return SQLITE_OK
894: }
895: catchsql {
896: CREATE TRIGGER r2 DELETE on t2 BEGIN
897: SELECT NULL;
898: END;
899: }
900: } {0 {}}
901: do_test auth-1.131 {
902: set ::authargs
903: } {r2 t2 main {}}
904: do_test auth-1.132 {
905: execsql {SELECT name FROM sqlite_master}
906: } {t2}
907: do_test auth-1.133 {
908: proc auth {code arg1 arg2 arg3 arg4} {
909: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
910: return SQLITE_IGNORE
911: }
912: return SQLITE_OK
913: }
914: catchsql {
915: CREATE TRIGGER r2 DELETE on t2 BEGIN
916: SELECT NULL;
917: END;
918: }
919: } {0 {}}
920: do_test auth-1.134 {
921: execsql {SELECT name FROM sqlite_master}
922: } {t2}
923: do_test auth-1.135 {
924: proc auth {code arg1 arg2 arg3 arg4} {
925: if {$code=="SQLITE_CREATE_TRIGGER"} {
926: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
927: return SQLITE_OK
928: }
929: return SQLITE_OK
930: }
931: catchsql {
932: CREATE TABLE tx(id);
933: CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN
934: INSERT INTO tx VALUES(NEW.rowid);
935: END;
936: }
937: } {0 {}}
938: do_test auth-1.136.1 {
939: set ::authargs
940: } {r2 t2 main {}}
941: do_test auth-1.136.2 {
942: execsql {
943: SELECT name FROM sqlite_master WHERE type='trigger'
944: }
945: } {r2}
946: do_test auth-1.136.3 {
947: proc auth {code arg1 arg2 arg3 arg4} {
948: lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
949: return SQLITE_OK
950: }
951: set ::authargs {}
952: execsql {
953: INSERT INTO t2 VALUES(1,2,3);
954: }
955: set ::authargs
956: } {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2}
957: do_test auth-1.136.4 {
958: execsql {
959: SELECT * FROM tx;
960: }
961: } {3}
962: do_test auth-1.137 {
963: execsql {SELECT name FROM sqlite_master}
964: } {t2 tx r2}
965: do_test auth-1.138 {
966: proc auth {code arg1 arg2 arg3 arg4} {
967: if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
968: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
969: return SQLITE_DENY
970: }
971: return SQLITE_OK
972: }
973: catchsql {
974: CREATE TRIGGER r1 DELETE on t1 BEGIN
975: SELECT NULL;
976: END;
977: }
978: } {1 {not authorized}}
979: do_test auth-1.139 {
980: set ::authargs
981: } {r1 t1 temp {}}
982: do_test auth-1.140 {
983: execsql {SELECT name FROM sqlite_temp_master}
984: } {t1}
985: do_test auth-1.141 {
986: proc auth {code arg1 arg2 arg3 arg4} {
987: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
988: return SQLITE_DENY
989: }
990: return SQLITE_OK
991: }
992: catchsql {
993: CREATE TRIGGER r1 DELETE on t1 BEGIN
994: SELECT NULL;
995: END;
996: }
997: } {1 {not authorized}}
998: do_test auth-1.142 {
999: execsql {SELECT name FROM sqlite_temp_master}
1000: } {t1}
1001: do_test auth-1.143 {
1002: proc auth {code arg1 arg2 arg3 arg4} {
1003: if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
1004: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1005: return SQLITE_IGNORE
1006: }
1007: return SQLITE_OK
1008: }
1009: catchsql {
1010: CREATE TRIGGER r1 DELETE on t1 BEGIN
1011: SELECT NULL;
1012: END;
1013: }
1014: } {0 {}}
1015: do_test auth-1.144 {
1016: set ::authargs
1017: } {r1 t1 temp {}}
1018: do_test auth-1.145 {
1019: execsql {SELECT name FROM sqlite_temp_master}
1020: } {t1}
1021: do_test auth-1.146 {
1022: proc auth {code arg1 arg2 arg3 arg4} {
1023: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1024: return SQLITE_IGNORE
1025: }
1026: return SQLITE_OK
1027: }
1028: catchsql {
1029: CREATE TRIGGER r1 DELETE on t1 BEGIN
1030: SELECT NULL;
1031: END;
1032: }
1033: } {0 {}}
1034: do_test auth-1.147 {
1035: execsql {SELECT name FROM sqlite_temp_master}
1036: } {t1}
1037: do_test auth-1.148 {
1038: proc auth {code arg1 arg2 arg3 arg4} {
1039: if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
1040: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1041: return SQLITE_OK
1042: }
1043: return SQLITE_OK
1044: }
1045: catchsql {
1046: CREATE TRIGGER r1 DELETE on t1 BEGIN
1047: SELECT NULL;
1048: END;
1049: }
1050: } {0 {}}
1051: do_test auth-1.149 {
1052: set ::authargs
1053: } {r1 t1 temp {}}
1054: do_test auth-1.150 {
1055: execsql {SELECT name FROM sqlite_temp_master}
1056: } {t1 r1}
1057:
1058: do_test auth-1.151 {
1059: proc auth {code arg1 arg2 arg3 arg4} {
1060: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1061: return SQLITE_DENY
1062: }
1063: return SQLITE_OK
1064: }
1065: catchsql {DROP TRIGGER r2}
1066: } {1 {not authorized}}
1067: do_test auth-1.152 {
1068: execsql {SELECT name FROM sqlite_master}
1069: } {t2 tx r2}
1070: do_test auth-1.153 {
1071: proc auth {code arg1 arg2 arg3 arg4} {
1072: if {$code=="SQLITE_DROP_TRIGGER"} {
1073: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1074: return SQLITE_DENY
1075: }
1076: return SQLITE_OK
1077: }
1078: catchsql {DROP TRIGGER r2}
1079: } {1 {not authorized}}
1080: do_test auth-1.154 {
1081: set ::authargs
1082: } {r2 t2 main {}}
1083: do_test auth-1.155 {
1084: execsql {SELECT name FROM sqlite_master}
1085: } {t2 tx r2}
1086: do_test auth-1.156 {
1087: proc auth {code arg1 arg2 arg3 arg4} {
1088: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1089: return SQLITE_IGNORE
1090: }
1091: return SQLITE_OK
1092: }
1093: catchsql {DROP TRIGGER r2}
1094: } {0 {}}
1095: do_test auth-1.157 {
1096: execsql {SELECT name FROM sqlite_master}
1097: } {t2 tx r2}
1098: do_test auth-1.158 {
1099: proc auth {code arg1 arg2 arg3 arg4} {
1100: if {$code=="SQLITE_DROP_TRIGGER"} {
1101: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1102: return SQLITE_IGNORE
1103: }
1104: return SQLITE_OK
1105: }
1106: catchsql {DROP TRIGGER r2}
1107: } {0 {}}
1108: do_test auth-1.159 {
1109: set ::authargs
1110: } {r2 t2 main {}}
1111: do_test auth-1.160 {
1112: execsql {SELECT name FROM sqlite_master}
1113: } {t2 tx r2}
1114: do_test auth-1.161 {
1115: proc auth {code arg1 arg2 arg3 arg4} {
1116: if {$code=="SQLITE_DROP_TRIGGER"} {
1117: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1118: return SQLITE_OK
1119: }
1120: return SQLITE_OK
1121: }
1122: catchsql {DROP TRIGGER r2}
1123: } {0 {}}
1124: do_test auth-1.162 {
1125: set ::authargs
1126: } {r2 t2 main {}}
1127: do_test auth-1.163 {
1128: execsql {
1129: DROP TABLE tx;
1130: DELETE FROM t2 WHERE a=1 AND b=2 AND c=3;
1131: SELECT name FROM sqlite_master;
1132: }
1133: } {t2}
1134:
1135: do_test auth-1.164 {
1136: proc auth {code arg1 arg2 arg3 arg4} {
1137: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1138: return SQLITE_DENY
1139: }
1140: return SQLITE_OK
1141: }
1142: catchsql {DROP TRIGGER r1}
1143: } {1 {not authorized}}
1144: do_test auth-1.165 {
1145: execsql {SELECT name FROM sqlite_temp_master}
1146: } {t1 r1}
1147: do_test auth-1.166 {
1148: proc auth {code arg1 arg2 arg3 arg4} {
1149: if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1150: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1151: return SQLITE_DENY
1152: }
1153: return SQLITE_OK
1154: }
1155: catchsql {DROP TRIGGER r1}
1156: } {1 {not authorized}}
1157: do_test auth-1.167 {
1158: set ::authargs
1159: } {r1 t1 temp {}}
1160: do_test auth-1.168 {
1161: execsql {SELECT name FROM sqlite_temp_master}
1162: } {t1 r1}
1163: do_test auth-1.169 {
1164: proc auth {code arg1 arg2 arg3 arg4} {
1165: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1166: return SQLITE_IGNORE
1167: }
1168: return SQLITE_OK
1169: }
1170: catchsql {DROP TRIGGER r1}
1171: } {0 {}}
1172: do_test auth-1.170 {
1173: execsql {SELECT name FROM sqlite_temp_master}
1174: } {t1 r1}
1175: do_test auth-1.171 {
1176: proc auth {code arg1 arg2 arg3 arg4} {
1177: if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1178: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1179: return SQLITE_IGNORE
1180: }
1181: return SQLITE_OK
1182: }
1183: catchsql {DROP TRIGGER r1}
1184: } {0 {}}
1185: do_test auth-1.172 {
1186: set ::authargs
1187: } {r1 t1 temp {}}
1188: do_test auth-1.173 {
1189: execsql {SELECT name FROM sqlite_temp_master}
1190: } {t1 r1}
1191: do_test auth-1.174 {
1192: proc auth {code arg1 arg2 arg3 arg4} {
1193: if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1194: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1195: return SQLITE_OK
1196: }
1197: return SQLITE_OK
1198: }
1199: catchsql {DROP TRIGGER r1}
1200: } {0 {}}
1201: do_test auth-1.175 {
1202: set ::authargs
1203: } {r1 t1 temp {}}
1204: do_test auth-1.176 {
1205: execsql {SELECT name FROM sqlite_temp_master}
1206: } {t1}
1207: } ;# ifcapable trigger
1208:
1209: do_test auth-1.177 {
1210: proc auth {code arg1 arg2 arg3 arg4} {
1211: if {$code=="SQLITE_CREATE_INDEX"} {
1212: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1213: return SQLITE_DENY
1214: }
1215: return SQLITE_OK
1216: }
1217: catchsql {CREATE INDEX i2 ON t2(a)}
1218: } {1 {not authorized}}
1219: do_test auth-1.178 {
1220: set ::authargs
1221: } {i2 t2 main {}}
1222: do_test auth-1.179 {
1223: execsql {SELECT name FROM sqlite_master}
1224: } {t2}
1225: do_test auth-1.180 {
1226: proc auth {code arg1 arg2 arg3 arg4} {
1227: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1228: return SQLITE_DENY
1229: }
1230: return SQLITE_OK
1231: }
1232: catchsql {CREATE INDEX i2 ON t2(a)}
1233: } {1 {not authorized}}
1234: do_test auth-1.181 {
1235: execsql {SELECT name FROM sqlite_master}
1236: } {t2}
1237: do_test auth-1.182 {
1238: proc auth {code arg1 arg2 arg3 arg4} {
1239: if {$code=="SQLITE_CREATE_INDEX"} {
1240: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1241: return SQLITE_IGNORE
1242: }
1243: return SQLITE_OK
1244: }
1245: catchsql {CREATE INDEX i2 ON t2(b)}
1246: } {0 {}}
1247: do_test auth-1.183 {
1248: set ::authargs
1249: } {i2 t2 main {}}
1250: do_test auth-1.184 {
1251: execsql {SELECT name FROM sqlite_master}
1252: } {t2}
1253: do_test auth-1.185 {
1254: proc auth {code arg1 arg2 arg3 arg4} {
1255: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1256: return SQLITE_IGNORE
1257: }
1258: return SQLITE_OK
1259: }
1260: catchsql {CREATE INDEX i2 ON t2(b)}
1261: } {0 {}}
1262: do_test auth-1.186 {
1263: execsql {SELECT name FROM sqlite_master}
1264: } {t2}
1265: do_test auth-1.187 {
1266: proc auth {code arg1 arg2 arg3 arg4} {
1267: if {$code=="SQLITE_CREATE_INDEX"} {
1268: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1269: return SQLITE_OK
1270: }
1271: return SQLITE_OK
1272: }
1273: catchsql {CREATE INDEX i2 ON t2(a)}
1274: } {0 {}}
1275: do_test auth-1.188 {
1276: set ::authargs
1277: } {i2 t2 main {}}
1278: do_test auth-1.189 {
1279: execsql {SELECT name FROM sqlite_master}
1280: } {t2 i2}
1281:
1282: ifcapable tempdb {
1283: do_test auth-1.190 {
1284: proc auth {code arg1 arg2 arg3 arg4} {
1285: if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1286: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1287: return SQLITE_DENY
1288: }
1289: return SQLITE_OK
1290: }
1291: catchsql {CREATE INDEX i1 ON t1(a)}
1292: } {1 {not authorized}}
1293: do_test auth-1.191 {
1294: set ::authargs
1295: } {i1 t1 temp {}}
1296: do_test auth-1.192 {
1297: execsql {SELECT name FROM sqlite_temp_master}
1298: } {t1}
1299: do_test auth-1.193 {
1300: proc auth {code arg1 arg2 arg3 arg4} {
1301: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1302: return SQLITE_DENY
1303: }
1304: return SQLITE_OK
1305: }
1306: catchsql {CREATE INDEX i1 ON t1(b)}
1307: } {1 {not authorized}}
1308: do_test auth-1.194 {
1309: execsql {SELECT name FROM sqlite_temp_master}
1310: } {t1}
1311: do_test auth-1.195 {
1312: proc auth {code arg1 arg2 arg3 arg4} {
1313: if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1314: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1315: return SQLITE_IGNORE
1316: }
1317: return SQLITE_OK
1318: }
1319: catchsql {CREATE INDEX i1 ON t1(b)}
1320: } {0 {}}
1321: do_test auth-1.196 {
1322: set ::authargs
1323: } {i1 t1 temp {}}
1324: do_test auth-1.197 {
1325: execsql {SELECT name FROM sqlite_temp_master}
1326: } {t1}
1327: do_test auth-1.198 {
1328: proc auth {code arg1 arg2 arg3 arg4} {
1329: if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1330: return SQLITE_IGNORE
1331: }
1332: return SQLITE_OK
1333: }
1334: catchsql {CREATE INDEX i1 ON t1(c)}
1335: } {0 {}}
1336: do_test auth-1.199 {
1337: execsql {SELECT name FROM sqlite_temp_master}
1338: } {t1}
1339: do_test auth-1.200 {
1340: proc auth {code arg1 arg2 arg3 arg4} {
1341: if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1342: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1343: return SQLITE_OK
1344: }
1345: return SQLITE_OK
1346: }
1347: catchsql {CREATE INDEX i1 ON t1(a)}
1348: } {0 {}}
1349: do_test auth-1.201 {
1350: set ::authargs
1351: } {i1 t1 temp {}}
1352: do_test auth-1.202 {
1353: execsql {SELECT name FROM sqlite_temp_master}
1354: } {t1 i1}
1355: }
1356:
1357: do_test auth-1.203 {
1358: proc auth {code arg1 arg2 arg3 arg4} {
1359: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1360: return SQLITE_DENY
1361: }
1362: return SQLITE_OK
1363: }
1364: catchsql {DROP INDEX i2}
1365: } {1 {not authorized}}
1366: do_test auth-1.204 {
1367: execsql {SELECT name FROM sqlite_master}
1368: } {t2 i2}
1369: do_test auth-1.205 {
1370: proc auth {code arg1 arg2 arg3 arg4} {
1371: if {$code=="SQLITE_DROP_INDEX"} {
1372: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1373: return SQLITE_DENY
1374: }
1375: return SQLITE_OK
1376: }
1377: catchsql {DROP INDEX i2}
1378: } {1 {not authorized}}
1379: do_test auth-1.206 {
1380: set ::authargs
1381: } {i2 t2 main {}}
1382: do_test auth-1.207 {
1383: execsql {SELECT name FROM sqlite_master}
1384: } {t2 i2}
1385: do_test auth-1.208 {
1386: proc auth {code arg1 arg2 arg3 arg4} {
1387: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1388: return SQLITE_IGNORE
1389: }
1390: return SQLITE_OK
1391: }
1392: catchsql {DROP INDEX i2}
1393: } {0 {}}
1394: do_test auth-1.209 {
1395: execsql {SELECT name FROM sqlite_master}
1396: } {t2 i2}
1397: do_test auth-1.210 {
1398: proc auth {code arg1 arg2 arg3 arg4} {
1399: if {$code=="SQLITE_DROP_INDEX"} {
1400: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1401: return SQLITE_IGNORE
1402: }
1403: return SQLITE_OK
1404: }
1405: catchsql {DROP INDEX i2}
1406: } {0 {}}
1407: do_test auth-1.211 {
1408: set ::authargs
1409: } {i2 t2 main {}}
1410: do_test auth-1.212 {
1411: execsql {SELECT name FROM sqlite_master}
1412: } {t2 i2}
1413: do_test auth-1.213 {
1414: proc auth {code arg1 arg2 arg3 arg4} {
1415: if {$code=="SQLITE_DROP_INDEX"} {
1416: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1417: return SQLITE_OK
1418: }
1419: return SQLITE_OK
1420: }
1421: catchsql {DROP INDEX i2}
1422: } {0 {}}
1423: do_test auth-1.214 {
1424: set ::authargs
1425: } {i2 t2 main {}}
1426: do_test auth-1.215 {
1427: execsql {SELECT name FROM sqlite_master}
1428: } {t2}
1429:
1430: ifcapable tempdb {
1431: do_test auth-1.216 {
1432: proc auth {code arg1 arg2 arg3 arg4} {
1433: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1434: return SQLITE_DENY
1435: }
1436: return SQLITE_OK
1437: }
1438: catchsql {DROP INDEX i1}
1439: } {1 {not authorized}}
1440: do_test auth-1.217 {
1441: execsql {SELECT name FROM sqlite_temp_master}
1442: } {t1 i1}
1443: do_test auth-1.218 {
1444: proc auth {code arg1 arg2 arg3 arg4} {
1445: if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1446: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1447: return SQLITE_DENY
1448: }
1449: return SQLITE_OK
1450: }
1451: catchsql {DROP INDEX i1}
1452: } {1 {not authorized}}
1453: do_test auth-1.219 {
1454: set ::authargs
1455: } {i1 t1 temp {}}
1456: do_test auth-1.220 {
1457: execsql {SELECT name FROM sqlite_temp_master}
1458: } {t1 i1}
1459: do_test auth-1.221 {
1460: proc auth {code arg1 arg2 arg3 arg4} {
1461: if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1462: return SQLITE_IGNORE
1463: }
1464: return SQLITE_OK
1465: }
1466: catchsql {DROP INDEX i1}
1467: } {0 {}}
1468: do_test auth-1.222 {
1469: execsql {SELECT name FROM sqlite_temp_master}
1470: } {t1 i1}
1471: do_test auth-1.223 {
1472: proc auth {code arg1 arg2 arg3 arg4} {
1473: if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1474: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1475: return SQLITE_IGNORE
1476: }
1477: return SQLITE_OK
1478: }
1479: catchsql {DROP INDEX i1}
1480: } {0 {}}
1481: do_test auth-1.224 {
1482: set ::authargs
1483: } {i1 t1 temp {}}
1484: do_test auth-1.225 {
1485: execsql {SELECT name FROM sqlite_temp_master}
1486: } {t1 i1}
1487: do_test auth-1.226 {
1488: proc auth {code arg1 arg2 arg3 arg4} {
1489: if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1490: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1491: return SQLITE_OK
1492: }
1493: return SQLITE_OK
1494: }
1495: catchsql {DROP INDEX i1}
1496: } {0 {}}
1497: do_test auth-1.227 {
1498: set ::authargs
1499: } {i1 t1 temp {}}
1500: do_test auth-1.228 {
1501: execsql {SELECT name FROM sqlite_temp_master}
1502: } {t1}
1503: }
1504:
1505: do_test auth-1.229 {
1506: proc auth {code arg1 arg2 arg3 arg4} {
1507: if {$code=="SQLITE_PRAGMA"} {
1508: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1509: return SQLITE_DENY
1510: }
1511: return SQLITE_OK
1512: }
1513: catchsql {PRAGMA full_column_names=on}
1514: } {1 {not authorized}}
1515: do_test auth-1.230 {
1516: set ::authargs
1517: } {full_column_names on {} {}}
1518: do_test auth-1.231 {
1519: execsql2 {SELECT a FROM t2}
1520: } {a 11 a 7}
1521: do_test auth-1.232 {
1522: proc auth {code arg1 arg2 arg3 arg4} {
1523: if {$code=="SQLITE_PRAGMA"} {
1524: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1525: return SQLITE_IGNORE
1526: }
1527: return SQLITE_OK
1528: }
1529: catchsql {PRAGMA full_column_names=on}
1530: } {0 {}}
1531: do_test auth-1.233 {
1532: set ::authargs
1533: } {full_column_names on {} {}}
1534: do_test auth-1.234 {
1535: execsql2 {SELECT a FROM t2}
1536: } {a 11 a 7}
1537: do_test auth-1.235 {
1538: proc auth {code arg1 arg2 arg3 arg4} {
1539: if {$code=="SQLITE_PRAGMA"} {
1540: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1541: return SQLITE_OK
1542: }
1543: return SQLITE_OK
1544: }
1545: catchsql {PRAGMA full_column_names=on}
1546: } {0 {}}
1547: do_test auth-1.236 {
1548: execsql2 {SELECT a FROM t2}
1549: } {t2.a 11 t2.a 7}
1550: do_test auth-1.237 {
1551: proc auth {code arg1 arg2 arg3 arg4} {
1552: if {$code=="SQLITE_PRAGMA"} {
1553: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1554: return SQLITE_OK
1555: }
1556: return SQLITE_OK
1557: }
1558: catchsql {PRAGMA full_column_names=OFF}
1559: } {0 {}}
1560: do_test auth-1.238 {
1561: set ::authargs
1562: } {full_column_names OFF {} {}}
1563: do_test auth-1.239 {
1564: execsql2 {SELECT a FROM t2}
1565: } {a 11 a 7}
1566:
1567: do_test auth-1.240 {
1568: proc auth {code arg1 arg2 arg3 arg4} {
1569: if {$code=="SQLITE_TRANSACTION"} {
1570: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1571: return SQLITE_DENY
1572: }
1573: return SQLITE_OK
1574: }
1575: catchsql {BEGIN}
1576: } {1 {not authorized}}
1577: do_test auth-1.241 {
1578: set ::authargs
1579: } {BEGIN {} {} {}}
1580: do_test auth-1.242 {
1581: proc auth {code arg1 arg2 arg3 arg4} {
1582: if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} {
1583: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1584: return SQLITE_DENY
1585: }
1586: return SQLITE_OK
1587: }
1588: catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT}
1589: } {1 {not authorized}}
1590: do_test auth-1.243 {
1591: set ::authargs
1592: } {COMMIT {} {} {}}
1593: do_test auth-1.244 {
1594: execsql {SELECT * FROM t2}
1595: } {11 2 33 7 8 9 44 55 66}
1596: do_test auth-1.245 {
1597: catchsql {ROLLBACK}
1598: } {1 {not authorized}}
1599: do_test auth-1.246 {
1600: set ::authargs
1601: } {ROLLBACK {} {} {}}
1602: do_test auth-1.247 {
1603: catchsql {END TRANSACTION}
1604: } {1 {not authorized}}
1605: do_test auth-1.248 {
1606: set ::authargs
1607: } {COMMIT {} {} {}}
1608: do_test auth-1.249 {
1609: db authorizer {}
1610: catchsql {ROLLBACK}
1611: } {0 {}}
1612: do_test auth-1.250 {
1613: execsql {SELECT * FROM t2}
1614: } {11 2 33 7 8 9}
1615:
1616: # ticket #340 - authorization for ATTACH and DETACH.
1617: #
1618: ifcapable attach {
1619: do_test auth-1.251 {
1620: db authorizer ::auth
1621: proc auth {code arg1 arg2 arg3 arg4} {
1622: if {$code=="SQLITE_ATTACH"} {
1623: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1624: }
1625: return SQLITE_OK
1626: }
1627: catchsql {
1628: ATTACH DATABASE ':memory:' AS test1
1629: }
1630: } {0 {}}
1631: do_test auth-1.252a {
1632: set ::authargs
1633: } {:memory: {} {} {}}
1634: do_test auth-1.252b {
1635: db eval {DETACH test1}
1636: set ::attachfilename :memory:
1637: db eval {ATTACH $::attachfilename AS test1}
1638: set ::authargs
1639: } {{} {} {} {}}
1640: do_test auth-1.252c {
1641: db eval {DETACH test1}
1642: db eval {ATTACH ':mem' || 'ory:' AS test1}
1643: set ::authargs
1644: } {{} {} {} {}}
1645: do_test auth-1.253 {
1646: catchsql {DETACH DATABASE test1}
1647: proc auth {code arg1 arg2 arg3 arg4} {
1648: if {$code=="SQLITE_ATTACH"} {
1649: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1650: return SQLITE_DENY
1651: }
1652: return SQLITE_OK
1653: }
1654: catchsql {
1655: ATTACH DATABASE ':memory:' AS test1;
1656: }
1657: } {1 {not authorized}}
1658: do_test auth-1.254 {
1659: lindex [execsql {PRAGMA database_list}] 7
1660: } {}
1661: do_test auth-1.255 {
1662: catchsql {DETACH DATABASE test1}
1663: proc auth {code arg1 arg2 arg3 arg4} {
1664: if {$code=="SQLITE_ATTACH"} {
1665: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1666: return SQLITE_IGNORE
1667: }
1668: return SQLITE_OK
1669: }
1670: catchsql {
1671: ATTACH DATABASE ':memory:' AS test1;
1672: }
1673: } {0 {}}
1674: do_test auth-1.256 {
1675: lindex [execsql {PRAGMA database_list}] 7
1676: } {}
1677: do_test auth-1.257 {
1678: proc auth {code arg1 arg2 arg3 arg4} {
1679: if {$code=="SQLITE_DETACH"} {
1680: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1681: return SQLITE_OK
1682: }
1683: return SQLITE_OK
1684: }
1685: execsql {ATTACH DATABASE ':memory:' AS test1}
1686: catchsql {
1687: DETACH DATABASE test1;
1688: }
1689: } {0 {}}
1690: do_test auth-1.258 {
1691: lindex [execsql {PRAGMA database_list}] 7
1692: } {}
1693: do_test auth-1.259 {
1694: execsql {ATTACH DATABASE ':memory:' AS test1}
1695: proc auth {code arg1 arg2 arg3 arg4} {
1696: if {$code=="SQLITE_DETACH"} {
1697: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1698: return SQLITE_IGNORE
1699: }
1700: return SQLITE_OK
1701: }
1702: catchsql {
1703: DETACH DATABASE test1;
1704: }
1705: } {0 {}}
1706: ifcapable tempdb {
1707: ifcapable schema_pragmas {
1708: do_test auth-1.260 {
1709: lindex [execsql {PRAGMA database_list}] 7
1710: } {test1}
1711: } ;# ifcapable schema_pragmas
1712: do_test auth-1.261 {
1713: proc auth {code arg1 arg2 arg3 arg4} {
1714: if {$code=="SQLITE_DETACH"} {
1715: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1716: return SQLITE_DENY
1717: }
1718: return SQLITE_OK
1719: }
1720: catchsql {
1721: DETACH DATABASE test1;
1722: }
1723: } {1 {not authorized}}
1724: ifcapable schema_pragmas {
1725: do_test auth-1.262 {
1726: lindex [execsql {PRAGMA database_list}] 7
1727: } {test1}
1728: } ;# ifcapable schema_pragmas
1729: db authorizer {}
1730: execsql {DETACH DATABASE test1}
1731: db authorizer ::auth
1732:
1733: # Authorization for ALTER TABLE. These tests are omitted if the library
1734: # was built without ALTER TABLE support.
1735: ifcapable altertable {
1736:
1737: do_test auth-1.263 {
1738: proc auth {code arg1 arg2 arg3 arg4} {
1739: if {$code=="SQLITE_ALTER_TABLE"} {
1740: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1741: return SQLITE_OK
1742: }
1743: return SQLITE_OK
1744: }
1745: catchsql {
1746: ALTER TABLE t1 RENAME TO t1x
1747: }
1748: } {0 {}}
1749: do_test auth-1.264 {
1750: execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
1751: } {t1x}
1752: do_test auth-1.265 {
1753: set authargs
1754: } {temp t1 {} {}}
1755: do_test auth-1.266 {
1756: proc auth {code arg1 arg2 arg3 arg4} {
1757: if {$code=="SQLITE_ALTER_TABLE"} {
1758: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1759: return SQLITE_IGNORE
1760: }
1761: return SQLITE_OK
1762: }
1763: catchsql {
1764: ALTER TABLE t1x RENAME TO t1
1765: }
1766: } {0 {}}
1767: do_test auth-1.267 {
1768: execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
1769: } {t1x}
1770: do_test auth-1.268 {
1771: set authargs
1772: } {temp t1x {} {}}
1773: do_test auth-1.269 {
1774: proc auth {code arg1 arg2 arg3 arg4} {
1775: if {$code=="SQLITE_ALTER_TABLE"} {
1776: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1777: return SQLITE_DENY
1778: }
1779: return SQLITE_OK
1780: }
1781: catchsql {
1782: ALTER TABLE t1x RENAME TO t1
1783: }
1784: } {1 {not authorized}}
1785: do_test auth-1.270 {
1786: execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
1787: } {t1x}
1788:
1789: do_test auth-1.271 {
1790: set authargs
1791: } {temp t1x {} {}}
1792: } ;# ifcapable altertable
1793:
1794: } else {
1795: db authorizer {}
1796: db eval {
1797: DETACH DATABASE test1;
1798: }
1799: }
1800: }
1801:
1802: ifcapable altertable {
1803: db authorizer {}
1804: catchsql {ALTER TABLE t1x RENAME TO t1}
1805: db authorizer ::auth
1806: do_test auth-1.272 {
1807: proc auth {code arg1 arg2 arg3 arg4} {
1808: if {$code=="SQLITE_ALTER_TABLE"} {
1809: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1810: return SQLITE_OK
1811: }
1812: return SQLITE_OK
1813: }
1814: catchsql {
1815: ALTER TABLE t2 RENAME TO t2x
1816: }
1817: } {0 {}}
1818: do_test auth-1.273 {
1819: execsql {SELECT name FROM sqlite_master WHERE type='table'}
1820: } {t2x}
1821: do_test auth-1.274 {
1822: set authargs
1823: } {main t2 {} {}}
1824: do_test auth-1.275 {
1825: proc auth {code arg1 arg2 arg3 arg4} {
1826: if {$code=="SQLITE_ALTER_TABLE"} {
1827: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1828: return SQLITE_IGNORE
1829: }
1830: return SQLITE_OK
1831: }
1832: catchsql {
1833: ALTER TABLE t2x RENAME TO t2
1834: }
1835: } {0 {}}
1836: do_test auth-1.276 {
1837: execsql {SELECT name FROM sqlite_master WHERE type='table'}
1838: } {t2x}
1839: do_test auth-1.277 {
1840: set authargs
1841: } {main t2x {} {}}
1842: do_test auth-1.278 {
1843: proc auth {code arg1 arg2 arg3 arg4} {
1844: if {$code=="SQLITE_ALTER_TABLE"} {
1845: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1846: return SQLITE_DENY
1847: }
1848: return SQLITE_OK
1849: }
1850: catchsql {
1851: ALTER TABLE t2x RENAME TO t2
1852: }
1853: } {1 {not authorized}}
1854: do_test auth-1.279 {
1855: execsql {SELECT name FROM sqlite_master WHERE type='table'}
1856: } {t2x}
1857: do_test auth-1.280 {
1858: set authargs
1859: } {main t2x {} {}}
1860: db authorizer {}
1861: catchsql {ALTER TABLE t2x RENAME TO t2}
1862:
1863: } ;# ifcapable altertable
1864:
1865: # Test the authorization callbacks for the REINDEX command.
1866: ifcapable reindex {
1867:
1868: proc auth {code args} {
1869: if {$code=="SQLITE_REINDEX"} {
1870: set ::authargs [concat $::authargs $args]
1871: }
1872: return SQLITE_OK
1873: }
1874: db authorizer auth
1875: do_test auth-1.281 {
1876: execsql {
1877: CREATE TABLE t3(a PRIMARY KEY, b, c);
1878: CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
1879: CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
1880: }
1881: } {}
1882: do_test auth-1.282 {
1883: set ::authargs {}
1884: execsql {
1885: REINDEX t3_idx1;
1886: }
1887: set ::authargs
1888: } {t3_idx1 {} main {}}
1889: do_test auth-1.283 {
1890: set ::authargs {}
1891: execsql {
1892: REINDEX BINARY;
1893: }
1894: set ::authargs
1895: } {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
1896: do_test auth-1.284 {
1897: set ::authargs {}
1898: execsql {
1899: REINDEX NOCASE;
1900: }
1901: set ::authargs
1902: } {t3_idx2 {} main {}}
1903: do_test auth-1.285 {
1904: set ::authargs {}
1905: execsql {
1906: REINDEX t3;
1907: }
1908: set ::authargs
1909: } {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
1910: do_test auth-1.286 {
1911: execsql {
1912: DROP TABLE t3;
1913: }
1914: } {}
1915: ifcapable tempdb {
1916: do_test auth-1.287 {
1917: execsql {
1918: CREATE TEMP TABLE t3(a PRIMARY KEY, b, c);
1919: CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
1920: CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
1921: }
1922: } {}
1923: do_test auth-1.288 {
1924: set ::authargs {}
1925: execsql {
1926: REINDEX temp.t3_idx1;
1927: }
1928: set ::authargs
1929: } {t3_idx1 {} temp {}}
1930: do_test auth-1.289 {
1931: set ::authargs {}
1932: execsql {
1933: REINDEX BINARY;
1934: }
1935: set ::authargs
1936: } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
1937: do_test auth-1.290 {
1938: set ::authargs {}
1939: execsql {
1940: REINDEX NOCASE;
1941: }
1942: set ::authargs
1943: } {t3_idx2 {} temp {}}
1944: do_test auth-1.291 {
1945: set ::authargs {}
1946: execsql {
1947: REINDEX temp.t3;
1948: }
1949: set ::authargs
1950: } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
1951: proc auth {code args} {
1952: if {$code=="SQLITE_REINDEX"} {
1953: set ::authargs [concat $::authargs $args]
1954: return SQLITE_DENY
1955: }
1956: return SQLITE_OK
1957: }
1958: do_test auth-1.292 {
1959: set ::authargs {}
1960: catchsql {
1961: REINDEX temp.t3;
1962: }
1963: } {1 {not authorized}}
1964: do_test auth-1.293 {
1965: execsql {
1966: DROP TABLE t3;
1967: }
1968: } {}
1969: }
1970:
1971: } ;# ifcapable reindex
1972:
1973: ifcapable analyze {
1974: proc auth {code args} {
1975: if {$code=="SQLITE_ANALYZE"} {
1976: set ::authargs [concat $::authargs $args]
1977: }
1978: return SQLITE_OK
1979: }
1980: do_test auth-1.294 {
1981: set ::authargs {}
1982: execsql {
1983: CREATE TABLE t4(a,b,c);
1984: CREATE INDEX t4i1 ON t4(a);
1985: CREATE INDEX t4i2 ON t4(b,a,c);
1986: INSERT INTO t4 VALUES(1,2,3);
1987: ANALYZE;
1988: }
1989: set ::authargs
1990: } {t4 {} main {} t2 {} main {}}
1991: do_test auth-1.295 {
1992: execsql {
1993: SELECT count(*) FROM sqlite_stat1;
1994: }
1995: } 3
1996: proc auth {code args} {
1997: if {$code=="SQLITE_ANALYZE"} {
1998: set ::authargs [concat $::authargs $args]
1999: return SQLITE_DENY
2000: }
2001: return SQLITE_OK
2002: }
2003: do_test auth-1.296 {
2004: set ::authargs {}
2005: catchsql {
2006: ANALYZE;
2007: }
2008: } {1 {not authorized}}
2009: do_test auth-1.297 {
2010: execsql {
2011: SELECT count(*) FROM sqlite_stat1;
2012: }
2013: } 3
2014: } ;# ifcapable analyze
2015:
2016:
2017: # Authorization for ALTER TABLE ADD COLUMN.
2018: # These tests are omitted if the library
2019: # was built without ALTER TABLE support.
2020: ifcapable {altertable} {
2021: do_test auth-1.300 {
2022: execsql {CREATE TABLE t5(x)}
2023: proc auth {code arg1 arg2 arg3 arg4} {
2024: if {$code=="SQLITE_ALTER_TABLE"} {
2025: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
2026: return SQLITE_OK
2027: }
2028: return SQLITE_OK
2029: }
2030: catchsql {
2031: ALTER TABLE t5 ADD COLUMN new_col_1;
2032: }
2033: } {0 {}}
2034: do_test auth-1.301 {
2035: set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
2036: regexp new_col_1 $x
2037: } {1}
2038: do_test auth-1.302 {
2039: set authargs
2040: } {main t5 {} {}}
2041: do_test auth-1.303 {
2042: proc auth {code arg1 arg2 arg3 arg4} {
2043: if {$code=="SQLITE_ALTER_TABLE"} {
2044: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
2045: return SQLITE_IGNORE
2046: }
2047: return SQLITE_OK
2048: }
2049: catchsql {
2050: ALTER TABLE t5 ADD COLUMN new_col_2;
2051: }
2052: } {0 {}}
2053: do_test auth-1.304 {
2054: set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
2055: regexp new_col_2 $x
2056: } {0}
2057: do_test auth-1.305 {
2058: set authargs
2059: } {main t5 {} {}}
2060: do_test auth-1.306 {
2061: proc auth {code arg1 arg2 arg3 arg4} {
2062: if {$code=="SQLITE_ALTER_TABLE"} {
2063: set ::authargs [list $arg1 $arg2 $arg3 $arg4]
2064: return SQLITE_DENY
2065: }
2066: return SQLITE_OK
2067: }
2068: catchsql {
2069: ALTER TABLE t5 ADD COLUMN new_col_3
2070: }
2071: } {1 {not authorized}}
2072: do_test auth-1.307 {
2073: set x [execsql {SELECT sql FROM sqlite_temp_master WHERE type='t5'}]
2074: regexp new_col_3 $x
2075: } {0}
2076:
2077: do_test auth-1.308 {
2078: set authargs
2079: } {main t5 {} {}}
2080: execsql {DROP TABLE t5}
2081: } ;# ifcapable altertable
2082:
2083: do_test auth-2.1 {
2084: proc auth {code arg1 arg2 arg3 arg4} {
2085: if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
2086: return SQLITE_DENY
2087: }
2088: return SQLITE_OK
2089: }
2090: db authorizer ::auth
2091: execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)}
2092: catchsql {SELECT * FROM t3}
2093: } {1 {access to t3.x is prohibited}}
2094: do_test auth-2.1 {
2095: catchsql {SELECT y,z FROM t3}
2096: } {0 {}}
2097: do_test auth-2.2 {
2098: catchsql {SELECT ROWID,y,z FROM t3}
2099: } {1 {access to t3.x is prohibited}}
2100: do_test auth-2.3 {
2101: catchsql {SELECT OID,y,z FROM t3}
2102: } {1 {access to t3.x is prohibited}}
2103: do_test auth-2.4 {
2104: proc auth {code arg1 arg2 arg3 arg4} {
2105: if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
2106: return SQLITE_IGNORE
2107: }
2108: return SQLITE_OK
2109: }
2110: execsql {INSERT INTO t3 VALUES(44,55,66)}
2111: catchsql {SELECT * FROM t3}
2112: } {0 {{} 55 66}}
2113: do_test auth-2.5 {
2114: catchsql {SELECT rowid,y,z FROM t3}
2115: } {0 {{} 55 66}}
2116: do_test auth-2.6 {
2117: proc auth {code arg1 arg2 arg3 arg4} {
2118: if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} {
2119: return SQLITE_IGNORE
2120: }
2121: return SQLITE_OK
2122: }
2123: catchsql {SELECT * FROM t3}
2124: } {0 {44 55 66}}
2125: do_test auth-2.7 {
2126: catchsql {SELECT ROWID,y,z FROM t3}
2127: } {0 {44 55 66}}
2128: do_test auth-2.8 {
2129: proc auth {code arg1 arg2 arg3 arg4} {
2130: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
2131: return SQLITE_IGNORE
2132: }
2133: return SQLITE_OK
2134: }
2135: catchsql {SELECT ROWID,b,c FROM t2}
2136: } {0 {{} 2 33 {} 8 9}}
2137: do_test auth-2.9.1 {
2138: # We have to flush the cache here in case the Tcl interface tries to
2139: # reuse a statement compiled with sqlite3_prepare_v2(). In this case,
2140: # the first error encountered is an SQLITE_SCHEMA error. Then, when
2141: # trying to recompile the statement, the authorization error is encountered.
2142: # If we do not flush the cache, the correct error message is returned, but
2143: # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test
2144: # case after this one.
2145: #
2146: db cache flush
2147:
2148: proc auth {code arg1 arg2 arg3 arg4} {
2149: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
2150: return bogus
2151: }
2152: return SQLITE_OK
2153: }
2154: catchsql {SELECT ROWID,b,c FROM t2}
2155: } {1 {authorizer malfunction}}
2156: do_test auth-2.9.2 {
2157: db errorcode
2158: } {1}
2159: do_test auth-2.10 {
2160: proc auth {code arg1 arg2 arg3 arg4} {
2161: if {$code=="SQLITE_SELECT"} {
2162: return bogus
2163: }
2164: return SQLITE_OK
2165: }
2166: catchsql {SELECT ROWID,b,c FROM t2}
2167: } {1 {authorizer malfunction}}
2168: do_test auth-2.11.1 {
2169: proc auth {code arg1 arg2 arg3 arg4} {
2170: if {$code=="SQLITE_READ" && $arg2=="a"} {
2171: return SQLITE_IGNORE
2172: }
2173: return SQLITE_OK
2174: }
2175: catchsql {SELECT * FROM t2, t3}
2176: } {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}}
2177: do_test auth-2.11.2 {
2178: proc auth {code arg1 arg2 arg3 arg4} {
2179: if {$code=="SQLITE_READ" && $arg2=="x"} {
2180: return SQLITE_IGNORE
2181: }
2182: return SQLITE_OK
2183: }
2184: catchsql {SELECT * FROM t2, t3}
2185: } {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}}
2186:
2187: # Make sure the OLD and NEW pseudo-tables of a trigger get authorized.
2188: #
2189: ifcapable trigger {
2190: do_test auth-3.1 {
2191: proc auth {code arg1 arg2 arg3 arg4} {
2192: return SQLITE_OK
2193: }
2194: execsql {
2195: CREATE TABLE tx(a1,a2,b1,b2,c1,c2);
2196: CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
2197: INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c);
2198: END;
2199: UPDATE t2 SET a=a+1;
2200: SELECT * FROM tx;
2201: }
2202: } {11 12 2 2 33 33 7 8 8 8 9 9}
2203: do_test auth-3.2 {
2204: proc auth {code arg1 arg2 arg3 arg4} {
2205: if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} {
2206: return SQLITE_IGNORE
2207: }
2208: return SQLITE_OK
2209: }
2210: execsql {
2211: DELETE FROM tx;
2212: UPDATE t2 SET a=a+100;
2213: SELECT * FROM tx;
2214: }
2215: } {12 112 2 2 {} {} 8 108 8 8 {} {}}
2216: } ;# ifcapable trigger
2217:
2218: # Make sure the names of views and triggers are passed on on arg4.
2219: #
2220: ifcapable trigger {
2221: do_test auth-4.1 {
2222: proc auth {code arg1 arg2 arg3 arg4} {
2223: lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
2224: return SQLITE_OK
2225: }
2226: set authargs {}
2227: execsql {
2228: UPDATE t2 SET a=a+1;
2229: }
2230: set authargs
2231: } [list \
2232: SQLITE_READ t2 a main {} \
2233: SQLITE_UPDATE t2 a main {} \
2234: SQLITE_INSERT tx {} main r1 \
2235: SQLITE_READ t2 a main r1 \
2236: SQLITE_READ t2 a main r1 \
2237: SQLITE_READ t2 b main r1 \
2238: SQLITE_READ t2 b main r1 \
2239: SQLITE_READ t2 c main r1 \
2240: SQLITE_READ t2 c main r1]
2241: }
2242:
2243: ifcapable {view && trigger} {
2244: do_test auth-4.2 {
2245: execsql {
2246: CREATE VIEW v1 AS SELECT a+b AS x FROM t2;
2247: CREATE TABLE v1chng(x1,x2);
2248: CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN
2249: INSERT INTO v1chng VALUES(OLD.x,NEW.x);
2250: END;
2251: SELECT * FROM v1;
2252: }
2253: } {115 117}
2254: do_test auth-4.3 {
2255: set authargs {}
2256: execsql {
2257: UPDATE v1 SET x=1 WHERE x=117
2258: }
2259: set authargs
2260: } [list \
2261: SQLITE_UPDATE v1 x main {} \
2262: SQLITE_SELECT {} {} {} v1 \
2263: SQLITE_READ t2 a main v1 \
2264: SQLITE_READ t2 b main v1 \
2265: SQLITE_SELECT {} {} {} {} \
2266: SQLITE_READ v1 x main v1 \
2267: SQLITE_INSERT v1chng {} main r2 \
2268: SQLITE_READ v1 x main r2 \
2269: SQLITE_READ v1 x main r2 \
2270: ]
2271:
2272: do_test auth-4.4 {
2273: execsql {
2274: CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
2275: INSERT INTO v1chng VALUES(OLD.x,NULL);
2276: END;
2277: SELECT * FROM v1;
2278: }
2279: } {115 117}
2280: do_test auth-4.5 {
2281: set authargs {}
2282: execsql {
2283: DELETE FROM v1 WHERE x=117
2284: }
2285: set authargs
2286: } [list \
2287: SQLITE_DELETE v1 {} main {} \
2288: SQLITE_SELECT {} {} {} v1 \
2289: SQLITE_READ t2 a main v1 \
2290: SQLITE_READ t2 b main v1 \
2291: SQLITE_SELECT {} {} {} {} \
2292: SQLITE_READ v1 x main v1 \
2293: SQLITE_INSERT v1chng {} main r3 \
2294: SQLITE_READ v1 x main r3 \
2295: ]
2296:
2297: } ;# ifcapable view && trigger
2298:
2299: # Ticket #1338: Make sure authentication works in the presence of an AS
2300: # clause.
2301: #
2302: do_test auth-5.1 {
2303: proc auth {code arg1 arg2 arg3 arg4} {
2304: return SQLITE_OK
2305: }
2306: execsql {
2307: SELECT count(a) AS cnt FROM t4 ORDER BY cnt
2308: }
2309: } {1}
2310:
2311: # Ticket #1607
2312: #
2313: ifcapable compound&&subquery {
2314: ifcapable trigger {
2315: execsql {
2316: DROP TABLE tx;
2317: }
2318: ifcapable view {
2319: execsql {
2320: DROP TABLE v1chng;
2321: }
2322: }
2323: }
2324: ifcapable stat3 {
2325: set stat3 "sqlite_stat3 "
2326: } else {
2327: set stat3 ""
2328: }
2329: do_test auth-5.2 {
2330: execsql {
2331: SELECT name FROM (
2332: SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
2333: WHERE type='table'
2334: ORDER BY name
2335: }
2336: } "sqlite_stat1 ${stat3}t1 t2 t3 t4"
2337: }
2338:
2339: # Ticket #3944
2340: #
2341: ifcapable trigger {
2342: do_test auth-5.3.1 {
2343: execsql {
2344: CREATE TABLE t5 ( x );
2345: CREATE TRIGGER t5_tr1 AFTER INSERT ON t5 BEGIN
2346: UPDATE t5 SET x = 1 WHERE NEW.x = 0;
2347: END;
2348: }
2349: } {}
2350: set ::authargs [list]
2351: proc auth {args} {
2352: eval lappend ::authargs $args
2353: return SQLITE_OK
2354: }
2355: do_test auth-5.3.2 {
2356: execsql { INSERT INTO t5 (x) values(0) }
2357: set ::authargs
2358: } [list SQLITE_INSERT t5 {} main {} \
2359: SQLITE_UPDATE t5 x main t5_tr1 \
2360: SQLITE_READ t5 x main t5_tr1 \
2361: ]
2362: do_test auth-5.3.2 {
2363: execsql { SELECT * FROM t5 }
2364: } {1}
2365: }
2366:
2367:
2368: rename proc {}
2369: rename proc_real proc
2370:
2371:
2372: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to auth.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test