AryaWu/sqlite
0
1# 2003 April 42#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. The12# focus of this script is testing the sqlite3_set_authorizer() API13# and related functionality.14#15# $Id: auth.test,v 1.46 2009/07/02 18:40:35 danielk1977 Exp $16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is22# defined during compilation.23if {[catch {db auth {}} msg]} {24 finish_test25 return26}27 28rename proc proc_real29proc_real proc {name arguments script} {30 proc_real $name $arguments $script31 if {$name=="auth"} {32 db authorizer ::auth33 }34}35 36do_test auth-1.1.1 {37 db close38 set ::DB [sqlite3 db test.db]39 proc authx {code arg1 arg2 arg3 arg4 args} {return SQLITE_DENY}40 proc auth {code arg1 arg2 arg3 arg4 args} {41 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {42 return SQLITE_DENY43 }44 return SQLITE_OK45 }46 db authorizer ::authx47 # EVIDENCE-OF: R-03993-24285 Only a single authorizer can be in place on48 # a database connection at a time. Each call to sqlite3_set_authorizer49 # overrides the previous call.50 #51 # The authx authorizer above is overridden by the auth authorizer below52 # authx is never invoked.53 db authorizer ::auth54 catchsql {CREATE TABLE t1(a,b,c)}55} {1 {not authorized}}56do_test auth-1.1.2 {57 db errorcode58} {23}59do_test auth-1.1.3 {60 db authorizer61} {::auth}62do_test auth-1.1.4 {63 # Ticket #896.64 catchsql {65 SELECT x;66 }67} {1 {no such column: x}}68do_test auth-1.2 {69 execsql {SELECT name FROM sqlite_master}70} {}71# EVIDENCE-OF: R-04452-49349 When the callback returns SQLITE_DENY, the72# sqlite3_prepare_v2() or equivalent call that triggered the authorizer73# will fail with an error message explaining that access is denied.74do_test auth-1.3.1 {75 proc auth {code arg1 arg2 arg3 arg4 args} {76 if {$code=="SQLITE_CREATE_TABLE"} {77 set ::authargs [list $arg1 $arg2 $arg3 $arg4]78 return SQLITE_DENY79 }80 return SQLITE_OK81 }82 catchsql {CREATE TABLE t1(a,b,c)}83} {1 {not authorized}}84do_test auth-1.3.2 {85 db errorcode86} {23}87do_test auth-1.3.3 {88 set ::authargs89} {t1 {} main {}}90do_test auth-1.4 {91 execsql {SELECT name FROM sqlite_master}92} {}93 94ifcapable tempdb {95 do_test auth-1.5 {96 proc auth {code arg1 arg2 arg3 arg4 args} {97 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {98 return SQLITE_DENY99 }100 return SQLITE_OK101 }102 catchsql {CREATE TEMP TABLE t1(a,b,c)}103 } {1 {not authorized}}104 do_test auth-1.6 {105 execsql {SELECT name FROM temp.sqlite_master}106 } {}107 do_test auth-1.7.1 {108 proc auth {code arg1 arg2 arg3 arg4 args} {109 if {$code=="SQLITE_CREATE_TEMP_TABLE"} {110 set ::authargs [list $arg1 $arg2 $arg3 $arg4]111 return SQLITE_DENY112 }113 return SQLITE_OK114 }115 catchsql {CREATE TEMP TABLE t1(a,b,c)}116 } {1 {not authorized}}117 do_test auth-1.7.2 {118 set ::authargs119 } {t1 {} temp {}}120 do_test auth-1.8 {121 execsql {SELECT name FROM sqlite_temp_master}122 } {}123}124 125do_test auth-1.9 {126 proc auth {code arg1 arg2 arg3 arg4 args} {127 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {128 return SQLITE_IGNORE129 }130 return SQLITE_OK131 }132 catchsql {CREATE TABLE t1(a,b,c)}133} {0 {}}134do_test auth-1.10 {135 execsql {SELECT name FROM sqlite_master}136} {}137do_test auth-1.11 {138 proc auth {code arg1 arg2 arg3 arg4 args} {139 if {$code=="SQLITE_CREATE_TABLE"} {140 set ::authargs [list $arg1 $arg2 $arg3 $arg4]141 return SQLITE_IGNORE142 }143 return SQLITE_OK144 }145 catchsql {CREATE TABLE t1(a,b,c)}146} {0 {}}147do_test auth-1.12 {148 execsql {SELECT name FROM sqlite_master}149} {}150 151ifcapable tempdb {152 do_test auth-1.13 {153 proc auth {code arg1 arg2 arg3 arg4 args} {154 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {155 return SQLITE_IGNORE156 }157 return SQLITE_OK158 }159 catchsql {CREATE TEMP TABLE t1(a,b,c)}160 } {0 {}}161 do_test auth-1.14 {162 execsql {SELECT name FROM temp.sqlite_master}163 } {}164 do_test auth-1.15 {165 proc auth {code arg1 arg2 arg3 arg4 args} {166 if {$code=="SQLITE_CREATE_TEMP_TABLE"} {167 set ::authargs [list $arg1 $arg2 $arg3 $arg4]168 return SQLITE_IGNORE169 }170 return SQLITE_OK171 }172 catchsql {CREATE TEMP TABLE t1(a,b,c)}173 } {0 {}}174 do_test auth-1.16 {175 execsql {SELECT name FROM sqlite_temp_master}176 } {}177 178 do_test auth-1.17 {179 proc auth {code arg1 arg2 arg3 arg4 args} {180 if {$code=="SQLITE_CREATE_TABLE"} {181 set ::authargs [list $arg1 $arg2 $arg3 $arg4]182 return SQLITE_DENY183 }184 return SQLITE_OK185 }186 catchsql {CREATE TEMP TABLE t1(a,b,c)}187 } {0 {}}188 do_test auth-1.18 {189 execsql {SELECT name FROM sqlite_temp_master}190 } {t1}191}192 193do_test auth-1.19.1 {194 set ::authargs {}195 proc auth {code arg1 arg2 arg3 arg4 args} {196 if {$code=="SQLITE_CREATE_TEMP_TABLE"} {197 set ::authargs [list $arg1 $arg2 $arg3 $arg4]198 return SQLITE_DENY199 }200 return SQLITE_OK201 }202 catchsql {CREATE TABLE t2(a,b,c)}203} {0 {}}204do_test auth-1.19.2 {205 set ::authargs206} {}207do_test auth-1.20 {208 execsql {SELECT name FROM sqlite_master}209} {t2}210 211do_test auth-1.21.1 {212 proc auth {code arg1 arg2 arg3 arg4 args} {213 if {$code=="SQLITE_DROP_TABLE"} {214 set ::authargs [list $arg1 $arg2 $arg3 $arg4]215 return SQLITE_DENY216 }217 return SQLITE_OK218 }219 catchsql {DROP TABLE t2}220} {1 {not authorized}}221do_test auth-1.21.2 {222 set ::authargs223} {t2 {} main {}}224do_test auth-1.22 {225 execsql {SELECT name FROM sqlite_master}226} {t2}227do_test auth-1.23.1 {228 proc auth {code arg1 arg2 arg3 arg4 args} {229 if {$code=="SQLITE_DROP_TABLE"} {230 set ::authargs [list $arg1 $arg2 $arg3 $arg4]231 return SQLITE_IGNORE232 }233 return SQLITE_OK234 }235 catchsql {DROP TABLE t2}236} {0 {}}237do_test auth-1.23.2 {238 set ::authargs239} {t2 {} main {}}240do_test auth-1.24 {241 execsql {SELECT name FROM sqlite_master}242} {t2}243 244ifcapable tempdb {245 do_test auth-1.25 {246 proc auth {code arg1 arg2 arg3 arg4 args} {247 if {$code=="SQLITE_DROP_TEMP_TABLE"} {248 set ::authargs [list $arg1 $arg2 $arg3 $arg4]249 return SQLITE_DENY250 }251 return SQLITE_OK252 }253 catchsql {DROP TABLE t1}254 } {1 {not authorized}}255 do_test auth-1.26 {256 execsql {SELECT name FROM sqlite_temp_master}257 } {t1}258 do_test auth-1.27 {259 proc auth {code arg1 arg2 arg3 arg4 args} {260 if {$code=="SQLITE_DROP_TEMP_TABLE"} {261 set ::authargs [list $arg1 $arg2 $arg3 $arg4]262 return SQLITE_IGNORE263 }264 return SQLITE_OK265 }266 catchsql {DROP TABLE t1}267 } {0 {}}268 do_test auth-1.28 {269 execsql {SELECT name FROM sqlite_temp_master}270 } {t1}271}272 273do_test auth-1.29 {274 proc auth {code arg1 arg2 arg3 arg4 args} {275 if {$code=="SQLITE_INSERT" && $arg1=="t2"} {276 return SQLITE_DENY277 }278 return SQLITE_OK279 }280 catchsql {INSERT INTO t2 VALUES(1,2,3)}281} {1 {not authorized}}282do_test auth-1.30 {283 execsql {SELECT * FROM t2}284} {}285do_test auth-1.31 {286 proc auth {code arg1 arg2 arg3 arg4 args} {287 if {$code=="SQLITE_INSERT" && $arg1=="t2"} {288 return SQLITE_IGNORE289 }290 return SQLITE_OK291 }292 catchsql {INSERT INTO t2 VALUES(1,2,3)}293} {0 {}}294do_test auth-1.32 {295 execsql {SELECT * FROM t2}296} {}297do_test auth-1.33 {298 proc auth {code arg1 arg2 arg3 arg4 args} {299 if {$code=="SQLITE_INSERT" && $arg1=="t1"} {300 return SQLITE_IGNORE301 }302 return SQLITE_OK303 }304 catchsql {INSERT INTO t2 VALUES(1,2,3)}305} {0 {}}306do_test auth-1.34 {307 execsql {SELECT * FROM t2}308} {1 2 3}309 310do_test auth-1.35.1 {311 proc auth {code arg1 arg2 arg3 arg4 args} {312 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {313 return SQLITE_DENY314 }315 return SQLITE_OK316 }317 catchsql {SELECT * FROM t2}318} {1 {access to t2.b is prohibited}}319ifcapable attach {320 do_test auth-1.35.2 {321 execsql {ATTACH DATABASE 'test.db' AS two}322 catchsql {SELECT * FROM two.t2}323 } {1 {access to two.t2.b is prohibited}}324 execsql {DETACH DATABASE two}325}326# EVIDENCE-OF: R-38392-49970 If the action code is SQLITE_READ and the327# callback returns SQLITE_IGNORE then the prepared statement statement328# is constructed to substitute a NULL value in place of the table column329# that would have been read if SQLITE_OK had been returned.330do_test auth-1.36 {331 proc auth {code arg1 arg2 arg3 arg4 args} {332 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {333 return SQLITE_IGNORE334 }335 return SQLITE_OK336 }337 catchsql {SELECT * FROM t2}338} {0 {1 {} 3}}339do_test auth-1.37 {340 proc auth {code arg1 arg2 arg3 arg4 args} {341 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {342 return SQLITE_IGNORE343 }344 return SQLITE_OK345 }346 catchsql {SELECT * FROM t2 WHERE b=2}347} {0 {}}348do_test auth-1.38 {349 proc auth {code arg1 arg2 arg3 arg4 args} {350 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} {351 return SQLITE_IGNORE352 }353 return SQLITE_OK354 }355 catchsql {SELECT * FROM t2 WHERE b=2}356} {0 {{} 2 3}}357do_test auth-1.39 {358 proc auth {code arg1 arg2 arg3 arg4 args} {359 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {360 return SQLITE_IGNORE361 }362 return SQLITE_OK363 }364 catchsql {SELECT * FROM t2 WHERE b IS NULL}365} {0 {1 {} 3}}366do_test auth-1.40 {367 proc auth {code arg1 arg2 arg3 arg4 args} {368 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} {369 return SQLITE_DENY370 }371 return SQLITE_OK372 }373 catchsql {SELECT a,c FROM t2 WHERE b IS NULL}374} {1 {access to t2.b is prohibited}}375 376do_test auth-1.41 {377 proc auth {code arg1 arg2 arg3 arg4 args} {378 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {379 return SQLITE_DENY380 }381 return SQLITE_OK382 }383 catchsql {UPDATE t2 SET a=11}384} {0 {}}385do_test auth-1.42 {386 execsql {SELECT * FROM t2}387} {11 2 3}388do_test auth-1.43 {389 proc auth {code arg1 arg2 arg3 arg4 args} {390 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {391 return SQLITE_DENY392 }393 return SQLITE_OK394 }395 catchsql {UPDATE t2 SET b=22, c=33}396} {1 {not authorized}}397do_test auth-1.44 {398 execsql {SELECT * FROM t2}399} {11 2 3}400do_test auth-1.45 {401 proc auth {code arg1 arg2 arg3 arg4 args} {402 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} {403 return SQLITE_IGNORE404 }405 return SQLITE_OK406 }407 catchsql {UPDATE t2 SET b=22, c=33}408} {0 {}}409do_test auth-1.46 {410 execsql {SELECT * FROM t2}411} {11 2 33}412 413do_test auth-1.47 {414 proc auth {code arg1 arg2 arg3 arg4 args} {415 if {$code=="SQLITE_DELETE" && $arg1=="t2"} {416 return SQLITE_DENY417 }418 return SQLITE_OK419 }420 catchsql {DELETE FROM t2 WHERE a=11}421} {1 {not authorized}}422do_test auth-1.48 {423 execsql {SELECT * FROM t2}424} {11 2 33}425do_test auth-1.49 {426 proc auth {code arg1 arg2 arg3 arg4 args} {427 if {$code=="SQLITE_DELETE" && $arg1=="t2"} {428 return SQLITE_IGNORE429 }430 return SQLITE_OK431 }432 catchsql {DELETE FROM t2 WHERE a=11}433} {0 {}}434do_test auth-1.50 {435 execsql {SELECT * FROM t2}436} {}437do_test auth-1.50.2 {438 execsql {INSERT INTO t2 VALUES(11, 2, 33)}439} {}440 441do_test auth-1.51 {442 proc auth {code arg1 arg2 arg3 arg4 args} {443 if {$code=="SQLITE_SELECT"} {444 return SQLITE_DENY445 }446 return SQLITE_OK447 }448 catchsql {SELECT * FROM t2}449} {1 {not authorized}}450do_test auth-1.52 {451 proc auth {code arg1 arg2 arg3 arg4 args} {452 if {$code=="SQLITE_SELECT"} {453 return SQLITE_IGNORE454 }455 return SQLITE_OK456 }457 catchsql {SELECT * FROM t2}458} {0 {}}459do_test auth-1.53 {460 proc auth {code arg1 arg2 arg3 arg4 args} {461 if {$code=="SQLITE_SELECT"} {462 return SQLITE_OK463 }464 return SQLITE_OK465 }466 catchsql {SELECT * FROM t2}467} {0 {11 2 33}}468 469# Update for version 3: There used to be a handful of test here that470# tested the authorisation callback with the COPY command. The following471# test makes the same database modifications as they used to.472do_test auth-1.54 {473 execsql {INSERT INTO t2 VALUES(7, 8, 9);}474} {}475do_test auth-1.55 {476 execsql {SELECT * FROM t2}477} {11 2 33 7 8 9}478 479do_test auth-1.63 {480 proc auth {code arg1 arg2 arg3 arg4 args} {481 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {482 return SQLITE_DENY483 }484 return SQLITE_OK485 }486 catchsql {DROP TABLE t2}487} {1 {not authorized}}488do_test auth-1.64 {489 execsql {SELECT name FROM sqlite_master}490} {t2}491do_test auth-1.65 {492 proc auth {code arg1 arg2 arg3 arg4 args} {493 if {$code=="SQLITE_DELETE" && $arg1=="t2"} {494 return SQLITE_DENY495 }496 return SQLITE_OK497 }498 catchsql {DROP TABLE t2}499} {1 {not authorized}}500do_test auth-1.66 {501 execsql {SELECT name FROM sqlite_master}502} {t2}503 504ifcapable tempdb {505 do_test auth-1.67 {506 proc auth {code arg1 arg2 arg3 arg4 args} {507 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {508 return SQLITE_DENY509 }510 return SQLITE_OK511 }512 catchsql {DROP TABLE t1}513 } {1 {not authorized}}514 do_test auth-1.68 {515 execsql {SELECT name FROM sqlite_temp_master}516 } {t1}517 do_test auth-1.69 {518 proc auth {code arg1 arg2 arg3 arg4 args} {519 if {$code=="SQLITE_DELETE" && $arg1=="t1"} {520 return SQLITE_DENY521 }522 return SQLITE_OK523 }524 catchsql {DROP TABLE t1}525 } {1 {not authorized}}526 do_test auth-1.70 {527 execsql {SELECT name FROM sqlite_temp_master}528 } {t1}529}530 531do_test auth-1.71 {532 proc auth {code arg1 arg2 arg3 arg4 args} {533 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {534 return SQLITE_IGNORE535 }536 return SQLITE_OK537 }538 catchsql {DROP TABLE t2}539} {0 {}}540do_test auth-1.72 {541 execsql {SELECT name FROM sqlite_master}542} {t2}543do_test auth-1.73 {544 proc auth {code arg1 arg2 arg3 arg4 args} {545 if {$code=="SQLITE_DELETE" && $arg1=="t2"} {546 return SQLITE_IGNORE547 }548 return SQLITE_OK549 }550 catchsql {DROP TABLE t2}551} {0 {}}552do_test auth-1.74 {553 execsql {SELECT name FROM sqlite_master}554} {t2}555 556ifcapable tempdb {557 do_test auth-1.75 {558 proc auth {code arg1 arg2 arg3 arg4 args} {559 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {560 return SQLITE_IGNORE561 }562 return SQLITE_OK563 }564 catchsql {DROP TABLE t1}565 } {0 {}}566 do_test auth-1.76 {567 execsql {SELECT name FROM sqlite_temp_master}568 } {t1}569 do_test auth-1.77 {570 proc auth {code arg1 arg2 arg3 arg4 args} {571 if {$code=="SQLITE_DELETE" && $arg1=="t1"} {572 return SQLITE_IGNORE573 }574 return SQLITE_OK575 }576 catchsql {DROP TABLE t1}577 } {0 {}}578 do_test auth-1.78 {579 execsql {SELECT name FROM temp.sqlite_master}580 } {t1}581}582 583# Test cases auth-1.79 to auth-1.124 test creating and dropping views.584# Omit these if the library was compiled with views omitted.585ifcapable view {586do_test auth-1.79 {587 proc auth {code arg1 arg2 arg3 arg4 args} {588 if {$code=="SQLITE_CREATE_VIEW"} {589 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 590 return SQLITE_DENY591 }592 return SQLITE_OK593 }594 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}595} {1 {not authorized}}596do_test auth-1.80 {597 set ::authargs598} {v1 {} main {}}599do_test auth-1.81 {600 execsql {SELECT name FROM sqlite_master}601} {t2}602do_test auth-1.82 {603 proc auth {code arg1 arg2 arg3 arg4 args} {604 if {$code=="SQLITE_CREATE_VIEW"} {605 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 606 return SQLITE_IGNORE607 }608 return SQLITE_OK609 }610 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}611} {0 {}}612do_test auth-1.83 {613 set ::authargs614} {v1 {} main {}}615do_test auth-1.84 {616 execsql {SELECT name FROM sqlite_master}617} {t2}618 619ifcapable tempdb {620 do_test auth-1.85 {621 proc auth {code arg1 arg2 arg3 arg4 args} {622 if {$code=="SQLITE_CREATE_TEMP_VIEW"} {623 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 624 return SQLITE_DENY625 }626 return SQLITE_OK627 }628 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}629 } {1 {not authorized}}630 do_test auth-1.86 {631 set ::authargs632 } {v1 {} temp {}}633 do_test auth-1.87 {634 execsql {SELECT name FROM sqlite_temp_master}635 } {t1}636 do_test auth-1.88 {637 proc auth {code arg1 arg2 arg3 arg4 args} {638 if {$code=="SQLITE_CREATE_TEMP_VIEW"} {639 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 640 return SQLITE_IGNORE641 }642 return SQLITE_OK643 }644 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}645 } {0 {}}646 do_test auth-1.89 {647 set ::authargs648 } {v1 {} temp {}}649 do_test auth-1.90 {650 execsql {SELECT name FROM temp.sqlite_master}651 } {t1}652}653 654do_test auth-1.91 {655 proc auth {code arg1 arg2 arg3 arg4 args} {656 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {657 return SQLITE_DENY658 }659 return SQLITE_OK660 }661 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}662} {1 {not authorized}}663do_test auth-1.92 {664 execsql {SELECT name FROM sqlite_master}665} {t2}666do_test auth-1.93 {667 proc auth {code arg1 arg2 arg3 arg4 args} {668 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {669 return SQLITE_IGNORE670 }671 return SQLITE_OK672 }673 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}674} {0 {}}675do_test auth-1.94 {676 execsql {SELECT name FROM sqlite_master}677} {t2}678 679ifcapable tempdb {680 do_test auth-1.95 {681 proc auth {code arg1 arg2 arg3 arg4 args} {682 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {683 return SQLITE_DENY684 }685 return SQLITE_OK686 }687 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}688 } {1 {not authorized}}689 do_test auth-1.96 {690 execsql {SELECT name FROM sqlite_temp_master}691 } {t1}692 do_test auth-1.97 {693 proc auth {code arg1 arg2 arg3 arg4 args} {694 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {695 return SQLITE_IGNORE696 }697 return SQLITE_OK698 }699 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}700 } {0 {}}701 do_test auth-1.98 {702 execsql {SELECT name FROM sqlite_temp_master}703 } {t1}704}705 706do_test auth-1.99 {707 proc auth {code arg1 arg2 arg3 arg4 args} {708 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {709 return SQLITE_DENY710 }711 return SQLITE_OK712 }713 catchsql {714 CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2;715 DROP VIEW v2716 }717} {1 {not authorized}}718do_test auth-1.100 {719 execsql {SELECT name FROM sqlite_master}720} {t2 v2}721do_test auth-1.101 {722 proc auth {code arg1 arg2 arg3 arg4 args} {723 if {$code=="SQLITE_DROP_VIEW"} {724 set ::authargs [list $arg1 $arg2 $arg3 $arg4]725 return SQLITE_DENY726 }727 return SQLITE_OK728 }729 catchsql {DROP VIEW v2}730} {1 {not authorized}}731do_test auth-1.102 {732 set ::authargs733} {v2 {} main {}}734do_test auth-1.103 {735 execsql {SELECT name FROM sqlite_master}736} {t2 v2}737do_test auth-1.104 {738 proc auth {code arg1 arg2 arg3 arg4 args} {739 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {740 return SQLITE_IGNORE741 }742 return SQLITE_OK743 }744 catchsql {DROP VIEW v2}745} {0 {}}746do_test auth-1.105 {747 execsql {SELECT name FROM sqlite_master}748} {t2 v2}749do_test auth-1.106 {750 proc auth {code arg1 arg2 arg3 arg4 args} {751 if {$code=="SQLITE_DROP_VIEW"} {752 set ::authargs [list $arg1 $arg2 $arg3 $arg4]753 return SQLITE_IGNORE754 }755 return SQLITE_OK756 }757 catchsql {DROP VIEW v2}758} {0 {}}759do_test auth-1.107 {760 set ::authargs761} {v2 {} main {}}762do_test auth-1.108 {763 execsql {SELECT name FROM sqlite_master}764} {t2 v2}765do_test auth-1.109 {766 proc auth {code arg1 arg2 arg3 arg4 args} {767 if {$code=="SQLITE_DROP_VIEW"} {768 set ::authargs [list $arg1 $arg2 $arg3 $arg4]769 return SQLITE_OK770 }771 return SQLITE_OK772 }773 catchsql {DROP VIEW v2}774} {0 {}}775do_test auth-1.110 {776 set ::authargs777} {v2 {} main {}}778do_test auth-1.111 {779 execsql {SELECT name FROM sqlite_master}780} {t2}781 782 783ifcapable tempdb {784 do_test auth-1.112 {785 proc auth {code arg1 arg2 arg3 arg4 args} {786 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {787 return SQLITE_DENY788 }789 return SQLITE_OK790 }791 catchsql {792 CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1;793 DROP VIEW v1794 }795 } {1 {not authorized}}796 do_test auth-1.113 {797 execsql {SELECT name FROM temp.sqlite_master}798 } {t1 v1}799 do_test auth-1.114 {800 proc auth {code arg1 arg2 arg3 arg4 args} {801 if {$code=="SQLITE_DROP_TEMP_VIEW"} {802 set ::authargs [list $arg1 $arg2 $arg3 $arg4]803 return SQLITE_DENY804 }805 return SQLITE_OK806 }807 catchsql {DROP VIEW v1}808 } {1 {not authorized}}809 do_test auth-1.115 {810 set ::authargs811 } {v1 {} temp {}}812 do_test auth-1.116 {813 execsql {SELECT name FROM sqlite_temp_master}814 } {t1 v1}815 do_test auth-1.117 {816 proc auth {code arg1 arg2 arg3 arg4 args} {817 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {818 return SQLITE_IGNORE819 }820 return SQLITE_OK821 }822 catchsql {DROP VIEW v1}823 } {0 {}}824 do_test auth-1.118 {825 execsql {SELECT name FROM sqlite_temp_master}826 } {t1 v1}827 do_test auth-1.119 {828 proc auth {code arg1 arg2 arg3 arg4 args} {829 if {$code=="SQLITE_DROP_TEMP_VIEW"} {830 set ::authargs [list $arg1 $arg2 $arg3 $arg4]831 return SQLITE_IGNORE832 }833 return SQLITE_OK834 }835 catchsql {DROP VIEW v1}836 } {0 {}}837 do_test auth-1.120 {838 set ::authargs839 } {v1 {} temp {}}840 do_test auth-1.121 {841 execsql {SELECT name FROM temp.sqlite_master}842 } {t1 v1}843 do_test auth-1.122 {844 proc auth {code arg1 arg2 arg3 arg4 args} {845 if {$code=="SQLITE_DROP_TEMP_VIEW"} {846 set ::authargs [list $arg1 $arg2 $arg3 $arg4]847 return SQLITE_OK848 }849 return SQLITE_OK850 }851 catchsql {DROP VIEW v1}852 } {0 {}}853 do_test auth-1.123 {854 set ::authargs855 } {v1 {} temp {}}856 do_test auth-1.124 {857 execsql {SELECT name FROM sqlite_temp_master}858 } {t1}859}860} ;# ifcapable view861 862# Test cases auth-1.125 to auth-1.176 test creating and dropping triggers.863# Omit these if the library was compiled with triggers omitted.864#865ifcapable trigger&&tempdb {866do_test auth-1.125 {867 proc auth {code arg1 arg2 arg3 arg4 args} {868 if {$code=="SQLITE_CREATE_TRIGGER"} {869 set ::authargs [list $arg1 $arg2 $arg3 $arg4]870 return SQLITE_DENY871 }872 return SQLITE_OK873 }874 catchsql {875 CREATE TRIGGER r2 DELETE on t2 BEGIN876 SELECT NULL;877 END;878 }879} {1 {not authorized}}880do_test auth-1.126 {881 set ::authargs882} {r2 t2 main {}}883do_test auth-1.127 {884 execsql {SELECT name FROM sqlite_master}885} {t2}886do_test auth-1.128 {887 proc auth {code arg1 arg2 arg3 arg4 args} {888 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {889 return SQLITE_DENY890 }891 return SQLITE_OK892 }893 catchsql {894 CREATE TRIGGER r2 DELETE on t2 BEGIN895 SELECT NULL;896 END;897 }898} {1 {not authorized}}899do_test auth-1.129 {900 execsql {SELECT name FROM sqlite_master}901} {t2}902do_test auth-1.130 {903 proc auth {code arg1 arg2 arg3 arg4 args} {904 if {$code=="SQLITE_CREATE_TRIGGER"} {905 set ::authargs [list $arg1 $arg2 $arg3 $arg4]906 return SQLITE_IGNORE907 }908 return SQLITE_OK909 }910 catchsql {911 CREATE TRIGGER r2 DELETE on t2 BEGIN912 SELECT NULL;913 END;914 }915} {0 {}}916do_test auth-1.131 {917 set ::authargs918} {r2 t2 main {}}919do_test auth-1.132 {920 execsql {SELECT name FROM sqlite_master}921} {t2}922do_test auth-1.133 {923 proc auth {code arg1 arg2 arg3 arg4 args} {924 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {925 return SQLITE_IGNORE926 }927 return SQLITE_OK928 }929 catchsql {930 CREATE TRIGGER r2 DELETE on t2 BEGIN931 SELECT NULL;932 END;933 }934} {0 {}}935do_test auth-1.134 {936 execsql {SELECT name FROM sqlite_master}937} {t2}938do_test auth-1.135 {939 proc auth {code arg1 arg2 arg3 arg4 args} {940 if {$code=="SQLITE_CREATE_TRIGGER"} {941 set ::authargs [list $arg1 $arg2 $arg3 $arg4]942 return SQLITE_OK943 }944 return SQLITE_OK945 }946 catchsql {947 CREATE TABLE tx(id);948 CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN949 INSERT INTO tx VALUES(NEW.rowid);950 END;951 }952} {0 {}}953do_test auth-1.136.1 {954 set ::authargs955} {r2 t2 main {}}956do_test auth-1.136.2 {957 execsql {958 SELECT name FROM sqlite_master WHERE type='trigger'959 }960} {r2}961do_test auth-1.136.3 {962 proc auth {code arg1 arg2 arg3 arg4 args} {963 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4964 return SQLITE_OK965 }966 set ::authargs {}967 execsql {968 INSERT INTO t2 VALUES(1,2,3);969 }970 set ::authargs 971} {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2}972do_test auth-1.136.4 {973 execsql {974 SELECT * FROM tx;975 }976} {3}977do_test auth-1.137 {978 execsql {SELECT name FROM sqlite_master}979} {t2 tx r2}980do_test auth-1.138 {981 proc auth {code arg1 arg2 arg3 arg4 args} {982 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {983 set ::authargs [list $arg1 $arg2 $arg3 $arg4]984 return SQLITE_DENY985 }986 return SQLITE_OK987 }988 catchsql {989 CREATE TRIGGER r1 DELETE on t1 BEGIN990 SELECT NULL;991 END;992 }993} {1 {not authorized}}994do_test auth-1.139 {995 set ::authargs996} {r1 t1 temp {}}997do_test auth-1.140 {998 execsql {SELECT name FROM temp.sqlite_master}999} {t1}1000do_test auth-1.141 {1001 proc auth {code arg1 arg2 arg3 arg4 args} {1002 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {1003 return SQLITE_DENY1004 }1005 return SQLITE_OK1006 }1007 catchsql {1008 CREATE TRIGGER r1 DELETE on t1 BEGIN1009 SELECT NULL;1010 END;1011 }1012} {1 {not authorized}}1013do_test auth-1.142 {1014 execsql {SELECT name FROM sqlite_temp_master}1015} {t1}1016do_test auth-1.143 {1017 proc auth {code arg1 arg2 arg3 arg4 args} {1018 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {1019 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1020 return SQLITE_IGNORE1021 }1022 return SQLITE_OK1023 }1024 catchsql {1025 CREATE TRIGGER r1 DELETE on t1 BEGIN1026 SELECT NULL;1027 END;1028 }1029} {0 {}}1030do_test auth-1.144 {1031 set ::authargs1032} {r1 t1 temp {}}1033do_test auth-1.145 {1034 execsql {SELECT name FROM temp.sqlite_master}1035} {t1}1036do_test auth-1.146 {1037 proc auth {code arg1 arg2 arg3 arg4 args} {1038 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {1039 return SQLITE_IGNORE1040 }1041 return SQLITE_OK1042 }1043 catchsql {1044 CREATE TRIGGER r1 DELETE on t1 BEGIN1045 SELECT NULL;1046 END;1047 }1048} {0 {}}1049do_test auth-1.147 {1050 execsql {SELECT name FROM sqlite_temp_master}1051} {t1}1052do_test auth-1.148 {1053 proc auth {code arg1 arg2 arg3 arg4 args} {1054 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} {1055 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1056 return SQLITE_OK1057 }1058 return SQLITE_OK1059 }1060 catchsql {1061 CREATE TRIGGER r1 DELETE on t1 BEGIN1062 SELECT NULL;1063 END;1064 }1065} {0 {}}1066do_test auth-1.149 {1067 set ::authargs1068} {r1 t1 temp {}}1069do_test auth-1.150 {1070 execsql {SELECT name FROM temp.sqlite_master}1071} {t1 r1}1072 1073do_test auth-1.151 {1074 proc auth {code arg1 arg2 arg3 arg4 args} {1075 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {1076 return SQLITE_DENY1077 }1078 return SQLITE_OK1079 }1080 catchsql {DROP TRIGGER r2}1081} {1 {not authorized}}1082do_test auth-1.152 {1083 execsql {SELECT name FROM sqlite_master}1084} {t2 tx r2}1085do_test auth-1.153 {1086 proc auth {code arg1 arg2 arg3 arg4 args} {1087 if {$code=="SQLITE_DROP_TRIGGER"} {1088 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1089 return SQLITE_DENY1090 }1091 return SQLITE_OK1092 }1093 catchsql {DROP TRIGGER r2}1094} {1 {not authorized}}1095do_test auth-1.154 {1096 set ::authargs1097} {r2 t2 main {}}1098do_test auth-1.155 {1099 execsql {SELECT name FROM sqlite_master}1100} {t2 tx r2}1101do_test auth-1.156 {1102 proc auth {code arg1 arg2 arg3 arg4 args} {1103 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {1104 return SQLITE_IGNORE1105 }1106 return SQLITE_OK1107 }1108 catchsql {DROP TRIGGER r2}1109} {0 {}}1110do_test auth-1.157 {1111 execsql {SELECT name FROM sqlite_master}1112} {t2 tx r2}1113do_test auth-1.158 {1114 proc auth {code arg1 arg2 arg3 arg4 args} {1115 if {$code=="SQLITE_DROP_TRIGGER"} {1116 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1117 return SQLITE_IGNORE1118 }1119 return SQLITE_OK1120 }1121 catchsql {DROP TRIGGER r2}1122} {0 {}}1123do_test auth-1.159 {1124 set ::authargs1125} {r2 t2 main {}}1126do_test auth-1.160 {1127 execsql {SELECT name FROM sqlite_master}1128} {t2 tx r2}1129do_test auth-1.161 {1130 proc auth {code arg1 arg2 arg3 arg4 args} {1131 if {$code=="SQLITE_DROP_TRIGGER"} {1132 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1133 return SQLITE_OK1134 }1135 return SQLITE_OK1136 }1137 catchsql {DROP TRIGGER r2}1138} {0 {}}1139do_test auth-1.162 {1140 set ::authargs1141} {r2 t2 main {}}1142do_test auth-1.163 {1143 execsql {1144 DROP TABLE tx;1145 DELETE FROM t2 WHERE a=1 AND b=2 AND c=3;1146 SELECT name FROM sqlite_master;1147 }1148} {t2}1149 1150do_test auth-1.164 {1151 proc auth {code arg1 arg2 arg3 arg4 args} {1152 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {1153 return SQLITE_DENY1154 }1155 return SQLITE_OK1156 }1157 catchsql {DROP TRIGGER r1}1158} {1 {not authorized}}1159do_test auth-1.165 {1160 execsql {SELECT name FROM temp.sqlite_master}1161} {t1 r1}1162do_test auth-1.166 {1163 proc auth {code arg1 arg2 arg3 arg4 args} {1164 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {1165 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1166 return SQLITE_DENY1167 }1168 return SQLITE_OK1169 }1170 catchsql {DROP TRIGGER r1}1171} {1 {not authorized}}1172do_test auth-1.167 {1173 set ::authargs1174} {r1 t1 temp {}}1175do_test auth-1.168 {1176 execsql {SELECT name FROM sqlite_temp_master}1177} {t1 r1}1178do_test auth-1.169 {1179 proc auth {code arg1 arg2 arg3 arg4 args} {1180 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {1181 return SQLITE_IGNORE1182 }1183 return SQLITE_OK1184 }1185 catchsql {DROP TRIGGER r1}1186} {0 {}}1187do_test auth-1.170 {1188 execsql {SELECT name FROM temp.sqlite_master}1189} {t1 r1}1190do_test auth-1.171 {1191 proc auth {code arg1 arg2 arg3 arg4 args} {1192 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} {1193 set ::authargs [list $arg1 $arg2 $arg3 $arg4]1194 return SQLITE_IGNORE1195 }1196 return SQLITE_OK1197 }1198 catchsql {DROP TRIGGER r1}1199} {0 {}}1200do_test auth-1.172 {