1# 2003 April 4 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this script is testing the sqlite3_set_authorizer() API 13# and related functionality. 14# 15# $Id: auth.test,v 1.46 2009/07/02 18:40:35 danielk1977 Exp $ 16# 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is 22# defined during compilation. 23if {[catch {db auth {}} msg]} { 24 finish_test 25 return 26} 27 28rename proc proc_real 29proc_real proc {name arguments script} { 30 proc_real $name $arguments $script 31 if {$name=="auth"} { 32 db authorizer ::auth 33 } 34} 35 36do_test auth-1.1.1 { 37 db close 38 set ::DB [sqlite3 db test.db] 39 proc authx {code arg1 arg2 arg3 arg4 args} {return SQLITE_DENY} 40 proc auth {code arg1 arg2 arg3 arg4 args} { 41 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 42 return SQLITE_DENY 43 } 44 return SQLITE_OK 45 } 46 db authorizer ::authx 47 # EVIDENCE-OF: R-03993-24285 Only a single authorizer can be in place on 48 # a database connection at a time. Each call to sqlite3_set_authorizer 49 # overrides the previous call. 50 # 51 # The authx authorizer above is overridden by the auth authorizer below 52 # authx is never invoked. 53 db authorizer ::auth 54 catchsql {CREATE TABLE t1(a,b,c)} 55} {1 {not authorized}} 56do_test auth-1.1.2 { 57 db errorcode 58} {23} 59do_test auth-1.1.3 { 60 db authorizer 61} {::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, the 72# sqlite3_prepare_v2() or equivalent call that triggered the authorizer 73# 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_DENY 79 } 80 return SQLITE_OK 81 } 82 catchsql {CREATE TABLE t1(a,b,c)} 83} {1 {not authorized}} 84do_test auth-1.3.2 { 85 db errorcode 86} {23} 87do_test auth-1.3.3 { 88 set ::authargs 89} {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_DENY 99 } 100 return SQLITE_OK 101 } 102 catchsql {CREATE TEMP TABLE t1(a,b,c)} 103 } {1 {not authorized}} 104 do_test auth-1.6 { 105 execsql {SELECT name FROM temp.sqlite_master} 106 } {} 107 do_test auth-1.7.1 { 108 proc auth {code arg1 arg2 arg3 arg4 args} { 109 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 110 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 111 return SQLITE_DENY 112 } 113 return SQLITE_OK 114 } 115 catchsql {CREATE TEMP TABLE t1(a,b,c)} 116 } {1 {not authorized}} 117 do_test auth-1.7.2 { 118 set ::authargs 119 } {t1 {} temp {}} 120 do_test auth-1.8 { 121 execsql {SELECT name FROM sqlite_temp_master} 122 } {} 123} 124 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_IGNORE 129 } 130 return SQLITE_OK 131 } 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_IGNORE 142 } 143 return SQLITE_OK 144 } 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_IGNORE 156 } 157 return SQLITE_OK 158 } 159 catchsql {CREATE TEMP TABLE t1(a,b,c)} 160 } {0 {}} 161 do_test auth-1.14 { 162 execsql {SELECT name FROM temp.sqlite_master} 163 } {} 164 do_test auth-1.15 { 165 proc auth {code arg1 arg2 arg3 arg4 args} { 166 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 167 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 168 return SQLITE_IGNORE 169 } 170 return SQLITE_OK 171 } 172 catchsql {CREATE TEMP TABLE t1(a,b,c)} 173 } {0 {}} 174 do_test auth-1.16 { 175 execsql {SELECT name FROM sqlite_temp_master} 176 } {} 177 178 do_test auth-1.17 { 179 proc auth {code arg1 arg2 arg3 arg4 args} { 180 if {$code=="SQLITE_CREATE_TABLE"} { 181 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 182 return SQLITE_DENY 183 } 184 return SQLITE_OK 185 } 186 catchsql {CREATE TEMP TABLE t1(a,b,c)} 187 } {0 {}} 188 do_test auth-1.18 { 189 execsql {SELECT name FROM sqlite_temp_master} 190 } {t1} 191} 192 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_DENY 199 } 200 return SQLITE_OK 201 } 202 catchsql {CREATE TABLE t2(a,b,c)} 203} {0 {}} 204do_test auth-1.19.2 { 205 set ::authargs 206} {} 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_DENY 216 } 217 return SQLITE_OK 218 } 219 catchsql {DROP TABLE t2} 220} {1 {not authorized}} 221do_test auth-1.21.2 { 222 set ::authargs 223} {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_IGNORE 232 } 233 return SQLITE_OK 234 } 235 catchsql {DROP TABLE t2} 236} {0 {}} 237do_test auth-1.23.2 { 238 set ::authargs 239} {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_DENY 250 } 251 return SQLITE_OK 252 } 253 catchsql {DROP TABLE t1} 254 } {1 {not authorized}} 255 do_test auth-1.26 { 256 execsql {SELECT name FROM sqlite_temp_master} 257 } {t1} 258 do_test auth-1.27 { 259 proc auth {code arg1 arg2 arg3 arg4 args} { 260 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 261 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 262 return SQLITE_IGNORE 263 } 264 return SQLITE_OK 265 } 266 catchsql {DROP TABLE t1} 267 } {0 {}} 268 do_test auth-1.28 { 269 execsql {SELECT name FROM sqlite_temp_master} 270 } {t1} 271} 272 273do_test auth-1.29 { 274 proc auth {code arg1 arg2 arg3 arg4 args} { 275 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 276 return SQLITE_DENY 277 } 278 return SQLITE_OK 279 } 280 catchsql {INSERT INTO t2 VALUES(1,2,3)} 281} {1 {not authorized}} 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_IGNORE 289 } 290 return SQLITE_OK 291 } 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_IGNORE 301 } 302 return SQLITE_OK 303 } 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_DENY 314 } 315 return SQLITE_OK 316 } 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 the 327# callback returns SQLITE_IGNORE then the prepared statement statement 328# is constructed to substitute a NULL value in place of the table column 329# that would have been read if SQLITE_OK had been returned. 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_IGNORE 334 } 335 return SQLITE_OK 336 } 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_IGNORE 343 } 344 return SQLITE_OK 345 } 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_IGNORE 352 } 353 return SQLITE_OK 354 } 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_IGNORE 361 } 362 return SQLITE_OK 363 } 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_DENY 370 } 371 return SQLITE_OK 372 } 373 catchsql {SELECT a,c FROM t2 WHERE b IS NULL} 374} {1 {access to t2.b is prohibited}} 375 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_DENY 380 } 381 return SQLITE_OK 382 } 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_DENY 392 } 393 return SQLITE_OK 394 } 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_IGNORE 404 } 405 return SQLITE_OK 406 } 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_DENY 417 } 418 return SQLITE_OK 419 } 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_IGNORE 429 } 430 return SQLITE_OK 431 } 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_DENY 445 } 446 return SQLITE_OK 447 } 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_IGNORE 454 } 455 return SQLITE_OK 456 } 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_OK 463 } 464 return SQLITE_OK 465 } 466 catchsql {SELECT * FROM t2} 467} {0 {11 2 33}} 468 469# Update for version 3: There used to be a handful of test here that 470# tested the authorisation callback with the COPY command. The following 471# test makes the same database modifications as they used to. 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_DENY 483 } 484 return SQLITE_OK 485 } 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_DENY 495 } 496 return SQLITE_OK 497 } 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_DENY 509 } 510 return SQLITE_OK 511 } 512 catchsql {DROP TABLE t1} 513 } {1 {not authorized}} 514 do_test auth-1.68 { 515 execsql {SELECT name FROM sqlite_temp_master} 516 } {t1} 517 do_test auth-1.69 { 518 proc auth {code arg1 arg2 arg3 arg4 args} { 519 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 520 return SQLITE_DENY 521 } 522 return SQLITE_OK 523 } 524 catchsql {DROP TABLE t1} 525 } {1 {not authorized}} 526 do_test auth-1.70 { 527 execsql {SELECT name FROM sqlite_temp_master} 528 } {t1} 529} 530 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_IGNORE 535 } 536 return SQLITE_OK 537 } 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_IGNORE 547 } 548 return SQLITE_OK 549 } 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_IGNORE 561 } 562 return SQLITE_OK 563 } 564 catchsql {DROP TABLE t1} 565 } {0 {}} 566 do_test auth-1.76 { 567 execsql {SELECT name FROM sqlite_temp_master} 568 } {t1} 569 do_test auth-1.77 { 570 proc auth {code arg1 arg2 arg3 arg4 args} { 571 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 572 return SQLITE_IGNORE 573 } 574 return SQLITE_OK 575 } 576 catchsql {DROP TABLE t1} 577 } {0 {}} 578 do_test auth-1.78 { 579 execsql {SELECT name FROM temp.sqlite_master} 580 } {t1} 581} 582 583# Test cases auth-1.79 to auth-1.124 test creating and dropping views. 584# Omit these if the library was compiled with views omitted. 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_DENY 591 } 592 return SQLITE_OK 593 } 594 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 595} {1 {not authorized}} 596do_test auth-1.80 { 597 set ::authargs 598} {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_IGNORE 607 } 608 return SQLITE_OK 609 } 610 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 611} {0 {}} 612do_test auth-1.83 { 613 set ::authargs 614} {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_DENY 625 } 626 return SQLITE_OK 627 } 628 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 629 } {1 {not authorized}} 630 do_test auth-1.86 { 631 set ::authargs 632 } {v1 {} temp {}} 633 do_test auth-1.87 { 634 execsql {SELECT name FROM sqlite_temp_master} 635 } {t1} 636 do_test auth-1.88 { 637 proc auth {code arg1 arg2 arg3 arg4 args} { 638 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 639 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 640 return SQLITE_IGNORE 641 } 642 return SQLITE_OK 643 } 644 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 645 } {0 {}} 646 do_test auth-1.89 { 647 set ::authargs 648 } {v1 {} temp {}} 649 do_test auth-1.90 { 650 execsql {SELECT name FROM temp.sqlite_master} 651 } {t1} 652} 653 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_DENY 658 } 659 return SQLITE_OK 660 } 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_IGNORE 670 } 671 return SQLITE_OK 672 } 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_DENY 684 } 685 return SQLITE_OK 686 } 687 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 688 } {1 {not authorized}} 689 do_test auth-1.96 { 690 execsql {SELECT name FROM sqlite_temp_master} 691 } {t1} 692 do_test auth-1.97 { 693 proc auth {code arg1 arg2 arg3 arg4 args} { 694 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 695 return SQLITE_IGNORE 696 } 697 return SQLITE_OK 698 } 699 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 700 } {0 {}} 701 do_test auth-1.98 { 702 execsql {SELECT name FROM sqlite_temp_master} 703 } {t1} 704} 705 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_DENY 710 } 711 return SQLITE_OK 712 } 713 catchsql { 714 CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2; 715 DROP VIEW v2 716 } 717} {1 {not authorized}} 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_DENY 726 } 727 return SQLITE_OK 728 } 729 catchsql {DROP VIEW v2} 730} {1 {not authorized}} 731do_test auth-1.102 { 732 set ::authargs 733} {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_IGNORE 741 } 742 return SQLITE_OK 743 } 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_IGNORE 754 } 755 return SQLITE_OK 756 } 757 catchsql {DROP VIEW v2} 758} {0 {}} 759do_test auth-1.107 { 760 set ::authargs 761} {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_OK 770 } 771 return SQLITE_OK 772 } 773 catchsql {DROP VIEW v2} 774} {0 {}} 775do_test auth-1.110 { 776 set ::authargs 777} {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_DENY 788 } 789 return SQLITE_OK 790 } 791 catchsql { 792 CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1; 793 DROP VIEW v1 794 } 795 } {1 {not authorized}} 796 do_test auth-1.113 { 797 execsql {SELECT name FROM temp.sqlite_master} 798 } {t1 v1} 799 do_test auth-1.114 { 800 proc auth {code arg1 arg2 arg3 arg4 args} { 801 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 802 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 803 return SQLITE_DENY 804 } 805 return SQLITE_OK 806 } 807 catchsql {DROP VIEW v1} 808 } {1 {not authorized}} 809 do_test auth-1.115 { 810 set ::authargs 811 } {v1 {} temp {}} 812 do_test auth-1.116 { 813 execsql {SELECT name FROM sqlite_temp_master} 814 } {t1 v1} 815 do_test auth-1.117 { 816 proc auth {code arg1 arg2 arg3 arg4 args} { 817 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 818 return SQLITE_IGNORE 819 } 820 return SQLITE_OK 821 } 822 catchsql {DROP VIEW v1} 823 } {0 {}} 824 do_test auth-1.118 { 825 execsql {SELECT name FROM sqlite_temp_master} 826 } {t1 v1} 827 do_test auth-1.119 { 828 proc auth {code arg1 arg2 arg3 arg4 args} { 829 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 830 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 831 return SQLITE_IGNORE 832 } 833 return SQLITE_OK 834 } 835 catchsql {DROP VIEW v1} 836 } {0 {}} 837 do_test auth-1.120 { 838 set ::authargs 839 } {v1 {} temp {}} 840 do_test auth-1.121 { 841 execsql {SELECT name FROM temp.sqlite_master} 842 } {t1 v1} 843 do_test auth-1.122 { 844 proc auth {code arg1 arg2 arg3 arg4 args} { 845 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 846 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 847 return SQLITE_OK 848 } 849 return SQLITE_OK 850 } 851 catchsql {DROP VIEW v1} 852 } {0 {}} 853 do_test auth-1.123 { 854 set ::authargs 855 } {v1 {} temp {}} 856 do_test auth-1.124 { 857 execsql {SELECT name FROM sqlite_temp_master} 858 } {t1} 859} 860} ;# ifcapable view 861 862# Test cases auth-1.125 to auth-1.176 test creating and dropping triggers. 863# Omit these if the library was compiled with triggers omitted. 864# 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_DENY 871 } 872 return SQLITE_OK 873 } 874 catchsql { 875 CREATE TRIGGER r2 DELETE on t2 BEGIN 876 SELECT NULL; 877 END; 878 } 879} {1 {not authorized}} 880do_test auth-1.126 { 881 set ::authargs 882} {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_DENY 890 } 891 return SQLITE_OK 892 } 893 catchsql { 894 CREATE TRIGGER r2 DELETE on t2 BEGIN 895 SELECT NULL; 896 END; 897 } 898} {1 {not authorized}} 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_IGNORE 907 } 908 return SQLITE_OK 909 } 910 catchsql { 911 CREATE TRIGGER r2 DELETE on t2 BEGIN 912 SELECT NULL; 913 END; 914 } 915} {0 {}} 916do_test auth-1.131 { 917 set ::authargs 918} {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_IGNORE 926 } 927 return SQLITE_OK 928 } 929 catchsql { 930 CREATE TRIGGER r2 DELETE on t2 BEGIN 931 SELECT NULL; 932 END; 933 } 934} {0 {}} 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_OK 943 } 944 return SQLITE_OK 945 } 946 catchsql { 947 CREATE TABLE tx(id); 948 CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN 949 INSERT INTO tx VALUES(NEW.rowid); 950 END; 951 } 952} {0 {}} 953do_test auth-1.136.1 { 954 set ::authargs 955} {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 $arg4 964 return SQLITE_OK 965 } 966 set ::authargs {} 967 execsql { 968 INSERT INTO t2 VALUES(1,2,3); 969 } 970 set ::authargs 971} {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2} 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_DENY 985 } 986 return SQLITE_OK 987 } 988 catchsql { 989 CREATE TRIGGER r1 DELETE on t1 BEGIN 990 SELECT NULL; 991 END; 992 } 993} {1 {not authorized}} 994do_test auth-1.139 { 995 set ::authargs 996} {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_DENY 1004 } 1005 return SQLITE_OK 1006 } 1007 catchsql { 1008 CREATE TRIGGER r1 DELETE on t1 BEGIN 1009 SELECT NULL; 1010 END; 1011 } 1012} {1 {not authorized}} 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_IGNORE 1021 } 1022 return SQLITE_OK 1023 } 1024 catchsql { 1025 CREATE TRIGGER r1 DELETE on t1 BEGIN 1026 SELECT NULL; 1027 END; 1028 } 1029} {0 {}} 1030do_test auth-1.144 { 1031 set ::authargs 1032} {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_IGNORE 1040 } 1041 return SQLITE_OK 1042 } 1043 catchsql { 1044 CREATE TRIGGER r1 DELETE on t1 BEGIN 1045 SELECT NULL; 1046 END; 1047 } 1048} {0 {}} 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_OK 1057 } 1058 return SQLITE_OK 1059 } 1060 catchsql { 1061 CREATE TRIGGER r1 DELETE on t1 BEGIN 1062 SELECT NULL; 1063 END; 1064 } 1065} {0 {}} 1066do_test auth-1.149 { 1067 set ::authargs 1068} {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_DENY 1077 } 1078 return SQLITE_OK 1079 } 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_DENY 1090 } 1091 return SQLITE_OK 1092 } 1093 catchsql {DROP TRIGGER r2} 1094} {1 {not authorized}} 1095do_test auth-1.154 { 1096 set ::authargs 1097} {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_IGNORE 1105 } 1106 return SQLITE_OK 1107 } 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_IGNORE 1118 } 1119 return SQLITE_OK 1120 } 1121 catchsql {DROP TRIGGER r2} 1122} {0 {}} 1123do_test auth-1.159 { 1124 set ::authargs 1125} {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_OK 1134 } 1135 return SQLITE_OK 1136 } 1137 catchsql {DROP TRIGGER r2} 1138} {0 {}} 1139do_test auth-1.162 { 1140 set ::authargs 1141} {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_DENY 1154 } 1155 return SQLITE_OK 1156 } 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_DENY 1167 } 1168 return SQLITE_OK 1169 } 1170 catchsql {DROP TRIGGER r1} 1171} {1 {not authorized}} 1172do_test auth-1.167 { 1173 set ::authargs 1174} {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_IGNORE 1182 } 1183 return SQLITE_OK 1184 } 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_IGNORE 1195 } 1196 return SQLITE_OK 1197 } 1198 catchsql {DROP TRIGGER r1} 1199} {0 {}} 1200do_test auth-1.172 { 1201 set ::authargs 1202} {r1 t1 temp {}} 1203do_test auth-1.173 { 1204 execsql {SELECT name FROM sqlite_temp_master} 1205} {t1 r1} 1206do_test auth-1.174 { 1207 proc auth {code arg1 arg2 arg3 arg4 args} { 1208 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1209 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1210 return SQLITE_OK 1211 } 1212 return SQLITE_OK 1213 } 1214 catchsql {DROP TRIGGER r1} 1215} {0 {}} 1216do_test auth-1.175 { 1217 set ::authargs 1218} {r1 t1 temp {}} 1219do_test auth-1.176 { 1220 execsql {SELECT name FROM temp.sqlite_master} 1221} {t1} 1222} ;# ifcapable trigger 1223 1224do_test auth-1.177 { 1225 proc auth {code arg1 arg2 arg3 arg4 args} { 1226 if {$code=="SQLITE_CREATE_INDEX"} { 1227 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1228 return SQLITE_DENY 1229 } 1230 return SQLITE_OK 1231 } 1232 catchsql {CREATE INDEX i2 ON t2(a)} 1233} {1 {not authorized}} 1234do_test auth-1.178 { 1235 set ::authargs 1236} {i2 t2 main {}} 1237do_test auth-1.179 { 1238 execsql {SELECT name FROM sqlite_master} 1239} {t2} 1240do_test auth-1.180 { 1241 proc auth {code arg1 arg2 arg3 arg4 args} { 1242 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 1243 return SQLITE_DENY 1244 } 1245 return SQLITE_OK 1246 } 1247 catchsql {CREATE INDEX i2 ON t2(a)} 1248} {1 {not authorized}} 1249do_test auth-1.181 { 1250 execsql {SELECT name FROM sqlite_master} 1251} {t2} 1252do_test auth-1.182 { 1253 proc auth {code arg1 arg2 arg3 arg4 args} { 1254 if {$code=="SQLITE_CREATE_INDEX"} { 1255 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1256 return SQLITE_IGNORE 1257 } 1258 return SQLITE_OK 1259 } 1260 catchsql {CREATE INDEX i2 ON t2(b)} 1261} {0 {}} 1262do_test auth-1.183 { 1263 set ::authargs 1264} {i2 t2 main {}} 1265do_test auth-1.184 { 1266 execsql {SELECT name FROM sqlite_master} 1267} {t2} 1268do_test auth-1.185 { 1269 proc auth {code arg1 arg2 arg3 arg4 args} { 1270 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 1271 return SQLITE_IGNORE 1272 } 1273 return SQLITE_OK 1274 } 1275 catchsql {CREATE INDEX i2 ON t2(b)} 1276} {0 {}} 1277do_test auth-1.186 { 1278 execsql {SELECT name FROM sqlite_master} 1279} {t2} 1280do_test auth-1.187 { 1281 proc auth {code arg1 arg2 arg3 arg4 args} { 1282 if {$code=="SQLITE_CREATE_INDEX"} { 1283 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1284 return SQLITE_OK 1285 } 1286 return SQLITE_OK 1287 } 1288 catchsql {CREATE INDEX i2 ON t2(a)} 1289} {0 {}} 1290do_test auth-1.188 { 1291 set ::authargs 1292} {i2 t2 main {}} 1293do_test auth-1.189 { 1294 execsql {SELECT name FROM sqlite_master} 1295} {t2 i2} 1296 1297ifcapable tempdb { 1298 do_test auth-1.190 { 1299 proc auth {code arg1 arg2 arg3 arg4 args} { 1300 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1301 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1302 return SQLITE_DENY 1303 } 1304 return SQLITE_OK 1305 } 1306 catchsql {CREATE INDEX i1 ON t1(a)} 1307 } {1 {not authorized}} 1308 do_test auth-1.191 { 1309 set ::authargs 1310 } {i1 t1 temp {}} 1311 do_test auth-1.192 { 1312 execsql {SELECT name FROM sqlite_temp_master} 1313 } {t1} 1314 do_test auth-1.193 { 1315 proc auth {code arg1 arg2 arg3 arg4 args} { 1316 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1317 return SQLITE_DENY 1318 } 1319 return SQLITE_OK 1320 } 1321 catchsql {CREATE INDEX i1 ON t1(b)} 1322 } {1 {not authorized}} 1323 do_test auth-1.194 { 1324 execsql {SELECT name FROM temp.sqlite_master} 1325 } {t1} 1326 do_test auth-1.195 { 1327 proc auth {code arg1 arg2 arg3 arg4 args} { 1328 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1329 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1330 return SQLITE_IGNORE 1331 } 1332 return SQLITE_OK 1333 } 1334 catchsql {CREATE INDEX i1 ON t1(b)} 1335 } {0 {}} 1336 do_test auth-1.196 { 1337 set ::authargs 1338 } {i1 t1 temp {}} 1339 do_test auth-1.197 { 1340 execsql {SELECT name FROM sqlite_temp_master} 1341 } {t1} 1342 do_test auth-1.198 { 1343 proc auth {code arg1 arg2 arg3 arg4 args} { 1344 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1345 return SQLITE_IGNORE 1346 } 1347 return SQLITE_OK 1348 } 1349 catchsql {CREATE INDEX i1 ON t1(c)} 1350 } {0 {}} 1351 do_test auth-1.199 { 1352 execsql {SELECT name FROM sqlite_temp_master} 1353 } {t1} 1354 do_test auth-1.200 { 1355 proc auth {code arg1 arg2 arg3 arg4 args} { 1356 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1357 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1358 return SQLITE_OK 1359 } 1360 return SQLITE_OK 1361 } 1362 catchsql {CREATE INDEX i1 ON t1(a)} 1363 } {0 {}} 1364 do_test auth-1.201 { 1365 set ::authargs 1366 } {i1 t1 temp {}} 1367 do_test auth-1.202 { 1368 execsql {SELECT name FROM temp.sqlite_master} 1369 } {t1 i1} 1370} 1371 1372do_test auth-1.203 { 1373 proc auth {code arg1 arg2 arg3 arg4 args} { 1374 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1375 return SQLITE_DENY 1376 } 1377 return SQLITE_OK 1378 } 1379 catchsql {DROP INDEX i2} 1380} {1 {not authorized}} 1381do_test auth-1.204 { 1382 execsql {SELECT name FROM sqlite_master} 1383} {t2 i2} 1384do_test auth-1.205 { 1385 proc auth {code arg1 arg2 arg3 arg4 args} { 1386 if {$code=="SQLITE_DROP_INDEX"} { 1387 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1388 return SQLITE_DENY 1389 } 1390 return SQLITE_OK 1391 } 1392 catchsql {DROP INDEX i2} 1393} {1 {not authorized}} 1394do_test auth-1.206 { 1395 set ::authargs 1396} {i2 t2 main {}} 1397do_test auth-1.207 { 1398 execsql {SELECT name FROM sqlite_master} 1399} {t2 i2} 1400do_test auth-1.208 { 1401 proc auth {code arg1 arg2 arg3 arg4 args} { 1402 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1403 return SQLITE_IGNORE 1404 } 1405 return SQLITE_OK 1406 } 1407 catchsql {DROP INDEX i2} 1408} {0 {}} 1409do_test auth-1.209 { 1410 execsql {SELECT name FROM sqlite_master} 1411} {t2 i2} 1412do_test auth-1.210 { 1413 proc auth {code arg1 arg2 arg3 arg4 args} { 1414 if {$code=="SQLITE_DROP_INDEX"} { 1415 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1416 return SQLITE_IGNORE 1417 } 1418 return SQLITE_OK 1419 } 1420 catchsql {DROP INDEX i2} 1421} {0 {}} 1422do_test auth-1.211 { 1423 set ::authargs 1424} {i2 t2 main {}} 1425do_test auth-1.212 { 1426 execsql {SELECT name FROM sqlite_master} 1427} {t2 i2} 1428do_test auth-1.213 { 1429 proc auth {code arg1 arg2 arg3 arg4 args} { 1430 if {$code=="SQLITE_DROP_INDEX"} { 1431 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1432 return SQLITE_OK 1433 } 1434 return SQLITE_OK 1435 } 1436 catchsql {DROP INDEX i2} 1437} {0 {}} 1438do_test auth-1.214 { 1439 set ::authargs 1440} {i2 t2 main {}} 1441do_test auth-1.215 { 1442 execsql {SELECT name FROM sqlite_master} 1443} {t2} 1444 1445ifcapable tempdb { 1446 do_test auth-1.216 { 1447 proc auth {code arg1 arg2 arg3 arg4 args} { 1448 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1449 return SQLITE_DENY 1450 } 1451 return SQLITE_OK 1452 } 1453 catchsql {DROP INDEX i1} 1454 } {1 {not authorized}} 1455 do_test auth-1.217 { 1456 execsql {SELECT name FROM sqlite_temp_master} 1457 } {t1 i1} 1458 do_test auth-1.218 { 1459 proc auth {code arg1 arg2 arg3 arg4 args} { 1460 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1461 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1462 return SQLITE_DENY 1463 } 1464 return SQLITE_OK 1465 } 1466 catchsql {DROP INDEX i1} 1467 } {1 {not authorized}} 1468 do_test auth-1.219 { 1469 set ::authargs 1470 } {i1 t1 temp {}} 1471 do_test auth-1.220 { 1472 execsql {SELECT name FROM sqlite_temp_master} 1473 } {t1 i1} 1474 do_test auth-1.221 { 1475 proc auth {code arg1 arg2 arg3 arg4 args} { 1476 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1477 return SQLITE_IGNORE 1478 } 1479 return SQLITE_OK 1480 } 1481 catchsql {DROP INDEX i1} 1482 } {0 {}} 1483 do_test auth-1.222 { 1484 execsql {SELECT name FROM temp.sqlite_master} 1485 } {t1 i1} 1486 do_test auth-1.223 { 1487 proc auth {code arg1 arg2 arg3 arg4 args} { 1488 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1489 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1490 return SQLITE_IGNORE 1491 } 1492 return SQLITE_OK 1493 } 1494 catchsql {DROP INDEX i1} 1495 } {0 {}} 1496 do_test auth-1.224 { 1497 set ::authargs 1498 } {i1 t1 temp {}} 1499 do_test auth-1.225 { 1500 execsql {SELECT name FROM temp.sqlite_master} 1501 } {t1 i1} 1502 do_test auth-1.226 { 1503 proc auth {code arg1 arg2 arg3 arg4 args} { 1504 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1505 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1506 return SQLITE_OK 1507 } 1508 return SQLITE_OK 1509 } 1510 catchsql {DROP INDEX i1} 1511 } {0 {}} 1512 do_test auth-1.227 { 1513 set ::authargs 1514 } {i1 t1 temp {}} 1515 do_test auth-1.228 { 1516 execsql {SELECT name FROM temp.sqlite_master} 1517 } {t1} 1518} 1519 1520do_test auth-1.229 { 1521 proc auth {code arg1 arg2 arg3 arg4 args} { 1522 if {$code=="SQLITE_PRAGMA"} { 1523 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1524 return SQLITE_DENY 1525 } 1526 return SQLITE_OK 1527 } 1528 catchsql {PRAGMA full_column_names=on} 1529} {1 {not authorized}} 1530do_test auth-1.230 { 1531 set ::authargs 1532} {full_column_names on {} {}} 1533do_test auth-1.231 { 1534 execsql2 {SELECT a FROM t2} 1535} {a 11 a 7} 1536do_test auth-1.232 { 1537 proc auth {code arg1 arg2 arg3 arg4 args} { 1538 if {$code=="SQLITE_PRAGMA"} { 1539 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1540 return SQLITE_IGNORE 1541 } 1542 return SQLITE_OK 1543 } 1544 catchsql {PRAGMA full_column_names=on} 1545} {0 {}} 1546do_test auth-1.233 { 1547 set ::authargs 1548} {full_column_names on {} {}} 1549do_test auth-1.234 { 1550 execsql2 {SELECT a FROM t2} 1551} {a 11 a 7} 1552do_test auth-1.235 { 1553 proc auth {code arg1 arg2 arg3 arg4 args} { 1554 if {$code=="SQLITE_PRAGMA"} { 1555 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1556 return SQLITE_OK 1557 } 1558 return SQLITE_OK 1559 } 1560 catchsql {PRAGMA full_column_names=on} 1561} {0 {}} 1562do_test auth-1.236 { 1563 execsql2 {SELECT a FROM t2} 1564} {t2.a 11 t2.a 7} 1565do_test auth-1.237 { 1566 proc auth {code arg1 arg2 arg3 arg4 args} { 1567 if {$code=="SQLITE_PRAGMA"} { 1568 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1569 return SQLITE_OK 1570 } 1571 return SQLITE_OK 1572 } 1573 catchsql {PRAGMA full_column_names=OFF} 1574} {0 {}} 1575do_test auth-1.238 { 1576 set ::authargs 1577} {full_column_names OFF {} {}} 1578do_test auth-1.239 { 1579 execsql2 {SELECT a FROM t2} 1580} {a 11 a 7} 1581 1582do_test auth-1.240 { 1583 proc auth {code arg1 arg2 arg3 arg4 args} { 1584 if {$code=="SQLITE_TRANSACTION"} { 1585 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1586 return SQLITE_DENY 1587 } 1588 return SQLITE_OK 1589 } 1590 catchsql {BEGIN} 1591} {1 {not authorized}} 1592do_test auth-1.241 { 1593 set ::authargs 1594} {BEGIN {} {} {}} 1595do_test auth-1.242 { 1596 proc auth {code arg1 arg2 arg3 arg4 args} { 1597 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} { 1598 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1599 return SQLITE_DENY 1600 } 1601 return SQLITE_OK 1602 } 1603 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT} 1604} {1 {not authorized}} 1605do_test auth-1.243 { 1606 set ::authargs 1607} {COMMIT {} {} {}} 1608do_test auth-1.244 { 1609 execsql {SELECT * FROM t2} 1610} {11 2 33 7 8 9 44 55 66} 1611do_test auth-1.245 { 1612 catchsql {ROLLBACK} 1613} {1 {not authorized}} 1614do_test auth-1.246 { 1615 set ::authargs 1616} {ROLLBACK {} {} {}} 1617do_test auth-1.247 { 1618 catchsql {END TRANSACTION} 1619} {1 {not authorized}} 1620do_test auth-1.248 { 1621 set ::authargs 1622} {COMMIT {} {} {}} 1623do_test auth-1.249 { 1624 # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL 1625 # callback. 1626 db authorizer {} 1627 catchsql {ROLLBACK} 1628} {0 {}} 1629do_test auth-1.250 { 1630 execsql {SELECT * FROM t2} 1631} {11 2 33 7 8 9} 1632 1633# ticket #340 - authorization for ATTACH and DETACH. 1634# 1635ifcapable attach { 1636 do_test auth-1.251 { 1637 db authorizer ::auth 1638 proc auth {code arg1 arg2 arg3 arg4 args} { 1639 if {$code=="SQLITE_ATTACH"} { 1640 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1641 } 1642 return SQLITE_OK 1643 } 1644 catchsql { 1645 ATTACH DATABASE ':memory:' AS test1 1646 } 1647 } {0 {}} 1648 do_test auth-1.252a { 1649 set ::authargs 1650 } {:memory: {} {} {}} 1651 do_test auth-1.252b { 1652 db eval {DETACH test1} 1653 set ::attachfilename :memory: 1654 db eval {ATTACH $::attachfilename AS test1} 1655 set ::authargs 1656 } {{} {} {} {}} 1657 do_test auth-1.252c { 1658 db eval {DETACH test1} 1659 db eval {ATTACH ':mem' || 'ory:' AS test1} 1660 set ::authargs 1661 } {{} {} {} {}} 1662 do_test auth-1.253 { 1663 catchsql {DETACH DATABASE test1} 1664 proc auth {code arg1 arg2 arg3 arg4 args} { 1665 if {$code=="SQLITE_ATTACH"} { 1666 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1667 return SQLITE_DENY 1668 } 1669 return SQLITE_OK 1670 } 1671 catchsql { 1672 ATTACH DATABASE ':memory:' AS test1; 1673 } 1674 } {1 {not authorized}} 1675 do_test auth-1.254 { 1676 lindex [execsql {PRAGMA database_list}] 7 1677 } {} 1678 do_test auth-1.255 { 1679 catchsql {DETACH DATABASE test1} 1680 proc auth {code arg1 arg2 arg3 arg4 args} { 1681 if {$code=="SQLITE_ATTACH"} { 1682 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1683 return SQLITE_IGNORE 1684 } 1685 return SQLITE_OK 1686 } 1687 catchsql { 1688 ATTACH DATABASE ':memory:' AS test1; 1689 } 1690 } {0 {}} 1691 do_test auth-1.256 { 1692 lindex [execsql {PRAGMA database_list}] 7 1693 } {} 1694 do_test auth-1.257 { 1695 proc auth {code arg1 arg2 arg3 arg4 args} { 1696 if {$code=="SQLITE_DETACH"} { 1697 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1698 return SQLITE_OK 1699 } 1700 return SQLITE_OK 1701 } 1702 execsql {ATTACH DATABASE ':memory:' AS test1} 1703 catchsql { 1704 DETACH DATABASE test1; 1705 } 1706 } {0 {}} 1707 do_test auth-1.258 { 1708 lindex [execsql {PRAGMA database_list}] 7 1709 } {} 1710 do_test auth-1.259 { 1711 execsql {ATTACH DATABASE ':memory:' AS test1} 1712 proc auth {code arg1 arg2 arg3 arg4 args} { 1713 if {$code=="SQLITE_DETACH"} { 1714 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1715 return SQLITE_IGNORE 1716 } 1717 return SQLITE_OK 1718 } 1719 catchsql { 1720 DETACH DATABASE test1; 1721 } 1722 } {0 {}} 1723 ifcapable tempdb { 1724 ifcapable schema_pragmas { 1725 do_test auth-1.260 { 1726 lindex [execsql {PRAGMA database_list}] 7 1727 } {test1} 1728 } ;# ifcapable schema_pragmas 1729 do_test auth-1.261 { 1730 proc auth {code arg1 arg2 arg3 arg4 args} { 1731 if {$code=="SQLITE_DETACH"} { 1732 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1733 return SQLITE_DENY 1734 } 1735 return SQLITE_OK 1736 } 1737 catchsql { 1738 DETACH DATABASE test1; 1739 } 1740 } {1 {not authorized}} 1741 ifcapable schema_pragmas { 1742 do_test auth-1.262 { 1743 lindex [execsql {PRAGMA database_list}] 7 1744 } {test1} 1745 } ;# ifcapable schema_pragmas 1746 db authorizer {} 1747 execsql {DETACH DATABASE test1} 1748 db authorizer ::auth 1749 1750 # Authorization for ALTER TABLE. These tests are omitted if the library 1751 # was built without ALTER TABLE support. 1752 ifcapable altertable { 1753 1754 do_test auth-1.263 { 1755 proc auth {code arg1 arg2 arg3 arg4 args} { 1756 if {$code=="SQLITE_ALTER_TABLE"} { 1757 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1758 return SQLITE_OK 1759 } 1760 return SQLITE_OK 1761 } 1762 catchsql { 1763 ALTER TABLE t1 RENAME TO t1x 1764 } 1765 } {0 {}} 1766 do_test auth-1.264 { 1767 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1768 } {t1x} 1769 do_test auth-1.265 { 1770 set authargs 1771 } {temp t1 {} {}} 1772 do_test auth-1.266 { 1773 proc auth {code arg1 arg2 arg3 arg4 args} { 1774 if {$code=="SQLITE_ALTER_TABLE"} { 1775 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1776 return SQLITE_IGNORE 1777 } 1778 return SQLITE_OK 1779 } 1780 catchsql { 1781 ALTER TABLE t1x RENAME TO t1 1782 } 1783 } {0 {}} 1784 do_test auth-1.267 { 1785 execsql {SELECT name FROM temp.sqlite_master WHERE type='table'} 1786 } {t1x} 1787 do_test auth-1.268 { 1788 set authargs 1789 } {temp t1x {} {}} 1790 do_test auth-1.269 { 1791 proc auth {code arg1 arg2 arg3 arg4 args} { 1792 if {$code=="SQLITE_ALTER_TABLE"} { 1793 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1794 return SQLITE_DENY 1795 } 1796 return SQLITE_OK 1797 } 1798 catchsql { 1799 ALTER TABLE t1x RENAME TO t1 1800 } 1801 } {1 {not authorized}} 1802 do_test auth-1.270 { 1803 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1804 } {t1x} 1805 1806 do_test auth-1.271 { 1807 set authargs 1808 } {temp t1x {} {}} 1809 } ;# ifcapable altertable 1810 1811 } else { 1812 db authorizer {} 1813 db eval { 1814 DETACH DATABASE test1; 1815 } 1816 } 1817} 1818 1819ifcapable altertable { 1820db authorizer {} 1821catchsql {ALTER TABLE t1x RENAME TO t1} 1822db authorizer ::auth 1823do_test auth-1.272 { 1824 proc auth {code arg1 arg2 arg3 arg4 args} { 1825 if {$code=="SQLITE_ALTER_TABLE"} { 1826 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1827 return SQLITE_OK 1828 } 1829 return SQLITE_OK 1830 } 1831 catchsql { 1832 ALTER TABLE t2 RENAME TO t2x 1833 } 1834} {0 {}} 1835do_test auth-1.273 { 1836 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1837} {t2x} 1838do_test auth-1.274 { 1839 set authargs 1840} {main t2 {} {}} 1841do_test auth-1.275 { 1842 proc auth {code arg1 arg2 arg3 arg4 args} { 1843 if {$code=="SQLITE_ALTER_TABLE"} { 1844 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1845 return SQLITE_IGNORE 1846 } 1847 return SQLITE_OK 1848 } 1849 catchsql { 1850 ALTER TABLE t2x RENAME TO t2 1851 } 1852} {0 {}} 1853do_test auth-1.276 { 1854 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1855} {t2x} 1856do_test auth-1.277 { 1857 set authargs 1858} {main t2x {} {}} 1859do_test auth-1.278 { 1860 proc auth {code arg1 arg2 arg3 arg4 args} { 1861 if {$code=="SQLITE_ALTER_TABLE"} { 1862 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1863 return SQLITE_DENY 1864 } 1865 return SQLITE_OK 1866 } 1867 catchsql { 1868 ALTER TABLE t2x RENAME TO t2 1869 } 1870} {1 {not authorized}} 1871do_test auth-1.279 { 1872 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1873} {t2x} 1874do_test auth-1.280 { 1875 set authargs 1876} {main t2x {} {}} 1877db authorizer {} 1878catchsql {ALTER TABLE t2x RENAME TO t2} 1879 1880} ;# ifcapable altertable 1881 1882# Test the authorization callbacks for the REINDEX command. 1883ifcapable reindex { 1884 1885proc auth {code args} { 1886 if {$code=="SQLITE_REINDEX"} { 1887 set ::authargs [concat $::authargs [lrange $args 0 3]] 1888 } 1889 return SQLITE_OK 1890} 1891db authorizer auth 1892do_test auth-1.281 { 1893 execsql { 1894 CREATE TABLE t3(a PRIMARY KEY, b, c); 1895 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1896 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1897 } 1898} {} 1899do_test auth-1.282 { 1900 set ::authargs {} 1901 execsql { 1902 REINDEX t3_idx1; 1903 } 1904 set ::authargs 1905} {t3_idx1 {} main {}} 1906do_test auth-1.283 { 1907 set ::authargs {} 1908 execsql { 1909 REINDEX BINARY; 1910 } 1911 set ::authargs 1912} {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1913do_test auth-1.284 { 1914 set ::authargs {} 1915 execsql { 1916 REINDEX NOCASE; 1917 } 1918 set ::authargs 1919} {t3_idx2 {} main {}} 1920do_test auth-1.285 { 1921 set ::authargs {} 1922 execsql { 1923 REINDEX t3; 1924 } 1925 set ::authargs 1926} {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1927do_test auth-1.286 { 1928 execsql { 1929 DROP TABLE t3; 1930 } 1931} {} 1932ifcapable tempdb { 1933 do_test auth-1.287 { 1934 execsql { 1935 CREATE TEMP TABLE t3(a PRIMARY KEY, b, c); 1936 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1937 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1938 } 1939 } {} 1940 do_test auth-1.288 { 1941 set ::authargs {} 1942 execsql { 1943 REINDEX temp.t3_idx1; 1944 } 1945 set ::authargs 1946 } {t3_idx1 {} temp {}} 1947 do_test auth-1.289 { 1948 set ::authargs {} 1949 execsql { 1950 REINDEX BINARY; 1951 } 1952 set ::authargs 1953 } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1954 do_test auth-1.290 { 1955 set ::authargs {} 1956 execsql { 1957 REINDEX NOCASE; 1958 } 1959 set ::authargs 1960 } {t3_idx2 {} temp {}} 1961 do_test auth-1.291 { 1962 set ::authargs {} 1963 execsql { 1964 REINDEX temp.t3; 1965 } 1966 set ::authargs 1967 } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1968 proc auth {code args} { 1969 if {$code=="SQLITE_REINDEX"} { 1970 set ::authargs [concat $::authargs [lrange $args 0 3]] 1971 return SQLITE_DENY 1972 } 1973 return SQLITE_OK 1974 } 1975 do_test auth-1.292 { 1976 set ::authargs {} 1977 catchsql { 1978 REINDEX temp.t3; 1979 } 1980 } {1 {not authorized}} 1981 do_test auth-1.293 { 1982 execsql { 1983 DROP TABLE t3; 1984 } 1985 } {} 1986} 1987 1988} ;# ifcapable reindex 1989 1990ifcapable analyze { 1991 proc auth {code args} { 1992 if {$code=="SQLITE_ANALYZE"} { 1993 set ::authargs [concat $::authargs [lrange $args 0 3]] 1994 } 1995 return SQLITE_OK 1996 } 1997 do_test auth-1.294 { 1998 set ::authargs {} 1999 execsql { 2000 CREATE TABLE t4(a,b,c); 2001 CREATE INDEX t4i1 ON t4(a); 2002 CREATE INDEX t4i2 ON t4(b,a,c); 2003 INSERT INTO t4 VALUES(1,2,3); 2004 ANALYZE; 2005 } 2006 set ::authargs 2007 } {t4 {} main {} t2 {} main {}} 2008 do_test auth-1.295 { 2009 execsql { 2010 SELECT count(*) FROM sqlite_stat1; 2011 } 2012 } 3 2013 proc auth {code args} { 2014 if {$code=="SQLITE_ANALYZE"} { 2015 set ::authargs [concat $::authargs $args] 2016 return SQLITE_DENY 2017 } 2018 return SQLITE_OK 2019 } 2020 do_test auth-1.296 { 2021 set ::authargs {} 2022 catchsql { 2023 ANALYZE; 2024 } 2025 } {1 {not authorized}} 2026 do_test auth-1.297 { 2027 execsql { 2028 SELECT count(*) FROM sqlite_stat1; 2029 } 2030 } 3 2031} ;# ifcapable analyze 2032 2033 2034# Authorization for ALTER TABLE ADD COLUMN. 2035# These tests are omitted if the library 2036# was built without ALTER TABLE support. 2037ifcapable {altertable} { 2038 do_test auth-1.300 { 2039 execsql {CREATE TABLE t5(x)} 2040 proc auth {code arg1 arg2 arg3 arg4 args} { 2041 if {$code=="SQLITE_ALTER_TABLE"} { 2042 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2043 return SQLITE_OK 2044 } 2045 return SQLITE_OK 2046 } 2047 catchsql { 2048 ALTER TABLE t5 ADD COLUMN new_col_1; 2049 } 2050 } {0 {}} 2051 do_test auth-1.301 { 2052 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2053 regexp new_col_1 $x 2054 } {1} 2055 do_test auth-1.302 { 2056 set authargs 2057 } {main t5 {} {}} 2058 do_test auth-1.303 { 2059 proc auth {code arg1 arg2 arg3 arg4 args} { 2060 if {$code=="SQLITE_ALTER_TABLE"} { 2061 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2062 return SQLITE_IGNORE 2063 } 2064 return SQLITE_OK 2065 } 2066 catchsql { 2067 ALTER TABLE t5 ADD COLUMN new_col_2; 2068 } 2069 } {0 {}} 2070 do_test auth-1.304 { 2071 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2072 regexp new_col_2 $x 2073 } {0} 2074 do_test auth-1.305 { 2075 set authargs 2076 } {main t5 {} {}} 2077 do_test auth-1.306 { 2078 proc auth {code arg1 arg2 arg3 arg4 args} { 2079 if {$code=="SQLITE_ALTER_TABLE"} { 2080 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2081 return SQLITE_DENY 2082 } 2083 return SQLITE_OK 2084 } 2085 catchsql { 2086 ALTER TABLE t5 ADD COLUMN new_col_3 2087 } 2088 } {1 {not authorized}} 2089 do_test auth-1.307 { 2090 set x [execsql {SELECT sql FROM temp.sqlite_master WHERE type='t5'}] 2091 regexp new_col_3 $x 2092 } {0} 2093 2094 do_test auth-1.308 { 2095 set authargs 2096 } {main t5 {} {}} 2097 execsql {DROP TABLE t5} 2098} ;# ifcapable altertable 2099 2100ifcapable {cte} { 2101 do_test auth-1.310 { 2102 proc auth {code arg1 arg2 arg3 arg4 args} { 2103 if {$code=="SQLITE_RECURSIVE"} { 2104 return SQLITE_DENY 2105 } 2106 return SQLITE_OK 2107 } 2108 db eval { 2109 DROP TABLE IF EXISTS t1; 2110 CREATE TABLE t1(a,b); 2111 INSERT INTO t1 VALUES(1,2),(3,4),(5,6); 2112 } 2113 } {} 2114 do_catchsql_test auth-1.311 { 2115 WITH 2116 auth1311(x,y) AS (SELECT a+b, b-a FROM t1) 2117 SELECT * FROM auth1311 ORDER BY x; 2118 } {0 {3 1 7 1 11 1}} 2119 do_catchsql_test auth-1.312 { 2120 WITH RECURSIVE 2121 auth1312(x,y) AS (SELECT a+b, b-a FROM t1) 2122 SELECT x, y FROM auth1312 ORDER BY x; 2123 } {0 {3 1 7 1 11 1}} 2124 do_catchsql_test auth-1.313 { 2125 WITH RECURSIVE 2126 auth1313(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1313 WHERE x<5) 2127 SELECT * FROM t1; 2128 } {0 {1 2 3 4 5 6}} 2129 do_catchsql_test auth-1.314 { 2130 WITH RECURSIVE 2131 auth1314(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1314 WHERE x<5) 2132 SELECT * FROM t1 LEFT JOIN auth1314; 2133 } {1 {not authorized}} 2134} ;# ifcapable cte 2135 2136do_test auth-2.1 { 2137 proc auth {code arg1 arg2 arg3 arg4 args} { 2138 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2139 return SQLITE_DENY 2140 } 2141 return SQLITE_OK 2142 } 2143 db authorizer ::auth 2144 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)} 2145 catchsql {SELECT * FROM t3} 2146} {1 {access to t3.x is prohibited}} 2147do_test auth-2.1 { 2148 catchsql {SELECT y,z FROM t3} 2149} {0 {}} 2150do_test auth-2.2 { 2151 catchsql {SELECT ROWID,y,z FROM t3} 2152} {1 {access to t3.x is prohibited}} 2153do_test auth-2.3 { 2154 catchsql {SELECT OID,y,z FROM t3} 2155} {1 {access to t3.x is prohibited}} 2156do_test auth-2.4 { 2157 proc auth {code arg1 arg2 arg3 arg4 args} { 2158 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2159 return SQLITE_IGNORE 2160 } 2161 return SQLITE_OK 2162 } 2163 execsql {INSERT INTO t3 VALUES(44,55,66)} 2164 catchsql {SELECT * FROM t3} 2165} {0 {{} 55 66}} 2166do_test auth-2.5 { 2167 catchsql {SELECT rowid,y,z FROM t3} 2168} {0 {{} 55 66}} 2169do_test auth-2.6 { 2170 proc auth {code arg1 arg2 arg3 arg4 args} { 2171 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} { 2172 return SQLITE_IGNORE 2173 } 2174 return SQLITE_OK 2175 } 2176 catchsql {SELECT * FROM t3} 2177} {0 {44 55 66}} 2178do_test auth-2.7 { 2179 catchsql {SELECT ROWID,y,z FROM t3} 2180} {0 {44 55 66}} 2181do_test auth-2.8 { 2182 proc auth {code arg1 arg2 arg3 arg4 args} { 2183 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2184 return SQLITE_IGNORE 2185 } 2186 return SQLITE_OK 2187 } 2188 catchsql {SELECT ROWID,b,c FROM t2} 2189} {0 {{} 2 33 {} 8 9}} 2190do_test auth-2.9.1 { 2191 # We have to flush the cache here in case the Tcl interface tries to 2192 # reuse a statement compiled with sqlite3_prepare_v2(). In this case, 2193 # the first error encountered is an SQLITE_SCHEMA error. Then, when 2194 # trying to recompile the statement, the authorization error is encountered. 2195 # If we do not flush the cache, the correct error message is returned, but 2196 # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test 2197 # case after this one. 2198 # 2199 db cache flush 2200 2201 proc auth {code arg1 arg2 arg3 arg4 args} { 2202 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2203 return bogus 2204 } 2205 return SQLITE_OK 2206 } 2207 catchsql {SELECT ROWID,b,c FROM t2} 2208} {1 {authorizer malfunction}} 2209do_test auth-2.9.2 { 2210 db errorcode 2211} {1} 2212do_test auth-2.10 { 2213 proc auth {code arg1 arg2 arg3 arg4 args} { 2214 if {$code=="SQLITE_SELECT"} { 2215 return bogus 2216 } 2217 return SQLITE_OK 2218 } 2219 catchsql {SELECT ROWID,b,c FROM t2} 2220} {1 {authorizer malfunction}} 2221do_test auth-2.11.1 { 2222 proc auth {code arg1 arg2 arg3 arg4 args} { 2223 if {$code=="SQLITE_READ" && $arg2=="a"} { 2224 return SQLITE_IGNORE 2225 } 2226 return SQLITE_OK 2227 } 2228 catchsql {SELECT * FROM t2, t3} 2229} {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}} 2230do_test auth-2.11.2 { 2231 proc auth {code arg1 arg2 arg3 arg4 args} { 2232 if {$code=="SQLITE_READ" && $arg2=="x"} { 2233 return SQLITE_IGNORE 2234 } 2235 return SQLITE_OK 2236 } 2237 catchsql {SELECT * FROM t2, t3} 2238} {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}} 2239 2240# Make sure the OLD and NEW pseudo-tables of a trigger get authorized. 2241# 2242ifcapable trigger { 2243 do_test auth-3.1 { 2244 proc auth {code arg1 arg2 arg3 arg4 args} { 2245 return SQLITE_OK 2246 } 2247 execsql { 2248 CREATE TABLE tx(a1,a2,b1,b2,c1,c2); 2249 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 2250 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c); 2251 END; 2252 UPDATE t2 SET a=a+1; 2253 SELECT * FROM tx; 2254 } 2255 } {11 12 2 2 33 33 7 8 8 8 9 9} 2256 do_test auth-3.2 { 2257 proc auth {code arg1 arg2 arg3 arg4 args} { 2258 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} { 2259 return SQLITE_IGNORE 2260 } 2261 return SQLITE_OK 2262 } 2263 execsql { 2264 DELETE FROM tx; 2265 UPDATE t2 SET a=a+100; 2266 SELECT * FROM tx; 2267 } 2268 } {12 112 2 2 {} {} 8 108 8 8 {} {}} 2269} ;# ifcapable trigger 2270 2271# Make sure the names of views and triggers are passed on on arg4. 2272# 2273ifcapable trigger { 2274do_test auth-4.1 { 2275 proc auth {code arg1 arg2 arg3 arg4 args} { 2276 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 2277 return SQLITE_OK 2278 } 2279 set authargs {} 2280 execsql { 2281 UPDATE t2 SET a=a+1; 2282 } 2283 set authargs 2284} [list \ 2285 SQLITE_READ t2 a main {} \ 2286 SQLITE_UPDATE t2 a main {} \ 2287 SQLITE_INSERT tx {} main r1 \ 2288 SQLITE_READ t2 a main r1 \ 2289 SQLITE_READ t2 a main r1 \ 2290 SQLITE_READ t2 b main r1 \ 2291 SQLITE_READ t2 b main r1 \ 2292 SQLITE_READ t2 c main r1 \ 2293 SQLITE_READ t2 c main r1] 2294} 2295 2296ifcapable {view && trigger} { 2297do_test auth-4.2 { 2298 execsql { 2299 CREATE VIEW v1 AS SELECT a+b AS x FROM t2; 2300 CREATE TABLE v1chng(x1,x2); 2301 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN 2302 INSERT INTO v1chng VALUES(OLD.x,NEW.x); 2303 END; 2304 SELECT * FROM v1; 2305 } 2306} {115 117} 2307do_test auth-4.3 { 2308 set authargs {} 2309 execsql { 2310 UPDATE v1 SET x=1 WHERE x=117 2311 } 2312 set authargs 2313} [list \ 2314 SQLITE_UPDATE v1 x main {} \ 2315 SQLITE_SELECT {} {} {} v1 \ 2316 SQLITE_READ t2 a main v1 \ 2317 SQLITE_READ t2 b main v1 \ 2318 SQLITE_READ v1 x main v1 \ 2319 SQLITE_READ v1 x main v1 \ 2320 SQLITE_SELECT {} {} {} v1 \ 2321 SQLITE_READ v1 x main v1 \ 2322 SQLITE_INSERT v1chng {} main r2 \ 2323 SQLITE_READ v1 x main r2 \ 2324 SQLITE_READ v1 x main r2 \ 2325] 2326 2327do_test auth-4.4 { 2328 execsql { 2329 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN 2330 INSERT INTO v1chng VALUES(OLD.x,NULL); 2331 END; 2332 SELECT * FROM v1; 2333 } 2334} {115 117} 2335do_test auth-4.5 { 2336 set authargs {} 2337 execsql { 2338 DELETE FROM v1 WHERE x=117 2339 } 2340 set authargs 2341} [list \ 2342 SQLITE_DELETE v1 {} main {} \ 2343 SQLITE_SELECT {} {} {} v1 \ 2344 SQLITE_READ t2 a main v1 \ 2345 SQLITE_READ t2 b main v1 \ 2346 SQLITE_READ v1 x main v1 \ 2347 SQLITE_READ v1 x main v1 \ 2348 SQLITE_SELECT {} {} {} v1 \ 2349 SQLITE_READ v1 x main v1 \ 2350 SQLITE_INSERT v1chng {} main r3 \ 2351 SQLITE_READ v1 x main r3 \ 2352] 2353 2354} ;# ifcapable view && trigger 2355 2356# Ticket #1338: Make sure authentication works in the presence of an AS 2357# clause. 2358# 2359do_test auth-5.1 { 2360 proc auth {code arg1 arg2 arg3 arg4 args} { 2361 return SQLITE_OK 2362 } 2363 execsql { 2364 SELECT count(a) AS cnt FROM t4 ORDER BY cnt 2365 } 2366} {1} 2367 2368# Ticket #1607 2369# 2370ifcapable compound&&subquery { 2371 ifcapable trigger { 2372 execsql { 2373 DROP TABLE tx; 2374 } 2375 ifcapable view { 2376 execsql { 2377 DROP TABLE v1chng; 2378 } 2379 } 2380 } 2381 ifcapable stat4 { 2382 set stat4 "sqlite_stat4 " 2383 } else { 2384 ifcapable stat3 { 2385 set stat4 "sqlite_stat3 " 2386 } else { 2387 set stat4 "" 2388 } 2389 } 2390 do_test auth-5.2 { 2391 execsql { 2392 SELECT name FROM ( 2393 SELECT * FROM sqlite_master UNION ALL SELECT * FROM temp.sqlite_master) 2394 WHERE type='table' 2395 ORDER BY name 2396 } 2397 } "sqlite_stat1 ${stat4}t1 t2 t3 t4" 2398} 2399 2400# Ticket #3944 2401# 2402ifcapable trigger { 2403 do_test auth-5.3.1 { 2404 execsql { 2405 CREATE TABLE t5 ( x ); 2406 CREATE TRIGGER t5_tr1 AFTER INSERT ON t5 BEGIN 2407 UPDATE t5 SET x = 1 WHERE NEW.x = 0; 2408 END; 2409 } 2410 } {} 2411 set ::authargs [list] 2412 proc auth {args} { 2413 eval lappend ::authargs [lrange $args 0 4] 2414 return SQLITE_OK 2415 } 2416 do_test auth-5.3.2 { 2417 execsql { INSERT INTO t5 (x) values(0) } 2418 set ::authargs 2419 } [list SQLITE_INSERT t5 {} main {} \ 2420 SQLITE_UPDATE t5 x main t5_tr1 \ 2421 SQLITE_READ t5 x main t5_tr1 \ 2422 ] 2423 do_test auth-5.3.2 { 2424 execsql { SELECT * FROM t5 } 2425 } {1} 2426} 2427 2428# Ticket [0eb70d77cb05bb22720]: Invalid pointer passsed to the authorizer 2429# callback when updating a ROWID. 2430# 2431do_test auth-6.1 { 2432 execsql { 2433 CREATE TABLE t6(a,b,c,d,e,f,g,h); 2434 INSERT INTO t6 VALUES(1,2,3,4,5,6,7,8); 2435 } 2436} {} 2437set ::authargs [list] 2438proc auth {args} { 2439 eval lappend ::authargs [lrange $args 0 4] 2440 return SQLITE_OK 2441} 2442do_test auth-6.2 { 2443 execsql {UPDATE t6 SET rowID=rowID+100} 2444 set ::authargs 2445} [list SQLITE_READ t6 ROWID main {} \ 2446 SQLITE_UPDATE t6 ROWID main {} \ 2447] 2448do_test auth-6.3 { 2449 execsql {SELECT rowid, * FROM t6} 2450} {101 1 2 3 4 5 6 7 8} 2451 2452#------------------------------------------------------------------------- 2453# Test that view names are included as zArg4. 2454# 2455do_execsql_test auth-7.1 { 2456 CREATE TABLE t7(a, b, c); 2457 CREATE VIEW v7 AS SELECT * FROM t7; 2458} {} 2459set ::authargs [list] 2460proc auth {args} { 2461 eval lappend ::authargs [lrange $args 0 4] 2462 return SQLITE_OK 2463} 2464 2465do_test auth-7.2 { 2466 execsql {SELECT a, c FROM v7} 2467 set ::authargs 2468} [list \ 2469 SQLITE_SELECT {} {} {} {} \ 2470 SQLITE_READ t7 a main v7 \ 2471 SQLITE_READ t7 b main v7 \ 2472 SQLITE_READ t7 c main v7 \ 2473 SQLITE_READ v7 a main {} \ 2474 SQLITE_READ v7 c main {} \ 2475 SQLITE_SELECT {} {} {} v7 \ 2476] 2477 2478set ::authargs [list] 2479do_test auth-7.3 { 2480 execsql {SELECT a, c FROM t7} 2481 set ::authargs 2482} [list \ 2483 SQLITE_SELECT {} {} {} {} \ 2484 SQLITE_READ t7 a main {} \ 2485 SQLITE_READ t7 c main {} \ 2486] 2487 2488set ::authargs [list] 2489do_test auth-7.4 { 2490 execsql {SELECT a, c FROM t7 AS v7} 2491 set ::authargs 2492} [list \ 2493 SQLITE_SELECT {} {} {} {} \ 2494 SQLITE_READ t7 a main {} \ 2495 SQLITE_READ t7 c main {} \ 2496] 2497 2498# If a table is referenced but no columns are read from the table, 2499# that causes a single SQLITE_READ authorization with a NULL column 2500# name. 2501# 2502# EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but 2503# no column values are extracted from that table (for example in a query 2504# like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer 2505# callback is invoked once for that table with a column name that is an 2506# empty string. 2507# 2508set ::authargs [list] 2509do_test auth-8.1 { 2510 execsql {SELECT count(*) FROM t7} 2511 set ::authargs 2512} [list \ 2513 SQLITE_SELECT {} {} {} {} \ 2514 SQLITE_FUNCTION {} count {} {} \ 2515 SQLITE_READ t7 {} {} {} \ 2516 ] 2517set ::authargs [list] 2518 2519do_test auth-8.2 { 2520 execsql {SELECT t6.a FROM t6, t7} 2521 set ::authargs 2522} [list \ 2523 SQLITE_SELECT {} {} {} {} \ 2524 SQLITE_READ t6 a main {} \ 2525 SQLITE_READ t7 {} {} {} \ 2526 ] 2527 2528# Test also that if SQLITE_DENY is returned from an SQLITE_READ authorizer 2529# invocation with no column name specified, compilation fails. 2530# 2531set ::authargs [list] 2532proc auth {op a b c d} { 2533 lappend ::authargs $op $a $b $c $d 2534 if {$op == "SQLITE_READ"} { return "SQLITE_DENY" } 2535 return "SQLITE_OK" 2536} 2537set ::authargs [list] 2538do_catchsql_test auth-8.3 { 2539 SELECT count(*) FROM t7 2540} {1 {not authorized}} 2541do_test auth-8.4 { 2542 set ::authargs 2543} [list \ 2544 SQLITE_SELECT {} {} {} {} \ 2545 SQLITE_FUNCTION {} count {} {} \ 2546 SQLITE_READ t7 {} {} {} \ 2547] 2548 2549 2550rename proc {} 2551rename proc_real proc 2552finish_test 2553