gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/auth.test (about) 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 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 # disable this test if the SQLITE_OMIT_AUTHORIZATION macro is 22 # defined during compilation. 23 if {[catch {db auth {}} msg]} { 24 finish_test 25 return 26 } 27 28 rename proc proc_real 29 proc_real proc {name arguments script} { 30 proc_real $name $arguments $script 31 if {$name=="auth"} { 32 db authorizer ::auth 33 } 34 } 35 36 do_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}} 56 do_test auth-1.1.2 { 57 db errorcode 58 } {23} 59 do_test auth-1.1.3 { 60 db authorizer 61 } {::auth} 62 do_test auth-1.1.4 { 63 # Ticket #896. 64 catchsql { 65 SELECT x; 66 } 67 } {1 {no such column: x}} 68 do_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. 74 do_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}} 84 do_test auth-1.3.2 { 85 db errorcode 86 } {23} 87 do_test auth-1.3.3 { 88 set ::authargs 89 } {t1 {} main {}} 90 do_test auth-1.4 { 91 execsql {SELECT name FROM sqlite_master} 92 } {} 93 94 ifcapable 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 125 do_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 {}} 134 do_test auth-1.10 { 135 execsql {SELECT name FROM sqlite_master} 136 } {} 137 do_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 {}} 147 do_test auth-1.12 { 148 execsql {SELECT name FROM sqlite_master} 149 } {} 150 151 ifcapable 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 193 do_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 {}} 204 do_test auth-1.19.2 { 205 set ::authargs 206 } {} 207 do_test auth-1.20 { 208 execsql {SELECT name FROM sqlite_master} 209 } {t2} 210 211 do_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}} 221 do_test auth-1.21.2 { 222 set ::authargs 223 } {t2 {} main {}} 224 do_test auth-1.22 { 225 execsql {SELECT name FROM sqlite_master} 226 } {t2} 227 do_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 {}} 237 do_test auth-1.23.2 { 238 set ::authargs 239 } {t2 {} main {}} 240 do_test auth-1.24 { 241 execsql {SELECT name FROM sqlite_master} 242 } {t2} 243 244 ifcapable 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 273 do_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}} 282 do_test auth-1.30 { 283 execsql {SELECT * FROM t2} 284 } {} 285 do_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 {}} 294 do_test auth-1.32 { 295 execsql {SELECT * FROM t2} 296 } {} 297 do_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 {}} 306 do_test auth-1.34 { 307 execsql {SELECT * FROM t2} 308 } {1 2 3} 309 310 do_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}} 319 ifcapable 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. 330 do_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}} 339 do_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 {}} 348 do_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}} 357 do_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}} 366 do_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 376 do_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 {}} 385 do_test auth-1.42 { 386 execsql {SELECT * FROM t2} 387 } {11 2 3} 388 do_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}} 397 do_test auth-1.44 { 398 execsql {SELECT * FROM t2} 399 } {11 2 3} 400 do_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 {}} 409 do_test auth-1.46 { 410 execsql {SELECT * FROM t2} 411 } {11 2 33} 412 413 do_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}} 422 do_test auth-1.48 { 423 execsql {SELECT * FROM t2} 424 } {11 2 33} 425 do_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 {}} 434 do_test auth-1.50 { 435 execsql {SELECT * FROM t2} 436 } {} 437 do_test auth-1.50.2 { 438 execsql {INSERT INTO t2 VALUES(11, 2, 33)} 439 } {} 440 441 do_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}} 450 do_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 {}} 459 do_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. 472 do_test auth-1.54 { 473 execsql {INSERT INTO t2 VALUES(7, 8, 9);} 474 } {} 475 do_test auth-1.55 { 476 execsql {SELECT * FROM t2} 477 } {11 2 33 7 8 9} 478 479 do_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}} 488 do_test auth-1.64 { 489 execsql {SELECT name FROM sqlite_master} 490 } {t2} 491 do_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}} 500 do_test auth-1.66 { 501 execsql {SELECT name FROM sqlite_master} 502 } {t2} 503 504 ifcapable 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 531 do_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 {}} 540 do_test auth-1.72 { 541 execsql {SELECT name FROM sqlite_master} 542 } {t2} 543 do_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 {}} 552 do_test auth-1.74 { 553 execsql {SELECT name FROM sqlite_master} 554 } {t2} 555 556 ifcapable 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. 585 ifcapable view { 586 do_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}} 596 do_test auth-1.80 { 597 set ::authargs 598 } {v1 {} main {}} 599 do_test auth-1.81 { 600 execsql {SELECT name FROM sqlite_master} 601 } {t2} 602 do_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 {}} 612 do_test auth-1.83 { 613 set ::authargs 614 } {v1 {} main {}} 615 do_test auth-1.84 { 616 execsql {SELECT name FROM sqlite_master} 617 } {t2} 618 619 ifcapable 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 654 do_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}} 663 do_test auth-1.92 { 664 execsql {SELECT name FROM sqlite_master} 665 } {t2} 666 do_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 {}} 675 do_test auth-1.94 { 676 execsql {SELECT name FROM sqlite_master} 677 } {t2} 678 679 ifcapable 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 706 do_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}} 718 do_test auth-1.100 { 719 execsql {SELECT name FROM sqlite_master} 720 } {t2 v2} 721 do_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}} 731 do_test auth-1.102 { 732 set ::authargs 733 } {v2 {} main {}} 734 do_test auth-1.103 { 735 execsql {SELECT name FROM sqlite_master} 736 } {t2 v2} 737 do_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 {}} 746 do_test auth-1.105 { 747 execsql {SELECT name FROM sqlite_master} 748 } {t2 v2} 749 do_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 {}} 759 do_test auth-1.107 { 760 set ::authargs 761 } {v2 {} main {}} 762 do_test auth-1.108 { 763 execsql {SELECT name FROM sqlite_master} 764 } {t2 v2} 765 do_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 {}} 775 do_test auth-1.110 { 776 set ::authargs 777 } {v2 {} main {}} 778 do_test auth-1.111 { 779 execsql {SELECT name FROM sqlite_master} 780 } {t2} 781 782 783 ifcapable 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 # 865 ifcapable trigger&&tempdb { 866 do_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}} 880 do_test auth-1.126 { 881 set ::authargs 882 } {r2 t2 main {}} 883 do_test auth-1.127 { 884 execsql {SELECT name FROM sqlite_master} 885 } {t2} 886 do_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}} 899 do_test auth-1.129 { 900 execsql {SELECT name FROM sqlite_master} 901 } {t2} 902 do_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 {}} 916 do_test auth-1.131 { 917 set ::authargs 918 } {r2 t2 main {}} 919 do_test auth-1.132 { 920 execsql {SELECT name FROM sqlite_master} 921 } {t2} 922 do_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 {}} 935 do_test auth-1.134 { 936 execsql {SELECT name FROM sqlite_master} 937 } {t2} 938 do_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 {}} 953 do_test auth-1.136.1 { 954 set ::authargs 955 } {r2 t2 main {}} 956 do_test auth-1.136.2 { 957 execsql { 958 SELECT name FROM sqlite_master WHERE type='trigger' 959 } 960 } {r2} 961 do_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} 972 do_test auth-1.136.4 { 973 execsql { 974 SELECT * FROM tx; 975 } 976 } {3} 977 do_test auth-1.137 { 978 execsql {SELECT name FROM sqlite_master} 979 } {t2 tx r2} 980 do_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}} 994 do_test auth-1.139 { 995 set ::authargs 996 } {r1 t1 temp {}} 997 do_test auth-1.140 { 998 execsql {SELECT name FROM temp.sqlite_master} 999 } {t1} 1000 do_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}} 1013 do_test auth-1.142 { 1014 execsql {SELECT name FROM sqlite_temp_master} 1015 } {t1} 1016 do_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 {}} 1030 do_test auth-1.144 { 1031 set ::authargs 1032 } {r1 t1 temp {}} 1033 do_test auth-1.145 { 1034 execsql {SELECT name FROM temp.sqlite_master} 1035 } {t1} 1036 do_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 {}} 1049 do_test auth-1.147 { 1050 execsql {SELECT name FROM sqlite_temp_master} 1051 } {t1} 1052 do_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 {}} 1066 do_test auth-1.149 { 1067 set ::authargs 1068 } {r1 t1 temp {}} 1069 do_test auth-1.150 { 1070 execsql {SELECT name FROM temp.sqlite_master} 1071 } {t1 r1} 1072 1073 do_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}} 1082 do_test auth-1.152 { 1083 execsql {SELECT name FROM sqlite_master} 1084 } {t2 tx r2} 1085 do_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}} 1095 do_test auth-1.154 { 1096 set ::authargs 1097 } {r2 t2 main {}} 1098 do_test auth-1.155 { 1099 execsql {SELECT name FROM sqlite_master} 1100 } {t2 tx r2} 1101 do_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 {}} 1110 do_test auth-1.157 { 1111 execsql {SELECT name FROM sqlite_master} 1112 } {t2 tx r2} 1113 do_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 {}} 1123 do_test auth-1.159 { 1124 set ::authargs 1125 } {r2 t2 main {}} 1126 do_test auth-1.160 { 1127 execsql {SELECT name FROM sqlite_master} 1128 } {t2 tx r2} 1129 do_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 {}} 1139 do_test auth-1.162 { 1140 set ::authargs 1141 } {r2 t2 main {}} 1142 do_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 1150 do_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}} 1159 do_test auth-1.165 { 1160 execsql {SELECT name FROM temp.sqlite_master} 1161 } {t1 r1} 1162 do_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}} 1172 do_test auth-1.167 { 1173 set ::authargs 1174 } {r1 t1 temp {}} 1175 do_test auth-1.168 { 1176 execsql {SELECT name FROM sqlite_temp_master} 1177 } {t1 r1} 1178 do_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 {}} 1187 do_test auth-1.170 { 1188 execsql {SELECT name FROM temp.sqlite_master} 1189 } {t1 r1} 1190 do_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 {}} 1200 do_test auth-1.172 { 1201 set ::authargs 1202 } {r1 t1 temp {}} 1203 do_test auth-1.173 { 1204 execsql {SELECT name FROM sqlite_temp_master} 1205 } {t1 r1} 1206 do_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 {}} 1216 do_test auth-1.175 { 1217 set ::authargs 1218 } {r1 t1 temp {}} 1219 do_test auth-1.176 { 1220 execsql {SELECT name FROM temp.sqlite_master} 1221 } {t1} 1222 } ;# ifcapable trigger 1223 1224 do_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}} 1234 do_test auth-1.178 { 1235 set ::authargs 1236 } {i2 t2 main {}} 1237 do_test auth-1.179 { 1238 execsql {SELECT name FROM sqlite_master} 1239 } {t2} 1240 do_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}} 1249 do_test auth-1.181 { 1250 execsql {SELECT name FROM sqlite_master} 1251 } {t2} 1252 do_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 {}} 1262 do_test auth-1.183 { 1263 set ::authargs 1264 } {i2 t2 main {}} 1265 do_test auth-1.184 { 1266 execsql {SELECT name FROM sqlite_master} 1267 } {t2} 1268 do_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 {}} 1277 do_test auth-1.186 { 1278 execsql {SELECT name FROM sqlite_master} 1279 } {t2} 1280 do_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 {}} 1290 do_test auth-1.188 { 1291 set ::authargs 1292 } {i2 t2 main {}} 1293 do_test auth-1.189 { 1294 execsql {SELECT name FROM sqlite_master} 1295 } {t2 i2} 1296 1297 ifcapable 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 1372 do_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}} 1381 do_test auth-1.204 { 1382 execsql {SELECT name FROM sqlite_master} 1383 } {t2 i2} 1384 do_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}} 1394 do_test auth-1.205a { 1395 set ::authargs 1396 } {i2 t2 main {}} 1397 db eval { 1398 ATTACH ':memory:' as di205; 1399 CREATE TABLE di205.t1(x); 1400 CREATE INDEX di205.t1x ON t1(x); 1401 } 1402 do_catchsql_test auth-1.205b { 1403 DROP INDEX di205.t1x; 1404 } {1 {not authorized}} 1405 db eval { 1406 DETACH di205; 1407 } 1408 do_test auth-1.206 { 1409 set ::authargs 1410 } {t1x t1 di205 {}} 1411 do_test auth-1.207 { 1412 execsql {SELECT name FROM sqlite_master} 1413 } {t2 i2} 1414 do_test auth-1.208 { 1415 proc auth {code arg1 arg2 arg3 arg4 args} { 1416 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1417 return SQLITE_IGNORE 1418 } 1419 return SQLITE_OK 1420 } 1421 catchsql {DROP INDEX i2} 1422 } {0 {}} 1423 do_test auth-1.209 { 1424 execsql {SELECT name FROM sqlite_master} 1425 } {t2 i2} 1426 do_test auth-1.210 { 1427 proc auth {code arg1 arg2 arg3 arg4 args} { 1428 if {$code=="SQLITE_DROP_INDEX"} { 1429 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1430 return SQLITE_IGNORE 1431 } 1432 return SQLITE_OK 1433 } 1434 catchsql {DROP INDEX i2} 1435 } {0 {}} 1436 do_test auth-1.211 { 1437 set ::authargs 1438 } {i2 t2 main {}} 1439 do_test auth-1.212 { 1440 execsql {SELECT name FROM sqlite_master} 1441 } {t2 i2} 1442 do_test auth-1.213 { 1443 proc auth {code arg1 arg2 arg3 arg4 args} { 1444 if {$code=="SQLITE_DROP_INDEX"} { 1445 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1446 return SQLITE_OK 1447 } 1448 return SQLITE_OK 1449 } 1450 catchsql {DROP INDEX i2} 1451 } {0 {}} 1452 do_test auth-1.214 { 1453 set ::authargs 1454 } {i2 t2 main {}} 1455 do_test auth-1.215 { 1456 execsql {SELECT name FROM sqlite_master} 1457 } {t2} 1458 1459 ifcapable tempdb { 1460 do_test auth-1.216 { 1461 proc auth {code arg1 arg2 arg3 arg4 args} { 1462 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1463 return SQLITE_DENY 1464 } 1465 return SQLITE_OK 1466 } 1467 catchsql {DROP INDEX i1} 1468 } {1 {not authorized}} 1469 do_test auth-1.217 { 1470 execsql {SELECT name FROM sqlite_temp_master} 1471 } {t1 i1} 1472 do_test auth-1.218 { 1473 proc auth {code arg1 arg2 arg3 arg4 args} { 1474 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1475 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1476 return SQLITE_DENY 1477 } 1478 return SQLITE_OK 1479 } 1480 catchsql {DROP INDEX i1} 1481 } {1 {not authorized}} 1482 do_test auth-1.219 { 1483 set ::authargs 1484 } {i1 t1 temp {}} 1485 do_test auth-1.220 { 1486 execsql {SELECT name FROM sqlite_temp_master} 1487 } {t1 i1} 1488 do_test auth-1.221 { 1489 proc auth {code arg1 arg2 arg3 arg4 args} { 1490 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1491 return SQLITE_IGNORE 1492 } 1493 return SQLITE_OK 1494 } 1495 catchsql {DROP INDEX i1} 1496 } {0 {}} 1497 do_test auth-1.222 { 1498 execsql {SELECT name FROM temp.sqlite_master} 1499 } {t1 i1} 1500 do_test auth-1.223 { 1501 proc auth {code arg1 arg2 arg3 arg4 args} { 1502 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1503 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1504 return SQLITE_IGNORE 1505 } 1506 return SQLITE_OK 1507 } 1508 catchsql {DROP INDEX i1} 1509 } {0 {}} 1510 do_test auth-1.224 { 1511 set ::authargs 1512 } {i1 t1 temp {}} 1513 do_test auth-1.225 { 1514 execsql {SELECT name FROM temp.sqlite_master} 1515 } {t1 i1} 1516 do_test auth-1.226 { 1517 proc auth {code arg1 arg2 arg3 arg4 args} { 1518 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1519 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1520 return SQLITE_OK 1521 } 1522 return SQLITE_OK 1523 } 1524 catchsql {DROP INDEX i1} 1525 } {0 {}} 1526 do_test auth-1.227 { 1527 set ::authargs 1528 } {i1 t1 temp {}} 1529 do_test auth-1.228 { 1530 execsql {SELECT name FROM temp.sqlite_master} 1531 } {t1} 1532 } 1533 1534 do_test auth-1.229 { 1535 proc auth {code arg1 arg2 arg3 arg4 args} { 1536 if {$code=="SQLITE_PRAGMA"} { 1537 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1538 return SQLITE_DENY 1539 } 1540 return SQLITE_OK 1541 } 1542 catchsql {PRAGMA full_column_names=on} 1543 } {1 {not authorized}} 1544 do_test auth-1.230 { 1545 set ::authargs 1546 } {full_column_names on {} {}} 1547 do_test auth-1.231 { 1548 execsql2 {SELECT a FROM t2} 1549 } {a 11 a 7} 1550 do_test auth-1.232 { 1551 proc auth {code arg1 arg2 arg3 arg4 args} { 1552 if {$code=="SQLITE_PRAGMA"} { 1553 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1554 return SQLITE_IGNORE 1555 } 1556 return SQLITE_OK 1557 } 1558 catchsql {PRAGMA full_column_names=on} 1559 } {0 {}} 1560 do_test auth-1.233 { 1561 set ::authargs 1562 } {full_column_names on {} {}} 1563 do_test auth-1.234 { 1564 execsql2 {SELECT a FROM t2} 1565 } {a 11 a 7} 1566 do_test auth-1.235 { 1567 proc auth {code arg1 arg2 arg3 arg4 args} { 1568 if {$code=="SQLITE_PRAGMA"} { 1569 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1570 return SQLITE_OK 1571 } 1572 return SQLITE_OK 1573 } 1574 catchsql {PRAGMA full_column_names=on} 1575 } {0 {}} 1576 do_test auth-1.236 { 1577 execsql2 {SELECT a FROM t2} 1578 } {t2.a 11 t2.a 7} 1579 do_test auth-1.237 { 1580 proc auth {code arg1 arg2 arg3 arg4 args} { 1581 if {$code=="SQLITE_PRAGMA"} { 1582 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1583 return SQLITE_OK 1584 } 1585 return SQLITE_OK 1586 } 1587 catchsql {PRAGMA full_column_names=OFF} 1588 } {0 {}} 1589 do_test auth-1.238 { 1590 set ::authargs 1591 } {full_column_names OFF {} {}} 1592 do_test auth-1.239 { 1593 execsql2 {SELECT a FROM t2} 1594 } {a 11 a 7} 1595 1596 do_test auth-1.240 { 1597 proc auth {code arg1 arg2 arg3 arg4 args} { 1598 if {$code=="SQLITE_TRANSACTION"} { 1599 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1600 return SQLITE_DENY 1601 } 1602 return SQLITE_OK 1603 } 1604 catchsql {BEGIN} 1605 } {1 {not authorized}} 1606 do_test auth-1.241 { 1607 set ::authargs 1608 } {BEGIN {} {} {}} 1609 do_test auth-1.242 { 1610 proc auth {code arg1 arg2 arg3 arg4 args} { 1611 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} { 1612 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1613 return SQLITE_DENY 1614 } 1615 return SQLITE_OK 1616 } 1617 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT} 1618 } {1 {not authorized}} 1619 do_test auth-1.243 { 1620 set ::authargs 1621 } {COMMIT {} {} {}} 1622 do_test auth-1.244 { 1623 execsql {SELECT * FROM t2} 1624 } {11 2 33 7 8 9 44 55 66} 1625 do_test auth-1.245 { 1626 catchsql {ROLLBACK} 1627 } {1 {not authorized}} 1628 do_test auth-1.246 { 1629 set ::authargs 1630 } {ROLLBACK {} {} {}} 1631 do_test auth-1.247 { 1632 catchsql {END TRANSACTION} 1633 } {1 {not authorized}} 1634 do_test auth-1.248 { 1635 set ::authargs 1636 } {COMMIT {} {} {}} 1637 do_test auth-1.249 { 1638 # EVIDENCE-OF: R-52112-44167 Disable the authorizer by installing a NULL 1639 # callback. 1640 db authorizer {} 1641 catchsql {ROLLBACK} 1642 } {0 {}} 1643 do_test auth-1.250 { 1644 execsql {SELECT * FROM t2} 1645 } {11 2 33 7 8 9} 1646 1647 # ticket #340 - authorization for ATTACH and DETACH. 1648 # 1649 ifcapable attach { 1650 do_test auth-1.251 { 1651 db authorizer ::auth 1652 proc auth {code arg1 arg2 arg3 arg4 args} { 1653 if {$code=="SQLITE_ATTACH"} { 1654 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1655 } 1656 return SQLITE_OK 1657 } 1658 catchsql { 1659 ATTACH DATABASE ':memory:' AS test1 1660 } 1661 } {0 {}} 1662 do_test auth-1.252a { 1663 set ::authargs 1664 } {:memory: {} {} {}} 1665 do_test auth-1.252b { 1666 db eval {DETACH test1} 1667 set ::attachfilename :memory: 1668 db eval {ATTACH $::attachfilename AS test1} 1669 set ::authargs 1670 } {{} {} {} {}} 1671 do_test auth-1.252c { 1672 db eval {DETACH test1} 1673 db eval {ATTACH ':mem' || 'ory:' AS test1} 1674 set ::authargs 1675 } {{} {} {} {}} 1676 do_test auth-1.253 { 1677 catchsql {DETACH DATABASE test1} 1678 proc auth {code arg1 arg2 arg3 arg4 args} { 1679 if {$code=="SQLITE_ATTACH"} { 1680 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1681 return SQLITE_DENY 1682 } 1683 return SQLITE_OK 1684 } 1685 catchsql { 1686 ATTACH DATABASE ':memory:' AS test1; 1687 } 1688 } {1 {not authorized}} 1689 do_test auth-1.254 { 1690 lindex [execsql {PRAGMA database_list}] 7 1691 } {} 1692 do_test auth-1.255 { 1693 catchsql {DETACH DATABASE test1} 1694 proc auth {code arg1 arg2 arg3 arg4 args} { 1695 if {$code=="SQLITE_ATTACH"} { 1696 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1697 return SQLITE_IGNORE 1698 } 1699 return SQLITE_OK 1700 } 1701 catchsql { 1702 ATTACH DATABASE ':memory:' AS test1; 1703 } 1704 } {0 {}} 1705 do_test auth-1.256 { 1706 lindex [execsql {PRAGMA database_list}] 7 1707 } {} 1708 do_test auth-1.257 { 1709 proc auth {code arg1 arg2 arg3 arg4 args} { 1710 if {$code=="SQLITE_DETACH"} { 1711 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1712 return SQLITE_OK 1713 } 1714 return SQLITE_OK 1715 } 1716 execsql {ATTACH DATABASE ':memory:' AS test1} 1717 catchsql { 1718 DETACH DATABASE test1; 1719 } 1720 } {0 {}} 1721 do_test auth-1.258 { 1722 lindex [execsql {PRAGMA database_list}] 7 1723 } {} 1724 do_test auth-1.259 { 1725 execsql {ATTACH DATABASE ':memory:' AS test1} 1726 proc auth {code arg1 arg2 arg3 arg4 args} { 1727 if {$code=="SQLITE_DETACH"} { 1728 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1729 return SQLITE_IGNORE 1730 } 1731 return SQLITE_OK 1732 } 1733 catchsql { 1734 DETACH DATABASE test1; 1735 } 1736 } {0 {}} 1737 ifcapable tempdb { 1738 ifcapable schema_pragmas { 1739 do_test auth-1.260 { 1740 lindex [execsql {PRAGMA database_list}] 7 1741 } {test1} 1742 } ;# ifcapable schema_pragmas 1743 do_test auth-1.261 { 1744 proc auth {code arg1 arg2 arg3 arg4 args} { 1745 if {$code=="SQLITE_DETACH"} { 1746 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1747 return SQLITE_DENY 1748 } 1749 return SQLITE_OK 1750 } 1751 catchsql { 1752 DETACH DATABASE test1; 1753 } 1754 } {1 {not authorized}} 1755 ifcapable schema_pragmas { 1756 do_test auth-1.262 { 1757 lindex [execsql {PRAGMA database_list}] 7 1758 } {test1} 1759 } ;# ifcapable schema_pragmas 1760 db authorizer {} 1761 execsql {DETACH DATABASE test1} 1762 db authorizer ::auth 1763 1764 # Authorization for ALTER TABLE. These tests are omitted if the library 1765 # was built without ALTER TABLE support. 1766 ifcapable altertable { 1767 1768 do_test auth-1.263 { 1769 proc auth {code arg1 arg2 arg3 arg4 args} { 1770 if {$code=="SQLITE_ALTER_TABLE"} { 1771 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1772 return SQLITE_OK 1773 } 1774 return SQLITE_OK 1775 } 1776 catchsql { 1777 ALTER TABLE t1 RENAME TO t1x 1778 } 1779 } {0 {}} 1780 do_test auth-1.264 { 1781 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1782 } {t1x} 1783 do_test auth-1.265 { 1784 set authargs 1785 } {temp t1 {} {}} 1786 do_test auth-1.266 { 1787 proc auth {code arg1 arg2 arg3 arg4 args} { 1788 if {$code=="SQLITE_ALTER_TABLE"} { 1789 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1790 return SQLITE_IGNORE 1791 } 1792 return SQLITE_OK 1793 } 1794 catchsql { 1795 ALTER TABLE t1x RENAME TO t1 1796 } 1797 } {0 {}} 1798 do_test auth-1.267 { 1799 execsql {SELECT name FROM temp.sqlite_master WHERE type='table'} 1800 } {t1x} 1801 do_test auth-1.268 { 1802 set authargs 1803 } {temp t1x {} {}} 1804 do_test auth-1.269 { 1805 proc auth {code arg1 arg2 arg3 arg4 args} { 1806 if {$code=="SQLITE_ALTER_TABLE"} { 1807 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1808 return SQLITE_DENY 1809 } 1810 return SQLITE_OK 1811 } 1812 catchsql { 1813 ALTER TABLE t1x RENAME TO t1 1814 } 1815 } {1 {not authorized}} 1816 do_test auth-1.270 { 1817 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1818 } {t1x} 1819 1820 do_test auth-1.271 { 1821 set authargs 1822 } {temp t1x {} {}} 1823 } ;# ifcapable altertable 1824 1825 } else { 1826 db authorizer {} 1827 db eval { 1828 DETACH DATABASE test1; 1829 } 1830 } 1831 } 1832 1833 ifcapable altertable { 1834 db authorizer {} 1835 catchsql {ALTER TABLE t1x RENAME TO t1} 1836 db authorizer ::auth 1837 do_test auth-1.272 { 1838 proc auth {code arg1 arg2 arg3 arg4 args} { 1839 if {$code=="SQLITE_ALTER_TABLE"} { 1840 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1841 return SQLITE_OK 1842 } 1843 return SQLITE_OK 1844 } 1845 catchsql { 1846 ALTER TABLE t2 RENAME TO t2x 1847 } 1848 } {0 {}} 1849 do_test auth-1.273 { 1850 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1851 } {t2x} 1852 do_test auth-1.274 { 1853 set authargs 1854 } {main t2 {} {}} 1855 do_test auth-1.275 { 1856 proc auth {code arg1 arg2 arg3 arg4 args} { 1857 if {$code=="SQLITE_ALTER_TABLE"} { 1858 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1859 return SQLITE_IGNORE 1860 } 1861 return SQLITE_OK 1862 } 1863 catchsql { 1864 ALTER TABLE t2x RENAME TO t2 1865 } 1866 } {0 {}} 1867 do_test auth-1.276 { 1868 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1869 } {t2x} 1870 do_test auth-1.277 { 1871 set authargs 1872 } {main t2x {} {}} 1873 do_test auth-1.278 { 1874 proc auth {code arg1 arg2 arg3 arg4 args} { 1875 if {$code=="SQLITE_ALTER_TABLE"} { 1876 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1877 return SQLITE_DENY 1878 } 1879 return SQLITE_OK 1880 } 1881 catchsql { 1882 ALTER TABLE t2x RENAME TO t2 1883 } 1884 } {1 {not authorized}} 1885 do_test auth-1.279 { 1886 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1887 } {t2x} 1888 do_test auth-1.280 { 1889 set authargs 1890 } {main t2x {} {}} 1891 db authorizer {} 1892 catchsql {ALTER TABLE t2x RENAME TO t2} 1893 1894 } ;# ifcapable altertable 1895 1896 # Test the authorization callbacks for the REINDEX command. 1897 ifcapable reindex { 1898 1899 proc auth {code args} { 1900 if {$code=="SQLITE_REINDEX"} { 1901 set ::authargs [concat $::authargs [lrange $args 0 3]] 1902 } 1903 return SQLITE_OK 1904 } 1905 db authorizer auth 1906 do_test auth-1.281 { 1907 execsql { 1908 CREATE TABLE t3(a PRIMARY KEY, b, c); 1909 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1910 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1911 } 1912 } {} 1913 do_test auth-1.282 { 1914 set ::authargs {} 1915 execsql { 1916 REINDEX t3_idx1; 1917 } 1918 set ::authargs 1919 } {t3_idx1 {} main {}} 1920 do_test auth-1.283 { 1921 set ::authargs {} 1922 execsql { 1923 REINDEX BINARY; 1924 } 1925 set ::authargs 1926 } {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1927 do_test auth-1.284 { 1928 set ::authargs {} 1929 execsql { 1930 REINDEX NOCASE; 1931 } 1932 set ::authargs 1933 } {t3_idx2 {} main {}} 1934 do_test auth-1.285 { 1935 set ::authargs {} 1936 execsql { 1937 REINDEX t3; 1938 } 1939 set ::authargs 1940 } {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1941 do_test auth-1.286 { 1942 execsql { 1943 DROP TABLE t3; 1944 } 1945 } {} 1946 ifcapable tempdb { 1947 do_test auth-1.287 { 1948 execsql { 1949 CREATE TEMP TABLE t3(a PRIMARY KEY, b, c); 1950 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1951 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1952 } 1953 } {} 1954 do_test auth-1.288 { 1955 set ::authargs {} 1956 execsql { 1957 REINDEX temp.t3_idx1; 1958 } 1959 set ::authargs 1960 } {t3_idx1 {} temp {}} 1961 do_test auth-1.289 { 1962 set ::authargs {} 1963 execsql { 1964 REINDEX BINARY; 1965 } 1966 set ::authargs 1967 } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1968 do_test auth-1.290 { 1969 set ::authargs {} 1970 execsql { 1971 REINDEX NOCASE; 1972 } 1973 set ::authargs 1974 } {t3_idx2 {} temp {}} 1975 do_test auth-1.291 { 1976 set ::authargs {} 1977 execsql { 1978 REINDEX temp.t3; 1979 } 1980 set ::authargs 1981 } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1982 proc auth {code args} { 1983 if {$code=="SQLITE_REINDEX"} { 1984 set ::authargs [concat $::authargs [lrange $args 0 3]] 1985 return SQLITE_DENY 1986 } 1987 return SQLITE_OK 1988 } 1989 do_test auth-1.292 { 1990 set ::authargs {} 1991 catchsql { 1992 REINDEX temp.t3; 1993 } 1994 } {1 {not authorized}} 1995 do_test auth-1.293 { 1996 execsql { 1997 DROP TABLE t3; 1998 } 1999 } {} 2000 } 2001 2002 } ;# ifcapable reindex 2003 2004 ifcapable analyze { 2005 proc auth {code args} { 2006 if {$code=="SQLITE_ANALYZE"} { 2007 set ::authargs [concat $::authargs [lrange $args 0 3]] 2008 } 2009 return SQLITE_OK 2010 } 2011 do_test auth-1.294 { 2012 set ::authargs {} 2013 execsql { 2014 CREATE TABLE t4(a,b,c); 2015 CREATE INDEX t4i1 ON t4(a); 2016 CREATE INDEX t4i2 ON t4(b,a,c); 2017 INSERT INTO t4 VALUES(1,2,3); 2018 ANALYZE; 2019 } 2020 set ::authargs 2021 } {t4 {} main {} t2 {} main {}} 2022 do_test auth-1.295 { 2023 execsql { 2024 SELECT count(*) FROM sqlite_stat1; 2025 } 2026 } 3 2027 proc auth {code args} { 2028 if {$code=="SQLITE_ANALYZE"} { 2029 set ::authargs [concat $::authargs $args] 2030 return SQLITE_DENY 2031 } 2032 return SQLITE_OK 2033 } 2034 do_test auth-1.296 { 2035 set ::authargs {} 2036 catchsql { 2037 ANALYZE; 2038 } 2039 } {1 {not authorized}} 2040 do_test auth-1.297 { 2041 execsql { 2042 SELECT count(*) FROM sqlite_stat1; 2043 } 2044 } 3 2045 } ;# ifcapable analyze 2046 2047 2048 # Authorization for ALTER TABLE ADD COLUMN. 2049 # These tests are omitted if the library 2050 # was built without ALTER TABLE support. 2051 ifcapable {altertable} { 2052 do_test auth-1.300 { 2053 execsql {CREATE TABLE t5(x)} 2054 proc auth {code arg1 arg2 arg3 arg4 args} { 2055 if {$code=="SQLITE_ALTER_TABLE"} { 2056 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2057 return SQLITE_OK 2058 } 2059 return SQLITE_OK 2060 } 2061 catchsql { 2062 ALTER TABLE t5 ADD COLUMN new_col_1; 2063 } 2064 } {0 {}} 2065 do_test auth-1.301 { 2066 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2067 regexp new_col_1 $x 2068 } {1} 2069 do_test auth-1.302 { 2070 set authargs 2071 } {main t5 {} {}} 2072 db eval BEGIN 2073 set authargs {} 2074 do_execsql_test auth-1.302-drop-1 { 2075 ALTER TABLE t5 DROP COLUMN new_col_1; 2076 } {} 2077 db eval ROLLBACK 2078 do_test auth-1.302-drop-2 { 2079 set authargs 2080 } {main t5 new_col_1 {}} 2081 do_test auth-1.303 { 2082 proc auth {code arg1 arg2 arg3 arg4 args} { 2083 if {$code=="SQLITE_ALTER_TABLE"} { 2084 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2085 return SQLITE_IGNORE 2086 } 2087 return SQLITE_OK 2088 } 2089 catchsql { 2090 ALTER TABLE t5 ADD COLUMN new_col_2; 2091 } 2092 } {0 {}} 2093 do_test auth-1.304 { 2094 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2095 regexp new_col_2 $x 2096 } {0} 2097 do_test auth-1.305 { 2098 set authargs 2099 } {main t5 {} {}} 2100 db eval BEGIN 2101 set authargs {} 2102 do_execsql_test auth-1.305-drop-1 { 2103 ALTER TABLE t5 DROP COLUMN new_col_1; 2104 SELECT 1 FROM sqlite_schema WHERE name='t5' AND sql LIKE '%new_col_1%'; 2105 } {1} 2106 db eval ROLLBACK 2107 do_test auth-1.305-drop-2 { 2108 set authargs 2109 } {main t5 new_col_1 {}} 2110 do_test auth-1.306 { 2111 proc auth {code arg1 arg2 arg3 arg4 args} { 2112 if {$code=="SQLITE_ALTER_TABLE"} { 2113 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2114 return SQLITE_DENY 2115 } 2116 return SQLITE_OK 2117 } 2118 catchsql { 2119 ALTER TABLE t5 ADD COLUMN new_col_3 2120 } 2121 } {1 {not authorized}} 2122 do_test auth-1.307 { 2123 set x [execsql {SELECT sql FROM temp.sqlite_master WHERE type='t5'}] 2124 regexp new_col_3 $x 2125 } {0} 2126 do_test auth-1.308 { 2127 set authargs 2128 } {main t5 {} {}} 2129 db eval BEGIN 2130 set authargs {} 2131 do_catchsql_test auth-1.308-drop-1 { 2132 ALTER TABLE t5 DROP COLUMN new_col_1; 2133 } {1 {not authorized}} 2134 do_execsql_test auth-1.308-drop-2 { 2135 SELECT 1 FROM sqlite_schema WHERE name='t5' AND sql LIKE '%new_col_1%'; 2136 } {1} 2137 do_test auth-1.308-drop-3 { 2138 set authargs 2139 } {main t5 new_col_1 {}} 2140 db eval ROLLBACK 2141 2142 execsql {DROP TABLE t5} 2143 } ;# ifcapable altertable 2144 2145 ifcapable {cte} { 2146 do_test auth-1.310 { 2147 proc auth {code arg1 arg2 arg3 arg4 args} { 2148 if {$code=="SQLITE_RECURSIVE"} { 2149 return SQLITE_DENY 2150 } 2151 return SQLITE_OK 2152 } 2153 db eval { 2154 DROP TABLE IF EXISTS t1; 2155 CREATE TABLE t1(a,b); 2156 INSERT INTO t1 VALUES(1,2),(3,4),(5,6); 2157 } 2158 } {} 2159 do_catchsql_test auth-1.311 { 2160 WITH 2161 auth1311(x,y) AS (SELECT a+b, b-a FROM t1) 2162 SELECT * FROM auth1311 ORDER BY x; 2163 } {0 {3 1 7 1 11 1}} 2164 do_catchsql_test auth-1.312 { 2165 WITH RECURSIVE 2166 auth1312(x,y) AS (SELECT a+b, b-a FROM t1) 2167 SELECT x, y FROM auth1312 ORDER BY x; 2168 } {0 {3 1 7 1 11 1}} 2169 do_catchsql_test auth-1.313 { 2170 WITH RECURSIVE 2171 auth1313(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1313 WHERE x<5) 2172 SELECT * FROM t1; 2173 } {0 {1 2 3 4 5 6}} 2174 do_catchsql_test auth-1.314 { 2175 WITH RECURSIVE 2176 auth1314(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1314 WHERE x<5) 2177 SELECT * FROM t1 LEFT JOIN auth1314; 2178 } {1 {not authorized}} 2179 } ;# ifcapable cte 2180 2181 # 2182 # db eval {SELECT sql FROM temp.sqlite_master} {puts "TEMP: $sql;"} 2183 # db eval {SELECT sql FROM main.sqlite_master} {puts "MAIN: $sql;"} 2184 # 2185 # MAIN: CREATE TABLE "t2"(a,b,c); 2186 # MAIN: CREATE TABLE t4(a,b,c); 2187 # MAIN: CREATE INDEX t4i1 ON t4(a); 2188 # MAIN: CREATE INDEX t4i2 ON t4(b,a,c); 2189 # MAIN: CREATE TABLE sqlite_stat1(tbl,idx,stat); 2190 # MAIN: CREATE TABLE t1(a,b); 2191 # 2192 ifcapable altertable&&vtab { 2193 do_test auth-1.350 { 2194 proc auth {code arg1 arg2 arg3 arg4 args} { 2195 if {$code=="SQLITE_ALTER_TABLE"} { 2196 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2197 return SQLITE_OK 2198 } 2199 return SQLITE_OK 2200 } 2201 catchsql { 2202 ALTER TABLE t1 RENAME COLUMN b TO bcdefg; 2203 } 2204 } {0 {}} 2205 do_execsql_test auth-1.351 { 2206 SELECT name FROM pragma_table_info('t1') ORDER BY cid; 2207 } {a bcdefg} 2208 do_test auth-1.352 { 2209 set authargs 2210 } {main t1 {} {}} 2211 do_test auth-1.353 { 2212 proc auth {code arg1 arg2 arg3 arg4 args} { 2213 if {$code=="SQLITE_ALTER_TABLE"} { 2214 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2215 return SQLITE_IGNORE 2216 } 2217 return SQLITE_OK 2218 } 2219 catchsql { 2220 ALTER TABLE t1 RENAME COLUMN bcdefg TO b; 2221 } 2222 } {0 {}} 2223 do_execsql_test auth-1.354 { 2224 SELECT name FROM pragma_table_info('t1') ORDER BY cid; 2225 } {a bcdefg} 2226 do_test auth-1.355 { 2227 set authargs 2228 } {main t1 {} {}} 2229 do_test auth-1.356 { 2230 proc auth {code arg1 arg2 arg3 arg4 args} { 2231 if {$code=="SQLITE_ALTER_TABLE"} { 2232 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2233 return SQLITE_DENY 2234 } 2235 return SQLITE_OK 2236 } 2237 catchsql { 2238 ALTER TABLE t1 RENAME COLUMN bcdefg TO b; 2239 } 2240 } {1 {not authorized}} 2241 do_execsql_test auth-1.357 { 2242 SELECT name FROM pragma_table_info('t1') ORDER BY cid; 2243 } {a bcdefg} 2244 do_test auth-1.358 { 2245 set authargs 2246 } {main t1 {} {}} 2247 } 2248 2249 2250 do_test auth-2.1 { 2251 proc auth {code arg1 arg2 arg3 arg4 args} { 2252 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2253 return SQLITE_DENY 2254 } 2255 return SQLITE_OK 2256 } 2257 db authorizer ::auth 2258 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)} 2259 catchsql {SELECT * FROM t3} 2260 } {1 {access to t3.x is prohibited}} 2261 do_test auth-2.1 { 2262 catchsql {SELECT y,z FROM t3} 2263 } {0 {}} 2264 do_test auth-2.2 { 2265 catchsql {SELECT ROWID,y,z FROM t3} 2266 } {1 {access to t3.x is prohibited}} 2267 do_test auth-2.3 { 2268 catchsql {SELECT OID,y,z FROM t3} 2269 } {1 {access to t3.x is prohibited}} 2270 do_test auth-2.4 { 2271 proc auth {code arg1 arg2 arg3 arg4 args} { 2272 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2273 return SQLITE_IGNORE 2274 } 2275 return SQLITE_OK 2276 } 2277 execsql {INSERT INTO t3 VALUES(44,55,66)} 2278 catchsql {SELECT * FROM t3} 2279 } {0 {{} 55 66}} 2280 do_test auth-2.5 { 2281 catchsql {SELECT rowid,y,z FROM t3} 2282 } {0 {{} 55 66}} 2283 do_test auth-2.6 { 2284 proc auth {code arg1 arg2 arg3 arg4 args} { 2285 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} { 2286 return SQLITE_IGNORE 2287 } 2288 return SQLITE_OK 2289 } 2290 catchsql {SELECT * FROM t3} 2291 } {0 {44 55 66}} 2292 do_test auth-2.7 { 2293 catchsql {SELECT ROWID,y,z FROM t3} 2294 } {0 {44 55 66}} 2295 do_test auth-2.8 { 2296 proc auth {code arg1 arg2 arg3 arg4 args} { 2297 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2298 return SQLITE_IGNORE 2299 } 2300 return SQLITE_OK 2301 } 2302 catchsql {SELECT ROWID,b,c FROM t2} 2303 } {0 {{} 2 33 {} 8 9}} 2304 do_test auth-2.9.1 { 2305 # We have to flush the cache here in case the Tcl interface tries to 2306 # reuse a statement compiled with sqlite3_prepare_v2(). In this case, 2307 # the first error encountered is an SQLITE_SCHEMA error. Then, when 2308 # trying to recompile the statement, the authorization error is encountered. 2309 # If we do not flush the cache, the correct error message is returned, but 2310 # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test 2311 # case after this one. 2312 # 2313 db cache flush 2314 2315 proc auth {code arg1 arg2 arg3 arg4 args} { 2316 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2317 return bogus 2318 } 2319 return SQLITE_OK 2320 } 2321 catchsql {SELECT ROWID,b,c FROM t2} 2322 } {1 {authorizer malfunction}} 2323 do_test auth-2.9.2 { 2324 db errorcode 2325 } {1} 2326 do_test auth-2.10 { 2327 proc auth {code arg1 arg2 arg3 arg4 args} { 2328 if {$code=="SQLITE_SELECT"} { 2329 return bogus 2330 } 2331 return SQLITE_OK 2332 } 2333 catchsql {SELECT ROWID,b,c FROM t2} 2334 } {1 {authorizer malfunction}} 2335 do_test auth-2.11.1 { 2336 proc auth {code arg1 arg2 arg3 arg4 args} { 2337 if {$code=="SQLITE_READ" && $arg2=="a"} { 2338 return SQLITE_IGNORE 2339 } 2340 return SQLITE_OK 2341 } 2342 catchsql {SELECT * FROM t2, t3} 2343 } {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}} 2344 do_test auth-2.11.2 { 2345 proc auth {code arg1 arg2 arg3 arg4 args} { 2346 if {$code=="SQLITE_READ" && $arg2=="x"} { 2347 return SQLITE_IGNORE 2348 } 2349 return SQLITE_OK 2350 } 2351 catchsql {SELECT * FROM t2, t3} 2352 } {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}} 2353 2354 # Make sure the OLD and NEW pseudo-tables of a trigger get authorized. 2355 # 2356 ifcapable trigger { 2357 do_test auth-3.1 { 2358 proc auth {code arg1 arg2 arg3 arg4 args} { 2359 return SQLITE_OK 2360 } 2361 execsql { 2362 CREATE TABLE tx(a1,a2,b1,b2,c1,c2); 2363 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 2364 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c); 2365 END; 2366 UPDATE t2 SET a=a+1; 2367 SELECT * FROM tx; 2368 } 2369 } {11 12 2 2 33 33 7 8 8 8 9 9} 2370 do_test auth-3.2 { 2371 proc auth {code arg1 arg2 arg3 arg4 args} { 2372 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} { 2373 return SQLITE_IGNORE 2374 } 2375 return SQLITE_OK 2376 } 2377 execsql { 2378 DELETE FROM tx; 2379 UPDATE t2 SET a=a+100; 2380 SELECT * FROM tx; 2381 } 2382 } {12 112 2 2 {} {} 8 108 8 8 {} {}} 2383 } ;# ifcapable trigger 2384 2385 # Make sure the names of views and triggers are passed on on arg4. 2386 # 2387 ifcapable trigger { 2388 do_test auth-4.1 { 2389 proc auth {code arg1 arg2 arg3 arg4 args} { 2390 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 2391 return SQLITE_OK 2392 } 2393 set authargs {} 2394 execsql { 2395 UPDATE t2 SET a=a+1; 2396 } 2397 set authargs 2398 } [list \ 2399 SQLITE_READ t2 a main {} \ 2400 SQLITE_UPDATE t2 a main {} \ 2401 SQLITE_INSERT tx {} main r1 \ 2402 SQLITE_READ t2 a main r1 \ 2403 SQLITE_READ t2 a main r1 \ 2404 SQLITE_READ t2 b main r1 \ 2405 SQLITE_READ t2 b main r1 \ 2406 SQLITE_READ t2 c main r1 \ 2407 SQLITE_READ t2 c main r1] 2408 } 2409 2410 ifcapable {view && trigger} { 2411 do_test auth-4.2 { 2412 execsql { 2413 CREATE VIEW v1 AS SELECT a+b AS x FROM t2; 2414 CREATE TABLE v1chng(x1,x2); 2415 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN 2416 INSERT INTO v1chng VALUES(OLD.x,NEW.x); 2417 END; 2418 SELECT * FROM v1; 2419 } 2420 } {115 117} 2421 do_test auth-4.3 { 2422 set authargs {} 2423 execsql { 2424 UPDATE v1 SET x=1 WHERE x=117 2425 } 2426 set authargs 2427 } [list \ 2428 SQLITE_UPDATE v1 x main {} \ 2429 SQLITE_SELECT {} {} {} v1 \ 2430 SQLITE_READ t2 a main v1 \ 2431 SQLITE_READ t2 b main v1 \ 2432 SQLITE_READ v1 x main v1 \ 2433 SQLITE_READ v1 x main v1 \ 2434 SQLITE_SELECT {} {} {} v1 \ 2435 SQLITE_READ v1 x main v1 \ 2436 SQLITE_INSERT v1chng {} main r2 \ 2437 SQLITE_READ v1 x main r2 \ 2438 SQLITE_READ v1 x main r2 \ 2439 ] 2440 2441 do_test auth-4.4 { 2442 execsql { 2443 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN 2444 INSERT INTO v1chng VALUES(OLD.x,NULL); 2445 END; 2446 SELECT * FROM v1; 2447 } 2448 } {115 117} 2449 do_test auth-4.5 { 2450 set authargs {} 2451 execsql { 2452 DELETE FROM v1 WHERE x=117 2453 } 2454 set authargs 2455 } [list \ 2456 SQLITE_DELETE v1 {} main {} \ 2457 SQLITE_SELECT {} {} {} v1 \ 2458 SQLITE_READ t2 a main v1 \ 2459 SQLITE_READ t2 b main v1 \ 2460 SQLITE_READ v1 x main v1 \ 2461 SQLITE_READ v1 x main v1 \ 2462 SQLITE_SELECT {} {} {} v1 \ 2463 SQLITE_READ v1 x main v1 \ 2464 SQLITE_INSERT v1chng {} main r3 \ 2465 SQLITE_READ v1 x main r3 \ 2466 ] 2467 2468 } ;# ifcapable view && trigger 2469 2470 # Ticket #1338: Make sure authentication works in the presence of an AS 2471 # clause. 2472 # 2473 do_test auth-5.1 { 2474 proc auth {code arg1 arg2 arg3 arg4 args} { 2475 return SQLITE_OK 2476 } 2477 execsql { 2478 SELECT count(a) AS cnt FROM t4 ORDER BY cnt 2479 } 2480 } {1} 2481 2482 # Ticket #1607 2483 # 2484 ifcapable compound&&subquery { 2485 ifcapable trigger { 2486 execsql { 2487 DROP TABLE tx; 2488 } 2489 ifcapable view { 2490 execsql { 2491 DROP TABLE v1chng; 2492 } 2493 } 2494 } 2495 ifcapable stat4 { 2496 set stat4 "sqlite_stat4 " 2497 } else { 2498 set stat4 "" 2499 } 2500 do_test auth-5.2 { 2501 execsql { 2502 SELECT name FROM ( 2503 SELECT * FROM sqlite_master UNION ALL SELECT * FROM temp.sqlite_master) 2504 WHERE type='table' 2505 ORDER BY name 2506 } 2507 } "sqlite_stat1 ${stat4}t1 t2 t3 t4" 2508 } 2509 2510 # Ticket #3944 2511 # 2512 ifcapable trigger { 2513 do_test auth-5.3.1 { 2514 execsql { 2515 CREATE TABLE t5 ( x ); 2516 CREATE TRIGGER t5_tr1 AFTER INSERT ON t5 BEGIN 2517 UPDATE t5 SET x = 1 WHERE NEW.x = 0; 2518 END; 2519 } 2520 } {} 2521 set ::authargs [list] 2522 proc auth {args} { 2523 eval lappend ::authargs [lrange $args 0 4] 2524 return SQLITE_OK 2525 } 2526 do_test auth-5.3.2 { 2527 execsql { INSERT INTO t5 (x) values(0) } 2528 set ::authargs 2529 } [list SQLITE_INSERT t5 {} main {} \ 2530 SQLITE_UPDATE t5 x main t5_tr1 \ 2531 SQLITE_READ t5 x main t5_tr1 \ 2532 ] 2533 do_test auth-5.3.2 { 2534 execsql { SELECT * FROM t5 } 2535 } {1} 2536 } 2537 2538 # Ticket [0eb70d77cb05bb22720]: Invalid pointer passsed to the authorizer 2539 # callback when updating a ROWID. 2540 # 2541 do_test auth-6.1 { 2542 execsql { 2543 CREATE TABLE t6(a,b,c,d,e,f,g,h); 2544 INSERT INTO t6 VALUES(1,2,3,4,5,6,7,8); 2545 } 2546 } {} 2547 set ::authargs [list] 2548 proc auth {args} { 2549 eval lappend ::authargs [lrange $args 0 4] 2550 return SQLITE_OK 2551 } 2552 do_test auth-6.2 { 2553 execsql {UPDATE t6 SET rowID=rowID+100} 2554 set ::authargs 2555 } [list SQLITE_READ t6 ROWID main {} \ 2556 SQLITE_UPDATE t6 ROWID main {} \ 2557 ] 2558 do_test auth-6.3 { 2559 execsql {SELECT rowid, * FROM t6} 2560 } {101 1 2 3 4 5 6 7 8} 2561 2562 #------------------------------------------------------------------------- 2563 # Test that view names are included as zArg4. 2564 # 2565 do_execsql_test auth-7.1 { 2566 CREATE TABLE t7(a, b, c); 2567 CREATE VIEW v7 AS SELECT * FROM t7; 2568 } {} 2569 set ::authargs [list] 2570 proc auth {args} { 2571 eval lappend ::authargs [lrange $args 0 4] 2572 return SQLITE_OK 2573 } 2574 2575 do_test auth-7.2 { 2576 execsql {SELECT a, c FROM v7} 2577 set ::authargs 2578 } [list \ 2579 SQLITE_SELECT {} {} {} {} \ 2580 SQLITE_READ t7 a main v7 \ 2581 SQLITE_READ t7 b main v7 \ 2582 SQLITE_READ t7 c main v7 \ 2583 SQLITE_READ v7 a main {} \ 2584 SQLITE_READ v7 c main {} \ 2585 SQLITE_SELECT {} {} {} v7 \ 2586 ] 2587 2588 set ::authargs [list] 2589 do_test auth-7.3 { 2590 execsql {SELECT a, c FROM t7} 2591 set ::authargs 2592 } [list \ 2593 SQLITE_SELECT {} {} {} {} \ 2594 SQLITE_READ t7 a main {} \ 2595 SQLITE_READ t7 c main {} \ 2596 ] 2597 2598 set ::authargs [list] 2599 do_test auth-7.4 { 2600 execsql {SELECT a, c FROM t7 AS v7} 2601 set ::authargs 2602 } [list \ 2603 SQLITE_SELECT {} {} {} {} \ 2604 SQLITE_READ t7 a main {} \ 2605 SQLITE_READ t7 c main {} \ 2606 ] 2607 2608 # If a table is referenced but no columns are read from the table, 2609 # that causes a single SQLITE_READ authorization with a NULL column 2610 # name. 2611 # 2612 # EVIDENCE-OF: R-31520-16302 When a table is referenced by a SELECT but 2613 # no column values are extracted from that table (for example in a query 2614 # like "SELECT count(*) FROM tab") then the SQLITE_READ authorizer 2615 # callback is invoked once for that table with a column name that is an 2616 # empty string. 2617 # 2618 set ::authargs [list] 2619 do_test auth-8.1 { 2620 execsql {SELECT count(*) FROM t7} 2621 set ::authargs 2622 } [list \ 2623 SQLITE_SELECT {} {} {} {} \ 2624 SQLITE_FUNCTION {} count {} {} \ 2625 SQLITE_READ t7 {} {} {} \ 2626 ] 2627 set ::authargs [list] 2628 2629 do_test auth-8.2 { 2630 execsql {SELECT t6.a FROM t6, t7} 2631 set ::authargs 2632 } [list \ 2633 SQLITE_SELECT {} {} {} {} \ 2634 SQLITE_READ t6 a main {} \ 2635 SQLITE_READ t7 {} {} {} \ 2636 ] 2637 2638 # Test also that if SQLITE_DENY is returned from an SQLITE_READ authorizer 2639 # invocation with no column name specified, compilation fails. 2640 # 2641 set ::authargs [list] 2642 proc auth {op args} { 2643 foreach {a b c d} $args break 2644 lappend ::authargs $op $a $b $c $d 2645 if {$op == "SQLITE_READ"} { return "SQLITE_DENY" } 2646 return "SQLITE_OK" 2647 } 2648 set ::authargs [list] 2649 do_catchsql_test auth-8.3 { 2650 SELECT count(*) FROM t7 2651 } {1 {not authorized}} 2652 do_test auth-8.4 { 2653 set ::authargs 2654 } [list \ 2655 SQLITE_SELECT {} {} {} {} \ 2656 SQLITE_FUNCTION {} count {} {} \ 2657 SQLITE_READ t7 {} {} {} \ 2658 ] 2659 2660 2661 rename proc {} 2662 rename proc_real proc 2663 finish_test