Debian Codegraph @

test/auth.test

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