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