| blob | 1719ec7ad1d7ce1f8149a4e1f0e363c8e893ef55 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
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 #
21 set testdir [file dirname $argv0]
22 source $testdir/tester.tcl
24 # disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
25 # defined during compilation.
27 do_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}}
39 do_test auth-1.1.2 {
40 db errorcode
41 } {23}
42 do_test auth-1.2 {
43 execsql {SELECT name FROM sqlite_master}
44 } {}
45 do_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}}
55 do_test auth-1.3.2 {
56 db errorcode
57 } {23}
58 do_test auth-1.3.3 {
59 set ::authargs
60 } {t1 {} main {}}
61 do_test auth-1.4 {
62 execsql {SELECT name FROM sqlite_master}
63 } {}
65 do_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}}
74 do_test auth-1.6 {
75 execsql {SELECT name FROM sqlite_temp_master}
76 } {}
77 do_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}}
87 do_test auth-1.7.2 {
88 set ::authargs
89 } {t1 {} temp {}}
90 do_test auth-1.8 {
91 execsql {SELECT name FROM sqlite_temp_master}
92 } {}
94 do_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 {}}
103 do_test auth-1.10 {
104 execsql {SELECT name FROM sqlite_master}
105 } {}
106 do_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 {}}
116 do_test auth-1.12 {
117 execsql {SELECT name FROM sqlite_master}
118 } {}
119 do_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 {}}
128 do_test auth-1.14 {
129 execsql {SELECT name FROM sqlite_temp_master}
130 } {}
131 do_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 {}}
141 do_test auth-1.16 {
142 execsql {SELECT name FROM sqlite_temp_master}
143 } {}
145 do_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 {}}
155 do_test auth-1.18 {
156 execsql {SELECT name FROM sqlite_temp_master}
157 } {t1}
158 do_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 {}}
169 do_test auth-1.19.2 {
170 set ::authargs
171 } {}
172 do_test auth-1.20 {
173 execsql {SELECT name FROM sqlite_master}
174 } {t2}
176 do_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}}
186 do_test auth-1.21.2 {
187 set ::authargs
188 } {t2 {} main {}}
189 do_test auth-1.22 {
190 execsql {SELECT name FROM sqlite_master}
191 } {t2}
192 do_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 {}}
202 do_test auth-1.23.2 {
203 set ::authargs
204 } {t2 {} main {}}
205 do_test auth-1.24 {
206 execsql {SELECT name FROM sqlite_master}
207 } {t2}
209 do_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}}
219 do_test auth-1.26 {
220 execsql {SELECT name FROM sqlite_temp_master}
221 } {t1}
222 do_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 {}}
232 do_test auth-1.28 {
233 execsql {SELECT name FROM sqlite_temp_master}
234 } {t1}
236 do_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}}
245 do_test auth-1.30 {
246 execsql {SELECT * FROM t2}
247 } {}
248 do_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 {}}
257 do_test auth-1.32 {
258 execsql {SELECT * FROM t2}
259 } {}
260 do_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 {}}
269 do_test auth-1.34 {
270 execsql {SELECT * FROM t2}
271 } {1 2 3}
273 do_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}}
282 do_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}}
286 execsql {DETACH DATABASE two}
287 do_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}}
296 do_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 {}}
305 do_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}}
314 do_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}}
323 do_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}}
333 do_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 {}}
342 do_test auth-1.42 {
343 execsql {SELECT * FROM t2}
344 } {11 2 3}
345 do_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}}
354 do_test auth-1.44 {
355 execsql {SELECT * FROM t2}
356 } {11 2 3}
357 do_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 {}}
366 do_test auth-1.46 {
367 execsql {SELECT * FROM t2}
368 } {11 2 33}
370 do_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}}
379 do_test auth-1.48 {
380 execsql {SELECT * FROM t2}
381 } {11 2 33}
382 do_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 {}}
391 do_test auth-1.50 {
392 execsql {SELECT * FROM t2}
393 } {11 2 33}
395 do_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}}
404 do_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 {}}
413 do_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}}
423 set f [open data1.txt w]
424 puts $f "7:8:9"
425 close $f
426 do_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}}
436 do_test auth-1.55 {
437 set ::authargs
438 } {t2 data1.txt main {}}
439 do_test auth-1.56 {
440 execsql {SELECT * FROM t2}
441 } {11 2 33}
442 do_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 {}}
452 do_test auth-1.58 {
453 set ::authargs
454 } {t2 data1.txt main {}}
455 do_test auth-1.59 {
456 execsql {SELECT * FROM t2}
457 } {11 2 33}
458 do_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 {}}
468 do_test auth-1.61 {
469 set ::authargs
470 } {t2 data1.txt main {}}
471 do_test auth-1.62 {
472 execsql {SELECT * FROM t2}
473 } {11 2 33 7 8 9}
475 do_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}}
484 do_test auth-1.64 {
485 execsql {SELECT name FROM sqlite_master}
486 } {t2}
487 do_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}}
496 do_test auth-1.66 {
497 execsql {SELECT name FROM sqlite_master}
498 } {t2}
499 do_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}}
508 do_test auth-1.68 {
509 execsql {SELECT name FROM sqlite_temp_master}
510 } {t1}
511 do_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}}
520 do_test auth-1.70 {
521 execsql {SELECT name FROM sqlite_temp_master}
522 } {t1}
524 do_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 {}}
533 do_test auth-1.72 {
534 execsql {SELECT name FROM sqlite_master}
535 } {t2}
536 do_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 {}}
545 do_test auth-1.74 {
546 execsql {SELECT name FROM sqlite_master}
547 } {t2}
548 do_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 {}}
557 do_test auth-1.76 {
558 execsql {SELECT name FROM sqlite_temp_master}
559 } {t1}
560 do_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 {}}
569 do_test auth-1.78 {
570 execsql {SELECT name FROM sqlite_temp_master}
571 } {t1}
573 do_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}}
583 do_test auth-1.80 {
584 set ::authargs
585 } {v1 {} main {}}
586 do_test auth-1.81 {
587 execsql {SELECT name FROM sqlite_master}
588 } {t2}
589 do_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 {}}
599 do_test auth-1.83 {
600 set ::authargs
601 } {v1 {} main {}}
602 do_test auth-1.84 {
603 execsql {SELECT name FROM sqlite_master}
604 } {t2}
606 do_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}}
616 do_test auth-1.86 {
617 set ::authargs
618 } {v1 {} temp {}}
619 do_test auth-1.87 {
620 execsql {SELECT name FROM sqlite_temp_master}
621 } {t1}
622 do_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 {}}
632 do_test auth-1.89 {
633 set ::authargs
634 } {v1 {} temp {}}
635 do_test auth-1.90 {
636 execsql {SELECT name FROM sqlite_temp_master}
637 } {t1}
639 do_test auth-1.91 {
640 proc auth {code arg1 arg2 arg3 arg4} {
641 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
642 return SQLITE_DENY
643 }
644 return SQLITE_OK
645 }
646 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
647 } {1 {not authorized}}
648 do_test auth-1.92 {
649 execsql {SELECT name FROM sqlite_master}
650 } {t2}
651 do_test auth-1.93 {
652 proc auth {code arg1 arg2 arg3 arg4} {
653 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
654 return SQLITE_IGNORE
655 }
656 return SQLITE_OK
657 }
658 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
659 } {0 {}}
660 do_test auth-1.94 {
661 execsql {SELECT name FROM sqlite_master}
662 } {t2}
664 do_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}}
673 do_test auth-1.96 {
674 execsql {SELECT name FROM sqlite_temp_master}
675 } {t1}
676 do_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 {}}
685 do_test auth-1.98 {
686 execsql {SELECT name FROM sqlite_temp_master}
687 } {t1}
689 do_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}}
701 do_test auth-1.100 {
702 execsql {SELECT name FROM sqlite_master}
703 } {t2 v2}
704 do_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}}
714 do_test auth-1.102 {
715 set ::authargs
716 } {v2 {} main {}}
717 do_test auth-1.103 {
718 execsql {SELECT name FROM sqlite_master}
719 } {t2 v2}
720 do_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 {}}
729 do_test auth-1.105 {
730 execsql {SELECT name FROM sqlite_master}
731 } {t2 v2}
732 do_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 {}}
742 do_test auth-1.107 {
743 set ::authargs
744 } {v2 {} main {}}
745 do_test auth-1.108 {
746 execsql {SELECT name FROM sqlite_master}
747 } {t2 v2}
748 do_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 {}}
758 do_test auth-1.110 {
759 set ::authargs
760 } {v2 {} main {}}
761 do_test auth-1.111 {
762 execsql {SELECT name FROM sqlite_master}
763 } {t2}
766 do_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}}
778 do_test auth-1.113 {
779 execsql {SELECT name FROM sqlite_temp_master}
780 } {t1 v1}
781 do_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}}
791 do_test auth-1.115 {
792 set ::authargs
793 } {v1 {} temp {}}
794 do_test auth-1.116 {
795 execsql {SELECT name FROM sqlite_temp_master}
796 } {t1 v1}
797 do_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 {}}
806 do_test auth-1.118 {
807 execsql {SELECT name FROM sqlite_temp_master}
808 } {t1 v1}
809 do_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 {}}
819 do_test auth-1.120 {
820 set ::authargs
821 } {v1 {} temp {}}
822 do_test auth-1.121 {
823 execsql {SELECT name FROM sqlite_temp_master}
824 } {t1 v1}
825 do_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 {}}
835 do_test auth-1.123 {
836 set ::authargs
837 } {v1 {} temp {}}
838 do_test auth-1.124 {
839 execsql {SELECT name FROM sqlite_temp_master}
840 } {t1}
842 do_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}}
856 do_test auth-1.126 {
857 set ::authargs
858 } {r2 t2 main {}}
859 do_test auth-1.127 {
860 execsql {SELECT name FROM sqlite_master}
861 } {t2}
862 do_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}}
875 do_test auth-1.129 {
876 execsql {SELECT name FROM sqlite_master}
877 } {t2}
878 do_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 {}}
892 do_test auth-1.131 {
893 set ::authargs
894 } {r2 t2 main {}}
895 do_test auth-1.132 {
896 execsql {SELECT name FROM sqlite_master}
897 } {t2}
898 do_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 {}}
911 do_test auth-1.134 {
912 execsql {SELECT name FROM sqlite_master}
913 } {t2}
914 do_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 {}}
929 do_test auth-1.136.1 {
930 set ::authargs
931 } {r2 t2 main {}}
932 do_test auth-1.136.2 {
933 execsql {
934 SELECT name FROM sqlite_master WHERE type='trigger'
935 }
936 } {r2}
937 do_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}
948 do_test auth-1.136.4 {
949 execsql {
950 SELECT * FROM tx;
951 }
952 } {3}
953 do_test auth-1.137 {
954 execsql {SELECT name FROM sqlite_master}
955 } {t2 tx r2}
956 do_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}}
970 do_test auth-1.139 {
971 set ::authargs
972 } {r1 t1 temp {}}
973 do_test auth-1.140 {
974 execsql {SELECT name FROM sqlite_temp_master}
975 } {t1}
976 do_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}}
989 do_test auth-1.142 {
990 execsql {SELECT name FROM sqlite_temp_master}
991 } {t1}
992 do_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 {}}
1006 do_test auth-1.144 {
1007 set ::authargs
1008 } {r1 t1 temp {}}
1009 do_test auth-1.145 {
1010 execsql {SELECT name FROM sqlite_temp_master}
1011 } {t1}
1012 do_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 {}}
1025 do_test auth-1.147 {
1026 execsql {SELECT name FROM sqlite_temp_master}
1027 } {t1}
1028 do_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 {}}
1042 do_test auth-1.149 {
1043 set ::authargs
1044 } {r1 t1 temp {}}
1045 do_test auth-1.150 {
1046 execsql {SELECT name FROM sqlite_temp_master}
1047 } {t1 r1}
1049 do_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}}
1058 do_test auth-1.152 {
1059 execsql {SELECT name FROM sqlite_master}
1060 } {t2 tx r2}
1061 do_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}}
1071 do_test auth-1.154 {
1072 set ::authargs
1073 } {r2 t2 main {}}
1074 do_test auth-1.155 {
1075 execsql {SELECT name FROM sqlite_master}
1076 } {t2 tx r2}
1077 do_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 {}}
1086 do_test auth-1.157 {
1087 execsql {SELECT name FROM sqlite_master}
1088 } {t2 tx r2}
1089 do_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 {}}
1099 do_test auth-1.159 {
1100 set ::authargs
1101 } {r2 t2 main {}}
1102 do_test auth-1.160 {
1103 execsql {SELECT name FROM sqlite_master}
1104 } {t2 tx r2}
1105 do_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 {}}
1115 do_test auth-1.162 {
1116 set ::authargs
1117 } {r2 t2 main {}}
1118 do_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}
1126 do_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}}
1135 do_test auth-1.165 {
1136 execsql {SELECT name FROM sqlite_temp_master}
1137 } {t1 r1}
1138 do_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}}
1148 do_test auth-1.167 {
1149 set ::authargs
1150 } {r1 t1 temp {}}
1151 do_test auth-1.168 {
1152 execsql {SELECT name FROM sqlite_temp_master}
1153 } {t1 r1}
1154 do_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 {}}
1163 do_test auth-1.170 {
1164 execsql {SELECT name FROM sqlite_temp_master}
1165 } {t1 r1}
1166 do_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 {}}
1176 do_test auth-1.172 {
1177 set ::authargs
1178 } {r1 t1 temp {}}
1179 do_test auth-1.173 {
1180 execsql {SELECT name FROM sqlite_temp_master}
1181 } {t1 r1}
1182 do_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 {}}
1192 do_test auth-1.175 {
1193 set ::authargs
1194 } {r1 t1 temp {}}
1195 do_test auth-1.176 {
1196 execsql {SELECT name FROM sqlite_temp_master}
1197 } {t1}
1199 do_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}}
1209 do_test auth-1.178 {
1210 set ::authargs
1211 } {i2 t2 main {}}
1212 do_test auth-1.179 {
1213 execsql {SELECT name FROM sqlite_master}
1214 } {t2}
1215 do_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}}
1224 do_test auth-1.181 {
1225 execsql {SELECT name FROM sqlite_master}
1226 } {t2}
1227 do_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 {}}
1237 do_test auth-1.183 {
1238 set ::authargs
1239 } {i2 t2 main {}}
1240 do_test auth-1.184 {
1241 execsql {SELECT name FROM sqlite_master}
1242 } {t2}
1243 do_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 {}}
1252 do_test auth-1.186 {
1253 execsql {SELECT name FROM sqlite_master}
1254 } {t2}
1255 do_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 {}}
1265 do_test auth-1.188 {
1266 set ::authargs
1267 } {i2 t2 main {}}
1268 do_test auth-1.189 {
1269 execsql {SELECT name FROM sqlite_master}
1270 } {t2 i2}
1272 do_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}}
1282 do_test auth-1.191 {
1283 set ::authargs
1284 } {i1 t1 temp {}}
1285 do_test auth-1.192 {
1286 execsql {SELECT name FROM sqlite_temp_master}
1287 } {t1}
1288 do_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}}
1297 do_test auth-1.194 {
1298 execsql {SELECT name FROM sqlite_temp_master}
1299 } {t1}
1300 do_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 {}}
1310 do_test auth-1.196 {
1311 set ::authargs
1312 } {i1 t1 temp {}}
1313 do_test auth-1.197 {
1314 execsql {SELECT name FROM sqlite_temp_master}
1315 } {t1}
1316 do_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 {}}
1325 do_test auth-1.199 {
1326 execsql {SELECT name FROM sqlite_temp_master}
1327 } {t1}
1328 do_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 {}}
1338 do_test auth-1.201 {
1339 set ::authargs
1340 } {i1 t1 temp {}}
1341 do_test auth-1.202 {
1342 execsql {SELECT name FROM sqlite_temp_master}
1343 } {t1 i1}
1345 do_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}}
1354 do_test auth-1.204 {
1355 execsql {SELECT name FROM sqlite_master}
1356 } {t2 i2}
1357 do_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}}
1367 do_test auth-1.206 {
1368 set ::authargs
1369 } {i2 t2 main {}}
1370 do_test auth-1.207 {
1371 execsql {SELECT name FROM sqlite_master}
1372 } {t2 i2}
1373 do_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 {}}
1382 do_test auth-1.209 {
1383 execsql {SELECT name FROM sqlite_master}
1384 } {t2 i2}
1385 do_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 {}}
1395 do_test auth-1.211 {
1396 set ::authargs
1397 } {i2 t2 main {}}
1398 do_test auth-1.212 {
1399 execsql {SELECT name FROM sqlite_master}
1400 } {t2 i2}
1401 do_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 {}}
1411 do_test auth-1.214 {
1412 set ::authargs
1413 } {i2 t2 main {}}
1414 do_test auth-1.215 {
1415 execsql {SELECT name FROM sqlite_master}
1416 } {t2}
1418 do_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}}
1427 do_test auth-1.217 {
1428 execsql {SELECT name FROM sqlite_temp_master}
1429 } {t1 i1}
1430 do_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}}
1440 do_test auth-1.219 {
1441 set ::authargs
1442 } {i1 t1 temp {}}
1443 do_test auth-1.220 {
1444 execsql {SELECT name FROM sqlite_temp_master}
1445 } {t1 i1}
1446 do_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 {}}
1455 do_test auth-1.222 {
1456 execsql {SELECT name FROM sqlite_temp_master}
1457 } {t1 i1}
1458 do_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 {}}
1468 do_test auth-1.224 {
1469 set ::authargs
1470 } {i1 t1 temp {}}
1471 do_test auth-1.225 {
1472 execsql {SELECT name FROM sqlite_temp_master}
1473 } {t1 i1}
1474 do_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 {}}
1484 do_test auth-1.227 {
1485 set ::authargs
1486 } {i1 t1 temp {}}
1487 do_test auth-1.228 {
1488 execsql {SELECT name FROM sqlite_temp_master}
1489 } {t1}
1491 do_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}}
1501 do_test auth-1.230 {
1502 set ::authargs
1503 } {full_column_names on {} {}}
1504 do_test auth-1.231 {
1505 execsql2 {SELECT a FROM t2}
1506 } {a 11 a 7}
1507 do_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 {}}
1517 do_test auth-1.233 {
1518 set ::authargs
1519 } {full_column_names on {} {}}
1520 do_test auth-1.234 {
1521 execsql2 {SELECT a FROM t2}
1522 } {a 11 a 7}
1523 do_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 {}}
1533 do_test auth-1.236 {
1534 execsql2 {SELECT a FROM t2}
1535 } {t2.a 11 t2.a 7}
1536 do_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 {}}
1546 do_test auth-1.238 {
1547 set ::authargs
1548 } {full_column_names OFF {} {}}
1549 do_test auth-1.239 {
1550 execsql2 {SELECT a FROM t2}
1551 } {a 11 a 7}
1553 do_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}}
1563 do_test auth-1.241 {
1564 set ::authargs
1565 } {BEGIN {} {} {}}
1566 do_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}}
1576 do_test auth-1.243 {
1577 set ::authargs
1578 } {COMMIT {} {} {}}
1579 do_test auth-1.244 {
1580 execsql {SELECT * FROM t2}
1581 } {11 2 33 7 8 9 44 55 66}
1582 do_test auth-1.245 {
1583 catchsql {ROLLBACK}
1584 } {1 {not authorized}}
1585 do_test auth-1.246 {
1586 set ::authargs
1587 } {ROLLBACK {} {} {}}
1588 do_test auth-1.247 {
1589 catchsql {END TRANSACTION}
1590 } {1 {not authorized}}
1591 do_test auth-1.248 {
1592 set ::authargs
1593 } {COMMIT {} {} {}}
1594 do_test auth-1.249 {
1595 db authorizer {}
1596 catchsql {ROLLBACK}
1597 } {0 {}}
1598 do_test auth-1.250 {
1599 execsql {SELECT * FROM t2}
1600 } {11 2 33 7 8 9}
1602 # ticket #340 - authorization for ATTACH and DETACH.
1603 #
1604 do_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 {}}
1616 do_test auth-1.252 {
1617 set ::authargs
1618 } {:memory: {} {} {}}
1619 do_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}}
1632 do_test auth-1.254 {
1633 lindex [execsql {PRAGMA database_list}] 7
1634 } {}
1635 do_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 {}}
1648 do_test auth-1.256 {
1649 lindex [execsql {PRAGMA database_list}] 7
1650 } {}
1651 do_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 {}}
1664 do_test auth-1.258 {
1665 lindex [execsql {PRAGMA database_list}] 7
1666 } {}
1667 do_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 {}}
1680 do_test auth-1.260 {
1681 lindex [execsql {PRAGMA database_list}] 7
1682 } {test1}
1683 do_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}}
1695 do_test auth-1.262 {
1696 lindex [execsql {PRAGMA database_list}] 7
1697 } {test1}
1698 db authorizer {}
1699 execsql {DETACH DATABASE test1}
1702 do_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}}
1713 do_test auth-2.1 {
1714 catchsql {SELECT y,z FROM t3}
1715 } {0 {}}
1716 do_test auth-2.2 {
1717 catchsql {SELECT ROWID,y,z FROM t3}
1718 } {1 {access to t3.x is prohibited}}
1719 do_test auth-2.3 {
1720 catchsql {SELECT OID,y,z FROM t3}
1721 } {1 {access to t3.x is prohibited}}
1722 do_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}}
1732 do_test auth-2.5 {
1733 catchsql {SELECT rowid,y,z FROM t3}
1734 } {0 {{} 55 66}}
1735 do_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}}
1744 do_test auth-2.7 {
1745 catchsql {SELECT ROWID,y,z FROM t3}
1746 } {0 {44 55 66}}
1747 do_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}}
1756 do_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}}
1765 do_test auth-2.9.2 {
1766 db errorcode
1767 } {21}
1768 do_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}}
1777 do_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}}
1786 do_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}}
1796 # Make sure the OLD and NEW pseudo-tables of a trigger get authorized.
1797 #
1798 do_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}
1811 do_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 {} {}}
1825 # Make sure the names of views and triggers are passed on on arg4.
1826 #
1827 do_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]
1847 do_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}
1857 do_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]
1872 do_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}
1880 do_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]
1895 finish_test