xref: /illumos-gate/usr/src/lib/libsqlite/test/auth.test (revision 1da57d551424de5a9d469760be7c4b4d4f10a755)
1#
2# 2003 April 4
3#
4# The author disclaims copyright to this source code.  In place of
5# a legal notice, here is a blessing:
6#
7#    May you do good and not evil.
8#    May you find forgiveness for yourself and forgive others.
9#    May you share freely, never taking more than you give.
10#
11#***********************************************************************
12# This file implements regression tests for SQLite library.  The
13# focus of this script is testing the ATTACH and DETACH commands
14# and related functionality.
15#
16# $Id: auth.test,v 1.12 2003/12/07 00:24:35 drh Exp $
17#
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
23# defined during compilation.
24
25do_test auth-1.1.1 {
26  db close
27  set ::DB [sqlite db test.db]
28  proc auth {code arg1 arg2 arg3 arg4} {
29    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
30      return SQLITE_DENY
31    }
32    return SQLITE_OK
33  }
34  db authorizer ::auth
35  catchsql {CREATE TABLE t1(a,b,c)}
36} {1 {not authorized}}
37do_test auth-1.1.2 {
38  db errorcode
39} {23}
40do_test auth-1.2 {
41  execsql {SELECT name FROM sqlite_master}
42} {}
43do_test auth-1.3.1 {
44  proc auth {code arg1 arg2 arg3 arg4} {
45    if {$code=="SQLITE_CREATE_TABLE"} {
46      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
47      return SQLITE_DENY
48    }
49    return SQLITE_OK
50  }
51  catchsql {CREATE TABLE t1(a,b,c)}
52} {1 {not authorized}}
53do_test auth-1.3.2 {
54  db errorcode
55} {23}
56do_test auth-1.3.3 {
57  set ::authargs
58} {t1 {} main {}}
59do_test auth-1.4 {
60  execsql {SELECT name FROM sqlite_master}
61} {}
62
63do_test auth-1.5 {
64  proc auth {code arg1 arg2 arg3 arg4} {
65    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
66      return SQLITE_DENY
67    }
68    return SQLITE_OK
69  }
70  catchsql {CREATE TEMP TABLE t1(a,b,c)}
71} {1 {not authorized}}
72do_test auth-1.6 {
73  execsql {SELECT name FROM sqlite_temp_master}
74} {}
75do_test auth-1.7.1 {
76  proc auth {code arg1 arg2 arg3 arg4} {
77    if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
78      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
79      return SQLITE_DENY
80    }
81    return SQLITE_OK
82  }
83  catchsql {CREATE TEMP TABLE t1(a,b,c)}
84} {1 {not authorized}}
85do_test auth-1.7.2 {
86   set ::authargs
87} {t1 {} temp {}}
88do_test auth-1.8 {
89  execsql {SELECT name FROM sqlite_temp_master}
90} {}
91
92do_test auth-1.9 {
93  proc auth {code arg1 arg2 arg3 arg4} {
94    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
95      return SQLITE_IGNORE
96    }
97    return SQLITE_OK
98  }
99  catchsql {CREATE TABLE t1(a,b,c)}
100} {0 {}}
101do_test auth-1.10 {
102  execsql {SELECT name FROM sqlite_master}
103} {}
104do_test auth-1.11 {
105  proc auth {code arg1 arg2 arg3 arg4} {
106    if {$code=="SQLITE_CREATE_TABLE"} {
107      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
108      return SQLITE_IGNORE
109    }
110    return SQLITE_OK
111  }
112  catchsql {CREATE TABLE t1(a,b,c)}
113} {0 {}}
114do_test auth-1.12 {
115  execsql {SELECT name FROM sqlite_master}
116} {}
117do_test auth-1.13 {
118  proc auth {code arg1 arg2 arg3 arg4} {
119    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
120      return SQLITE_IGNORE
121    }
122    return SQLITE_OK
123  }
124  catchsql {CREATE TEMP TABLE t1(a,b,c)}
125} {0 {}}
126do_test auth-1.14 {
127  execsql {SELECT name FROM sqlite_temp_master}
128} {}
129do_test auth-1.15 {
130  proc auth {code arg1 arg2 arg3 arg4} {
131    if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
132      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
133      return SQLITE_IGNORE
134    }
135    return SQLITE_OK
136  }
137  catchsql {CREATE TEMP TABLE t1(a,b,c)}
138} {0 {}}
139do_test auth-1.16 {
140  execsql {SELECT name FROM sqlite_temp_master}
141} {}
142
143do_test auth-1.17 {
144  proc auth {code arg1 arg2 arg3 arg4} {
145    if {$code=="SQLITE_CREATE_TABLE"} {
146      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
147      return SQLITE_DENY
148    }
149    return SQLITE_OK
150  }
151  catchsql {CREATE TEMP TABLE t1(a,b,c)}
152} {0 {}}
153do_test auth-1.18 {
154  execsql {SELECT name FROM sqlite_temp_master}
155} {t1}
156do_test auth-1.19.1 {
157  set ::authargs {}
158  proc auth {code arg1 arg2 arg3 arg4} {
159    if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
160      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
161      return SQLITE_DENY
162    }
163    return SQLITE_OK
164  }
165  catchsql {CREATE TABLE t2(a,b,c)}
166} {0 {}}
167do_test auth-1.19.2 {
168  set ::authargs
169} {}
170do_test auth-1.20 {
171  execsql {SELECT name FROM sqlite_master}
172} {t2}
173
174do_test auth-1.21.1 {
175  proc auth {code arg1 arg2 arg3 arg4} {
176    if {$code=="SQLITE_DROP_TABLE"} {
177      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
178      return SQLITE_DENY
179    }
180    return SQLITE_OK
181  }
182  catchsql {DROP TABLE t2}
183} {1 {not authorized}}
184do_test auth-1.21.2 {
185  set ::authargs
186} {t2 {} main {}}
187do_test auth-1.22 {
188  execsql {SELECT name FROM sqlite_master}
189} {t2}
190do_test auth-1.23.1 {
191  proc auth {code arg1 arg2 arg3 arg4} {
192    if {$code=="SQLITE_DROP_TABLE"} {
193      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
194      return SQLITE_IGNORE
195    }
196    return SQLITE_OK
197  }
198  catchsql {DROP TABLE t2}
199} {0 {}}
200do_test auth-1.23.2 {
201  set ::authargs
202} {t2 {} main {}}
203do_test auth-1.24 {
204  execsql {SELECT name FROM sqlite_master}
205} {t2}
206
207do_test auth-1.25 {
208  proc auth {code arg1 arg2 arg3 arg4} {
209    if {$code=="SQLITE_DROP_TEMP_TABLE"} {
210      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
211      return SQLITE_DENY
212    }
213    return SQLITE_OK
214  }
215  catchsql {DROP TABLE t1}
216} {1 {not authorized}}
217do_test auth-1.26 {
218  execsql {SELECT name FROM sqlite_temp_master}
219} {t1}
220do_test auth-1.27 {
221  proc auth {code arg1 arg2 arg3 arg4} {
222    if {$code=="SQLITE_DROP_TEMP_TABLE"} {
223      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
224      return SQLITE_IGNORE
225    }
226    return SQLITE_OK
227  }
228  catchsql {DROP TABLE t1}
229} {0 {}}
230do_test auth-1.28 {
231  execsql {SELECT name FROM sqlite_temp_master}
232} {t1}
233
234do_test auth-1.29 {
235  proc auth {code arg1 arg2 arg3 arg4} {
236    if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
237      return SQLITE_DENY
238    }
239    return SQLITE_OK
240  }
241  catchsql {INSERT INTO t2 VALUES(1,2,3)}
242} {1 {not authorized}}
243do_test auth-1.30 {
244  execsql {SELECT * FROM t2}
245} {}
246do_test auth-1.31 {
247  proc auth {code arg1 arg2 arg3 arg4} {
248    if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
249      return SQLITE_IGNORE
250    }
251    return SQLITE_OK
252  }
253  catchsql {INSERT INTO t2 VALUES(1,2,3)}
254} {0 {}}
255do_test auth-1.32 {
256  execsql {SELECT * FROM t2}
257} {}
258do_test auth-1.33 {
259  proc auth {code arg1 arg2 arg3 arg4} {
260    if {$code=="SQLITE_INSERT" && $arg1=="t1"} {
261      return SQLITE_IGNORE
262    }
263    return SQLITE_OK
264  }
265  catchsql {INSERT INTO t2 VALUES(1,2,3)}
266} {0 {}}
267do_test auth-1.34 {
268  execsql {SELECT * FROM t2}
269} {1 2 3}
270
271do_test auth-1.35.1 {
272  proc auth {code arg1 arg2 arg3 arg4} {
273    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
274      return SQLITE_DENY
275    }
276    return SQLITE_OK
277  }
278  catchsql {SELECT * FROM t2}
279} {1 {access to t2.b is prohibited}}
280do_test auth-1.35.2 {
281  execsql {ATTACH DATABASE 'test.db' AS two}
282  catchsql {SELECT * FROM two.t2}
283} {1 {access to two.t2.b is prohibited}}
284execsql {DETACH DATABASE two}
285do_test auth-1.36 {
286  proc auth {code arg1 arg2 arg3 arg4} {
287    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
288      return SQLITE_IGNORE
289    }
290    return SQLITE_OK
291  }
292  catchsql {SELECT * FROM t2}
293} {0 {1 {} 3}}
294do_test auth-1.37 {
295  proc auth {code arg1 arg2 arg3 arg4} {
296    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
297      return SQLITE_IGNORE
298    }
299    return SQLITE_OK
300  }
301  catchsql {SELECT * FROM t2 WHERE b=2}
302} {0 {}}
303do_test auth-1.38 {
304  proc auth {code arg1 arg2 arg3 arg4} {
305    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} {
306      return SQLITE_IGNORE
307    }
308    return SQLITE_OK
309  }
310  catchsql {SELECT * FROM t2 WHERE b=2}
311} {0 {{} 2 3}}
312do_test auth-1.39 {
313  proc auth {code arg1 arg2 arg3 arg4} {
314    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
315      return SQLITE_IGNORE
316    }
317    return SQLITE_OK
318  }
319  catchsql {SELECT * FROM t2 WHERE b IS NULL}
320} {0 {1 {} 3}}
321do_test auth-1.40 {
322  proc auth {code arg1 arg2 arg3 arg4} {
323    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {
324      return SQLITE_DENY
325    }
326    return SQLITE_OK
327  }
328  catchsql {SELECT a,c FROM t2 WHERE b IS NULL}
329} {1 {access to t2.b is prohibited}}
330
331do_test auth-1.41 {
332  proc auth {code arg1 arg2 arg3 arg4} {
333    if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
334      return SQLITE_DENY
335    }
336    return SQLITE_OK
337  }
338  catchsql {UPDATE t2 SET a=11}
339} {0 {}}
340do_test auth-1.42 {
341  execsql {SELECT * FROM t2}
342} {11 2 3}
343do_test auth-1.43 {
344  proc auth {code arg1 arg2 arg3 arg4} {
345    if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
346      return SQLITE_DENY
347    }
348    return SQLITE_OK
349  }
350  catchsql {UPDATE t2 SET b=22, c=33}
351} {1 {not authorized}}
352do_test auth-1.44 {
353  execsql {SELECT * FROM t2}
354} {11 2 3}
355do_test auth-1.45 {
356  proc auth {code arg1 arg2 arg3 arg4} {
357    if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {
358      return SQLITE_IGNORE
359    }
360    return SQLITE_OK
361  }
362  catchsql {UPDATE t2 SET b=22, c=33}
363} {0 {}}
364do_test auth-1.46 {
365  execsql {SELECT * FROM t2}
366} {11 2 33}
367
368do_test auth-1.47 {
369  proc auth {code arg1 arg2 arg3 arg4} {
370    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
371      return SQLITE_DENY
372    }
373    return SQLITE_OK
374  }
375  catchsql {DELETE FROM t2 WHERE a=11}
376} {1 {not authorized}}
377do_test auth-1.48 {
378  execsql {SELECT * FROM t2}
379} {11 2 33}
380do_test auth-1.49 {
381  proc auth {code arg1 arg2 arg3 arg4} {
382    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
383      return SQLITE_IGNORE
384    }
385    return SQLITE_OK
386  }
387  catchsql {DELETE FROM t2 WHERE a=11}
388} {0 {}}
389do_test auth-1.50 {
390  execsql {SELECT * FROM t2}
391} {11 2 33}
392
393do_test auth-1.51 {
394  proc auth {code arg1 arg2 arg3 arg4} {
395    if {$code=="SQLITE_SELECT"} {
396      return SQLITE_DENY
397    }
398    return SQLITE_OK
399  }
400  catchsql {SELECT * FROM t2}
401} {1 {not authorized}}
402do_test auth-1.52 {
403  proc auth {code arg1 arg2 arg3 arg4} {
404    if {$code=="SQLITE_SELECT"} {
405      return SQLITE_IGNORE
406    }
407    return SQLITE_OK
408  }
409  catchsql {SELECT * FROM t2}
410} {0 {}}
411do_test auth-1.53 {
412  proc auth {code arg1 arg2 arg3 arg4} {
413    if {$code=="SQLITE_SELECT"} {
414      return SQLITE_OK
415    }
416    return SQLITE_OK
417  }
418  catchsql {SELECT * FROM t2}
419} {0 {11 2 33}}
420
421set f [open data1.txt w]
422puts $f "7:8:9"
423close $f
424do_test auth-1.54 {
425  proc auth {code arg1 arg2 arg3 arg4} {
426    if {$code=="SQLITE_COPY"} {
427      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
428      return SQLITE_DENY
429    }
430    return SQLITE_OK
431  }
432  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
433} {1 {not authorized}}
434do_test auth-1.55 {
435  set ::authargs
436} {t2 data1.txt main {}}
437do_test auth-1.56 {
438  execsql {SELECT * FROM t2}
439} {11 2 33}
440do_test auth-1.57 {
441  proc auth {code arg1 arg2 arg3 arg4} {
442    if {$code=="SQLITE_COPY"} {
443      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
444      return SQLITE_IGNORE
445    }
446    return SQLITE_OK
447  }
448  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
449} {0 {}}
450do_test auth-1.58 {
451  set ::authargs
452} {t2 data1.txt main {}}
453do_test auth-1.59 {
454  execsql {SELECT * FROM t2}
455} {11 2 33}
456do_test auth-1.60 {
457  proc auth {code arg1 arg2 arg3 arg4} {
458    if {$code=="SQLITE_COPY"} {
459      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
460      return SQLITE_OK
461    }
462    return SQLITE_OK
463  }
464  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
465} {0 {}}
466do_test auth-1.61 {
467  set ::authargs
468} {t2 data1.txt main {}}
469do_test auth-1.62 {
470  execsql {SELECT * FROM t2}
471} {11 2 33 7 8 9}
472
473do_test auth-1.63 {
474  proc auth {code arg1 arg2 arg3 arg4} {
475    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
476       return SQLITE_DENY
477    }
478    return SQLITE_OK
479  }
480  catchsql {DROP TABLE t2}
481} {1 {not authorized}}
482do_test auth-1.64 {
483  execsql {SELECT name FROM sqlite_master}
484} {t2}
485do_test auth-1.65 {
486  proc auth {code arg1 arg2 arg3 arg4} {
487    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
488       return SQLITE_DENY
489    }
490    return SQLITE_OK
491  }
492  catchsql {DROP TABLE t2}
493} {1 {not authorized}}
494do_test auth-1.66 {
495  execsql {SELECT name FROM sqlite_master}
496} {t2}
497do_test auth-1.67 {
498  proc auth {code arg1 arg2 arg3 arg4} {
499    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
500       return SQLITE_DENY
501    }
502    return SQLITE_OK
503  }
504  catchsql {DROP TABLE t1}
505} {1 {not authorized}}
506do_test auth-1.68 {
507  execsql {SELECT name FROM sqlite_temp_master}
508} {t1}
509do_test auth-1.69 {
510  proc auth {code arg1 arg2 arg3 arg4} {
511    if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
512       return SQLITE_DENY
513    }
514    return SQLITE_OK
515  }
516  catchsql {DROP TABLE t1}
517} {1 {not authorized}}
518do_test auth-1.70 {
519  execsql {SELECT name FROM sqlite_temp_master}
520} {t1}
521
522do_test auth-1.71 {
523  proc auth {code arg1 arg2 arg3 arg4} {
524    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
525       return SQLITE_IGNORE
526    }
527    return SQLITE_OK
528  }
529  catchsql {DROP TABLE t2}
530} {0 {}}
531do_test auth-1.72 {
532  execsql {SELECT name FROM sqlite_master}
533} {t2}
534do_test auth-1.73 {
535  proc auth {code arg1 arg2 arg3 arg4} {
536    if {$code=="SQLITE_DELETE" && $arg1=="t2"} {
537       return SQLITE_IGNORE
538    }
539    return SQLITE_OK
540  }
541  catchsql {DROP TABLE t2}
542} {0 {}}
543do_test auth-1.74 {
544  execsql {SELECT name FROM sqlite_master}
545} {t2}
546do_test auth-1.75 {
547  proc auth {code arg1 arg2 arg3 arg4} {
548    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
549       return SQLITE_IGNORE
550    }
551    return SQLITE_OK
552  }
553  catchsql {DROP TABLE t1}
554} {0 {}}
555do_test auth-1.76 {
556  execsql {SELECT name FROM sqlite_temp_master}
557} {t1}
558do_test auth-1.77 {
559  proc auth {code arg1 arg2 arg3 arg4} {
560    if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
561       return SQLITE_IGNORE
562    }
563    return SQLITE_OK
564  }
565  catchsql {DROP TABLE t1}
566} {0 {}}
567do_test auth-1.78 {
568  execsql {SELECT name FROM sqlite_temp_master}
569} {t1}
570
571do_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}}
581do_test auth-1.80 {
582  set ::authargs
583} {v1 {} main {}}
584do_test auth-1.81 {
585  execsql {SELECT name FROM sqlite_master}
586} {t2}
587do_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 {}}
597do_test auth-1.83 {
598  set ::authargs
599} {v1 {} main {}}
600do_test auth-1.84 {
601  execsql {SELECT name FROM sqlite_master}
602} {t2}
603
604do_test auth-1.85 {
605  proc auth {code arg1 arg2 arg3 arg4} {
606    if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
607      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
608      return SQLITE_DENY
609    }
610    return SQLITE_OK
611  }
612  catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
613} {1 {not authorized}}
614do_test auth-1.86 {
615  set ::authargs
616} {v1 {} temp {}}
617do_test auth-1.87 {
618  execsql {SELECT name FROM sqlite_temp_master}
619} {t1}
620do_test auth-1.88 {
621  proc auth {code arg1 arg2 arg3 arg4} {
622    if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
623      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
624      return SQLITE_IGNORE
625    }
626    return SQLITE_OK
627  }
628  catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
629} {0 {}}
630do_test auth-1.89 {
631  set ::authargs
632} {v1 {} temp {}}
633do_test auth-1.90 {
634  execsql {SELECT name FROM sqlite_temp_master}
635} {t1}
636
637do_test auth-1.91 {
638  proc auth {code arg1 arg2 arg3 arg4} {
639    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
640      return SQLITE_DENY
641    }
642    return SQLITE_OK
643  }
644  catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
645} {1 {not authorized}}
646do_test auth-1.92 {
647  execsql {SELECT name FROM sqlite_master}
648} {t2}
649do_test auth-1.93 {
650  proc auth {code arg1 arg2 arg3 arg4} {
651    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
652      return SQLITE_IGNORE
653    }
654    return SQLITE_OK
655  }
656  catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
657} {0 {}}
658do_test auth-1.94 {
659  execsql {SELECT name FROM sqlite_master}
660} {t2}
661
662do_test auth-1.95 {
663  proc auth {code arg1 arg2 arg3 arg4} {
664    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
665      return SQLITE_DENY
666    }
667    return SQLITE_OK
668  }
669  catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
670} {1 {not authorized}}
671do_test auth-1.96 {
672  execsql {SELECT name FROM sqlite_temp_master}
673} {t1}
674do_test auth-1.97 {
675  proc auth {code arg1 arg2 arg3 arg4} {
676    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
677      return SQLITE_IGNORE
678    }
679    return SQLITE_OK
680  }
681  catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
682} {0 {}}
683do_test auth-1.98 {
684  execsql {SELECT name FROM sqlite_temp_master}
685} {t1}
686
687do_test auth-1.99 {
688  proc auth {code arg1 arg2 arg3 arg4} {
689    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
690      return SQLITE_DENY
691    }
692    return SQLITE_OK
693  }
694  catchsql {
695    CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2;
696    DROP VIEW v2
697  }
698} {1 {not authorized}}
699do_test auth-1.100 {
700  execsql {SELECT name FROM sqlite_master}
701} {t2 v2}
702do_test auth-1.101 {
703  proc auth {code arg1 arg2 arg3 arg4} {
704    if {$code=="SQLITE_DROP_VIEW"} {
705      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
706      return SQLITE_DENY
707    }
708    return SQLITE_OK
709  }
710  catchsql {DROP VIEW v2}
711} {1 {not authorized}}
712do_test auth-1.102 {
713  set ::authargs
714} {v2 {} main {}}
715do_test auth-1.103 {
716  execsql {SELECT name FROM sqlite_master}
717} {t2 v2}
718do_test auth-1.104 {
719  proc auth {code arg1 arg2 arg3 arg4} {
720    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
721      return SQLITE_IGNORE
722    }
723    return SQLITE_OK
724  }
725  catchsql {DROP VIEW v2}
726} {0 {}}
727do_test auth-1.105 {
728  execsql {SELECT name FROM sqlite_master}
729} {t2 v2}
730do_test auth-1.106 {
731  proc auth {code arg1 arg2 arg3 arg4} {
732    if {$code=="SQLITE_DROP_VIEW"} {
733      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
734      return SQLITE_IGNORE
735    }
736    return SQLITE_OK
737  }
738  catchsql {DROP VIEW v2}
739} {0 {}}
740do_test auth-1.107 {
741  set ::authargs
742} {v2 {} main {}}
743do_test auth-1.108 {
744  execsql {SELECT name FROM sqlite_master}
745} {t2 v2}
746do_test auth-1.109 {
747  proc auth {code arg1 arg2 arg3 arg4} {
748    if {$code=="SQLITE_DROP_VIEW"} {
749      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
750      return SQLITE_OK
751    }
752    return SQLITE_OK
753  }
754  catchsql {DROP VIEW v2}
755} {0 {}}
756do_test auth-1.110 {
757  set ::authargs
758} {v2 {} main {}}
759do_test auth-1.111 {
760  execsql {SELECT name FROM sqlite_master}
761} {t2}
762
763
764do_test auth-1.112 {
765  proc auth {code arg1 arg2 arg3 arg4} {
766    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
767      return SQLITE_DENY
768    }
769    return SQLITE_OK
770  }
771  catchsql {
772    CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1;
773    DROP VIEW v1
774  }
775} {1 {not authorized}}
776do_test auth-1.113 {
777  execsql {SELECT name FROM sqlite_temp_master}
778} {t1 v1}
779do_test auth-1.114 {
780  proc auth {code arg1 arg2 arg3 arg4} {
781    if {$code=="SQLITE_DROP_TEMP_VIEW"} {
782      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
783      return SQLITE_DENY
784    }
785    return SQLITE_OK
786  }
787  catchsql {DROP VIEW v1}
788} {1 {not authorized}}
789do_test auth-1.115 {
790  set ::authargs
791} {v1 {} temp {}}
792do_test auth-1.116 {
793  execsql {SELECT name FROM sqlite_temp_master}
794} {t1 v1}
795do_test auth-1.117 {
796  proc auth {code arg1 arg2 arg3 arg4} {
797    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
798      return SQLITE_IGNORE
799    }
800    return SQLITE_OK
801  }
802  catchsql {DROP VIEW v1}
803} {0 {}}
804do_test auth-1.118 {
805  execsql {SELECT name FROM sqlite_temp_master}
806} {t1 v1}
807do_test auth-1.119 {
808  proc auth {code arg1 arg2 arg3 arg4} {
809    if {$code=="SQLITE_DROP_TEMP_VIEW"} {
810      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
811      return SQLITE_IGNORE
812    }
813    return SQLITE_OK
814  }
815  catchsql {DROP VIEW v1}
816} {0 {}}
817do_test auth-1.120 {
818  set ::authargs
819} {v1 {} temp {}}
820do_test auth-1.121 {
821  execsql {SELECT name FROM sqlite_temp_master}
822} {t1 v1}
823do_test auth-1.122 {
824  proc auth {code arg1 arg2 arg3 arg4} {
825    if {$code=="SQLITE_DROP_TEMP_VIEW"} {
826      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
827      return SQLITE_OK
828    }
829    return SQLITE_OK
830  }
831  catchsql {DROP VIEW v1}
832} {0 {}}
833do_test auth-1.123 {
834  set ::authargs
835} {v1 {} temp {}}
836do_test auth-1.124 {
837  execsql {SELECT name FROM sqlite_temp_master}
838} {t1}
839
840do_test auth-1.125 {
841  proc auth {code arg1 arg2 arg3 arg4} {
842    if {$code=="SQLITE_CREATE_TRIGGER"} {
843      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
844      return SQLITE_DENY
845    }
846    return SQLITE_OK
847  }
848  catchsql {
849    CREATE TRIGGER r2 DELETE on t2 BEGIN
850        SELECT NULL;
851    END;
852  }
853} {1 {not authorized}}
854do_test auth-1.126 {
855  set ::authargs
856} {r2 t2 main {}}
857do_test auth-1.127 {
858  execsql {SELECT name FROM sqlite_master}
859} {t2}
860do_test auth-1.128 {
861  proc auth {code arg1 arg2 arg3 arg4} {
862    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
863      return SQLITE_DENY
864    }
865    return SQLITE_OK
866  }
867  catchsql {
868    CREATE TRIGGER r2 DELETE on t2 BEGIN
869        SELECT NULL;
870    END;
871  }
872} {1 {not authorized}}
873do_test auth-1.129 {
874  execsql {SELECT name FROM sqlite_master}
875} {t2}
876do_test auth-1.130 {
877  proc auth {code arg1 arg2 arg3 arg4} {
878    if {$code=="SQLITE_CREATE_TRIGGER"} {
879      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
880      return SQLITE_IGNORE
881    }
882    return SQLITE_OK
883  }
884  catchsql {
885    CREATE TRIGGER r2 DELETE on t2 BEGIN
886        SELECT NULL;
887    END;
888  }
889} {0 {}}
890do_test auth-1.131 {
891  set ::authargs
892} {r2 t2 main {}}
893do_test auth-1.132 {
894  execsql {SELECT name FROM sqlite_master}
895} {t2}
896do_test auth-1.133 {
897  proc auth {code arg1 arg2 arg3 arg4} {
898    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
899      return SQLITE_IGNORE
900    }
901    return SQLITE_OK
902  }
903  catchsql {
904    CREATE TRIGGER r2 DELETE on t2 BEGIN
905        SELECT NULL;
906    END;
907  }
908} {0 {}}
909do_test auth-1.134 {
910  execsql {SELECT name FROM sqlite_master}
911} {t2}
912do_test auth-1.135 {
913  proc auth {code arg1 arg2 arg3 arg4} {
914    if {$code=="SQLITE_CREATE_TRIGGER"} {
915      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
916      return SQLITE_OK
917    }
918    return SQLITE_OK
919  }
920  catchsql {
921    CREATE TABLE tx(id);
922    CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN
923       INSERT INTO tx VALUES(NEW.rowid);
924    END;
925  }
926} {0 {}}
927do_test auth-1.136.1 {
928  set ::authargs
929} {r2 t2 main {}}
930do_test auth-1.136.2 {
931  execsql {
932    SELECT name FROM sqlite_master WHERE type='trigger'
933  }
934} {r2}
935do_test auth-1.136.3 {
936  proc auth {code arg1 arg2 arg3 arg4} {
937    lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
938    return SQLITE_OK
939  }
940  set ::authargs {}
941  execsql {
942    INSERT INTO t2 VALUES(1,2,3);
943  }
944  set ::authargs
945} {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2}
946do_test auth-1.136.4 {
947  execsql {
948    SELECT * FROM tx;
949  }
950} {3}
951do_test auth-1.137 {
952  execsql {SELECT name FROM sqlite_master}
953} {t2 tx r2}
954do_test auth-1.138 {
955  proc auth {code arg1 arg2 arg3 arg4} {
956    if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
957      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
958      return SQLITE_DENY
959    }
960    return SQLITE_OK
961  }
962  catchsql {
963    CREATE TRIGGER r1 DELETE on t1 BEGIN
964        SELECT NULL;
965    END;
966  }
967} {1 {not authorized}}
968do_test auth-1.139 {
969  set ::authargs
970} {r1 t1 temp {}}
971do_test auth-1.140 {
972  execsql {SELECT name FROM sqlite_temp_master}
973} {t1}
974do_test auth-1.141 {
975  proc auth {code arg1 arg2 arg3 arg4} {
976    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
977      return SQLITE_DENY
978    }
979    return SQLITE_OK
980  }
981  catchsql {
982    CREATE TRIGGER r1 DELETE on t1 BEGIN
983        SELECT NULL;
984    END;
985  }
986} {1 {not authorized}}
987do_test auth-1.142 {
988  execsql {SELECT name FROM sqlite_temp_master}
989} {t1}
990do_test auth-1.143 {
991  proc auth {code arg1 arg2 arg3 arg4} {
992    if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
993      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
994      return SQLITE_IGNORE
995    }
996    return SQLITE_OK
997  }
998  catchsql {
999    CREATE TRIGGER r1 DELETE on t1 BEGIN
1000        SELECT NULL;
1001    END;
1002  }
1003} {0 {}}
1004do_test auth-1.144 {
1005  set ::authargs
1006} {r1 t1 temp {}}
1007do_test auth-1.145 {
1008  execsql {SELECT name FROM sqlite_temp_master}
1009} {t1}
1010do_test auth-1.146 {
1011  proc auth {code arg1 arg2 arg3 arg4} {
1012    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1013      return SQLITE_IGNORE
1014    }
1015    return SQLITE_OK
1016  }
1017  catchsql {
1018    CREATE TRIGGER r1 DELETE on t1 BEGIN
1019        SELECT NULL;
1020    END;
1021  }
1022} {0 {}}
1023do_test auth-1.147 {
1024  execsql {SELECT name FROM sqlite_temp_master}
1025} {t1}
1026do_test auth-1.148 {
1027  proc auth {code arg1 arg2 arg3 arg4} {
1028    if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {
1029      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1030      return SQLITE_OK
1031    }
1032    return SQLITE_OK
1033  }
1034  catchsql {
1035    CREATE TRIGGER r1 DELETE on t1 BEGIN
1036        SELECT NULL;
1037    END;
1038  }
1039} {0 {}}
1040do_test auth-1.149 {
1041  set ::authargs
1042} {r1 t1 temp {}}
1043do_test auth-1.150 {
1044  execsql {SELECT name FROM sqlite_temp_master}
1045} {t1 r1}
1046
1047do_test auth-1.151 {
1048  proc auth {code arg1 arg2 arg3 arg4} {
1049    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1050      return SQLITE_DENY
1051    }
1052    return SQLITE_OK
1053  }
1054  catchsql {DROP TRIGGER r2}
1055} {1 {not authorized}}
1056do_test auth-1.152 {
1057  execsql {SELECT name FROM sqlite_master}
1058} {t2 tx r2}
1059do_test auth-1.153 {
1060  proc auth {code arg1 arg2 arg3 arg4} {
1061    if {$code=="SQLITE_DROP_TRIGGER"} {
1062      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1063      return SQLITE_DENY
1064    }
1065    return SQLITE_OK
1066  }
1067  catchsql {DROP TRIGGER r2}
1068} {1 {not authorized}}
1069do_test auth-1.154 {
1070  set ::authargs
1071} {r2 t2 main {}}
1072do_test auth-1.155 {
1073  execsql {SELECT name FROM sqlite_master}
1074} {t2 tx r2}
1075do_test auth-1.156 {
1076  proc auth {code arg1 arg2 arg3 arg4} {
1077    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1078      return SQLITE_IGNORE
1079    }
1080    return SQLITE_OK
1081  }
1082  catchsql {DROP TRIGGER r2}
1083} {0 {}}
1084do_test auth-1.157 {
1085  execsql {SELECT name FROM sqlite_master}
1086} {t2 tx r2}
1087do_test auth-1.158 {
1088  proc auth {code arg1 arg2 arg3 arg4} {
1089    if {$code=="SQLITE_DROP_TRIGGER"} {
1090      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1091      return SQLITE_IGNORE
1092    }
1093    return SQLITE_OK
1094  }
1095  catchsql {DROP TRIGGER r2}
1096} {0 {}}
1097do_test auth-1.159 {
1098  set ::authargs
1099} {r2 t2 main {}}
1100do_test auth-1.160 {
1101  execsql {SELECT name FROM sqlite_master}
1102} {t2 tx r2}
1103do_test auth-1.161 {
1104  proc auth {code arg1 arg2 arg3 arg4} {
1105    if {$code=="SQLITE_DROP_TRIGGER"} {
1106      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1107      return SQLITE_OK
1108    }
1109    return SQLITE_OK
1110  }
1111  catchsql {DROP TRIGGER r2}
1112} {0 {}}
1113do_test auth-1.162 {
1114  set ::authargs
1115} {r2 t2 main {}}
1116do_test auth-1.163 {
1117  execsql {
1118    DROP TABLE tx;
1119    DELETE FROM t2 WHERE a=1 AND b=2 AND c=3;
1120    SELECT name FROM sqlite_master;
1121  }
1122} {t2}
1123
1124do_test auth-1.164 {
1125  proc auth {code arg1 arg2 arg3 arg4} {
1126    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1127      return SQLITE_DENY
1128    }
1129    return SQLITE_OK
1130  }
1131  catchsql {DROP TRIGGER r1}
1132} {1 {not authorized}}
1133do_test auth-1.165 {
1134  execsql {SELECT name FROM sqlite_temp_master}
1135} {t1 r1}
1136do_test auth-1.166 {
1137  proc auth {code arg1 arg2 arg3 arg4} {
1138    if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1139      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1140      return SQLITE_DENY
1141    }
1142    return SQLITE_OK
1143  }
1144  catchsql {DROP TRIGGER r1}
1145} {1 {not authorized}}
1146do_test auth-1.167 {
1147  set ::authargs
1148} {r1 t1 temp {}}
1149do_test auth-1.168 {
1150  execsql {SELECT name FROM sqlite_temp_master}
1151} {t1 r1}
1152do_test auth-1.169 {
1153  proc auth {code arg1 arg2 arg3 arg4} {
1154    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1155      return SQLITE_IGNORE
1156    }
1157    return SQLITE_OK
1158  }
1159  catchsql {DROP TRIGGER r1}
1160} {0 {}}
1161do_test auth-1.170 {
1162  execsql {SELECT name FROM sqlite_temp_master}
1163} {t1 r1}
1164do_test auth-1.171 {
1165  proc auth {code arg1 arg2 arg3 arg4} {
1166    if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1167      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1168      return SQLITE_IGNORE
1169    }
1170    return SQLITE_OK
1171  }
1172  catchsql {DROP TRIGGER r1}
1173} {0 {}}
1174do_test auth-1.172 {
1175  set ::authargs
1176} {r1 t1 temp {}}
1177do_test auth-1.173 {
1178  execsql {SELECT name FROM sqlite_temp_master}
1179} {t1 r1}
1180do_test auth-1.174 {
1181  proc auth {code arg1 arg2 arg3 arg4} {
1182    if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {
1183      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1184      return SQLITE_OK
1185    }
1186    return SQLITE_OK
1187  }
1188  catchsql {DROP TRIGGER r1}
1189} {0 {}}
1190do_test auth-1.175 {
1191  set ::authargs
1192} {r1 t1 temp {}}
1193do_test auth-1.176 {
1194  execsql {SELECT name FROM sqlite_temp_master}
1195} {t1}
1196
1197do_test auth-1.177 {
1198  proc auth {code arg1 arg2 arg3 arg4} {
1199    if {$code=="SQLITE_CREATE_INDEX"} {
1200      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1201      return SQLITE_DENY
1202    }
1203    return SQLITE_OK
1204  }
1205  catchsql {CREATE INDEX i2 ON t2(a)}
1206} {1 {not authorized}}
1207do_test auth-1.178 {
1208  set ::authargs
1209} {i2 t2 main {}}
1210do_test auth-1.179 {
1211  execsql {SELECT name FROM sqlite_master}
1212} {t2}
1213do_test auth-1.180 {
1214  proc auth {code arg1 arg2 arg3 arg4} {
1215    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1216      return SQLITE_DENY
1217    }
1218    return SQLITE_OK
1219  }
1220  catchsql {CREATE INDEX i2 ON t2(a)}
1221} {1 {not authorized}}
1222do_test auth-1.181 {
1223  execsql {SELECT name FROM sqlite_master}
1224} {t2}
1225do_test auth-1.182 {
1226  proc auth {code arg1 arg2 arg3 arg4} {
1227    if {$code=="SQLITE_CREATE_INDEX"} {
1228      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1229      return SQLITE_IGNORE
1230    }
1231    return SQLITE_OK
1232  }
1233  catchsql {CREATE INDEX i2 ON t2(b)}
1234} {0 {}}
1235do_test auth-1.183 {
1236  set ::authargs
1237} {i2 t2 main {}}
1238do_test auth-1.184 {
1239  execsql {SELECT name FROM sqlite_master}
1240} {t2}
1241do_test auth-1.185 {
1242  proc auth {code arg1 arg2 arg3 arg4} {
1243    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
1244      return SQLITE_IGNORE
1245    }
1246    return SQLITE_OK
1247  }
1248  catchsql {CREATE INDEX i2 ON t2(b)}
1249} {0 {}}
1250do_test auth-1.186 {
1251  execsql {SELECT name FROM sqlite_master}
1252} {t2}
1253do_test auth-1.187 {
1254  proc auth {code arg1 arg2 arg3 arg4} {
1255    if {$code=="SQLITE_CREATE_INDEX"} {
1256      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1257      return SQLITE_OK
1258    }
1259    return SQLITE_OK
1260  }
1261  catchsql {CREATE INDEX i2 ON t2(a)}
1262} {0 {}}
1263do_test auth-1.188 {
1264  set ::authargs
1265} {i2 t2 main {}}
1266do_test auth-1.189 {
1267  execsql {SELECT name FROM sqlite_master}
1268} {t2 i2}
1269
1270do_test auth-1.190 {
1271  proc auth {code arg1 arg2 arg3 arg4} {
1272    if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1273      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1274      return SQLITE_DENY
1275    }
1276    return SQLITE_OK
1277  }
1278  catchsql {CREATE INDEX i1 ON t1(a)}
1279} {1 {not authorized}}
1280do_test auth-1.191 {
1281  set ::authargs
1282} {i1 t1 temp {}}
1283do_test auth-1.192 {
1284  execsql {SELECT name FROM sqlite_temp_master}
1285} {t1}
1286do_test auth-1.193 {
1287  proc auth {code arg1 arg2 arg3 arg4} {
1288    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1289      return SQLITE_DENY
1290    }
1291    return SQLITE_OK
1292  }
1293  catchsql {CREATE INDEX i1 ON t1(b)}
1294} {1 {not authorized}}
1295do_test auth-1.194 {
1296  execsql {SELECT name FROM sqlite_temp_master}
1297} {t1}
1298do_test auth-1.195 {
1299  proc auth {code arg1 arg2 arg3 arg4} {
1300    if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1301      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1302      return SQLITE_IGNORE
1303    }
1304    return SQLITE_OK
1305  }
1306  catchsql {CREATE INDEX i1 ON t1(b)}
1307} {0 {}}
1308do_test auth-1.196 {
1309  set ::authargs
1310} {i1 t1 temp {}}
1311do_test auth-1.197 {
1312  execsql {SELECT name FROM sqlite_temp_master}
1313} {t1}
1314do_test auth-1.198 {
1315  proc auth {code arg1 arg2 arg3 arg4} {
1316    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
1317      return SQLITE_IGNORE
1318    }
1319    return SQLITE_OK
1320  }
1321  catchsql {CREATE INDEX i1 ON t1(c)}
1322} {0 {}}
1323do_test auth-1.199 {
1324  execsql {SELECT name FROM sqlite_temp_master}
1325} {t1}
1326do_test auth-1.200 {
1327  proc auth {code arg1 arg2 arg3 arg4} {
1328    if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
1329      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1330      return SQLITE_OK
1331    }
1332    return SQLITE_OK
1333  }
1334  catchsql {CREATE INDEX i1 ON t1(a)}
1335} {0 {}}
1336do_test auth-1.201 {
1337  set ::authargs
1338} {i1 t1 temp {}}
1339do_test auth-1.202 {
1340  execsql {SELECT name FROM sqlite_temp_master}
1341} {t1 i1}
1342
1343do_test auth-1.203 {
1344  proc auth {code arg1 arg2 arg3 arg4} {
1345    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1346      return SQLITE_DENY
1347    }
1348    return SQLITE_OK
1349  }
1350  catchsql {DROP INDEX i2}
1351} {1 {not authorized}}
1352do_test auth-1.204 {
1353  execsql {SELECT name FROM sqlite_master}
1354} {t2 i2}
1355do_test auth-1.205 {
1356  proc auth {code arg1 arg2 arg3 arg4} {
1357    if {$code=="SQLITE_DROP_INDEX"} {
1358      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1359      return SQLITE_DENY
1360    }
1361    return SQLITE_OK
1362  }
1363  catchsql {DROP INDEX i2}
1364} {1 {not authorized}}
1365do_test auth-1.206 {
1366  set ::authargs
1367} {i2 t2 main {}}
1368do_test auth-1.207 {
1369  execsql {SELECT name FROM sqlite_master}
1370} {t2 i2}
1371do_test auth-1.208 {
1372  proc auth {code arg1 arg2 arg3 arg4} {
1373    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
1374      return SQLITE_IGNORE
1375    }
1376    return SQLITE_OK
1377  }
1378  catchsql {DROP INDEX i2}
1379} {0 {}}
1380do_test auth-1.209 {
1381  execsql {SELECT name FROM sqlite_master}
1382} {t2 i2}
1383do_test auth-1.210 {
1384  proc auth {code arg1 arg2 arg3 arg4} {
1385    if {$code=="SQLITE_DROP_INDEX"} {
1386      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1387      return SQLITE_IGNORE
1388    }
1389    return SQLITE_OK
1390  }
1391  catchsql {DROP INDEX i2}
1392} {0 {}}
1393do_test auth-1.211 {
1394  set ::authargs
1395} {i2 t2 main {}}
1396do_test auth-1.212 {
1397  execsql {SELECT name FROM sqlite_master}
1398} {t2 i2}
1399do_test auth-1.213 {
1400  proc auth {code arg1 arg2 arg3 arg4} {
1401    if {$code=="SQLITE_DROP_INDEX"} {
1402      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1403      return SQLITE_OK
1404    }
1405    return SQLITE_OK
1406  }
1407  catchsql {DROP INDEX i2}
1408} {0 {}}
1409do_test auth-1.214 {
1410  set ::authargs
1411} {i2 t2 main {}}
1412do_test auth-1.215 {
1413  execsql {SELECT name FROM sqlite_master}
1414} {t2}
1415
1416do_test auth-1.216 {
1417  proc auth {code arg1 arg2 arg3 arg4} {
1418    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1419      return SQLITE_DENY
1420    }
1421    return SQLITE_OK
1422  }
1423  catchsql {DROP INDEX i1}
1424} {1 {not authorized}}
1425do_test auth-1.217 {
1426  execsql {SELECT name FROM sqlite_temp_master}
1427} {t1 i1}
1428do_test auth-1.218 {
1429  proc auth {code arg1 arg2 arg3 arg4} {
1430    if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1431      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1432      return SQLITE_DENY
1433    }
1434    return SQLITE_OK
1435  }
1436  catchsql {DROP INDEX i1}
1437} {1 {not authorized}}
1438do_test auth-1.219 {
1439  set ::authargs
1440} {i1 t1 temp {}}
1441do_test auth-1.220 {
1442  execsql {SELECT name FROM sqlite_temp_master}
1443} {t1 i1}
1444do_test auth-1.221 {
1445  proc auth {code arg1 arg2 arg3 arg4} {
1446    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
1447      return SQLITE_IGNORE
1448    }
1449    return SQLITE_OK
1450  }
1451  catchsql {DROP INDEX i1}
1452} {0 {}}
1453do_test auth-1.222 {
1454  execsql {SELECT name FROM sqlite_temp_master}
1455} {t1 i1}
1456do_test auth-1.223 {
1457  proc auth {code arg1 arg2 arg3 arg4} {
1458    if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1459      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1460      return SQLITE_IGNORE
1461    }
1462    return SQLITE_OK
1463  }
1464  catchsql {DROP INDEX i1}
1465} {0 {}}
1466do_test auth-1.224 {
1467  set ::authargs
1468} {i1 t1 temp {}}
1469do_test auth-1.225 {
1470  execsql {SELECT name FROM sqlite_temp_master}
1471} {t1 i1}
1472do_test auth-1.226 {
1473  proc auth {code arg1 arg2 arg3 arg4} {
1474    if {$code=="SQLITE_DROP_TEMP_INDEX"} {
1475      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1476      return SQLITE_OK
1477    }
1478    return SQLITE_OK
1479  }
1480  catchsql {DROP INDEX i1}
1481} {0 {}}
1482do_test auth-1.227 {
1483  set ::authargs
1484} {i1 t1 temp {}}
1485do_test auth-1.228 {
1486  execsql {SELECT name FROM sqlite_temp_master}
1487} {t1}
1488
1489do_test auth-1.229 {
1490  proc auth {code arg1 arg2 arg3 arg4} {
1491    if {$code=="SQLITE_PRAGMA"} {
1492      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1493      return SQLITE_DENY
1494    }
1495    return SQLITE_OK
1496  }
1497  catchsql {PRAGMA full_column_names=on}
1498} {1 {not authorized}}
1499do_test auth-1.230 {
1500  set ::authargs
1501} {full_column_names on {} {}}
1502do_test auth-1.231 {
1503  execsql2 {SELECT a FROM t2}
1504} {a 11 a 7}
1505do_test auth-1.232 {
1506  proc auth {code arg1 arg2 arg3 arg4} {
1507    if {$code=="SQLITE_PRAGMA"} {
1508      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1509      return SQLITE_IGNORE
1510    }
1511    return SQLITE_OK
1512  }
1513  catchsql {PRAGMA full_column_names=on}
1514} {0 {}}
1515do_test auth-1.233 {
1516  set ::authargs
1517} {full_column_names on {} {}}
1518do_test auth-1.234 {
1519  execsql2 {SELECT a FROM t2}
1520} {a 11 a 7}
1521do_test auth-1.235 {
1522  proc auth {code arg1 arg2 arg3 arg4} {
1523    if {$code=="SQLITE_PRAGMA"} {
1524      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1525      return SQLITE_OK
1526    }
1527    return SQLITE_OK
1528  }
1529  catchsql {PRAGMA full_column_names=on}
1530} {0 {}}
1531do_test auth-1.236 {
1532  execsql2 {SELECT a FROM t2}
1533} {t2.a 11 t2.a 7}
1534do_test auth-1.237 {
1535  proc auth {code arg1 arg2 arg3 arg4} {
1536    if {$code=="SQLITE_PRAGMA"} {
1537      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1538      return SQLITE_OK
1539    }
1540    return SQLITE_OK
1541  }
1542  catchsql {PRAGMA full_column_names=OFF}
1543} {0 {}}
1544do_test auth-1.238 {
1545  set ::authargs
1546} {full_column_names OFF {} {}}
1547do_test auth-1.239 {
1548  execsql2 {SELECT a FROM t2}
1549} {a 11 a 7}
1550
1551do_test auth-1.240 {
1552  proc auth {code arg1 arg2 arg3 arg4} {
1553    if {$code=="SQLITE_TRANSACTION"} {
1554      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1555      return SQLITE_DENY
1556    }
1557    return SQLITE_OK
1558  }
1559  catchsql {BEGIN}
1560} {1 {not authorized}}
1561do_test auth-1.241 {
1562  set ::authargs
1563} {BEGIN {} {} {}}
1564do_test auth-1.242 {
1565  proc auth {code arg1 arg2 arg3 arg4} {
1566    if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} {
1567      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1568      return SQLITE_DENY
1569    }
1570    return SQLITE_OK
1571  }
1572  catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT}
1573} {1 {not authorized}}
1574do_test auth-1.243 {
1575  set ::authargs
1576} {COMMIT {} {} {}}
1577do_test auth-1.244 {
1578  execsql {SELECT * FROM t2}
1579} {11 2 33 7 8 9 44 55 66}
1580do_test auth-1.245 {
1581  catchsql {ROLLBACK}
1582} {1 {not authorized}}
1583do_test auth-1.246 {
1584  set ::authargs
1585} {ROLLBACK {} {} {}}
1586do_test auth-1.247 {
1587  catchsql {END TRANSACTION}
1588} {1 {not authorized}}
1589do_test auth-1.248 {
1590  set ::authargs
1591} {COMMIT {} {} {}}
1592do_test auth-1.249 {
1593  db authorizer {}
1594  catchsql {ROLLBACK}
1595} {0 {}}
1596do_test auth-1.250 {
1597  execsql {SELECT * FROM t2}
1598} {11 2 33 7 8 9}
1599
1600# ticket #340 - authorization for ATTACH and DETACH.
1601#
1602do_test auth-1.251 {
1603  db authorizer ::auth
1604  proc auth {code arg1 arg2 arg3 arg4} {
1605    if {$code=="SQLITE_ATTACH"} {
1606      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1607    }
1608    return SQLITE_OK
1609  }
1610  catchsql {
1611    ATTACH DATABASE ':memory:' AS test1
1612  }
1613} {0 {}}
1614do_test auth-1.252 {
1615  set ::authargs
1616} {:memory: {} {} {}}
1617do_test auth-1.253 {
1618  catchsql {DETACH DATABASE test1}
1619  proc auth {code arg1 arg2 arg3 arg4} {
1620    if {$code=="SQLITE_ATTACH"} {
1621      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1622      return SQLITE_DENY
1623    }
1624    return SQLITE_OK
1625  }
1626  catchsql {
1627    ATTACH DATABASE ':memory:' AS test1;
1628  }
1629} {1 {not authorized}}
1630do_test auth-1.254 {
1631  lindex [execsql {PRAGMA database_list}] 7
1632} {}
1633do_test auth-1.255 {
1634  catchsql {DETACH DATABASE test1}
1635  proc auth {code arg1 arg2 arg3 arg4} {
1636    if {$code=="SQLITE_ATTACH"} {
1637      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1638      return SQLITE_IGNORE
1639    }
1640    return SQLITE_OK
1641  }
1642  catchsql {
1643    ATTACH DATABASE ':memory:' AS test1;
1644  }
1645} {0 {}}
1646do_test auth-1.256 {
1647  lindex [execsql {PRAGMA database_list}] 7
1648} {}
1649do_test auth-1.257 {
1650  proc auth {code arg1 arg2 arg3 arg4} {
1651    if {$code=="SQLITE_DETACH"} {
1652      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1653      return SQLITE_OK
1654    }
1655    return SQLITE_OK
1656  }
1657  execsql {ATTACH DATABASE ':memory:' AS test1}
1658  catchsql {
1659    DETACH DATABASE test1;
1660  }
1661} {0 {}}
1662do_test auth-1.258 {
1663  lindex [execsql {PRAGMA database_list}] 7
1664} {}
1665do_test auth-1.259 {
1666  execsql {ATTACH DATABASE ':memory:' AS test1}
1667  proc auth {code arg1 arg2 arg3 arg4} {
1668    if {$code=="SQLITE_DETACH"} {
1669      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1670      return SQLITE_IGNORE
1671    }
1672    return SQLITE_OK
1673  }
1674  catchsql {
1675    DETACH DATABASE test1;
1676  }
1677} {0 {}}
1678do_test auth-1.260 {
1679  lindex [execsql {PRAGMA database_list}] 7
1680} {test1}
1681do_test auth-1.261 {
1682  proc auth {code arg1 arg2 arg3 arg4} {
1683    if {$code=="SQLITE_DETACH"} {
1684      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
1685      return SQLITE_DENY
1686    }
1687    return SQLITE_OK
1688  }
1689  catchsql {
1690    DETACH DATABASE test1;
1691  }
1692} {1 {not authorized}}
1693do_test auth-1.262 {
1694  lindex [execsql {PRAGMA database_list}] 7
1695} {test1}
1696db authorizer {}
1697execsql {DETACH DATABASE test1}
1698
1699
1700do_test auth-2.1 {
1701  proc auth {code arg1 arg2 arg3 arg4} {
1702    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
1703      return SQLITE_DENY
1704    }
1705    return SQLITE_OK
1706  }
1707  db authorizer ::auth
1708  execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)}
1709  catchsql {SELECT * FROM t3}
1710} {1 {access to t3.x is prohibited}}
1711do_test auth-2.1 {
1712  catchsql {SELECT y,z FROM t3}
1713} {0 {}}
1714do_test auth-2.2 {
1715  catchsql {SELECT ROWID,y,z FROM t3}
1716} {1 {access to t3.x is prohibited}}
1717do_test auth-2.3 {
1718  catchsql {SELECT OID,y,z FROM t3}
1719} {1 {access to t3.x is prohibited}}
1720do_test auth-2.4 {
1721  proc auth {code arg1 arg2 arg3 arg4} {
1722    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
1723      return SQLITE_IGNORE
1724    }
1725    return SQLITE_OK
1726  }
1727  execsql {INSERT INTO t3 VALUES(44,55,66)}
1728  catchsql {SELECT * FROM t3}
1729} {0 {{} 55 66}}
1730do_test auth-2.5 {
1731  catchsql {SELECT rowid,y,z FROM t3}
1732} {0 {{} 55 66}}
1733do_test auth-2.6 {
1734  proc auth {code arg1 arg2 arg3 arg4} {
1735    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} {
1736      return SQLITE_IGNORE
1737    }
1738    return SQLITE_OK
1739  }
1740  catchsql {SELECT * FROM t3}
1741} {0 {44 55 66}}
1742do_test auth-2.7 {
1743  catchsql {SELECT ROWID,y,z FROM t3}
1744} {0 {44 55 66}}
1745do_test auth-2.8 {
1746  proc auth {code arg1 arg2 arg3 arg4} {
1747    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
1748      return SQLITE_IGNORE
1749    }
1750    return SQLITE_OK
1751  }
1752  catchsql {SELECT ROWID,b,c FROM t2}
1753} {0 {{} 2 33 {} 8 9}}
1754do_test auth-2.9.1 {
1755  proc auth {code arg1 arg2 arg3 arg4} {
1756    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
1757      return bogus
1758    }
1759    return SQLITE_OK
1760  }
1761  catchsql {SELECT ROWID,b,c FROM t2}
1762} {1 {illegal return value (999) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
1763do_test auth-2.9.2 {
1764  db errorcode
1765} {21}
1766do_test auth-2.10 {
1767  proc auth {code arg1 arg2 arg3 arg4} {
1768    if {$code=="SQLITE_SELECT"} {
1769      return bogus
1770    }
1771    return SQLITE_OK
1772  }
1773  catchsql {SELECT ROWID,b,c FROM t2}
1774} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
1775do_test auth-2.11.1 {
1776  proc auth {code arg1 arg2 arg3 arg4} {
1777    if {$code=="SQLITE_READ" && $arg2=="a"} {
1778      return SQLITE_IGNORE
1779    }
1780    return SQLITE_OK
1781  }
1782  catchsql {SELECT * FROM t2, t3}
1783} {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}}
1784do_test auth-2.11.2 {
1785  proc auth {code arg1 arg2 arg3 arg4} {
1786    if {$code=="SQLITE_READ" && $arg2=="x"} {
1787      return SQLITE_IGNORE
1788    }
1789    return SQLITE_OK
1790  }
1791  catchsql {SELECT * FROM t2, t3}
1792} {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}}
1793
1794# Make sure the OLD and NEW pseudo-tables of a trigger get authorized.
1795#
1796do_test auth-3.1 {
1797  proc auth {code arg1 arg2 arg3 arg4} {
1798    return SQLITE_OK
1799  }
1800  execsql {
1801    CREATE TABLE tx(a1,a2,b1,b2,c1,c2);
1802    CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
1803      INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c);
1804    END;
1805    UPDATE t2 SET a=a+1;
1806    SELECT * FROM tx;
1807  }
1808} {11 12 2 2 33 33 7 8 8 8 9 9}
1809do_test auth-3.2 {
1810  proc auth {code arg1 arg2 arg3 arg4} {
1811    if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} {
1812      return SQLITE_IGNORE
1813    }
1814    return SQLITE_OK
1815  }
1816  execsql {
1817    DELETE FROM tx;
1818    UPDATE t2 SET a=a+100;
1819    SELECT * FROM tx;
1820  }
1821} {12 112 2 2 {} {} 8 108 8 8 {} {}}
1822
1823# Make sure the names of views and triggers are passed on on arg4.
1824#
1825do_test auth-4.1 {
1826  proc auth {code arg1 arg2 arg3 arg4} {
1827    lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
1828    return SQLITE_OK
1829  }
1830  set authargs {}
1831  execsql {
1832    UPDATE t2 SET a=a+1;
1833  }
1834  set authargs
1835} [list \
1836  SQLITE_READ   t2 a  main {} \
1837  SQLITE_UPDATE t2 a  main {} \
1838  SQLITE_INSERT tx {} main r1 \
1839  SQLITE_READ   t2 a  main r1 \
1840  SQLITE_READ   t2 a  main r1 \
1841  SQLITE_READ   t2 b  main r1 \
1842  SQLITE_READ   t2 b  main r1 \
1843  SQLITE_READ   t2 c  main r1 \
1844  SQLITE_READ   t2 c  main r1]
1845do_test auth-4.2 {
1846  execsql {
1847    CREATE VIEW v1 AS SELECT a+b AS x FROM t2;
1848    CREATE TABLE v1chng(x1,x2);
1849    CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN
1850      INSERT INTO v1chng VALUES(OLD.x,NEW.x);
1851    END;
1852    SELECT * FROM v1;
1853  }
1854} {115 117}
1855do_test auth-4.3 {
1856  set authargs {}
1857  execsql {
1858    UPDATE v1 SET x=1 WHERE x=117
1859  }
1860  set authargs
1861} [list \
1862  SQLITE_UPDATE v1     x  main {} \
1863  SQLITE_READ   v1     x  main {} \
1864  SQLITE_SELECT {}     {} {}   v1 \
1865  SQLITE_READ   t2     a  main v1 \
1866  SQLITE_READ   t2     b  main v1 \
1867  SQLITE_INSERT v1chng {} main r2 \
1868  SQLITE_READ   v1     x  main r2 \
1869  SQLITE_READ   v1     x  main r2]
1870do_test auth-4.4 {
1871  execsql {
1872    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
1873      INSERT INTO v1chng VALUES(OLD.x,NULL);
1874    END;
1875    SELECT * FROM v1;
1876  }
1877} {115 117}
1878do_test auth-4.5 {
1879  set authargs {}
1880  execsql {
1881    DELETE FROM v1 WHERE x=117
1882  }
1883  set authargs
1884} [list \
1885  SQLITE_DELETE v1     {} main {} \
1886  SQLITE_READ   v1     x  main {} \
1887  SQLITE_SELECT {}     {} {}   v1 \
1888  SQLITE_READ   t2     a  main v1 \
1889  SQLITE_READ   t2     b  main v1 \
1890  SQLITE_INSERT v1chng {} main r3 \
1891  SQLITE_READ   v1     x  main r3]
1892
1893finish_test
1894