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