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.205a {
001395 set ::authargs
001396 } {i2 t2 main {}}
001397 db eval {
001398 ATTACH ':memory:' as di205;
001399 CREATE TABLE di205.t1(x);
001400 CREATE INDEX di205.t1x ON t1(x);
001401 }
001402 do_catchsql_test auth-1.205b {
001403 DROP INDEX di205.t1x;
001404 } {1 {not authorized}}
001405 db eval {
001406 DETACH di205;
001407 }
001408 do_test auth-1.206 {
001409 set ::authargs
001410 } {t1x t1 di205 {}}
001411 do_test auth-1.207 {
001412 execsql {SELECT name FROM sqlite_master}
001413 } {t2 i2}
001414 do_test auth-1.208 {
001415 proc auth {code arg1 arg2 arg3 arg4 args} {
001416 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
001417 return SQLITE_IGNORE
001418 }
001419 return SQLITE_OK
001420 }
001421 catchsql {DROP INDEX i2}
001422 } {0 {}}
001423 do_test auth-1.209 {
001424 execsql {SELECT name FROM sqlite_master}
001425 } {t2 i2}
001426 do_test auth-1.210 {
001427 proc auth {code arg1 arg2 arg3 arg4 args} {
001428 if {$code=="SQLITE_DROP_INDEX"} {
001429 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001430 return SQLITE_IGNORE
001431 }
001432 return SQLITE_OK
001433 }
001434 catchsql {DROP INDEX i2}
001435 } {0 {}}
001436 do_test auth-1.211 {
001437 set ::authargs
001438 } {i2 t2 main {}}
001439 do_test auth-1.212 {
001440 execsql {SELECT name FROM sqlite_master}
001441 } {t2 i2}
001442 do_test auth-1.213 {
001443 proc auth {code arg1 arg2 arg3 arg4 args} {
001444 if {$code=="SQLITE_DROP_INDEX"} {
001445 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001446 return SQLITE_OK
001447 }
001448 return SQLITE_OK
001449 }
001450 catchsql {DROP INDEX i2}
001451 } {0 {}}
001452 do_test auth-1.214 {
001453 set ::authargs
001454 } {i2 t2 main {}}
001455 do_test auth-1.215 {
001456 execsql {SELECT name FROM sqlite_master}
001457 } {t2}
001458
001459 ifcapable tempdb {
001460 do_test auth-1.216 {
001461 proc auth {code arg1 arg2 arg3 arg4 args} {
001462 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
001463 return SQLITE_DENY
001464 }
001465 return SQLITE_OK
001466 }
001467 catchsql {DROP INDEX i1}
001468 } {1 {not authorized}}
001469 do_test auth-1.217 {
001470 execsql {SELECT name FROM sqlite_temp_master}
001471 } {t1 i1}
001472 do_test auth-1.218 {
001473 proc auth {code arg1 arg2 arg3 arg4 args} {
001474 if {$code=="SQLITE_DROP_TEMP_INDEX"} {
001475 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001476 return SQLITE_DENY
001477 }
001478 return SQLITE_OK
001479 }
001480 catchsql {DROP INDEX i1}
001481 } {1 {not authorized}}
001482 do_test auth-1.219 {
001483 set ::authargs
001484 } {i1 t1 temp {}}
001485 do_test auth-1.220 {
001486 execsql {SELECT name FROM sqlite_temp_master}
001487 } {t1 i1}
001488 do_test auth-1.221 {
001489 proc auth {code arg1 arg2 arg3 arg4 args} {
001490 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
001491 return SQLITE_IGNORE
001492 }
001493 return SQLITE_OK
001494 }
001495 catchsql {DROP INDEX i1}
001496 } {0 {}}
001497 do_test auth-1.222 {
001498 execsql {SELECT name FROM temp.sqlite_master}
001499 } {t1 i1}
001500 do_test auth-1.223 {
001501 proc auth {code arg1 arg2 arg3 arg4 args} {
001502 if {$code=="SQLITE_DROP_TEMP_INDEX"} {
001503 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001504 return SQLITE_IGNORE
001505 }
001506 return SQLITE_OK
001507 }
001508 catchsql {DROP INDEX i1}
001509 } {0 {}}
001510 do_test auth-1.224 {
001511 set ::authargs
001512 } {i1 t1 temp {}}
001513 do_test auth-1.225 {
001514 execsql {SELECT name FROM temp.sqlite_master}
001515 } {t1 i1}
001516 do_test auth-1.226 {
001517 proc auth {code arg1 arg2 arg3 arg4 args} {
001518 if {$code=="SQLITE_DROP_TEMP_INDEX"} {
001519 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001520 return SQLITE_OK
001521 }
001522 return SQLITE_OK
001523 }
001524 catchsql {DROP INDEX i1}
001525 } {0 {}}
001526 do_test auth-1.227 {
001527 set ::authargs
001528 } {i1 t1 temp {}}
001529 do_test auth-1.228 {
001530 execsql {SELECT name FROM temp.sqlite_master}
001531 } {t1}
001532 }
001533
001534 do_test auth-1.229 {
001535 proc auth {code arg1 arg2 arg3 arg4 args} {
001536 if {$code=="SQLITE_PRAGMA"} {
001537 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001538 return SQLITE_DENY
001539 }
001540 return SQLITE_OK
001541 }
001542 catchsql {PRAGMA full_column_names=on}
001543 } {1 {not authorized}}
001544 do_test auth-1.230 {
001545 set ::authargs
001546 } {full_column_names on {} {}}
001547 do_test auth-1.231 {
001548 execsql2 {SELECT a FROM t2}
001549 } {a 11 a 7}
001550 do_test auth-1.232 {
001551 proc auth {code arg1 arg2 arg3 arg4 args} {
001552 if {$code=="SQLITE_PRAGMA"} {
001553 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001554 return SQLITE_IGNORE
001555 }
001556 return SQLITE_OK
001557 }
001558 catchsql {PRAGMA full_column_names=on}
001559 } {0 {}}
001560 do_test auth-1.233 {
001561 set ::authargs
001562 } {full_column_names on {} {}}
001563 do_test auth-1.234 {
001564 execsql2 {SELECT a FROM t2}
001565 } {a 11 a 7}
001566 do_test auth-1.235 {
001567 proc auth {code arg1 arg2 arg3 arg4 args} {
001568 if {$code=="SQLITE_PRAGMA"} {
001569 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001570 return SQLITE_OK
001571 }
001572 return SQLITE_OK
001573 }
001574 catchsql {PRAGMA full_column_names=on}
001575 } {0 {}}
001576 do_test auth-1.236 {
001577 execsql2 {SELECT a FROM t2}
001578 } {t2.a 11 t2.a 7}
001579 do_test auth-1.237 {
001580 proc auth {code arg1 arg2 arg3 arg4 args} {
001581 if {$code=="SQLITE_PRAGMA"} {
001582 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001583 return SQLITE_OK
001584 }
001585 return SQLITE_OK
001586 }
001587 catchsql {PRAGMA full_column_names=OFF}
001588 } {0 {}}
001589 do_test auth-1.238 {
001590 set ::authargs
001591 } {full_column_names OFF {} {}}
001592 do_test auth-1.239 {
001593 execsql2 {SELECT a FROM t2}
001594 } {a 11 a 7}
001595
001596 do_test auth-1.240 {
001597 proc auth {code arg1 arg2 arg3 arg4 args} {
001598 if {$code=="SQLITE_TRANSACTION"} {
001599 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001600 return SQLITE_DENY
001601 }
001602 return SQLITE_OK
001603 }
001604 catchsql {BEGIN}
001605 } {1 {not authorized}}
001606 do_test auth-1.241 {
001607 set ::authargs
001608 } {BEGIN {} {} {}}
001609 do_test auth-1.242 {
001610 proc auth {code arg1 arg2 arg3 arg4 args} {
001611 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} {
001612 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001613 return SQLITE_DENY
001614 }
001615 return SQLITE_OK
001616 }
001617 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT}
001618 } {1 {not authorized}}
001619 do_test auth-1.243 {
001620 set ::authargs
001621 } {COMMIT {} {} {}}
001622 do_test auth-1.244 {
001623 execsql {SELECT * FROM t2}
001624 } {11 2 33 7 8 9 44 55 66}
001625 do_test auth-1.245 {
001626 catchsql {ROLLBACK}
001627 } {1 {not authorized}}
001628 do_test auth-1.246 {
001629 set ::authargs
001630 } {ROLLBACK {} {} {}}
001631 do_test auth-1.247 {
001632 catchsql {END TRANSACTION}
001633 } {1 {not authorized}}
001634 do_test auth-1.248 {
001635 set ::authargs
001636 } {COMMIT {} {} {}}
001637 do_test auth-1.249 {
001638 # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL
001639 # callback.
001640 db authorizer {}
001641 catchsql {ROLLBACK}
001642 } {0 {}}
001643 do_test auth-1.250 {
001644 execsql {SELECT * FROM t2}
001645 } {11 2 33 7 8 9}
001646
001647 # ticket #340 - authorization for ATTACH and DETACH.
001648 #
001649 ifcapable attach {
001650 do_test auth-1.251 {
001651 db authorizer ::auth
001652 proc auth {code arg1 arg2 arg3 arg4 args} {
001653 if {$code=="SQLITE_ATTACH"} {
001654 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001655 }
001656 return SQLITE_OK
001657 }
001658 catchsql {
001659 ATTACH DATABASE ':memory:' AS test1
001660 }
001661 } {0 {}}
001662 do_test auth-1.252a {
001663 set ::authargs
001664 } {:memory: {} {} {}}
001665 do_test auth-1.252b {
001666 db eval {DETACH test1}
001667 set ::attachfilename :memory:
001668 db eval {ATTACH $::attachfilename AS test1}
001669 set ::authargs
001670 } {{} {} {} {}}
001671 do_test auth-1.252c {
001672 db eval {DETACH test1}
001673 db eval {ATTACH ':mem' || 'ory:' AS test1}
001674 set ::authargs
001675 } {{} {} {} {}}
001676 do_test auth-1.253 {
001677 catchsql {DETACH DATABASE test1}
001678 proc auth {code arg1 arg2 arg3 arg4 args} {
001679 if {$code=="SQLITE_ATTACH"} {
001680 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001681 return SQLITE_DENY
001682 }
001683 return SQLITE_OK
001684 }
001685 catchsql {
001686 ATTACH DATABASE ':memory:' AS test1;
001687 }
001688 } {1 {not authorized}}
001689 do_test auth-1.254 {
001690 lindex [execsql {PRAGMA database_list}] 7
001691 } {}
001692 do_test auth-1.255 {
001693 catchsql {DETACH DATABASE test1}
001694 proc auth {code arg1 arg2 arg3 arg4 args} {
001695 if {$code=="SQLITE_ATTACH"} {
001696 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001697 return SQLITE_IGNORE
001698 }
001699 return SQLITE_OK
001700 }
001701 catchsql {
001702 ATTACH DATABASE ':memory:' AS test1;
001703 }
001704 } {0 {}}
001705 do_test auth-1.256 {
001706 lindex [execsql {PRAGMA database_list}] 7
001707 } {}
001708 do_test auth-1.257 {
001709 proc auth {code arg1 arg2 arg3 arg4 args} {
001710 if {$code=="SQLITE_DETACH"} {
001711 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001712 return SQLITE_OK
001713 }
001714 return SQLITE_OK
001715 }
001716 execsql {ATTACH DATABASE ':memory:' AS test1}
001717 catchsql {
001718 DETACH DATABASE test1;
001719 }
001720 } {0 {}}
001721 do_test auth-1.258 {
001722 lindex [execsql {PRAGMA database_list}] 7
001723 } {}
001724 do_test auth-1.259 {
001725 execsql {ATTACH DATABASE ':memory:' AS test1}
001726 proc auth {code arg1 arg2 arg3 arg4 args} {
001727 if {$code=="SQLITE_DETACH"} {
001728 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001729 return SQLITE_IGNORE
001730 }
001731 return SQLITE_OK
001732 }
001733 catchsql {
001734 DETACH DATABASE test1;
001735 }
001736 } {0 {}}
001737 ifcapable tempdb {
001738 ifcapable schema_pragmas {
001739 do_test auth-1.260 {
001740 lindex [execsql {PRAGMA database_list}] 7
001741 } {test1}
001742 } ;# ifcapable schema_pragmas
001743 do_test auth-1.261 {
001744 proc auth {code arg1 arg2 arg3 arg4 args} {
001745 if {$code=="SQLITE_DETACH"} {
001746 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001747 return SQLITE_DENY
001748 }
001749 return SQLITE_OK
001750 }
001751 catchsql {
001752 DETACH DATABASE test1;
001753 }
001754 } {1 {not authorized}}
001755 ifcapable schema_pragmas {
001756 do_test auth-1.262 {
001757 lindex [execsql {PRAGMA database_list}] 7
001758 } {test1}
001759 } ;# ifcapable schema_pragmas
001760 db authorizer {}
001761 execsql {DETACH DATABASE test1}
001762 db authorizer ::auth
001763
001764 # Authorization for ALTER TABLE. These tests are omitted if the library
001765 # was built without ALTER TABLE support.
001766 ifcapable altertable {
001767
001768 do_test auth-1.263 {
001769 proc auth {code arg1 arg2 arg3 arg4 args} {
001770 if {$code=="SQLITE_ALTER_TABLE"} {
001771 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001772 return SQLITE_OK
001773 }
001774 return SQLITE_OK
001775 }
001776 catchsql {
001777 ALTER TABLE t1 RENAME TO t1x
001778 }
001779 } {0 {}}
001780 do_test auth-1.264 {
001781 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
001782 } {t1x}
001783 do_test auth-1.265 {
001784 set authargs
001785 } {temp t1 {} {}}
001786 do_test auth-1.266 {
001787 proc auth {code arg1 arg2 arg3 arg4 args} {
001788 if {$code=="SQLITE_ALTER_TABLE"} {
001789 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001790 return SQLITE_IGNORE
001791 }
001792 return SQLITE_OK
001793 }
001794 catchsql {
001795 ALTER TABLE t1x RENAME TO t1
001796 }
001797 } {0 {}}
001798 do_test auth-1.267 {
001799 execsql {SELECT name FROM temp.sqlite_master WHERE type='table'}
001800 } {t1x}
001801 do_test auth-1.268 {
001802 set authargs
001803 } {temp t1x {} {}}
001804 do_test auth-1.269 {
001805 proc auth {code arg1 arg2 arg3 arg4 args} {
001806 if {$code=="SQLITE_ALTER_TABLE"} {
001807 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001808 return SQLITE_DENY
001809 }
001810 return SQLITE_OK
001811 }
001812 catchsql {
001813 ALTER TABLE t1x RENAME TO t1
001814 }
001815 } {1 {not authorized}}
001816 do_test auth-1.270 {
001817 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
001818 } {t1x}
001819
001820 do_test auth-1.271 {
001821 set authargs
001822 } {temp t1x {} {}}
001823 } ;# ifcapable altertable
001824
001825 } else {
001826 db authorizer {}
001827 db eval {
001828 DETACH DATABASE test1;
001829 }
001830 }
001831 }
001832
001833 ifcapable altertable {
001834 db authorizer {}
001835 catchsql {ALTER TABLE t1x RENAME TO t1}
001836 db authorizer ::auth
001837 do_test auth-1.272 {
001838 proc auth {code arg1 arg2 arg3 arg4 args} {
001839 if {$code=="SQLITE_ALTER_TABLE"} {
001840 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001841 return SQLITE_OK
001842 }
001843 return SQLITE_OK
001844 }
001845 catchsql {
001846 ALTER TABLE t2 RENAME TO t2x
001847 }
001848 } {0 {}}
001849 do_test auth-1.273 {
001850 execsql {SELECT name FROM sqlite_master WHERE type='table'}
001851 } {t2x}
001852 do_test auth-1.274 {
001853 set authargs
001854 } {main t2 {} {}}
001855 do_test auth-1.275 {
001856 proc auth {code arg1 arg2 arg3 arg4 args} {
001857 if {$code=="SQLITE_ALTER_TABLE"} {
001858 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001859 return SQLITE_IGNORE
001860 }
001861 return SQLITE_OK
001862 }
001863 catchsql {
001864 ALTER TABLE t2x RENAME TO t2
001865 }
001866 } {0 {}}
001867 do_test auth-1.276 {
001868 execsql {SELECT name FROM sqlite_master WHERE type='table'}
001869 } {t2x}
001870 do_test auth-1.277 {
001871 set authargs
001872 } {main t2x {} {}}
001873 do_test auth-1.278 {
001874 proc auth {code arg1 arg2 arg3 arg4 args} {
001875 if {$code=="SQLITE_ALTER_TABLE"} {
001876 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
001877 return SQLITE_DENY
001878 }
001879 return SQLITE_OK
001880 }
001881 catchsql {
001882 ALTER TABLE t2x RENAME TO t2
001883 }
001884 } {1 {not authorized}}
001885 do_test auth-1.279 {
001886 execsql {SELECT name FROM sqlite_master WHERE type='table'}
001887 } {t2x}
001888 do_test auth-1.280 {
001889 set authargs
001890 } {main t2x {} {}}
001891 db authorizer {}
001892 catchsql {ALTER TABLE t2x RENAME TO t2}
001893
001894 } ;# ifcapable altertable
001895
001896 # Test the authorization callbacks for the REINDEX command.
001897 ifcapable reindex {
001898
001899 proc auth {code args} {
001900 if {$code=="SQLITE_REINDEX"} {
001901 set ::authargs [concat $::authargs [lrange $args 0 3]]
001902 }
001903 return SQLITE_OK
001904 }
001905 db authorizer auth
001906 do_test auth-1.281 {
001907 execsql {
001908 CREATE TABLE t3(a PRIMARY KEY, b, c);
001909 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
001910 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
001911 }
001912 } {}
001913 do_test auth-1.282 {
001914 set ::authargs {}
001915 execsql {
001916 REINDEX t3_idx1;
001917 }
001918 set ::authargs
001919 } {t3_idx1 {} main {}}
001920 do_test auth-1.283 {
001921 set ::authargs {}
001922 execsql {
001923 REINDEX BINARY;
001924 }
001925 set ::authargs
001926 } {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
001927 do_test auth-1.284 {
001928 set ::authargs {}
001929 execsql {
001930 REINDEX NOCASE;
001931 }
001932 set ::authargs
001933 } {t3_idx2 {} main {}}
001934 do_test auth-1.285 {
001935 set ::authargs {}
001936 execsql {
001937 REINDEX t3;
001938 }
001939 set ::authargs
001940 } {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
001941 do_test auth-1.286 {
001942 execsql {
001943 DROP TABLE t3;
001944 }
001945 } {}
001946 ifcapable tempdb {
001947 do_test auth-1.287 {
001948 execsql {
001949 CREATE TEMP TABLE t3(a PRIMARY KEY, b, c);
001950 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
001951 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
001952 }
001953 } {}
001954 do_test auth-1.288 {
001955 set ::authargs {}
001956 execsql {
001957 REINDEX temp.t3_idx1;
001958 }
001959 set ::authargs
001960 } {t3_idx1 {} temp {}}
001961 do_test auth-1.289 {
001962 set ::authargs {}
001963 execsql {
001964 REINDEX BINARY;
001965 }
001966 set ::authargs
001967 } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
001968 do_test auth-1.290 {
001969 set ::authargs {}
001970 execsql {
001971 REINDEX NOCASE;
001972 }
001973 set ::authargs
001974 } {t3_idx2 {} temp {}}
001975 do_test auth-1.291 {
001976 set ::authargs {}
001977 execsql {
001978 REINDEX temp.t3;
001979 }
001980 set ::authargs
001981 } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
001982 proc auth {code args} {
001983 if {$code=="SQLITE_REINDEX"} {
001984 set ::authargs [concat $::authargs [lrange $args 0 3]]
001985 return SQLITE_DENY
001986 }
001987 return SQLITE_OK
001988 }
001989 do_test auth-1.292 {
001990 set ::authargs {}
001991 catchsql {
001992 REINDEX temp.t3;
001993 }
001994 } {1 {not authorized}}
001995 do_test auth-1.293 {
001996 execsql {
001997 DROP TABLE t3;
001998 }
001999 } {}
002000 }
002001
002002 } ;# ifcapable reindex
002003
002004 ifcapable analyze {
002005 proc auth {code args} {
002006 if {$code=="SQLITE_ANALYZE"} {
002007 set ::authargs [concat $::authargs [lrange $args 0 3]]
002008 }
002009 return SQLITE_OK
002010 }
002011 do_test auth-1.294 {
002012 set ::authargs {}
002013 execsql {
002014 CREATE TABLE t4(a,b,c);
002015 CREATE INDEX t4i1 ON t4(a);
002016 CREATE INDEX t4i2 ON t4(b,a,c);
002017 INSERT INTO t4 VALUES(1,2,3);
002018 ANALYZE;
002019 }
002020 set ::authargs
002021 } {t4 {} main {} t2 {} main {}}
002022 do_test auth-1.295 {
002023 execsql {
002024 SELECT count(*) FROM sqlite_stat1;
002025 }
002026 } 3
002027 proc auth {code args} {
002028 if {$code=="SQLITE_ANALYZE"} {
002029 set ::authargs [concat $::authargs $args]
002030 return SQLITE_DENY
002031 }
002032 return SQLITE_OK
002033 }
002034 do_test auth-1.296 {
002035 set ::authargs {}
002036 catchsql {
002037 ANALYZE;
002038 }
002039 } {1 {not authorized}}
002040 do_test auth-1.297 {
002041 execsql {
002042 SELECT count(*) FROM sqlite_stat1;
002043 }
002044 } 3
002045 } ;# ifcapable analyze
002046
002047
002048 # Authorization for ALTER TABLE ADD COLUMN.
002049 # These tests are omitted if the library
002050 # was built without ALTER TABLE support.
002051 ifcapable {altertable} {
002052 do_test auth-1.300 {
002053 execsql {CREATE TABLE t5(x)}
002054 proc auth {code arg1 arg2 arg3 arg4 args} {
002055 if {$code=="SQLITE_ALTER_TABLE"} {
002056 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
002057 return SQLITE_OK
002058 }
002059 return SQLITE_OK
002060 }
002061 catchsql {
002062 ALTER TABLE t5 ADD COLUMN new_col_1;
002063 }
002064 } {0 {}}
002065 do_test auth-1.301 {
002066 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
002067 regexp new_col_1 $x
002068 } {1}
002069 do_test auth-1.302 {
002070 set authargs
002071 } {main t5 {} {}}
002072 db eval BEGIN
002073 set authargs {}
002074 do_execsql_test auth-1.302-drop-1 {
002075 ALTER TABLE t5 DROP COLUMN new_col_1;
002076 } {}
002077 db eval ROLLBACK
002078 do_test auth-1.302-drop-2 {
002079 set authargs
002080 } {main t5 new_col_1 {}}
002081 do_test auth-1.303 {
002082 proc auth {code arg1 arg2 arg3 arg4 args} {
002083 if {$code=="SQLITE_ALTER_TABLE"} {
002084 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
002085 return SQLITE_IGNORE
002086 }
002087 return SQLITE_OK
002088 }
002089 catchsql {
002090 ALTER TABLE t5 ADD COLUMN new_col_2;
002091 }
002092 } {0 {}}
002093 do_test auth-1.304 {
002094 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}]
002095 regexp new_col_2 $x
002096 } {0}
002097 do_test auth-1.305 {
002098 set authargs
002099 } {main t5 {} {}}
002100 db eval BEGIN
002101 set authargs {}
002102 do_execsql_test auth-1.305-drop-1 {
002103 ALTER TABLE t5 DROP COLUMN new_col_1;
002104 SELECT 1 FROM sqlite_schema WHERE name='t5' AND sql LIKE '%new_col_1%';
002105 } {1}
002106 db eval ROLLBACK
002107 do_test auth-1.305-drop-2 {
002108 set authargs
002109 } {main t5 new_col_1 {}}
002110 do_test auth-1.306 {
002111 proc auth {code arg1 arg2 arg3 arg4 args} {
002112 if {$code=="SQLITE_ALTER_TABLE"} {
002113 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
002114 return SQLITE_DENY
002115 }
002116 return SQLITE_OK
002117 }
002118 catchsql {
002119 ALTER TABLE t5 ADD COLUMN new_col_3
002120 }
002121 } {1 {not authorized}}
002122 do_test auth-1.307 {
002123 set x [execsql {SELECT sql FROM temp.sqlite_master WHERE type='t5'}]
002124 regexp new_col_3 $x
002125 } {0}
002126 do_test auth-1.308 {
002127 set authargs
002128 } {main t5 {} {}}
002129 db eval BEGIN
002130 set authargs {}
002131 do_catchsql_test auth-1.308-drop-1 {
002132 ALTER TABLE t5 DROP COLUMN new_col_1;
002133 } {1 {not authorized}}
002134 do_execsql_test auth-1.308-drop-2 {
002135 SELECT 1 FROM sqlite_schema WHERE name='t5' AND sql LIKE '%new_col_1%';
002136 } {1}
002137 do_test auth-1.308-drop-3 {
002138 set authargs
002139 } {main t5 new_col_1 {}}
002140 db eval ROLLBACK
002141
002142 execsql {DROP TABLE t5}
002143 } ;# ifcapable altertable
002144
002145 ifcapable {cte} {
002146 do_test auth-1.310 {
002147 proc auth {code arg1 arg2 arg3 arg4 args} {
002148 if {$code=="SQLITE_RECURSIVE"} {
002149 return SQLITE_DENY
002150 }
002151 return SQLITE_OK
002152 }
002153 db eval {
002154 DROP TABLE IF EXISTS t1;
002155 CREATE TABLE t1(a,b);
002156 INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
002157 }
002158 } {}
002159 do_catchsql_test auth-1.311 {
002160 WITH
002161 auth1311(x,y) AS (SELECT a+b, b-a FROM t1)
002162 SELECT * FROM auth1311 ORDER BY x;
002163 } {0 {3 1 7 1 11 1}}
002164 do_catchsql_test auth-1.312 {
002165 WITH RECURSIVE
002166 auth1312(x,y) AS (SELECT a+b, b-a FROM t1)
002167 SELECT x, y FROM auth1312 ORDER BY x;
002168 } {0 {3 1 7 1 11 1}}
002169 do_catchsql_test auth-1.313 {
002170 WITH RECURSIVE
002171 auth1313(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1313 WHERE x<5)
002172 SELECT * FROM t1;
002173 } {0 {1 2 3 4 5 6}}
002174 do_catchsql_test auth-1.314 {
002175 WITH RECURSIVE
002176 auth1314(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1314 WHERE x<5)
002177 SELECT * FROM t1 LEFT JOIN auth1314;
002178 } {1 {not authorized}}
002179 } ;# ifcapable cte
002180
002181 #
002182 # db eval {SELECT sql FROM temp.sqlite_master} {puts "TEMP: $sql;"}
002183 # db eval {SELECT sql FROM main.sqlite_master} {puts "MAIN: $sql;"}
002184 #
002185 # MAIN: CREATE TABLE "t2"(a,b,c);
002186 # MAIN: CREATE TABLE t4(a,b,c);
002187 # MAIN: CREATE INDEX t4i1 ON t4(a);
002188 # MAIN: CREATE INDEX t4i2 ON t4(b,a,c);
002189 # MAIN: CREATE TABLE sqlite_stat1(tbl,idx,stat);
002190 # MAIN: CREATE TABLE t1(a,b);
002191 #
002192 ifcapable altertable&&vtab {
002193 do_test auth-1.350 {
002194 proc auth {code arg1 arg2 arg3 arg4 args} {
002195 if {$code=="SQLITE_ALTER_TABLE"} {
002196 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
002197 return SQLITE_OK
002198 }
002199 return SQLITE_OK
002200 }
002201 catchsql {
002202 ALTER TABLE t1 RENAME COLUMN b TO bcdefg;
002203 }
002204 } {0 {}}
002205 do_execsql_test auth-1.351 {
002206 SELECT name FROM pragma_table_info('t1') ORDER BY cid;
002207 } {a bcdefg}
002208 do_test auth-1.352 {
002209 set authargs
002210 } {main t1 {} {}}
002211 do_test auth-1.353 {
002212 proc auth {code arg1 arg2 arg3 arg4 args} {
002213 if {$code=="SQLITE_ALTER_TABLE"} {
002214 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
002215 return SQLITE_IGNORE
002216 }
002217 return SQLITE_OK
002218 }
002219 catchsql {
002220 ALTER TABLE t1 RENAME COLUMN bcdefg TO b;
002221 }
002222 } {0 {}}
002223 do_execsql_test auth-1.354 {
002224 SELECT name FROM pragma_table_info('t1') ORDER BY cid;
002225 } {a bcdefg}
002226 do_test auth-1.355 {
002227 set authargs
002228 } {main t1 {} {}}
002229 do_test auth-1.356 {
002230 proc auth {code arg1 arg2 arg3 arg4 args} {
002231 if {$code=="SQLITE_ALTER_TABLE"} {
002232 set ::authargs [list $arg1 $arg2 $arg3 $arg4]
002233 return SQLITE_DENY
002234 }
002235 return SQLITE_OK
002236 }
002237 catchsql {
002238 ALTER TABLE t1 RENAME COLUMN bcdefg TO b;
002239 }
002240 } {1 {not authorized}}
002241 do_execsql_test auth-1.357 {
002242 SELECT name FROM pragma_table_info('t1') ORDER BY cid;
002243 } {a bcdefg}
002244 do_test auth-1.358 {
002245 set authargs
002246 } {main t1 {} {}}
002247 }
002248
002249 # 2022-12-28
002250 # The sqlite3_declare_vtab() call that occurs during pragma_table_list
002251 # should not cause an authentication failure.
002252 #
002253 ifcapable vtab {
002254 do_test auth-1.359 {
002255 proc auth {code arg1 arg2 arg3 arg4 args} {
002256 if {$code=="SQLITE_UPDATE"} {
002257 return SQLITE_DENY
002258 }
002259 return SQLITE_OK
002260 }
002261 catchsql {SELECT * FROM pragma_table_list WHERE name='xyzzy';}
002262 } {0 {}}
002263 }
002264
002265 do_test auth-2.1 {
002266 proc auth {code arg1 arg2 arg3 arg4 args} {
002267 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
002268 return SQLITE_DENY
002269 }
002270 return SQLITE_OK
002271 }
002272 db authorizer ::auth
002273 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)}
002274 catchsql {SELECT * FROM t3}
002275 } {1 {access to t3.x is prohibited}}
002276 do_test auth-2.1 {
002277 catchsql {SELECT y,z FROM t3}
002278 } {0 {}}
002279 do_test auth-2.2 {
002280 catchsql {SELECT ROWID,y,z FROM t3}
002281 } {1 {access to t3.x is prohibited}}
002282 do_test auth-2.3 {
002283 catchsql {SELECT OID,y,z FROM t3}
002284 } {1 {access to t3.x is prohibited}}
002285 do_test auth-2.4 {
002286 proc auth {code arg1 arg2 arg3 arg4 args} {
002287 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
002288 return SQLITE_IGNORE
002289 }
002290 return SQLITE_OK
002291 }
002292 execsql {INSERT INTO t3 VALUES(44,55,66)}
002293 catchsql {SELECT * FROM t3}
002294 } {0 {{} 55 66}}
002295 do_test auth-2.5 {
002296 catchsql {SELECT rowid,y,z FROM t3}
002297 } {0 {{} 55 66}}
002298 do_test auth-2.6 {
002299 proc auth {code arg1 arg2 arg3 arg4 args} {
002300 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} {
002301 return SQLITE_IGNORE
002302 }
002303 return SQLITE_OK
002304 }
002305 catchsql {SELECT * FROM t3}
002306 } {0 {44 55 66}}
002307 do_test auth-2.7 {
002308 catchsql {SELECT ROWID,y,z FROM t3}
002309 } {0 {44 55 66}}
002310 do_test auth-2.8 {
002311 proc auth {code arg1 arg2 arg3 arg4 args} {
002312 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
002313 return SQLITE_IGNORE
002314 }
002315 return SQLITE_OK
002316 }
002317 catchsql {SELECT ROWID,b,c FROM t2}
002318 } {0 {{} 2 33 {} 8 9}}
002319 do_test auth-2.9.1 {
002320 # We have to flush the cache here in case the Tcl interface tries to
002321 # reuse a statement compiled with sqlite3_prepare_v2(). In this case,
002322 # the first error encountered is an SQLITE_SCHEMA error. Then, when
002323 # trying to recompile the statement, the authorization error is encountered.
002324 # If we do not flush the cache, the correct error message is returned, but
002325 # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test
002326 # case after this one.
002327 #
002328 db cache flush
002329
002330 proc auth {code arg1 arg2 arg3 arg4 args} {
002331 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} {
002332 return bogus
002333 }
002334 return SQLITE_OK
002335 }
002336 catchsql {SELECT ROWID,b,c FROM t2}
002337 } {1 {authorizer malfunction}}
002338 do_test auth-2.9.2 {
002339 db errorcode
002340 } {1}
002341 do_test auth-2.10 {
002342 proc auth {code arg1 arg2 arg3 arg4 args} {
002343 if {$code=="SQLITE_SELECT"} {
002344 return bogus
002345 }
002346 return SQLITE_OK
002347 }
002348 catchsql {SELECT ROWID,b,c FROM t2}
002349 } {1 {authorizer malfunction}}
002350 do_test auth-2.11.1 {
002351 proc auth {code arg1 arg2 arg3 arg4 args} {
002352 if {$code=="SQLITE_READ" && $arg2=="a"} {
002353 return SQLITE_IGNORE
002354 }
002355 return SQLITE_OK
002356 }
002357 catchsql {SELECT * FROM t2, t3}
002358 } {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}}
002359 do_test auth-2.11.2 {
002360 proc auth {code arg1 arg2 arg3 arg4 args} {
002361 if {$code=="SQLITE_READ" && $arg2=="x"} {
002362 return SQLITE_IGNORE
002363 }
002364 return SQLITE_OK
002365 }
002366 catchsql {SELECT * FROM t2, t3}
002367 } {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}}
002368
002369 # Make sure the OLD and NEW pseudo-tables of a trigger get authorized.
002370 #
002371 ifcapable trigger {
002372 do_test auth-3.1 {
002373 proc auth {code arg1 arg2 arg3 arg4 args} {
002374 return SQLITE_OK
002375 }
002376 execsql {
002377 CREATE TABLE tx(a1,a2,b1,b2,c1,c2);
002378 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN
002379 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c);
002380 END;
002381 UPDATE t2 SET a=a+1;
002382 SELECT * FROM tx;
002383 }
002384 } {11 12 2 2 33 33 7 8 8 8 9 9}
002385 do_test auth-3.2 {
002386 proc auth {code arg1 arg2 arg3 arg4 args} {
002387 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} {
002388 return SQLITE_IGNORE
002389 }
002390 return SQLITE_OK
002391 }
002392 execsql {
002393 DELETE FROM tx;
002394 UPDATE t2 SET a=a+100;
002395 SELECT * FROM tx;
002396 }
002397 } {12 112 2 2 {} {} 8 108 8 8 {} {}}
002398 } ;# ifcapable trigger
002399
002400 # Make sure the names of views and triggers are passed on on arg4.
002401 #
002402 ifcapable trigger {
002403 do_test auth-4.1 {
002404 proc auth {code arg1 arg2 arg3 arg4 args} {
002405 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4
002406 return SQLITE_OK
002407 }
002408 set authargs {}
002409 execsql {
002410 UPDATE t2 SET a=a+1;
002411 }
002412 set authargs
002413 } [list \
002414 SQLITE_READ t2 a main {} \
002415 SQLITE_UPDATE t2 a main {} \
002416 SQLITE_INSERT tx {} main r1 \
002417 SQLITE_READ t2 a main r1 \
002418 SQLITE_READ t2 a main r1 \
002419 SQLITE_READ t2 b main r1 \
002420 SQLITE_READ t2 b main r1 \
002421 SQLITE_READ t2 c main r1 \
002422 SQLITE_READ t2 c main r1]
002423 }
002424
002425 ifcapable {view && trigger} {
002426 do_test auth-4.2 {
002427 execsql {
002428 CREATE VIEW v1 AS SELECT a+b AS x FROM t2;
002429 CREATE TABLE v1chng(x1,x2);
002430 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN
002431 INSERT INTO v1chng VALUES(OLD.x,NEW.x);
002432 END;
002433 SELECT * FROM v1;
002434 }
002435 } {115 117}
002436 do_test auth-4.3 {
002437 set authargs {}
002438 execsql {
002439 UPDATE v1 SET x=1 WHERE x=117
002440 }
002441 set authargs
002442 } [list \
002443 SQLITE_UPDATE v1 x main {} \
002444 SQLITE_SELECT {} {} {} v1 \
002445 SQLITE_READ t2 a main v1 \
002446 SQLITE_READ t2 b main v1 \
002447 SQLITE_READ v1 x main v1 \
002448 SQLITE_READ v1 x main v1 \
002449 SQLITE_SELECT {} {} {} v1 \
002450 SQLITE_READ v1 x main v1 \
002451 SQLITE_INSERT v1chng {} main r2 \
002452 SQLITE_READ v1 x main r2 \
002453 SQLITE_READ v1 x main r2 \
002454 ]
002455
002456 do_test auth-4.4 {
002457 execsql {
002458 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
002459 INSERT INTO v1chng VALUES(OLD.x,NULL);
002460 END;
002461 SELECT * FROM v1;
002462 }
002463 } {115 117}
002464 do_test auth-4.5 {
002465 set authargs {}
002466 execsql {
002467 DELETE FROM v1 WHERE x=117
002468 }
002469 set authargs
002470 } [list \
002471 SQLITE_DELETE v1 {} main {} \
002472 SQLITE_SELECT {} {} {} v1 \
002473 SQLITE_READ t2 a main v1 \
002474 SQLITE_READ t2 b main v1 \
002475 SQLITE_READ v1 x main v1 \
002476 SQLITE_READ v1 x main v1 \
002477 SQLITE_SELECT {} {} {} v1 \
002478 SQLITE_READ v1 x main v1 \
002479 SQLITE_INSERT v1chng {} main r3 \
002480 SQLITE_READ v1 x main r3 \
002481 ]
002482
002483 } ;# ifcapable view && trigger
002484
002485 # Ticket #1338: Make sure authentication works in the presence of an AS
002486 # clause.
002487 #
002488 do_test auth-5.1 {
002489 proc auth {code arg1 arg2 arg3 arg4 args} {
002490 return SQLITE_OK
002491 }
002492 execsql {
002493 SELECT count(a) AS cnt FROM t4 ORDER BY cnt
002494 }
002495 } {1}
002496
002497 # Ticket #1607
002498 #
002499 ifcapable compound&&subquery {
002500 ifcapable trigger {
002501 execsql {
002502 DROP TABLE tx;
002503 }
002504 ifcapable view {
002505 execsql {
002506 DROP TABLE v1chng;
002507 }
002508 }
002509 }
002510 ifcapable stat4 {
002511 set stat4 "sqlite_stat4 "
002512 } else {
002513 set stat4 ""
002514 }
002515 do_test auth-5.2 {
002516 execsql {
002517 SELECT name FROM (
002518 SELECT * FROM sqlite_master UNION ALL SELECT * FROM temp.sqlite_master)
002519 WHERE type='table'
002520 ORDER BY name
002521 }
002522 } "sqlite_stat1 ${stat4}t1 t2 t3 t4"
002523 }
002524
002525 # Ticket #3944
002526 #
002527 ifcapable trigger {
002528 do_test auth-5.3.1 {
002529 execsql {
002530 CREATE TABLE t5 ( x );
002531 CREATE TRIGGER t5_tr1 AFTER INSERT ON t5 BEGIN
002532 UPDATE t5 SET x = 1 WHERE NEW.x = 0;
002533 END;
002534 }
002535 } {}
002536 set ::authargs [list]
002537 proc auth {args} {
002538 eval lappend ::authargs [lrange $args 0 4]
002539 return SQLITE_OK
002540 }
002541 do_test auth-5.3.2 {
002542 execsql { INSERT INTO t5 (x) values(0) }
002543 set ::authargs
002544 } [list SQLITE_INSERT t5 {} main {} \
002545 SQLITE_UPDATE t5 x main t5_tr1 \
002546 SQLITE_READ t5 x main t5_tr1 \
002547 ]
002548 do_test auth-5.3.2 {
002549 execsql { SELECT * FROM t5 }
002550 } {1}
002551 }
002552
002553 # Ticket [0eb70d77cb05bb22720]: Invalid pointer passsed to the authorizer
002554 # callback when updating a ROWID.
002555 #
002556 do_test auth-6.1 {
002557 execsql {
002558 CREATE TABLE t6(a,b,c,d,e,f,g,h);
002559 INSERT INTO t6 VALUES(1,2,3,4,5,6,7,8);
002560 }
002561 } {}
002562 set ::authargs [list]
002563 proc auth {args} {
002564 eval lappend ::authargs [lrange $args 0 4]
002565 return SQLITE_OK
002566 }
002567 do_test auth-6.2 {
002568 execsql {UPDATE t6 SET rowID=rowID+100}
002569 set ::authargs
002570 } [list SQLITE_READ t6 ROWID main {} \
002571 SQLITE_UPDATE t6 ROWID main {} \
002572 ]
002573 do_test auth-6.3 {
002574 execsql {SELECT rowid, * FROM t6}
002575 } {101 1 2 3 4 5 6 7 8}
002576
002577 #-------------------------------------------------------------------------
002578 # Test that view names are included as zArg4.
002579 #
002580 do_execsql_test auth-7.1 {
002581 CREATE TABLE t7(a, b, c);
002582 CREATE VIEW v7 AS SELECT * FROM t7;
002583 } {}
002584 set ::authargs [list]
002585 proc auth {args} {
002586 eval lappend ::authargs [lrange $args 0 4]
002587 return SQLITE_OK
002588 }
002589
002590 do_test auth-7.2 {
002591 execsql {SELECT a, c FROM v7}
002592 set ::authargs
002593 } [list \
002594 SQLITE_SELECT {} {} {} {} \
002595 SQLITE_READ t7 a main v7 \
002596 SQLITE_READ t7 b main v7 \
002597 SQLITE_READ t7 c main v7 \
002598 SQLITE_READ v7 a main {} \
002599 SQLITE_READ v7 c main {} \
002600 SQLITE_SELECT {} {} {} v7 \
002601 ]
002602
002603 set ::authargs [list]
002604 do_test auth-7.3 {
002605 execsql {SELECT a, c FROM t7}
002606 set ::authargs
002607 } [list \
002608 SQLITE_SELECT {} {} {} {} \
002609 SQLITE_READ t7 a main {} \
002610 SQLITE_READ t7 c main {} \
002611 ]
002612
002613 set ::authargs [list]
002614 do_test auth-7.4 {
002615 execsql {SELECT a, c FROM t7 AS v7}
002616 set ::authargs
002617 } [list \
002618 SQLITE_SELECT {} {} {} {} \
002619 SQLITE_READ t7 a main {} \
002620 SQLITE_READ t7 c main {} \
002621 ]
002622
002623 # If a table is referenced but no columns are read from the table,
002624 # that causes a single SQLITE_READ authorization with a NULL column
002625 # name.
002626 #
002627 # EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but
002628 # no column values are extracted from that table (for example in a query
002629 # like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer
002630 # callback is invoked once for that table with a column name that is an
002631 # empty string.
002632 #
002633 set ::authargs [list]
002634 do_test auth-8.1 {
002635 execsql {SELECT count(*) FROM t7}
002636 set ::authargs
002637 } [list \
002638 SQLITE_SELECT {} {} {} {} \
002639 SQLITE_FUNCTION {} count {} {} \
002640 SQLITE_READ t7 {} {} {} \
002641 ]
002642 set ::authargs [list]
002643
002644 do_test auth-8.2 {
002645 execsql {SELECT t6.a FROM t6, t7}
002646 set ::authargs
002647 } [list \
002648 SQLITE_SELECT {} {} {} {} \
002649 SQLITE_READ t6 a main {} \
002650 SQLITE_READ t7 {} {} {} \
002651 ]
002652
002653 # Test also that if SQLITE_DENY is returned from an SQLITE_READ authorizer
002654 # invocation with no column name specified, compilation fails.
002655 #
002656 set ::authargs [list]
002657 proc auth {op args} {
002658 foreach {a b c d} $args break
002659 lappend ::authargs $op $a $b $c $d
002660 if {$op == "SQLITE_READ"} { return "SQLITE_DENY" }
002661 return "SQLITE_OK"
002662 }
002663 set ::authargs [list]
002664 do_catchsql_test auth-8.3 {
002665 SELECT count(*) FROM t7
002666 } {1 {not authorized}}
002667 do_test auth-8.4 {
002668 set ::authargs
002669 } [list \
002670 SQLITE_SELECT {} {} {} {} \
002671 SQLITE_FUNCTION {} count {} {} \
002672 SQLITE_READ t7 {} {} {} \
002673 ]
002674
002675
002676 rename proc {}
002677 rename proc_real proc
002678 finish_test