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