000001 # 2003 April 4 000002 # 000003 # The author disclaims copyright to this source code. In place of 000004 # a legal notice, here is a blessing: 000005 # 000006 # May you do good and not evil. 000007 # May you find forgiveness for yourself and forgive others. 000008 # May you share freely, never taking more than you give. 000009 # 000010 #*********************************************************************** 000011 # This file implements regression tests for SQLite library. The 000012 # focus of this script is testing the sqlite3_set_authorizer() API 000013 # and related functionality. 000014 # 000015 # $Id: auth.test,v 1.46 2009/07/02 18:40:35 danielk1977 Exp $ 000016 # 000017 000018 set testdir [file dirname $argv0] 000019 source $testdir/tester.tcl 000020 000021 # disable this test if the SQLITE_OMIT_AUTHORIZATION macro is 000022 # defined during compilation. 000023 if {[catch {db auth {}} msg]} { 000024 finish_test 000025 return 000026 } 000027 000028 rename proc proc_real 000029 proc_real proc {name arguments script} { 000030 proc_real $name $arguments $script 000031 if {$name=="auth"} { 000032 db authorizer ::auth 000033 } 000034 } 000035 000036 do_test auth-1.1.1 { 000037 db close 000038 set ::DB [sqlite3 db test.db] 000039 proc authx {code arg1 arg2 arg3 arg4 args} {return SQLITE_DENY} 000040 proc auth {code arg1 arg2 arg3 arg4 args} { 000041 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 000042 return SQLITE_DENY 000043 } 000044 return SQLITE_OK 000045 } 000046 db authorizer ::authx 000047 # EVIDENCE-OF: R-03993-24285 Only a single authorizer can be in place on 000048 # a database connection at a time. Each call to sqlite3_set_authorizer 000049 # overrides the previous call. 000050 # 000051 # The authx authorizer above is overridden by the auth authorizer below 000052 # authx is never invoked. 000053 db authorizer ::auth 000054 catchsql {CREATE TABLE t1(a,b,c)} 000055 } {1 {not authorized}} 000056 do_test auth-1.1.2 { 000057 db errorcode 000058 } {23} 000059 do_test auth-1.1.3 { 000060 db authorizer 000061 } {::auth} 000062 do_test auth-1.1.4 { 000063 # Ticket #896. 000064 catchsql { 000065 SELECT x; 000066 } 000067 } {1 {no such column: x}} 000068 do_test auth-1.2 { 000069 execsql {SELECT name FROM sqlite_master} 000070 } {} 000071 # EVIDENCE-OF: R-04452-49349 When the callback returns SQLITE_DENY, the 000072 # sqlite3_prepare_v2() or equivalent call that triggered the authorizer 000073 # will fail with an error message explaining that access is denied. 000074 do_test auth-1.3.1 { 000075 proc auth {code arg1 arg2 arg3 arg4 args} { 000076 if {$code=="SQLITE_CREATE_TABLE"} { 000077 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000078 return SQLITE_DENY 000079 } 000080 return SQLITE_OK 000081 } 000082 catchsql {CREATE TABLE t1(a,b,c)} 000083 } {1 {not authorized}} 000084 do_test auth-1.3.2 { 000085 db errorcode 000086 } {23} 000087 do_test auth-1.3.3 { 000088 set ::authargs 000089 } {t1 {} main {}} 000090 do_test auth-1.4 { 000091 execsql {SELECT name FROM sqlite_master} 000092 } {} 000093 000094 ifcapable tempdb { 000095 do_test auth-1.5 { 000096 proc auth {code arg1 arg2 arg3 arg4 args} { 000097 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 000098 return SQLITE_DENY 000099 } 000100 return SQLITE_OK 000101 } 000102 catchsql {CREATE TEMP TABLE t1(a,b,c)} 000103 } {1 {not authorized}} 000104 do_test auth-1.6 { 000105 execsql {SELECT name FROM temp.sqlite_master} 000106 } {} 000107 do_test auth-1.7.1 { 000108 proc auth {code arg1 arg2 arg3 arg4 args} { 000109 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 000110 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000111 return SQLITE_DENY 000112 } 000113 return SQLITE_OK 000114 } 000115 catchsql {CREATE TEMP TABLE t1(a,b,c)} 000116 } {1 {not authorized}} 000117 do_test auth-1.7.2 { 000118 set ::authargs 000119 } {t1 {} temp {}} 000120 do_test auth-1.8 { 000121 execsql {SELECT name FROM sqlite_temp_master} 000122 } {} 000123 } 000124 000125 do_test auth-1.9 { 000126 proc auth {code arg1 arg2 arg3 arg4 args} { 000127 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 000128 return SQLITE_IGNORE 000129 } 000130 return SQLITE_OK 000131 } 000132 catchsql {CREATE TABLE t1(a,b,c)} 000133 } {0 {}} 000134 do_test auth-1.10 { 000135 execsql {SELECT name FROM sqlite_master} 000136 } {} 000137 do_test auth-1.11 { 000138 proc auth {code arg1 arg2 arg3 arg4 args} { 000139 if {$code=="SQLITE_CREATE_TABLE"} { 000140 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000141 return SQLITE_IGNORE 000142 } 000143 return SQLITE_OK 000144 } 000145 catchsql {CREATE TABLE t1(a,b,c)} 000146 } {0 {}} 000147 do_test auth-1.12 { 000148 execsql {SELECT name FROM sqlite_master} 000149 } {} 000150 000151 ifcapable tempdb { 000152 do_test auth-1.13 { 000153 proc auth {code arg1 arg2 arg3 arg4 args} { 000154 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 000155 return SQLITE_IGNORE 000156 } 000157 return SQLITE_OK 000158 } 000159 catchsql {CREATE TEMP TABLE t1(a,b,c)} 000160 } {0 {}} 000161 do_test auth-1.14 { 000162 execsql {SELECT name FROM temp.sqlite_master} 000163 } {} 000164 do_test auth-1.15 { 000165 proc auth {code arg1 arg2 arg3 arg4 args} { 000166 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 000167 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000168 return SQLITE_IGNORE 000169 } 000170 return SQLITE_OK 000171 } 000172 catchsql {CREATE TEMP TABLE t1(a,b,c)} 000173 } {0 {}} 000174 do_test auth-1.16 { 000175 execsql {SELECT name FROM sqlite_temp_master} 000176 } {} 000177 000178 do_test auth-1.17 { 000179 proc auth {code arg1 arg2 arg3 arg4 args} { 000180 if {$code=="SQLITE_CREATE_TABLE"} { 000181 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000182 return SQLITE_DENY 000183 } 000184 return SQLITE_OK 000185 } 000186 catchsql {CREATE TEMP TABLE t1(a,b,c)} 000187 } {0 {}} 000188 do_test auth-1.18 { 000189 execsql {SELECT name FROM sqlite_temp_master} 000190 } {t1} 000191 } 000192 000193 do_test auth-1.19.1 { 000194 set ::authargs {} 000195 proc auth {code arg1 arg2 arg3 arg4 args} { 000196 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 000197 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000198 return SQLITE_DENY 000199 } 000200 return SQLITE_OK 000201 } 000202 catchsql {CREATE TABLE t2(a,b,c)} 000203 } {0 {}} 000204 do_test auth-1.19.2 { 000205 set ::authargs 000206 } {} 000207 do_test auth-1.20 { 000208 execsql {SELECT name FROM sqlite_master} 000209 } {t2} 000210 000211 do_test auth-1.21.1 { 000212 proc auth {code arg1 arg2 arg3 arg4 args} { 000213 if {$code=="SQLITE_DROP_TABLE"} { 000214 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000215 return SQLITE_DENY 000216 } 000217 return SQLITE_OK 000218 } 000219 catchsql {DROP TABLE t2} 000220 } {1 {not authorized}} 000221 do_test auth-1.21.2 { 000222 set ::authargs 000223 } {t2 {} main {}} 000224 do_test auth-1.22 { 000225 execsql {SELECT name FROM sqlite_master} 000226 } {t2} 000227 do_test auth-1.23.1 { 000228 proc auth {code arg1 arg2 arg3 arg4 args} { 000229 if {$code=="SQLITE_DROP_TABLE"} { 000230 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000231 return SQLITE_IGNORE 000232 } 000233 return SQLITE_OK 000234 } 000235 catchsql {DROP TABLE t2} 000236 } {0 {}} 000237 do_test auth-1.23.2 { 000238 set ::authargs 000239 } {t2 {} main {}} 000240 do_test auth-1.24 { 000241 execsql {SELECT name FROM sqlite_master} 000242 } {t2} 000243 000244 ifcapable tempdb { 000245 do_test auth-1.25 { 000246 proc auth {code arg1 arg2 arg3 arg4 args} { 000247 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 000248 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000249 return SQLITE_DENY 000250 } 000251 return SQLITE_OK 000252 } 000253 catchsql {DROP TABLE t1} 000254 } {1 {not authorized}} 000255 do_test auth-1.26 { 000256 execsql {SELECT name FROM sqlite_temp_master} 000257 } {t1} 000258 do_test auth-1.27 { 000259 proc auth {code arg1 arg2 arg3 arg4 args} { 000260 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 000261 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000262 return SQLITE_IGNORE 000263 } 000264 return SQLITE_OK 000265 } 000266 catchsql {DROP TABLE t1} 000267 } {0 {}} 000268 do_test auth-1.28 { 000269 execsql {SELECT name FROM sqlite_temp_master} 000270 } {t1} 000271 } 000272 000273 do_test auth-1.29 { 000274 proc auth {code arg1 arg2 arg3 arg4 args} { 000275 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 000276 return SQLITE_DENY 000277 } 000278 return SQLITE_OK 000279 } 000280 catchsql {INSERT INTO t2 VALUES(1,2,3)} 000281 } {1 {not authorized}} 000282 do_test auth-1.30 { 000283 execsql {SELECT * FROM t2} 000284 } {} 000285 do_test auth-1.31 { 000286 proc auth {code arg1 arg2 arg3 arg4 args} { 000287 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 000288 return SQLITE_IGNORE 000289 } 000290 return SQLITE_OK 000291 } 000292 catchsql {INSERT INTO t2 VALUES(1,2,3)} 000293 } {0 {}} 000294 do_test auth-1.32 { 000295 execsql {SELECT * FROM t2} 000296 } {} 000297 do_test auth-1.33 { 000298 proc auth {code arg1 arg2 arg3 arg4 args} { 000299 if {$code=="SQLITE_INSERT" && $arg1=="t1"} { 000300 return SQLITE_IGNORE 000301 } 000302 return SQLITE_OK 000303 } 000304 catchsql {INSERT INTO t2 VALUES(1,2,3)} 000305 } {0 {}} 000306 do_test auth-1.34 { 000307 execsql {SELECT * FROM t2} 000308 } {1 2 3} 000309 000310 do_test auth-1.35.1 { 000311 proc auth {code arg1 arg2 arg3 arg4 args} { 000312 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 000313 return SQLITE_DENY 000314 } 000315 return SQLITE_OK 000316 } 000317 catchsql {SELECT * FROM t2} 000318 } {1 {access to t2.b is prohibited}} 000319 ifcapable attach { 000320 do_test auth-1.35.2 { 000321 execsql {ATTACH DATABASE 'test.db' AS two} 000322 catchsql {SELECT * FROM two.t2} 000323 } {1 {access to two.t2.b is prohibited}} 000324 execsql {DETACH DATABASE two} 000325 } 000326 # EVIDENCE-OF: R-38392-49970 If the action code is SQLITE_READ and the 000327 # callback returns SQLITE_IGNORE then the prepared statement statement 000328 # is constructed to substitute a NULL value in place of the table column 000329 # that would have been read if SQLITE_OK had been returned. 000330 do_test auth-1.36 { 000331 proc auth {code arg1 arg2 arg3 arg4 args} { 000332 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 000333 return SQLITE_IGNORE 000334 } 000335 return SQLITE_OK 000336 } 000337 catchsql {SELECT * FROM t2} 000338 } {0 {1 {} 3}} 000339 do_test auth-1.37 { 000340 proc auth {code arg1 arg2 arg3 arg4 args} { 000341 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 000342 return SQLITE_IGNORE 000343 } 000344 return SQLITE_OK 000345 } 000346 catchsql {SELECT * FROM t2 WHERE b=2} 000347 } {0 {}} 000348 do_test auth-1.38 { 000349 proc auth {code arg1 arg2 arg3 arg4 args} { 000350 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} { 000351 return SQLITE_IGNORE 000352 } 000353 return SQLITE_OK 000354 } 000355 catchsql {SELECT * FROM t2 WHERE b=2} 000356 } {0 {{} 2 3}} 000357 do_test auth-1.39 { 000358 proc auth {code arg1 arg2 arg3 arg4 args} { 000359 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 000360 return SQLITE_IGNORE 000361 } 000362 return SQLITE_OK 000363 } 000364 catchsql {SELECT * FROM t2 WHERE b IS NULL} 000365 } {0 {1 {} 3}} 000366 do_test auth-1.40 { 000367 proc auth {code arg1 arg2 arg3 arg4 args} { 000368 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 000369 return SQLITE_DENY 000370 } 000371 return SQLITE_OK 000372 } 000373 catchsql {SELECT a,c FROM t2 WHERE b IS NULL} 000374 } {1 {access to t2.b is prohibited}} 000375 000376 do_test auth-1.41 { 000377 proc auth {code arg1 arg2 arg3 arg4 args} { 000378 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 000379 return SQLITE_DENY 000380 } 000381 return SQLITE_OK 000382 } 000383 catchsql {UPDATE t2 SET a=11} 000384 } {0 {}} 000385 do_test auth-1.42 { 000386 execsql {SELECT * FROM t2} 000387 } {11 2 3} 000388 do_test auth-1.43 { 000389 proc auth {code arg1 arg2 arg3 arg4 args} { 000390 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 000391 return SQLITE_DENY 000392 } 000393 return SQLITE_OK 000394 } 000395 catchsql {UPDATE t2 SET b=22, c=33} 000396 } {1 {not authorized}} 000397 do_test auth-1.44 { 000398 execsql {SELECT * FROM t2} 000399 } {11 2 3} 000400 do_test auth-1.45 { 000401 proc auth {code arg1 arg2 arg3 arg4 args} { 000402 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 000403 return SQLITE_IGNORE 000404 } 000405 return SQLITE_OK 000406 } 000407 catchsql {UPDATE t2 SET b=22, c=33} 000408 } {0 {}} 000409 do_test auth-1.46 { 000410 execsql {SELECT * FROM t2} 000411 } {11 2 33} 000412 000413 do_test auth-1.47 { 000414 proc auth {code arg1 arg2 arg3 arg4 args} { 000415 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 000416 return SQLITE_DENY 000417 } 000418 return SQLITE_OK 000419 } 000420 catchsql {DELETE FROM t2 WHERE a=11} 000421 } {1 {not authorized}} 000422 do_test auth-1.48 { 000423 execsql {SELECT * FROM t2} 000424 } {11 2 33} 000425 do_test auth-1.49 { 000426 proc auth {code arg1 arg2 arg3 arg4 args} { 000427 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 000428 return SQLITE_IGNORE 000429 } 000430 return SQLITE_OK 000431 } 000432 catchsql {DELETE FROM t2 WHERE a=11} 000433 } {0 {}} 000434 do_test auth-1.50 { 000435 execsql {SELECT * FROM t2} 000436 } {} 000437 do_test auth-1.50.2 { 000438 execsql {INSERT INTO t2 VALUES(11, 2, 33)} 000439 } {} 000440 000441 do_test auth-1.51 { 000442 proc auth {code arg1 arg2 arg3 arg4 args} { 000443 if {$code=="SQLITE_SELECT"} { 000444 return SQLITE_DENY 000445 } 000446 return SQLITE_OK 000447 } 000448 catchsql {SELECT * FROM t2} 000449 } {1 {not authorized}} 000450 do_test auth-1.52 { 000451 proc auth {code arg1 arg2 arg3 arg4 args} { 000452 if {$code=="SQLITE_SELECT"} { 000453 return SQLITE_IGNORE 000454 } 000455 return SQLITE_OK 000456 } 000457 catchsql {SELECT * FROM t2} 000458 } {0 {}} 000459 do_test auth-1.53 { 000460 proc auth {code arg1 arg2 arg3 arg4 args} { 000461 if {$code=="SQLITE_SELECT"} { 000462 return SQLITE_OK 000463 } 000464 return SQLITE_OK 000465 } 000466 catchsql {SELECT * FROM t2} 000467 } {0 {11 2 33}} 000468 000469 # Update for version 3: There used to be a handful of test here that 000470 # tested the authorisation callback with the COPY command. The following 000471 # test makes the same database modifications as they used to. 000472 do_test auth-1.54 { 000473 execsql {INSERT INTO t2 VALUES(7, 8, 9);} 000474 } {} 000475 do_test auth-1.55 { 000476 execsql {SELECT * FROM t2} 000477 } {11 2 33 7 8 9} 000478 000479 do_test auth-1.63 { 000480 proc auth {code arg1 arg2 arg3 arg4 args} { 000481 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 000482 return SQLITE_DENY 000483 } 000484 return SQLITE_OK 000485 } 000486 catchsql {DROP TABLE t2} 000487 } {1 {not authorized}} 000488 do_test auth-1.64 { 000489 execsql {SELECT name FROM sqlite_master} 000490 } {t2} 000491 do_test auth-1.65 { 000492 proc auth {code arg1 arg2 arg3 arg4 args} { 000493 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 000494 return SQLITE_DENY 000495 } 000496 return SQLITE_OK 000497 } 000498 catchsql {DROP TABLE t2} 000499 } {1 {not authorized}} 000500 do_test auth-1.66 { 000501 execsql {SELECT name FROM sqlite_master} 000502 } {t2} 000503 000504 ifcapable tempdb { 000505 do_test auth-1.67 { 000506 proc auth {code arg1 arg2 arg3 arg4 args} { 000507 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 000508 return SQLITE_DENY 000509 } 000510 return SQLITE_OK 000511 } 000512 catchsql {DROP TABLE t1} 000513 } {1 {not authorized}} 000514 do_test auth-1.68 { 000515 execsql {SELECT name FROM sqlite_temp_master} 000516 } {t1} 000517 do_test auth-1.69 { 000518 proc auth {code arg1 arg2 arg3 arg4 args} { 000519 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 000520 return SQLITE_DENY 000521 } 000522 return SQLITE_OK 000523 } 000524 catchsql {DROP TABLE t1} 000525 } {1 {not authorized}} 000526 do_test auth-1.70 { 000527 execsql {SELECT name FROM sqlite_temp_master} 000528 } {t1} 000529 } 000530 000531 do_test auth-1.71 { 000532 proc auth {code arg1 arg2 arg3 arg4 args} { 000533 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 000534 return SQLITE_IGNORE 000535 } 000536 return SQLITE_OK 000537 } 000538 catchsql {DROP TABLE t2} 000539 } {0 {}} 000540 do_test auth-1.72 { 000541 execsql {SELECT name FROM sqlite_master} 000542 } {t2} 000543 do_test auth-1.73 { 000544 proc auth {code arg1 arg2 arg3 arg4 args} { 000545 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 000546 return SQLITE_IGNORE 000547 } 000548 return SQLITE_OK 000549 } 000550 catchsql {DROP TABLE t2} 000551 } {0 {}} 000552 do_test auth-1.74 { 000553 execsql {SELECT name FROM sqlite_master} 000554 } {t2} 000555 000556 ifcapable tempdb { 000557 do_test auth-1.75 { 000558 proc auth {code arg1 arg2 arg3 arg4 args} { 000559 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 000560 return SQLITE_IGNORE 000561 } 000562 return SQLITE_OK 000563 } 000564 catchsql {DROP TABLE t1} 000565 } {0 {}} 000566 do_test auth-1.76 { 000567 execsql {SELECT name FROM sqlite_temp_master} 000568 } {t1} 000569 do_test auth-1.77 { 000570 proc auth {code arg1 arg2 arg3 arg4 args} { 000571 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 000572 return SQLITE_IGNORE 000573 } 000574 return SQLITE_OK 000575 } 000576 catchsql {DROP TABLE t1} 000577 } {0 {}} 000578 do_test auth-1.78 { 000579 execsql {SELECT name FROM temp.sqlite_master} 000580 } {t1} 000581 } 000582 000583 # Test cases auth-1.79 to auth-1.124 test creating and dropping views. 000584 # Omit these if the library was compiled with views omitted. 000585 ifcapable view { 000586 do_test auth-1.79 { 000587 proc auth {code arg1 arg2 arg3 arg4 args} { 000588 if {$code=="SQLITE_CREATE_VIEW"} { 000589 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000590 return SQLITE_DENY 000591 } 000592 return SQLITE_OK 000593 } 000594 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 000595 } {1 {not authorized}} 000596 do_test auth-1.80 { 000597 set ::authargs 000598 } {v1 {} main {}} 000599 do_test auth-1.81 { 000600 execsql {SELECT name FROM sqlite_master} 000601 } {t2} 000602 do_test auth-1.82 { 000603 proc auth {code arg1 arg2 arg3 arg4 args} { 000604 if {$code=="SQLITE_CREATE_VIEW"} { 000605 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000606 return SQLITE_IGNORE 000607 } 000608 return SQLITE_OK 000609 } 000610 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 000611 } {0 {}} 000612 do_test auth-1.83 { 000613 set ::authargs 000614 } {v1 {} main {}} 000615 do_test auth-1.84 { 000616 execsql {SELECT name FROM sqlite_master} 000617 } {t2} 000618 000619 ifcapable tempdb { 000620 do_test auth-1.85 { 000621 proc auth {code arg1 arg2 arg3 arg4 args} { 000622 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 000623 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000624 return SQLITE_DENY 000625 } 000626 return SQLITE_OK 000627 } 000628 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 000629 } {1 {not authorized}} 000630 do_test auth-1.86 { 000631 set ::authargs 000632 } {v1 {} temp {}} 000633 do_test auth-1.87 { 000634 execsql {SELECT name FROM sqlite_temp_master} 000635 } {t1} 000636 do_test auth-1.88 { 000637 proc auth {code arg1 arg2 arg3 arg4 args} { 000638 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 000639 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000640 return SQLITE_IGNORE 000641 } 000642 return SQLITE_OK 000643 } 000644 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 000645 } {0 {}} 000646 do_test auth-1.89 { 000647 set ::authargs 000648 } {v1 {} temp {}} 000649 do_test auth-1.90 { 000650 execsql {SELECT name FROM temp.sqlite_master} 000651 } {t1} 000652 } 000653 000654 do_test auth-1.91 { 000655 proc auth {code arg1 arg2 arg3 arg4 args} { 000656 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 000657 return SQLITE_DENY 000658 } 000659 return SQLITE_OK 000660 } 000661 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 000662 } {1 {not authorized}} 000663 do_test auth-1.92 { 000664 execsql {SELECT name FROM sqlite_master} 000665 } {t2} 000666 do_test auth-1.93 { 000667 proc auth {code arg1 arg2 arg3 arg4 args} { 000668 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 000669 return SQLITE_IGNORE 000670 } 000671 return SQLITE_OK 000672 } 000673 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 000674 } {0 {}} 000675 do_test auth-1.94 { 000676 execsql {SELECT name FROM sqlite_master} 000677 } {t2} 000678 000679 ifcapable tempdb { 000680 do_test auth-1.95 { 000681 proc auth {code arg1 arg2 arg3 arg4 args} { 000682 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 000683 return SQLITE_DENY 000684 } 000685 return SQLITE_OK 000686 } 000687 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 000688 } {1 {not authorized}} 000689 do_test auth-1.96 { 000690 execsql {SELECT name FROM sqlite_temp_master} 000691 } {t1} 000692 do_test auth-1.97 { 000693 proc auth {code arg1 arg2 arg3 arg4 args} { 000694 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 000695 return SQLITE_IGNORE 000696 } 000697 return SQLITE_OK 000698 } 000699 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 000700 } {0 {}} 000701 do_test auth-1.98 { 000702 execsql {SELECT name FROM sqlite_temp_master} 000703 } {t1} 000704 } 000705 000706 do_test auth-1.99 { 000707 proc auth {code arg1 arg2 arg3 arg4 args} { 000708 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 000709 return SQLITE_DENY 000710 } 000711 return SQLITE_OK 000712 } 000713 catchsql { 000714 CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2; 000715 DROP VIEW v2 000716 } 000717 } {1 {not authorized}} 000718 do_test auth-1.100 { 000719 execsql {SELECT name FROM sqlite_master} 000720 } {t2 v2} 000721 do_test auth-1.101 { 000722 proc auth {code arg1 arg2 arg3 arg4 args} { 000723 if {$code=="SQLITE_DROP_VIEW"} { 000724 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000725 return SQLITE_DENY 000726 } 000727 return SQLITE_OK 000728 } 000729 catchsql {DROP VIEW v2} 000730 } {1 {not authorized}} 000731 do_test auth-1.102 { 000732 set ::authargs 000733 } {v2 {} main {}} 000734 do_test auth-1.103 { 000735 execsql {SELECT name FROM sqlite_master} 000736 } {t2 v2} 000737 do_test auth-1.104 { 000738 proc auth {code arg1 arg2 arg3 arg4 args} { 000739 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 000740 return SQLITE_IGNORE 000741 } 000742 return SQLITE_OK 000743 } 000744 catchsql {DROP VIEW v2} 000745 } {0 {}} 000746 do_test auth-1.105 { 000747 execsql {SELECT name FROM sqlite_master} 000748 } {t2 v2} 000749 do_test auth-1.106 { 000750 proc auth {code arg1 arg2 arg3 arg4 args} { 000751 if {$code=="SQLITE_DROP_VIEW"} { 000752 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000753 return SQLITE_IGNORE 000754 } 000755 return SQLITE_OK 000756 } 000757 catchsql {DROP VIEW v2} 000758 } {0 {}} 000759 do_test auth-1.107 { 000760 set ::authargs 000761 } {v2 {} main {}} 000762 do_test auth-1.108 { 000763 execsql {SELECT name FROM sqlite_master} 000764 } {t2 v2} 000765 do_test auth-1.109 { 000766 proc auth {code arg1 arg2 arg3 arg4 args} { 000767 if {$code=="SQLITE_DROP_VIEW"} { 000768 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000769 return SQLITE_OK 000770 } 000771 return SQLITE_OK 000772 } 000773 catchsql {DROP VIEW v2} 000774 } {0 {}} 000775 do_test auth-1.110 { 000776 set ::authargs 000777 } {v2 {} main {}} 000778 do_test auth-1.111 { 000779 execsql {SELECT name FROM sqlite_master} 000780 } {t2} 000781 000782 000783 ifcapable tempdb { 000784 do_test auth-1.112 { 000785 proc auth {code arg1 arg2 arg3 arg4 args} { 000786 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 000787 return SQLITE_DENY 000788 } 000789 return SQLITE_OK 000790 } 000791 catchsql { 000792 CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1; 000793 DROP VIEW v1 000794 } 000795 } {1 {not authorized}} 000796 do_test auth-1.113 { 000797 execsql {SELECT name FROM temp.sqlite_master} 000798 } {t1 v1} 000799 do_test auth-1.114 { 000800 proc auth {code arg1 arg2 arg3 arg4 args} { 000801 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 000802 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000803 return SQLITE_DENY 000804 } 000805 return SQLITE_OK 000806 } 000807 catchsql {DROP VIEW v1} 000808 } {1 {not authorized}} 000809 do_test auth-1.115 { 000810 set ::authargs 000811 } {v1 {} temp {}} 000812 do_test auth-1.116 { 000813 execsql {SELECT name FROM sqlite_temp_master} 000814 } {t1 v1} 000815 do_test auth-1.117 { 000816 proc auth {code arg1 arg2 arg3 arg4 args} { 000817 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 000818 return SQLITE_IGNORE 000819 } 000820 return SQLITE_OK 000821 } 000822 catchsql {DROP VIEW v1} 000823 } {0 {}} 000824 do_test auth-1.118 { 000825 execsql {SELECT name FROM sqlite_temp_master} 000826 } {t1 v1} 000827 do_test auth-1.119 { 000828 proc auth {code arg1 arg2 arg3 arg4 args} { 000829 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 000830 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000831 return SQLITE_IGNORE 000832 } 000833 return SQLITE_OK 000834 } 000835 catchsql {DROP VIEW v1} 000836 } {0 {}} 000837 do_test auth-1.120 { 000838 set ::authargs 000839 } {v1 {} temp {}} 000840 do_test auth-1.121 { 000841 execsql {SELECT name FROM temp.sqlite_master} 000842 } {t1 v1} 000843 do_test auth-1.122 { 000844 proc auth {code arg1 arg2 arg3 arg4 args} { 000845 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 000846 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000847 return SQLITE_OK 000848 } 000849 return SQLITE_OK 000850 } 000851 catchsql {DROP VIEW v1} 000852 } {0 {}} 000853 do_test auth-1.123 { 000854 set ::authargs 000855 } {v1 {} temp {}} 000856 do_test auth-1.124 { 000857 execsql {SELECT name FROM sqlite_temp_master} 000858 } {t1} 000859 } 000860 } ;# ifcapable view 000861 000862 # Test cases auth-1.125 to auth-1.176 test creating and dropping triggers. 000863 # Omit these if the library was compiled with triggers omitted. 000864 # 000865 ifcapable trigger&&tempdb { 000866 do_test auth-1.125 { 000867 proc auth {code arg1 arg2 arg3 arg4 args} { 000868 if {$code=="SQLITE_CREATE_TRIGGER"} { 000869 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000870 return SQLITE_DENY 000871 } 000872 return SQLITE_OK 000873 } 000874 catchsql { 000875 CREATE TRIGGER r2 DELETE on t2 BEGIN 000876 SELECT NULL; 000877 END; 000878 } 000879 } {1 {not authorized}} 000880 do_test auth-1.126 { 000881 set ::authargs 000882 } {r2 t2 main {}} 000883 do_test auth-1.127 { 000884 execsql {SELECT name FROM sqlite_master} 000885 } {t2} 000886 do_test auth-1.128 { 000887 proc auth {code arg1 arg2 arg3 arg4 args} { 000888 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 000889 return SQLITE_DENY 000890 } 000891 return SQLITE_OK 000892 } 000893 catchsql { 000894 CREATE TRIGGER r2 DELETE on t2 BEGIN 000895 SELECT NULL; 000896 END; 000897 } 000898 } {1 {not authorized}} 000899 do_test auth-1.129 { 000900 execsql {SELECT name FROM sqlite_master} 000901 } {t2} 000902 do_test auth-1.130 { 000903 proc auth {code arg1 arg2 arg3 arg4 args} { 000904 if {$code=="SQLITE_CREATE_TRIGGER"} { 000905 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000906 return SQLITE_IGNORE 000907 } 000908 return SQLITE_OK 000909 } 000910 catchsql { 000911 CREATE TRIGGER r2 DELETE on t2 BEGIN 000912 SELECT NULL; 000913 END; 000914 } 000915 } {0 {}} 000916 do_test auth-1.131 { 000917 set ::authargs 000918 } {r2 t2 main {}} 000919 do_test auth-1.132 { 000920 execsql {SELECT name FROM sqlite_master} 000921 } {t2} 000922 do_test auth-1.133 { 000923 proc auth {code arg1 arg2 arg3 arg4 args} { 000924 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 000925 return SQLITE_IGNORE 000926 } 000927 return SQLITE_OK 000928 } 000929 catchsql { 000930 CREATE TRIGGER r2 DELETE on t2 BEGIN 000931 SELECT NULL; 000932 END; 000933 } 000934 } {0 {}} 000935 do_test auth-1.134 { 000936 execsql {SELECT name FROM sqlite_master} 000937 } {t2} 000938 do_test auth-1.135 { 000939 proc auth {code arg1 arg2 arg3 arg4 args} { 000940 if {$code=="SQLITE_CREATE_TRIGGER"} { 000941 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000942 return SQLITE_OK 000943 } 000944 return SQLITE_OK 000945 } 000946 catchsql { 000947 CREATE TABLE tx(id); 000948 CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN 000949 INSERT INTO tx VALUES(NEW.rowid); 000950 END; 000951 } 000952 } {0 {}} 000953 do_test auth-1.136.1 { 000954 set ::authargs 000955 } {r2 t2 main {}} 000956 do_test auth-1.136.2 { 000957 execsql { 000958 SELECT name FROM sqlite_master WHERE type='trigger' 000959 } 000960 } {r2} 000961 do_test auth-1.136.3 { 000962 proc auth {code arg1 arg2 arg3 arg4 args} { 000963 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 000964 return SQLITE_OK 000965 } 000966 set ::authargs {} 000967 execsql { 000968 INSERT INTO t2 VALUES(1,2,3); 000969 } 000970 set ::authargs 000971 } {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2} 000972 do_test auth-1.136.4 { 000973 execsql { 000974 SELECT * FROM tx; 000975 } 000976 } {3} 000977 do_test auth-1.137 { 000978 execsql {SELECT name FROM sqlite_master} 000979 } {t2 tx r2} 000980 do_test auth-1.138 { 000981 proc auth {code arg1 arg2 arg3 arg4 args} { 000982 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 000983 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 000984 return SQLITE_DENY 000985 } 000986 return SQLITE_OK 000987 } 000988 catchsql { 000989 CREATE TRIGGER r1 DELETE on t1 BEGIN 000990 SELECT NULL; 000991 END; 000992 } 000993 } {1 {not authorized}} 000994 do_test auth-1.139 { 000995 set ::authargs 000996 } {r1 t1 temp {}} 000997 do_test auth-1.140 { 000998 execsql {SELECT name FROM temp.sqlite_master} 000999 } {t1} 001000 do_test auth-1.141 { 001001 proc auth {code arg1 arg2 arg3 arg4 args} { 001002 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 001003 return SQLITE_DENY 001004 } 001005 return SQLITE_OK 001006 } 001007 catchsql { 001008 CREATE TRIGGER r1 DELETE on t1 BEGIN 001009 SELECT NULL; 001010 END; 001011 } 001012 } {1 {not authorized}} 001013 do_test auth-1.142 { 001014 execsql {SELECT name FROM sqlite_temp_master} 001015 } {t1} 001016 do_test auth-1.143 { 001017 proc auth {code arg1 arg2 arg3 arg4 args} { 001018 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 001019 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001020 return SQLITE_IGNORE 001021 } 001022 return SQLITE_OK 001023 } 001024 catchsql { 001025 CREATE TRIGGER r1 DELETE on t1 BEGIN 001026 SELECT NULL; 001027 END; 001028 } 001029 } {0 {}} 001030 do_test auth-1.144 { 001031 set ::authargs 001032 } {r1 t1 temp {}} 001033 do_test auth-1.145 { 001034 execsql {SELECT name FROM temp.sqlite_master} 001035 } {t1} 001036 do_test auth-1.146 { 001037 proc auth {code arg1 arg2 arg3 arg4 args} { 001038 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 001039 return SQLITE_IGNORE 001040 } 001041 return SQLITE_OK 001042 } 001043 catchsql { 001044 CREATE TRIGGER r1 DELETE on t1 BEGIN 001045 SELECT NULL; 001046 END; 001047 } 001048 } {0 {}} 001049 do_test auth-1.147 { 001050 execsql {SELECT name FROM sqlite_temp_master} 001051 } {t1} 001052 do_test auth-1.148 { 001053 proc auth {code arg1 arg2 arg3 arg4 args} { 001054 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 001055 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001056 return SQLITE_OK 001057 } 001058 return SQLITE_OK 001059 } 001060 catchsql { 001061 CREATE TRIGGER r1 DELETE on t1 BEGIN 001062 SELECT NULL; 001063 END; 001064 } 001065 } {0 {}} 001066 do_test auth-1.149 { 001067 set ::authargs 001068 } {r1 t1 temp {}} 001069 do_test auth-1.150 { 001070 execsql {SELECT name FROM temp.sqlite_master} 001071 } {t1 r1} 001072 001073 do_test auth-1.151 { 001074 proc auth {code arg1 arg2 arg3 arg4 args} { 001075 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 001076 return SQLITE_DENY 001077 } 001078 return SQLITE_OK 001079 } 001080 catchsql {DROP TRIGGER r2} 001081 } {1 {not authorized}} 001082 do_test auth-1.152 { 001083 execsql {SELECT name FROM sqlite_master} 001084 } {t2 tx r2} 001085 do_test auth-1.153 { 001086 proc auth {code arg1 arg2 arg3 arg4 args} { 001087 if {$code=="SQLITE_DROP_TRIGGER"} { 001088 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001089 return SQLITE_DENY 001090 } 001091 return SQLITE_OK 001092 } 001093 catchsql {DROP TRIGGER r2} 001094 } {1 {not authorized}} 001095 do_test auth-1.154 { 001096 set ::authargs 001097 } {r2 t2 main {}} 001098 do_test auth-1.155 { 001099 execsql {SELECT name FROM sqlite_master} 001100 } {t2 tx r2} 001101 do_test auth-1.156 { 001102 proc auth {code arg1 arg2 arg3 arg4 args} { 001103 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 001104 return SQLITE_IGNORE 001105 } 001106 return SQLITE_OK 001107 } 001108 catchsql {DROP TRIGGER r2} 001109 } {0 {}} 001110 do_test auth-1.157 { 001111 execsql {SELECT name FROM sqlite_master} 001112 } {t2 tx r2} 001113 do_test auth-1.158 { 001114 proc auth {code arg1 arg2 arg3 arg4 args} { 001115 if {$code=="SQLITE_DROP_TRIGGER"} { 001116 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001117 return SQLITE_IGNORE 001118 } 001119 return SQLITE_OK 001120 } 001121 catchsql {DROP TRIGGER r2} 001122 } {0 {}} 001123 do_test auth-1.159 { 001124 set ::authargs 001125 } {r2 t2 main {}} 001126 do_test auth-1.160 { 001127 execsql {SELECT name FROM sqlite_master} 001128 } {t2 tx r2} 001129 do_test auth-1.161 { 001130 proc auth {code arg1 arg2 arg3 arg4 args} { 001131 if {$code=="SQLITE_DROP_TRIGGER"} { 001132 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001133 return SQLITE_OK 001134 } 001135 return SQLITE_OK 001136 } 001137 catchsql {DROP TRIGGER r2} 001138 } {0 {}} 001139 do_test auth-1.162 { 001140 set ::authargs 001141 } {r2 t2 main {}} 001142 do_test auth-1.163 { 001143 execsql { 001144 DROP TABLE tx; 001145 DELETE FROM t2 WHERE a=1 AND b=2 AND c=3; 001146 SELECT name FROM sqlite_master; 001147 } 001148 } {t2} 001149 001150 do_test auth-1.164 { 001151 proc auth {code arg1 arg2 arg3 arg4 args} { 001152 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 001153 return SQLITE_DENY 001154 } 001155 return SQLITE_OK 001156 } 001157 catchsql {DROP TRIGGER r1} 001158 } {1 {not authorized}} 001159 do_test auth-1.165 { 001160 execsql {SELECT name FROM temp.sqlite_master} 001161 } {t1 r1} 001162 do_test auth-1.166 { 001163 proc auth {code arg1 arg2 arg3 arg4 args} { 001164 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 001165 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001166 return SQLITE_DENY 001167 } 001168 return SQLITE_OK 001169 } 001170 catchsql {DROP TRIGGER r1} 001171 } {1 {not authorized}} 001172 do_test auth-1.167 { 001173 set ::authargs 001174 } {r1 t1 temp {}} 001175 do_test auth-1.168 { 001176 execsql {SELECT name FROM sqlite_temp_master} 001177 } {t1 r1} 001178 do_test auth-1.169 { 001179 proc auth {code arg1 arg2 arg3 arg4 args} { 001180 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 001181 return SQLITE_IGNORE 001182 } 001183 return SQLITE_OK 001184 } 001185 catchsql {DROP TRIGGER r1} 001186 } {0 {}} 001187 do_test auth-1.170 { 001188 execsql {SELECT name FROM temp.sqlite_master} 001189 } {t1 r1} 001190 do_test auth-1.171 { 001191 proc auth {code arg1 arg2 arg3 arg4 args} { 001192 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 001193 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001194 return SQLITE_IGNORE 001195 } 001196 return SQLITE_OK 001197 } 001198 catchsql {DROP TRIGGER r1} 001199 } {0 {}} 001200 do_test auth-1.172 { 001201 set ::authargs 001202 } {r1 t1 temp {}} 001203 do_test auth-1.173 { 001204 execsql {SELECT name FROM sqlite_temp_master} 001205 } {t1 r1} 001206 do_test auth-1.174 { 001207 proc auth {code arg1 arg2 arg3 arg4 args} { 001208 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 001209 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001210 return SQLITE_OK 001211 } 001212 return SQLITE_OK 001213 } 001214 catchsql {DROP TRIGGER r1} 001215 } {0 {}} 001216 do_test auth-1.175 { 001217 set ::authargs 001218 } {r1 t1 temp {}} 001219 do_test auth-1.176 { 001220 execsql {SELECT name FROM temp.sqlite_master} 001221 } {t1} 001222 } ;# ifcapable trigger 001223 001224 do_test auth-1.177 { 001225 proc auth {code arg1 arg2 arg3 arg4 args} { 001226 if {$code=="SQLITE_CREATE_INDEX"} { 001227 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001228 return SQLITE_DENY 001229 } 001230 return SQLITE_OK 001231 } 001232 catchsql {CREATE INDEX i2 ON t2(a)} 001233 } {1 {not authorized}} 001234 do_test auth-1.178 { 001235 set ::authargs 001236 } {i2 t2 main {}} 001237 do_test auth-1.179 { 001238 execsql {SELECT name FROM sqlite_master} 001239 } {t2} 001240 do_test auth-1.180 { 001241 proc auth {code arg1 arg2 arg3 arg4 args} { 001242 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 001243 return SQLITE_DENY 001244 } 001245 return SQLITE_OK 001246 } 001247 catchsql {CREATE INDEX i2 ON t2(a)} 001248 } {1 {not authorized}} 001249 do_test auth-1.181 { 001250 execsql {SELECT name FROM sqlite_master} 001251 } {t2} 001252 do_test auth-1.182 { 001253 proc auth {code arg1 arg2 arg3 arg4 args} { 001254 if {$code=="SQLITE_CREATE_INDEX"} { 001255 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001256 return SQLITE_IGNORE 001257 } 001258 return SQLITE_OK 001259 } 001260 catchsql {CREATE INDEX i2 ON t2(b)} 001261 } {0 {}} 001262 do_test auth-1.183 { 001263 set ::authargs 001264 } {i2 t2 main {}} 001265 do_test auth-1.184 { 001266 execsql {SELECT name FROM sqlite_master} 001267 } {t2} 001268 do_test auth-1.185 { 001269 proc auth {code arg1 arg2 arg3 arg4 args} { 001270 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 001271 return SQLITE_IGNORE 001272 } 001273 return SQLITE_OK 001274 } 001275 catchsql {CREATE INDEX i2 ON t2(b)} 001276 } {0 {}} 001277 do_test auth-1.186 { 001278 execsql {SELECT name FROM sqlite_master} 001279 } {t2} 001280 do_test auth-1.187 { 001281 proc auth {code arg1 arg2 arg3 arg4 args} { 001282 if {$code=="SQLITE_CREATE_INDEX"} { 001283 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001284 return SQLITE_OK 001285 } 001286 return SQLITE_OK 001287 } 001288 catchsql {CREATE INDEX i2 ON t2(a)} 001289 } {0 {}} 001290 do_test auth-1.188 { 001291 set ::authargs 001292 } {i2 t2 main {}} 001293 do_test auth-1.189 { 001294 execsql {SELECT name FROM sqlite_master} 001295 } {t2 i2} 001296 001297 ifcapable tempdb { 001298 do_test auth-1.190 { 001299 proc auth {code arg1 arg2 arg3 arg4 args} { 001300 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 001301 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001302 return SQLITE_DENY 001303 } 001304 return SQLITE_OK 001305 } 001306 catchsql {CREATE INDEX i1 ON t1(a)} 001307 } {1 {not authorized}} 001308 do_test auth-1.191 { 001309 set ::authargs 001310 } {i1 t1 temp {}} 001311 do_test auth-1.192 { 001312 execsql {SELECT name FROM sqlite_temp_master} 001313 } {t1} 001314 do_test auth-1.193 { 001315 proc auth {code arg1 arg2 arg3 arg4 args} { 001316 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 001317 return SQLITE_DENY 001318 } 001319 return SQLITE_OK 001320 } 001321 catchsql {CREATE INDEX i1 ON t1(b)} 001322 } {1 {not authorized}} 001323 do_test auth-1.194 { 001324 execsql {SELECT name FROM temp.sqlite_master} 001325 } {t1} 001326 do_test auth-1.195 { 001327 proc auth {code arg1 arg2 arg3 arg4 args} { 001328 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 001329 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001330 return SQLITE_IGNORE 001331 } 001332 return SQLITE_OK 001333 } 001334 catchsql {CREATE INDEX i1 ON t1(b)} 001335 } {0 {}} 001336 do_test auth-1.196 { 001337 set ::authargs 001338 } {i1 t1 temp {}} 001339 do_test auth-1.197 { 001340 execsql {SELECT name FROM sqlite_temp_master} 001341 } {t1} 001342 do_test auth-1.198 { 001343 proc auth {code arg1 arg2 arg3 arg4 args} { 001344 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 001345 return SQLITE_IGNORE 001346 } 001347 return SQLITE_OK 001348 } 001349 catchsql {CREATE INDEX i1 ON t1(c)} 001350 } {0 {}} 001351 do_test auth-1.199 { 001352 execsql {SELECT name FROM sqlite_temp_master} 001353 } {t1} 001354 do_test auth-1.200 { 001355 proc auth {code arg1 arg2 arg3 arg4 args} { 001356 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 001357 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001358 return SQLITE_OK 001359 } 001360 return SQLITE_OK 001361 } 001362 catchsql {CREATE INDEX i1 ON t1(a)} 001363 } {0 {}} 001364 do_test auth-1.201 { 001365 set ::authargs 001366 } {i1 t1 temp {}} 001367 do_test auth-1.202 { 001368 execsql {SELECT name FROM temp.sqlite_master} 001369 } {t1 i1} 001370 } 001371 001372 do_test auth-1.203 { 001373 proc auth {code arg1 arg2 arg3 arg4 args} { 001374 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 001375 return SQLITE_DENY 001376 } 001377 return SQLITE_OK 001378 } 001379 catchsql {DROP INDEX i2} 001380 } {1 {not authorized}} 001381 do_test auth-1.204 { 001382 execsql {SELECT name FROM sqlite_master} 001383 } {t2 i2} 001384 do_test auth-1.205 { 001385 proc auth {code arg1 arg2 arg3 arg4 args} { 001386 if {$code=="SQLITE_DROP_INDEX"} { 001387 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001388 return SQLITE_DENY 001389 } 001390 return SQLITE_OK 001391 } 001392 catchsql {DROP INDEX i2} 001393 } {1 {not authorized}} 001394 do_test auth-1.206 { 001395 set ::authargs 001396 } {i2 t2 main {}} 001397 do_test auth-1.207 { 001398 execsql {SELECT name FROM sqlite_master} 001399 } {t2 i2} 001400 do_test auth-1.208 { 001401 proc auth {code arg1 arg2 arg3 arg4 args} { 001402 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 001403 return SQLITE_IGNORE 001404 } 001405 return SQLITE_OK 001406 } 001407 catchsql {DROP INDEX i2} 001408 } {0 {}} 001409 do_test auth-1.209 { 001410 execsql {SELECT name FROM sqlite_master} 001411 } {t2 i2} 001412 do_test auth-1.210 { 001413 proc auth {code arg1 arg2 arg3 arg4 args} { 001414 if {$code=="SQLITE_DROP_INDEX"} { 001415 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001416 return SQLITE_IGNORE 001417 } 001418 return SQLITE_OK 001419 } 001420 catchsql {DROP INDEX i2} 001421 } {0 {}} 001422 do_test auth-1.211 { 001423 set ::authargs 001424 } {i2 t2 main {}} 001425 do_test auth-1.212 { 001426 execsql {SELECT name FROM sqlite_master} 001427 } {t2 i2} 001428 do_test auth-1.213 { 001429 proc auth {code arg1 arg2 arg3 arg4 args} { 001430 if {$code=="SQLITE_DROP_INDEX"} { 001431 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001432 return SQLITE_OK 001433 } 001434 return SQLITE_OK 001435 } 001436 catchsql {DROP INDEX i2} 001437 } {0 {}} 001438 do_test auth-1.214 { 001439 set ::authargs 001440 } {i2 t2 main {}} 001441 do_test auth-1.215 { 001442 execsql {SELECT name FROM sqlite_master} 001443 } {t2} 001444 001445 ifcapable tempdb { 001446 do_test auth-1.216 { 001447 proc auth {code arg1 arg2 arg3 arg4 args} { 001448 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 001449 return SQLITE_DENY 001450 } 001451 return SQLITE_OK 001452 } 001453 catchsql {DROP INDEX i1} 001454 } {1 {not authorized}} 001455 do_test auth-1.217 { 001456 execsql {SELECT name FROM sqlite_temp_master} 001457 } {t1 i1} 001458 do_test auth-1.218 { 001459 proc auth {code arg1 arg2 arg3 arg4 args} { 001460 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 001461 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001462 return SQLITE_DENY 001463 } 001464 return SQLITE_OK 001465 } 001466 catchsql {DROP INDEX i1} 001467 } {1 {not authorized}} 001468 do_test auth-1.219 { 001469 set ::authargs 001470 } {i1 t1 temp {}} 001471 do_test auth-1.220 { 001472 execsql {SELECT name FROM sqlite_temp_master} 001473 } {t1 i1} 001474 do_test auth-1.221 { 001475 proc auth {code arg1 arg2 arg3 arg4 args} { 001476 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 001477 return SQLITE_IGNORE 001478 } 001479 return SQLITE_OK 001480 } 001481 catchsql {DROP INDEX i1} 001482 } {0 {}} 001483 do_test auth-1.222 { 001484 execsql {SELECT name FROM temp.sqlite_master} 001485 } {t1 i1} 001486 do_test auth-1.223 { 001487 proc auth {code arg1 arg2 arg3 arg4 args} { 001488 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 001489 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001490 return SQLITE_IGNORE 001491 } 001492 return SQLITE_OK 001493 } 001494 catchsql {DROP INDEX i1} 001495 } {0 {}} 001496 do_test auth-1.224 { 001497 set ::authargs 001498 } {i1 t1 temp {}} 001499 do_test auth-1.225 { 001500 execsql {SELECT name FROM temp.sqlite_master} 001501 } {t1 i1} 001502 do_test auth-1.226 { 001503 proc auth {code arg1 arg2 arg3 arg4 args} { 001504 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 001505 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001506 return SQLITE_OK 001507 } 001508 return SQLITE_OK 001509 } 001510 catchsql {DROP INDEX i1} 001511 } {0 {}} 001512 do_test auth-1.227 { 001513 set ::authargs 001514 } {i1 t1 temp {}} 001515 do_test auth-1.228 { 001516 execsql {SELECT name FROM temp.sqlite_master} 001517 } {t1} 001518 } 001519 001520 do_test auth-1.229 { 001521 proc auth {code arg1 arg2 arg3 arg4 args} { 001522 if {$code=="SQLITE_PRAGMA"} { 001523 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001524 return SQLITE_DENY 001525 } 001526 return SQLITE_OK 001527 } 001528 catchsql {PRAGMA full_column_names=on} 001529 } {1 {not authorized}} 001530 do_test auth-1.230 { 001531 set ::authargs 001532 } {full_column_names on {} {}} 001533 do_test auth-1.231 { 001534 execsql2 {SELECT a FROM t2} 001535 } {a 11 a 7} 001536 do_test auth-1.232 { 001537 proc auth {code arg1 arg2 arg3 arg4 args} { 001538 if {$code=="SQLITE_PRAGMA"} { 001539 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001540 return SQLITE_IGNORE 001541 } 001542 return SQLITE_OK 001543 } 001544 catchsql {PRAGMA full_column_names=on} 001545 } {0 {}} 001546 do_test auth-1.233 { 001547 set ::authargs 001548 } {full_column_names on {} {}} 001549 do_test auth-1.234 { 001550 execsql2 {SELECT a FROM t2} 001551 } {a 11 a 7} 001552 do_test auth-1.235 { 001553 proc auth {code arg1 arg2 arg3 arg4 args} { 001554 if {$code=="SQLITE_PRAGMA"} { 001555 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001556 return SQLITE_OK 001557 } 001558 return SQLITE_OK 001559 } 001560 catchsql {PRAGMA full_column_names=on} 001561 } {0 {}} 001562 do_test auth-1.236 { 001563 execsql2 {SELECT a FROM t2} 001564 } {t2.a 11 t2.a 7} 001565 do_test auth-1.237 { 001566 proc auth {code arg1 arg2 arg3 arg4 args} { 001567 if {$code=="SQLITE_PRAGMA"} { 001568 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001569 return SQLITE_OK 001570 } 001571 return SQLITE_OK 001572 } 001573 catchsql {PRAGMA full_column_names=OFF} 001574 } {0 {}} 001575 do_test auth-1.238 { 001576 set ::authargs 001577 } {full_column_names OFF {} {}} 001578 do_test auth-1.239 { 001579 execsql2 {SELECT a FROM t2} 001580 } {a 11 a 7} 001581 001582 do_test auth-1.240 { 001583 proc auth {code arg1 arg2 arg3 arg4 args} { 001584 if {$code=="SQLITE_TRANSACTION"} { 001585 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001586 return SQLITE_DENY 001587 } 001588 return SQLITE_OK 001589 } 001590 catchsql {BEGIN} 001591 } {1 {not authorized}} 001592 do_test auth-1.241 { 001593 set ::authargs 001594 } {BEGIN {} {} {}} 001595 do_test auth-1.242 { 001596 proc auth {code arg1 arg2 arg3 arg4 args} { 001597 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} { 001598 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001599 return SQLITE_DENY 001600 } 001601 return SQLITE_OK 001602 } 001603 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT} 001604 } {1 {not authorized}} 001605 do_test auth-1.243 { 001606 set ::authargs 001607 } {COMMIT {} {} {}} 001608 do_test auth-1.244 { 001609 execsql {SELECT * FROM t2} 001610 } {11 2 33 7 8 9 44 55 66} 001611 do_test auth-1.245 { 001612 catchsql {ROLLBACK} 001613 } {1 {not authorized}} 001614 do_test auth-1.246 { 001615 set ::authargs 001616 } {ROLLBACK {} {} {}} 001617 do_test auth-1.247 { 001618 catchsql {END TRANSACTION} 001619 } {1 {not authorized}} 001620 do_test auth-1.248 { 001621 set ::authargs 001622 } {COMMIT {} {} {}} 001623 do_test auth-1.249 { 001624 # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL 001625 # callback. 001626 db authorizer {} 001627 catchsql {ROLLBACK} 001628 } {0 {}} 001629 do_test auth-1.250 { 001630 execsql {SELECT * FROM t2} 001631 } {11 2 33 7 8 9} 001632 001633 # ticket #340 - authorization for ATTACH and DETACH. 001634 # 001635 ifcapable attach { 001636 do_test auth-1.251 { 001637 db authorizer ::auth 001638 proc auth {code arg1 arg2 arg3 arg4 args} { 001639 if {$code=="SQLITE_ATTACH"} { 001640 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001641 } 001642 return SQLITE_OK 001643 } 001644 catchsql { 001645 ATTACH DATABASE ':memory:' AS test1 001646 } 001647 } {0 {}} 001648 do_test auth-1.252a { 001649 set ::authargs 001650 } {:memory: {} {} {}} 001651 do_test auth-1.252b { 001652 db eval {DETACH test1} 001653 set ::attachfilename :memory: 001654 db eval {ATTACH $::attachfilename AS test1} 001655 set ::authargs 001656 } {{} {} {} {}} 001657 do_test auth-1.252c { 001658 db eval {DETACH test1} 001659 db eval {ATTACH ':mem' || 'ory:' AS test1} 001660 set ::authargs 001661 } {{} {} {} {}} 001662 do_test auth-1.253 { 001663 catchsql {DETACH DATABASE test1} 001664 proc auth {code arg1 arg2 arg3 arg4 args} { 001665 if {$code=="SQLITE_ATTACH"} { 001666 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001667 return SQLITE_DENY 001668 } 001669 return SQLITE_OK 001670 } 001671 catchsql { 001672 ATTACH DATABASE ':memory:' AS test1; 001673 } 001674 } {1 {not authorized}} 001675 do_test auth-1.254 { 001676 lindex [execsql {PRAGMA database_list}] 7 001677 } {} 001678 do_test auth-1.255 { 001679 catchsql {DETACH DATABASE test1} 001680 proc auth {code arg1 arg2 arg3 arg4 args} { 001681 if {$code=="SQLITE_ATTACH"} { 001682 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001683 return SQLITE_IGNORE 001684 } 001685 return SQLITE_OK 001686 } 001687 catchsql { 001688 ATTACH DATABASE ':memory:' AS test1; 001689 } 001690 } {0 {}} 001691 do_test auth-1.256 { 001692 lindex [execsql {PRAGMA database_list}] 7 001693 } {} 001694 do_test auth-1.257 { 001695 proc auth {code arg1 arg2 arg3 arg4 args} { 001696 if {$code=="SQLITE_DETACH"} { 001697 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001698 return SQLITE_OK 001699 } 001700 return SQLITE_OK 001701 } 001702 execsql {ATTACH DATABASE ':memory:' AS test1} 001703 catchsql { 001704 DETACH DATABASE test1; 001705 } 001706 } {0 {}} 001707 do_test auth-1.258 { 001708 lindex [execsql {PRAGMA database_list}] 7 001709 } {} 001710 do_test auth-1.259 { 001711 execsql {ATTACH DATABASE ':memory:' AS test1} 001712 proc auth {code arg1 arg2 arg3 arg4 args} { 001713 if {$code=="SQLITE_DETACH"} { 001714 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001715 return SQLITE_IGNORE 001716 } 001717 return SQLITE_OK 001718 } 001719 catchsql { 001720 DETACH DATABASE test1; 001721 } 001722 } {0 {}} 001723 ifcapable tempdb { 001724 ifcapable schema_pragmas { 001725 do_test auth-1.260 { 001726 lindex [execsql {PRAGMA database_list}] 7 001727 } {test1} 001728 } ;# ifcapable schema_pragmas 001729 do_test auth-1.261 { 001730 proc auth {code arg1 arg2 arg3 arg4 args} { 001731 if {$code=="SQLITE_DETACH"} { 001732 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001733 return SQLITE_DENY 001734 } 001735 return SQLITE_OK 001736 } 001737 catchsql { 001738 DETACH DATABASE test1; 001739 } 001740 } {1 {not authorized}} 001741 ifcapable schema_pragmas { 001742 do_test auth-1.262 { 001743 lindex [execsql {PRAGMA database_list}] 7 001744 } {test1} 001745 } ;# ifcapable schema_pragmas 001746 db authorizer {} 001747 execsql {DETACH DATABASE test1} 001748 db authorizer ::auth 001749 001750 # Authorization for ALTER TABLE. These tests are omitted if the library 001751 # was built without ALTER TABLE support. 001752 ifcapable altertable { 001753 001754 do_test auth-1.263 { 001755 proc auth {code arg1 arg2 arg3 arg4 args} { 001756 if {$code=="SQLITE_ALTER_TABLE"} { 001757 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001758 return SQLITE_OK 001759 } 001760 return SQLITE_OK 001761 } 001762 catchsql { 001763 ALTER TABLE t1 RENAME TO t1x 001764 } 001765 } {0 {}} 001766 do_test auth-1.264 { 001767 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 001768 } {t1x} 001769 do_test auth-1.265 { 001770 set authargs 001771 } {temp t1 {} {}} 001772 do_test auth-1.266 { 001773 proc auth {code arg1 arg2 arg3 arg4 args} { 001774 if {$code=="SQLITE_ALTER_TABLE"} { 001775 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001776 return SQLITE_IGNORE 001777 } 001778 return SQLITE_OK 001779 } 001780 catchsql { 001781 ALTER TABLE t1x RENAME TO t1 001782 } 001783 } {0 {}} 001784 do_test auth-1.267 { 001785 execsql {SELECT name FROM temp.sqlite_master WHERE type='table'} 001786 } {t1x} 001787 do_test auth-1.268 { 001788 set authargs 001789 } {temp t1x {} {}} 001790 do_test auth-1.269 { 001791 proc auth {code arg1 arg2 arg3 arg4 args} { 001792 if {$code=="SQLITE_ALTER_TABLE"} { 001793 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001794 return SQLITE_DENY 001795 } 001796 return SQLITE_OK 001797 } 001798 catchsql { 001799 ALTER TABLE t1x RENAME TO t1 001800 } 001801 } {1 {not authorized}} 001802 do_test auth-1.270 { 001803 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 001804 } {t1x} 001805 001806 do_test auth-1.271 { 001807 set authargs 001808 } {temp t1x {} {}} 001809 } ;# ifcapable altertable 001810 001811 } else { 001812 db authorizer {} 001813 db eval { 001814 DETACH DATABASE test1; 001815 } 001816 } 001817 } 001818 001819 ifcapable altertable { 001820 db authorizer {} 001821 catchsql {ALTER TABLE t1x RENAME TO t1} 001822 db authorizer ::auth 001823 do_test auth-1.272 { 001824 proc auth {code arg1 arg2 arg3 arg4 args} { 001825 if {$code=="SQLITE_ALTER_TABLE"} { 001826 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001827 return SQLITE_OK 001828 } 001829 return SQLITE_OK 001830 } 001831 catchsql { 001832 ALTER TABLE t2 RENAME TO t2x 001833 } 001834 } {0 {}} 001835 do_test auth-1.273 { 001836 execsql {SELECT name FROM sqlite_master WHERE type='table'} 001837 } {t2x} 001838 do_test auth-1.274 { 001839 set authargs 001840 } {main t2 {} {}} 001841 do_test auth-1.275 { 001842 proc auth {code arg1 arg2 arg3 arg4 args} { 001843 if {$code=="SQLITE_ALTER_TABLE"} { 001844 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001845 return SQLITE_IGNORE 001846 } 001847 return SQLITE_OK 001848 } 001849 catchsql { 001850 ALTER TABLE t2x RENAME TO t2 001851 } 001852 } {0 {}} 001853 do_test auth-1.276 { 001854 execsql {SELECT name FROM sqlite_master WHERE type='table'} 001855 } {t2x} 001856 do_test auth-1.277 { 001857 set authargs 001858 } {main t2x {} {}} 001859 do_test auth-1.278 { 001860 proc auth {code arg1 arg2 arg3 arg4 args} { 001861 if {$code=="SQLITE_ALTER_TABLE"} { 001862 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 001863 return SQLITE_DENY 001864 } 001865 return SQLITE_OK 001866 } 001867 catchsql { 001868 ALTER TABLE t2x RENAME TO t2 001869 } 001870 } {1 {not authorized}} 001871 do_test auth-1.279 { 001872 execsql {SELECT name FROM sqlite_master WHERE type='table'} 001873 } {t2x} 001874 do_test auth-1.280 { 001875 set authargs 001876 } {main t2x {} {}} 001877 db authorizer {} 001878 catchsql {ALTER TABLE t2x RENAME TO t2} 001879 001880 } ;# ifcapable altertable 001881 001882 # Test the authorization callbacks for the REINDEX command. 001883 ifcapable reindex { 001884 001885 proc auth {code args} { 001886 if {$code=="SQLITE_REINDEX"} { 001887 set ::authargs [concat $::authargs [lrange $args 0 3]] 001888 } 001889 return SQLITE_OK 001890 } 001891 db authorizer auth 001892 do_test auth-1.281 { 001893 execsql { 001894 CREATE TABLE t3(a PRIMARY KEY, b, c); 001895 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 001896 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 001897 } 001898 } {} 001899 do_test auth-1.282 { 001900 set ::authargs {} 001901 execsql { 001902 REINDEX t3_idx1; 001903 } 001904 set ::authargs 001905 } {t3_idx1 {} main {}} 001906 do_test auth-1.283 { 001907 set ::authargs {} 001908 execsql { 001909 REINDEX BINARY; 001910 } 001911 set ::authargs 001912 } {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 001913 do_test auth-1.284 { 001914 set ::authargs {} 001915 execsql { 001916 REINDEX NOCASE; 001917 } 001918 set ::authargs 001919 } {t3_idx2 {} main {}} 001920 do_test auth-1.285 { 001921 set ::authargs {} 001922 execsql { 001923 REINDEX t3; 001924 } 001925 set ::authargs 001926 } {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 001927 do_test auth-1.286 { 001928 execsql { 001929 DROP TABLE t3; 001930 } 001931 } {} 001932 ifcapable tempdb { 001933 do_test auth-1.287 { 001934 execsql { 001935 CREATE TEMP TABLE t3(a PRIMARY KEY, b, c); 001936 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 001937 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 001938 } 001939 } {} 001940 do_test auth-1.288 { 001941 set ::authargs {} 001942 execsql { 001943 REINDEX temp.t3_idx1; 001944 } 001945 set ::authargs 001946 } {t3_idx1 {} temp {}} 001947 do_test auth-1.289 { 001948 set ::authargs {} 001949 execsql { 001950 REINDEX BINARY; 001951 } 001952 set ::authargs 001953 } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 001954 do_test auth-1.290 { 001955 set ::authargs {} 001956 execsql { 001957 REINDEX NOCASE; 001958 } 001959 set ::authargs 001960 } {t3_idx2 {} temp {}} 001961 do_test auth-1.291 { 001962 set ::authargs {} 001963 execsql { 001964 REINDEX temp.t3; 001965 } 001966 set ::authargs 001967 } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 001968 proc auth {code args} { 001969 if {$code=="SQLITE_REINDEX"} { 001970 set ::authargs [concat $::authargs [lrange $args 0 3]] 001971 return SQLITE_DENY 001972 } 001973 return SQLITE_OK 001974 } 001975 do_test auth-1.292 { 001976 set ::authargs {} 001977 catchsql { 001978 REINDEX temp.t3; 001979 } 001980 } {1 {not authorized}} 001981 do_test auth-1.293 { 001982 execsql { 001983 DROP TABLE t3; 001984 } 001985 } {} 001986 } 001987 001988 } ;# ifcapable reindex 001989 001990 ifcapable analyze { 001991 proc auth {code args} { 001992 if {$code=="SQLITE_ANALYZE"} { 001993 set ::authargs [concat $::authargs [lrange $args 0 3]] 001994 } 001995 return SQLITE_OK 001996 } 001997 do_test auth-1.294 { 001998 set ::authargs {} 001999 execsql { 002000 CREATE TABLE t4(a,b,c); 002001 CREATE INDEX t4i1 ON t4(a); 002002 CREATE INDEX t4i2 ON t4(b,a,c); 002003 INSERT INTO t4 VALUES(1,2,3); 002004 ANALYZE; 002005 } 002006 set ::authargs 002007 } {t4 {} main {} t2 {} main {}} 002008 do_test auth-1.295 { 002009 execsql { 002010 SELECT count(*) FROM sqlite_stat1; 002011 } 002012 } 3 002013 proc auth {code args} { 002014 if {$code=="SQLITE_ANALYZE"} { 002015 set ::authargs [concat $::authargs $args] 002016 return SQLITE_DENY 002017 } 002018 return SQLITE_OK 002019 } 002020 do_test auth-1.296 { 002021 set ::authargs {} 002022 catchsql { 002023 ANALYZE; 002024 } 002025 } {1 {not authorized}} 002026 do_test auth-1.297 { 002027 execsql { 002028 SELECT count(*) FROM sqlite_stat1; 002029 } 002030 } 3 002031 } ;# ifcapable analyze 002032 002033 002034 # Authorization for ALTER TABLE ADD COLUMN. 002035 # These tests are omitted if the library 002036 # was built without ALTER TABLE support. 002037 ifcapable {altertable} { 002038 do_test auth-1.300 { 002039 execsql {CREATE TABLE t5(x)} 002040 proc auth {code arg1 arg2 arg3 arg4 args} { 002041 if {$code=="SQLITE_ALTER_TABLE"} { 002042 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 002043 return SQLITE_OK 002044 } 002045 return SQLITE_OK 002046 } 002047 catchsql { 002048 ALTER TABLE t5 ADD COLUMN new_col_1; 002049 } 002050 } {0 {}} 002051 do_test auth-1.301 { 002052 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 002053 regexp new_col_1 $x 002054 } {1} 002055 do_test auth-1.302 { 002056 set authargs 002057 } {main t5 {} {}} 002058 do_test auth-1.303 { 002059 proc auth {code arg1 arg2 arg3 arg4 args} { 002060 if {$code=="SQLITE_ALTER_TABLE"} { 002061 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 002062 return SQLITE_IGNORE 002063 } 002064 return SQLITE_OK 002065 } 002066 catchsql { 002067 ALTER TABLE t5 ADD COLUMN new_col_2; 002068 } 002069 } {0 {}} 002070 do_test auth-1.304 { 002071 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 002072 regexp new_col_2 $x 002073 } {0} 002074 do_test auth-1.305 { 002075 set authargs 002076 } {main t5 {} {}} 002077 do_test auth-1.306 { 002078 proc auth {code arg1 arg2 arg3 arg4 args} { 002079 if {$code=="SQLITE_ALTER_TABLE"} { 002080 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 002081 return SQLITE_DENY 002082 } 002083 return SQLITE_OK 002084 } 002085 catchsql { 002086 ALTER TABLE t5 ADD COLUMN new_col_3 002087 } 002088 } {1 {not authorized}} 002089 do_test auth-1.307 { 002090 set x [execsql {SELECT sql FROM temp.sqlite_master WHERE type='t5'}] 002091 regexp new_col_3 $x 002092 } {0} 002093 002094 do_test auth-1.308 { 002095 set authargs 002096 } {main t5 {} {}} 002097 execsql {DROP TABLE t5} 002098 } ;# ifcapable altertable 002099 002100 ifcapable {cte} { 002101 do_test auth-1.310 { 002102 proc auth {code arg1 arg2 arg3 arg4 args} { 002103 if {$code=="SQLITE_RECURSIVE"} { 002104 return SQLITE_DENY 002105 } 002106 return SQLITE_OK 002107 } 002108 db eval { 002109 DROP TABLE IF EXISTS t1; 002110 CREATE TABLE t1(a,b); 002111 INSERT INTO t1 VALUES(1,2),(3,4),(5,6); 002112 } 002113 } {} 002114 do_catchsql_test auth-1.311 { 002115 WITH 002116 auth1311(x,y) AS (SELECT a+b, b-a FROM t1) 002117 SELECT * FROM auth1311 ORDER BY x; 002118 } {0 {3 1 7 1 11 1}} 002119 do_catchsql_test auth-1.312 { 002120 WITH RECURSIVE 002121 auth1312(x,y) AS (SELECT a+b, b-a FROM t1) 002122 SELECT x, y FROM auth1312 ORDER BY x; 002123 } {0 {3 1 7 1 11 1}} 002124 do_catchsql_test auth-1.313 { 002125 WITH RECURSIVE 002126 auth1313(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1313 WHERE x<5) 002127 SELECT * FROM t1; 002128 } {0 {1 2 3 4 5 6}} 002129 do_catchsql_test auth-1.314 { 002130 WITH RECURSIVE 002131 auth1314(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1314 WHERE x<5) 002132 SELECT * FROM t1 LEFT JOIN auth1314; 002133 } {1 {not authorized}} 002134 } ;# ifcapable cte 002135 002136 # 002137 # db eval {SELECT sql FROM temp.sqlite_master} {puts "TEMP: $sql;"} 002138 # db eval {SELECT sql FROM main.sqlite_master} {puts "MAIN: $sql;"} 002139 # 002140 # MAIN: CREATE TABLE "t2"(a,b,c); 002141 # MAIN: CREATE TABLE t4(a,b,c); 002142 # MAIN: CREATE INDEX t4i1 ON t4(a); 002143 # MAIN: CREATE INDEX t4i2 ON t4(b,a,c); 002144 # MAIN: CREATE TABLE sqlite_stat1(tbl,idx,stat); 002145 # MAIN: CREATE TABLE t1(a,b); 002146 # 002147 ifcapable altertable&&vtab { 002148 do_test 1.350 { 002149 proc auth {code arg1 arg2 arg3 arg4 args} { 002150 if {$code=="SQLITE_ALTER_TABLE"} { 002151 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 002152 return SQLITE_OK 002153 } 002154 return SQLITE_OK 002155 } 002156 catchsql { 002157 ALTER TABLE t1 RENAME COLUMN b TO bcdefg; 002158 } 002159 } {0 {}} 002160 do_execsql_test auth-1.351 { 002161 SELECT name FROM pragma_table_info('t1') ORDER BY cid; 002162 } {a bcdefg} 002163 do_test auth-1.352 { 002164 set authargs 002165 } {main t1 {} {}} 002166 do_test 1.353 { 002167 proc auth {code arg1 arg2 arg3 arg4 args} { 002168 if {$code=="SQLITE_ALTER_TABLE"} { 002169 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 002170 return SQLITE_IGNORE 002171 } 002172 return SQLITE_OK 002173 } 002174 catchsql { 002175 ALTER TABLE t1 RENAME COLUMN bcdefg TO b; 002176 } 002177 } {0 {}} 002178 do_execsql_test auth-1.354 { 002179 SELECT name FROM pragma_table_info('t1') ORDER BY cid; 002180 } {a bcdefg} 002181 do_test auth-1.355 { 002182 set authargs 002183 } {main t1 {} {}} 002184 do_test 1.356 { 002185 proc auth {code arg1 arg2 arg3 arg4 args} { 002186 if {$code=="SQLITE_ALTER_TABLE"} { 002187 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 002188 return SQLITE_DENY 002189 } 002190 return SQLITE_OK 002191 } 002192 catchsql { 002193 ALTER TABLE t1 RENAME COLUMN bcdefg TO b; 002194 } 002195 } {1 {not authorized}} 002196 do_execsql_test auth-1.356 { 002197 SELECT name FROM pragma_table_info('t1') ORDER BY cid; 002198 } {a bcdefg} 002199 do_test auth-1.357 { 002200 set authargs 002201 } {main t1 {} {}} 002202 } 002203 002204 002205 do_test auth-2.1 { 002206 proc auth {code arg1 arg2 arg3 arg4 args} { 002207 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 002208 return SQLITE_DENY 002209 } 002210 return SQLITE_OK 002211 } 002212 db authorizer ::auth 002213 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)} 002214 catchsql {SELECT * FROM t3} 002215 } {1 {access to t3.x is prohibited}} 002216 do_test auth-2.1 { 002217 catchsql {SELECT y,z FROM t3} 002218 } {0 {}} 002219 do_test auth-2.2 { 002220 catchsql {SELECT ROWID,y,z FROM t3} 002221 } {1 {access to t3.x is prohibited}} 002222 do_test auth-2.3 { 002223 catchsql {SELECT OID,y,z FROM t3} 002224 } {1 {access to t3.x is prohibited}} 002225 do_test auth-2.4 { 002226 proc auth {code arg1 arg2 arg3 arg4 args} { 002227 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 002228 return SQLITE_IGNORE 002229 } 002230 return SQLITE_OK 002231 } 002232 execsql {INSERT INTO t3 VALUES(44,55,66)} 002233 catchsql {SELECT * FROM t3} 002234 } {0 {{} 55 66}} 002235 do_test auth-2.5 { 002236 catchsql {SELECT rowid,y,z FROM t3} 002237 } {0 {{} 55 66}} 002238 do_test auth-2.6 { 002239 proc auth {code arg1 arg2 arg3 arg4 args} { 002240 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} { 002241 return SQLITE_IGNORE 002242 } 002243 return SQLITE_OK 002244 } 002245 catchsql {SELECT * FROM t3} 002246 } {0 {44 55 66}} 002247 do_test auth-2.7 { 002248 catchsql {SELECT ROWID,y,z FROM t3} 002249 } {0 {44 55 66}} 002250 do_test auth-2.8 { 002251 proc auth {code arg1 arg2 arg3 arg4 args} { 002252 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 002253 return SQLITE_IGNORE 002254 } 002255 return SQLITE_OK 002256 } 002257 catchsql {SELECT ROWID,b,c FROM t2} 002258 } {0 {{} 2 33 {} 8 9}} 002259 do_test auth-2.9.1 { 002260 # We have to flush the cache here in case the Tcl interface tries to 002261 # reuse a statement compiled with sqlite3_prepare_v2(). In this case, 002262 # the first error encountered is an SQLITE_SCHEMA error. Then, when 002263 # trying to recompile the statement, the authorization error is encountered. 002264 # If we do not flush the cache, the correct error message is returned, but 002265 # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test 002266 # case after this one. 002267 # 002268 db cache flush 002269 002270 proc auth {code arg1 arg2 arg3 arg4 args} { 002271 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 002272 return bogus 002273 } 002274 return SQLITE_OK 002275 } 002276 catchsql {SELECT ROWID,b,c FROM t2} 002277 } {1 {authorizer malfunction}} 002278 do_test auth-2.9.2 { 002279 db errorcode 002280 } {1} 002281 do_test auth-2.10 { 002282 proc auth {code arg1 arg2 arg3 arg4 args} { 002283 if {$code=="SQLITE_SELECT"} { 002284 return bogus 002285 } 002286 return SQLITE_OK 002287 } 002288 catchsql {SELECT ROWID,b,c FROM t2} 002289 } {1 {authorizer malfunction}} 002290 do_test auth-2.11.1 { 002291 proc auth {code arg1 arg2 arg3 arg4 args} { 002292 if {$code=="SQLITE_READ" && $arg2=="a"} { 002293 return SQLITE_IGNORE 002294 } 002295 return SQLITE_OK 002296 } 002297 catchsql {SELECT * FROM t2, t3} 002298 } {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}} 002299 do_test auth-2.11.2 { 002300 proc auth {code arg1 arg2 arg3 arg4 args} { 002301 if {$code=="SQLITE_READ" && $arg2=="x"} { 002302 return SQLITE_IGNORE 002303 } 002304 return SQLITE_OK 002305 } 002306 catchsql {SELECT * FROM t2, t3} 002307 } {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}} 002308 002309 # Make sure the OLD and NEW pseudo-tables of a trigger get authorized. 002310 # 002311 ifcapable trigger { 002312 do_test auth-3.1 { 002313 proc auth {code arg1 arg2 arg3 arg4 args} { 002314 return SQLITE_OK 002315 } 002316 execsql { 002317 CREATE TABLE tx(a1,a2,b1,b2,c1,c2); 002318 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 002319 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c); 002320 END; 002321 UPDATE t2 SET a=a+1; 002322 SELECT * FROM tx; 002323 } 002324 } {11 12 2 2 33 33 7 8 8 8 9 9} 002325 do_test auth-3.2 { 002326 proc auth {code arg1 arg2 arg3 arg4 args} { 002327 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} { 002328 return SQLITE_IGNORE 002329 } 002330 return SQLITE_OK 002331 } 002332 execsql { 002333 DELETE FROM tx; 002334 UPDATE t2 SET a=a+100; 002335 SELECT * FROM tx; 002336 } 002337 } {12 112 2 2 {} {} 8 108 8 8 {} {}} 002338 } ;# ifcapable trigger 002339 002340 # Make sure the names of views and triggers are passed on on arg4. 002341 # 002342 ifcapable trigger { 002343 do_test auth-4.1 { 002344 proc auth {code arg1 arg2 arg3 arg4 args} { 002345 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 002346 return SQLITE_OK 002347 } 002348 set authargs {} 002349 execsql { 002350 UPDATE t2 SET a=a+1; 002351 } 002352 set authargs 002353 } [list \ 002354 SQLITE_READ t2 a main {} \ 002355 SQLITE_UPDATE t2 a main {} \ 002356 SQLITE_INSERT tx {} main r1 \ 002357 SQLITE_READ t2 a main r1 \ 002358 SQLITE_READ t2 a main r1 \ 002359 SQLITE_READ t2 b main r1 \ 002360 SQLITE_READ t2 b main r1 \ 002361 SQLITE_READ t2 c main r1 \ 002362 SQLITE_READ t2 c main r1] 002363 } 002364 002365 ifcapable {view && trigger} { 002366 do_test auth-4.2 { 002367 execsql { 002368 CREATE VIEW v1 AS SELECT a+b AS x FROM t2; 002369 CREATE TABLE v1chng(x1,x2); 002370 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN 002371 INSERT INTO v1chng VALUES(OLD.x,NEW.x); 002372 END; 002373 SELECT * FROM v1; 002374 } 002375 } {115 117} 002376 do_test auth-4.3 { 002377 set authargs {} 002378 execsql { 002379 UPDATE v1 SET x=1 WHERE x=117 002380 } 002381 set authargs 002382 } [list \ 002383 SQLITE_UPDATE v1 x main {} \ 002384 SQLITE_SELECT {} {} {} v1 \ 002385 SQLITE_READ t2 a main v1 \ 002386 SQLITE_READ t2 b main v1 \ 002387 SQLITE_READ v1 x main v1 \ 002388 SQLITE_READ v1 x main v1 \ 002389 SQLITE_SELECT {} {} {} v1 \ 002390 SQLITE_READ v1 x main v1 \ 002391 SQLITE_INSERT v1chng {} main r2 \ 002392 SQLITE_READ v1 x main r2 \ 002393 SQLITE_READ v1 x main r2 \ 002394 ] 002395 002396 do_test auth-4.4 { 002397 execsql { 002398 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN 002399 INSERT INTO v1chng VALUES(OLD.x,NULL); 002400 END; 002401 SELECT * FROM v1; 002402 } 002403 } {115 117} 002404 do_test auth-4.5 { 002405 set authargs {} 002406 execsql { 002407 DELETE FROM v1 WHERE x=117 002408 } 002409 set authargs 002410 } [list \ 002411 SQLITE_DELETE v1 {} main {} \ 002412 SQLITE_SELECT {} {} {} v1 \ 002413 SQLITE_READ t2 a main v1 \ 002414 SQLITE_READ t2 b main v1 \ 002415 SQLITE_READ v1 x main v1 \ 002416 SQLITE_READ v1 x main v1 \ 002417 SQLITE_SELECT {} {} {} v1 \ 002418 SQLITE_READ v1 x main v1 \ 002419 SQLITE_INSERT v1chng {} main r3 \ 002420 SQLITE_READ v1 x main r3 \ 002421 ] 002422 002423 } ;# ifcapable view && trigger 002424 002425 # Ticket #1338: Make sure authentication works in the presence of an AS 002426 # clause. 002427 # 002428 do_test auth-5.1 { 002429 proc auth {code arg1 arg2 arg3 arg4 args} { 002430 return SQLITE_OK 002431 } 002432 execsql { 002433 SELECT count(a) AS cnt FROM t4 ORDER BY cnt 002434 } 002435 } {1} 002436 002437 # Ticket #1607 002438 # 002439 ifcapable compound&&subquery { 002440 ifcapable trigger { 002441 execsql { 002442 DROP TABLE tx; 002443 } 002444 ifcapable view { 002445 execsql { 002446 DROP TABLE v1chng; 002447 } 002448 } 002449 } 002450 ifcapable stat4 { 002451 set stat4 "sqlite_stat4 " 002452 } else { 002453 set stat4 "" 002454 } 002455 do_test auth-5.2 { 002456 execsql { 002457 SELECT name FROM ( 002458 SELECT * FROM sqlite_master UNION ALL SELECT * FROM temp.sqlite_master) 002459 WHERE type='table' 002460 ORDER BY name 002461 } 002462 } "sqlite_stat1 ${stat4}t1 t2 t3 t4" 002463 } 002464 002465 # Ticket #3944 002466 # 002467 ifcapable trigger { 002468 do_test auth-5.3.1 { 002469 execsql { 002470 CREATE TABLE t5 ( x ); 002471 CREATE TRIGGER t5_tr1 AFTER INSERT ON t5 BEGIN 002472 UPDATE t5 SET x = 1 WHERE NEW.x = 0; 002473 END; 002474 } 002475 } {} 002476 set ::authargs [list] 002477 proc auth {args} { 002478 eval lappend ::authargs [lrange $args 0 4] 002479 return SQLITE_OK 002480 } 002481 do_test auth-5.3.2 { 002482 execsql { INSERT INTO t5 (x) values(0) } 002483 set ::authargs 002484 } [list SQLITE_INSERT t5 {} main {} \ 002485 SQLITE_UPDATE t5 x main t5_tr1 \ 002486 SQLITE_READ t5 x main t5_tr1 \ 002487 ] 002488 do_test auth-5.3.2 { 002489 execsql { SELECT * FROM t5 } 002490 } {1} 002491 } 002492 002493 # Ticket [0eb70d77cb05bb22720]: Invalid pointer passsed to the authorizer 002494 # callback when updating a ROWID. 002495 # 002496 do_test auth-6.1 { 002497 execsql { 002498 CREATE TABLE t6(a,b,c,d,e,f,g,h); 002499 INSERT INTO t6 VALUES(1,2,3,4,5,6,7,8); 002500 } 002501 } {} 002502 set ::authargs [list] 002503 proc auth {args} { 002504 eval lappend ::authargs [lrange $args 0 4] 002505 return SQLITE_OK 002506 } 002507 do_test auth-6.2 { 002508 execsql {UPDATE t6 SET rowID=rowID+100} 002509 set ::authargs 002510 } [list SQLITE_READ t6 ROWID main {} \ 002511 SQLITE_UPDATE t6 ROWID main {} \ 002512 ] 002513 do_test auth-6.3 { 002514 execsql {SELECT rowid, * FROM t6} 002515 } {101 1 2 3 4 5 6 7 8} 002516 002517 #------------------------------------------------------------------------- 002518 # Test that view names are included as zArg4. 002519 # 002520 do_execsql_test auth-7.1 { 002521 CREATE TABLE t7(a, b, c); 002522 CREATE VIEW v7 AS SELECT * FROM t7; 002523 } {} 002524 set ::authargs [list] 002525 proc auth {args} { 002526 eval lappend ::authargs [lrange $args 0 4] 002527 return SQLITE_OK 002528 } 002529 002530 do_test auth-7.2 { 002531 execsql {SELECT a, c FROM v7} 002532 set ::authargs 002533 } [list \ 002534 SQLITE_SELECT {} {} {} {} \ 002535 SQLITE_READ t7 a main v7 \ 002536 SQLITE_READ t7 b main v7 \ 002537 SQLITE_READ t7 c main v7 \ 002538 SQLITE_READ v7 a main {} \ 002539 SQLITE_READ v7 c main {} \ 002540 SQLITE_SELECT {} {} {} v7 \ 002541 ] 002542 002543 set ::authargs [list] 002544 do_test auth-7.3 { 002545 execsql {SELECT a, c FROM t7} 002546 set ::authargs 002547 } [list \ 002548 SQLITE_SELECT {} {} {} {} \ 002549 SQLITE_READ t7 a main {} \ 002550 SQLITE_READ t7 c main {} \ 002551 ] 002552 002553 set ::authargs [list] 002554 do_test auth-7.4 { 002555 execsql {SELECT a, c FROM t7 AS v7} 002556 set ::authargs 002557 } [list \ 002558 SQLITE_SELECT {} {} {} {} \ 002559 SQLITE_READ t7 a main {} \ 002560 SQLITE_READ t7 c main {} \ 002561 ] 002562 002563 # If a table is referenced but no columns are read from the table, 002564 # that causes a single SQLITE_READ authorization with a NULL column 002565 # name. 002566 # 002567 # EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but 002568 # no column values are extracted from that table (for example in a query 002569 # like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer 002570 # callback is invoked once for that table with a column name that is an 002571 # empty string. 002572 # 002573 set ::authargs [list] 002574 do_test auth-8.1 { 002575 execsql {SELECT count(*) FROM t7} 002576 set ::authargs 002577 } [list \ 002578 SQLITE_SELECT {} {} {} {} \ 002579 SQLITE_FUNCTION {} count {} {} \ 002580 SQLITE_READ t7 {} {} {} \ 002581 ] 002582 set ::authargs [list] 002583 002584 do_test auth-8.2 { 002585 execsql {SELECT t6.a FROM t6, t7} 002586 set ::authargs 002587 } [list \ 002588 SQLITE_SELECT {} {} {} {} \ 002589 SQLITE_READ t6 a main {} \ 002590 SQLITE_READ t7 {} {} {} \ 002591 ] 002592 002593 # Test also that if SQLITE_DENY is returned from an SQLITE_READ authorizer 002594 # invocation with no column name specified, compilation fails. 002595 # 002596 set ::authargs [list] 002597 proc auth {op args} { 002598 foreach {a b c d} $args break 002599 lappend ::authargs $op $a $b $c $d 002600 if {$op == "SQLITE_READ"} { return "SQLITE_DENY" } 002601 return "SQLITE_OK" 002602 } 002603 set ::authargs [list] 002604 do_catchsql_test auth-8.3 { 002605 SELECT count(*) FROM t7 002606 } {1 {not authorized}} 002607 do_test auth-8.4 { 002608 set ::authargs 002609 } [list \ 002610 SQLITE_SELECT {} {} {} {} \ 002611 SQLITE_FUNCTION {} count {} {} \ 002612 SQLITE_READ t7 {} {} {} \ 002613 ] 002614 002615 002616 rename proc {} 002617 rename proc_real proc 002618 finish_test