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