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 auth {code arg1 arg2 arg3 arg4} { 40 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 41 return SQLITE_DENY 42 } 43 return SQLITE_OK 44 } 45 db authorizer ::auth 46 catchsql {CREATE TABLE t1(a,b,c)} 47 } {1 {not authorized}} 48 do_test auth-1.1.2 { 49 db errorcode 50 } {23} 51 do_test auth-1.1.3 { 52 db authorizer 53 } {::auth} 54 do_test auth-1.1.4 { 55 # Ticket #896. 56 catchsql { 57 SELECT x; 58 } 59 } {1 {no such column: x}} 60 do_test auth-1.2 { 61 execsql {SELECT name FROM sqlite_master} 62 } {} 63 do_test auth-1.3.1 { 64 proc auth {code arg1 arg2 arg3 arg4} { 65 if {$code=="SQLITE_CREATE_TABLE"} { 66 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 67 return SQLITE_DENY 68 } 69 return SQLITE_OK 70 } 71 catchsql {CREATE TABLE t1(a,b,c)} 72 } {1 {not authorized}} 73 do_test auth-1.3.2 { 74 db errorcode 75 } {23} 76 do_test auth-1.3.3 { 77 set ::authargs 78 } {t1 {} main {}} 79 do_test auth-1.4 { 80 execsql {SELECT name FROM sqlite_master} 81 } {} 82 83 ifcapable tempdb { 84 do_test auth-1.5 { 85 proc auth {code arg1 arg2 arg3 arg4} { 86 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 87 return SQLITE_DENY 88 } 89 return SQLITE_OK 90 } 91 catchsql {CREATE TEMP TABLE t1(a,b,c)} 92 } {1 {not authorized}} 93 do_test auth-1.6 { 94 execsql {SELECT name FROM sqlite_temp_master} 95 } {} 96 do_test auth-1.7.1 { 97 proc auth {code arg1 arg2 arg3 arg4} { 98 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 99 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 100 return SQLITE_DENY 101 } 102 return SQLITE_OK 103 } 104 catchsql {CREATE TEMP TABLE t1(a,b,c)} 105 } {1 {not authorized}} 106 do_test auth-1.7.2 { 107 set ::authargs 108 } {t1 {} temp {}} 109 do_test auth-1.8 { 110 execsql {SELECT name FROM sqlite_temp_master} 111 } {} 112 } 113 114 do_test auth-1.9 { 115 proc auth {code arg1 arg2 arg3 arg4} { 116 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 117 return SQLITE_IGNORE 118 } 119 return SQLITE_OK 120 } 121 catchsql {CREATE TABLE t1(a,b,c)} 122 } {0 {}} 123 do_test auth-1.10 { 124 execsql {SELECT name FROM sqlite_master} 125 } {} 126 do_test auth-1.11 { 127 proc auth {code arg1 arg2 arg3 arg4} { 128 if {$code=="SQLITE_CREATE_TABLE"} { 129 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 130 return SQLITE_IGNORE 131 } 132 return SQLITE_OK 133 } 134 catchsql {CREATE TABLE t1(a,b,c)} 135 } {0 {}} 136 do_test auth-1.12 { 137 execsql {SELECT name FROM sqlite_master} 138 } {} 139 140 ifcapable tempdb { 141 do_test auth-1.13 { 142 proc auth {code arg1 arg2 arg3 arg4} { 143 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 144 return SQLITE_IGNORE 145 } 146 return SQLITE_OK 147 } 148 catchsql {CREATE TEMP TABLE t1(a,b,c)} 149 } {0 {}} 150 do_test auth-1.14 { 151 execsql {SELECT name FROM sqlite_temp_master} 152 } {} 153 do_test auth-1.15 { 154 proc auth {code arg1 arg2 arg3 arg4} { 155 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 156 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 157 return SQLITE_IGNORE 158 } 159 return SQLITE_OK 160 } 161 catchsql {CREATE TEMP TABLE t1(a,b,c)} 162 } {0 {}} 163 do_test auth-1.16 { 164 execsql {SELECT name FROM sqlite_temp_master} 165 } {} 166 167 do_test auth-1.17 { 168 proc auth {code arg1 arg2 arg3 arg4} { 169 if {$code=="SQLITE_CREATE_TABLE"} { 170 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 171 return SQLITE_DENY 172 } 173 return SQLITE_OK 174 } 175 catchsql {CREATE TEMP TABLE t1(a,b,c)} 176 } {0 {}} 177 do_test auth-1.18 { 178 execsql {SELECT name FROM sqlite_temp_master} 179 } {t1} 180 } 181 182 do_test auth-1.19.1 { 183 set ::authargs {} 184 proc auth {code arg1 arg2 arg3 arg4} { 185 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 186 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 187 return SQLITE_DENY 188 } 189 return SQLITE_OK 190 } 191 catchsql {CREATE TABLE t2(a,b,c)} 192 } {0 {}} 193 do_test auth-1.19.2 { 194 set ::authargs 195 } {} 196 do_test auth-1.20 { 197 execsql {SELECT name FROM sqlite_master} 198 } {t2} 199 200 do_test auth-1.21.1 { 201 proc auth {code arg1 arg2 arg3 arg4} { 202 if {$code=="SQLITE_DROP_TABLE"} { 203 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 204 return SQLITE_DENY 205 } 206 return SQLITE_OK 207 } 208 catchsql {DROP TABLE t2} 209 } {1 {not authorized}} 210 do_test auth-1.21.2 { 211 set ::authargs 212 } {t2 {} main {}} 213 do_test auth-1.22 { 214 execsql {SELECT name FROM sqlite_master} 215 } {t2} 216 do_test auth-1.23.1 { 217 proc auth {code arg1 arg2 arg3 arg4} { 218 if {$code=="SQLITE_DROP_TABLE"} { 219 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 220 return SQLITE_IGNORE 221 } 222 return SQLITE_OK 223 } 224 catchsql {DROP TABLE t2} 225 } {0 {}} 226 do_test auth-1.23.2 { 227 set ::authargs 228 } {t2 {} main {}} 229 do_test auth-1.24 { 230 execsql {SELECT name FROM sqlite_master} 231 } {t2} 232 233 ifcapable tempdb { 234 do_test auth-1.25 { 235 proc auth {code arg1 arg2 arg3 arg4} { 236 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 237 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 238 return SQLITE_DENY 239 } 240 return SQLITE_OK 241 } 242 catchsql {DROP TABLE t1} 243 } {1 {not authorized}} 244 do_test auth-1.26 { 245 execsql {SELECT name FROM sqlite_temp_master} 246 } {t1} 247 do_test auth-1.27 { 248 proc auth {code arg1 arg2 arg3 arg4} { 249 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 250 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 251 return SQLITE_IGNORE 252 } 253 return SQLITE_OK 254 } 255 catchsql {DROP TABLE t1} 256 } {0 {}} 257 do_test auth-1.28 { 258 execsql {SELECT name FROM sqlite_temp_master} 259 } {t1} 260 } 261 262 do_test auth-1.29 { 263 proc auth {code arg1 arg2 arg3 arg4} { 264 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 265 return SQLITE_DENY 266 } 267 return SQLITE_OK 268 } 269 catchsql {INSERT INTO t2 VALUES(1,2,3)} 270 } {1 {not authorized}} 271 do_test auth-1.30 { 272 execsql {SELECT * FROM t2} 273 } {} 274 do_test auth-1.31 { 275 proc auth {code arg1 arg2 arg3 arg4} { 276 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 277 return SQLITE_IGNORE 278 } 279 return SQLITE_OK 280 } 281 catchsql {INSERT INTO t2 VALUES(1,2,3)} 282 } {0 {}} 283 do_test auth-1.32 { 284 execsql {SELECT * FROM t2} 285 } {} 286 do_test auth-1.33 { 287 proc auth {code arg1 arg2 arg3 arg4} { 288 if {$code=="SQLITE_INSERT" && $arg1=="t1"} { 289 return SQLITE_IGNORE 290 } 291 return SQLITE_OK 292 } 293 catchsql {INSERT INTO t2 VALUES(1,2,3)} 294 } {0 {}} 295 do_test auth-1.34 { 296 execsql {SELECT * FROM t2} 297 } {1 2 3} 298 299 do_test auth-1.35.1 { 300 proc auth {code arg1 arg2 arg3 arg4} { 301 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 302 return SQLITE_DENY 303 } 304 return SQLITE_OK 305 } 306 catchsql {SELECT * FROM t2} 307 } {1 {access to t2.b is prohibited}} 308 ifcapable attach { 309 do_test auth-1.35.2 { 310 execsql {ATTACH DATABASE 'test.db' AS two} 311 catchsql {SELECT * FROM two.t2} 312 } {1 {access to two.t2.b is prohibited}} 313 execsql {DETACH DATABASE two} 314 } 315 do_test auth-1.36 { 316 proc auth {code arg1 arg2 arg3 arg4} { 317 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 318 return SQLITE_IGNORE 319 } 320 return SQLITE_OK 321 } 322 catchsql {SELECT * FROM t2} 323 } {0 {1 {} 3}} 324 do_test auth-1.37 { 325 proc auth {code arg1 arg2 arg3 arg4} { 326 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 327 return SQLITE_IGNORE 328 } 329 return SQLITE_OK 330 } 331 catchsql {SELECT * FROM t2 WHERE b=2} 332 } {0 {}} 333 do_test auth-1.38 { 334 proc auth {code arg1 arg2 arg3 arg4} { 335 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} { 336 return SQLITE_IGNORE 337 } 338 return SQLITE_OK 339 } 340 catchsql {SELECT * FROM t2 WHERE b=2} 341 } {0 {{} 2 3}} 342 do_test auth-1.39 { 343 proc auth {code arg1 arg2 arg3 arg4} { 344 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 345 return SQLITE_IGNORE 346 } 347 return SQLITE_OK 348 } 349 catchsql {SELECT * FROM t2 WHERE b IS NULL} 350 } {0 {1 {} 3}} 351 do_test auth-1.40 { 352 proc auth {code arg1 arg2 arg3 arg4} { 353 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 354 return SQLITE_DENY 355 } 356 return SQLITE_OK 357 } 358 catchsql {SELECT a,c FROM t2 WHERE b IS NULL} 359 } {1 {access to t2.b is prohibited}} 360 361 do_test auth-1.41 { 362 proc auth {code arg1 arg2 arg3 arg4} { 363 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 364 return SQLITE_DENY 365 } 366 return SQLITE_OK 367 } 368 catchsql {UPDATE t2 SET a=11} 369 } {0 {}} 370 do_test auth-1.42 { 371 execsql {SELECT * FROM t2} 372 } {11 2 3} 373 do_test auth-1.43 { 374 proc auth {code arg1 arg2 arg3 arg4} { 375 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 376 return SQLITE_DENY 377 } 378 return SQLITE_OK 379 } 380 catchsql {UPDATE t2 SET b=22, c=33} 381 } {1 {not authorized}} 382 do_test auth-1.44 { 383 execsql {SELECT * FROM t2} 384 } {11 2 3} 385 do_test auth-1.45 { 386 proc auth {code arg1 arg2 arg3 arg4} { 387 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 388 return SQLITE_IGNORE 389 } 390 return SQLITE_OK 391 } 392 catchsql {UPDATE t2 SET b=22, c=33} 393 } {0 {}} 394 do_test auth-1.46 { 395 execsql {SELECT * FROM t2} 396 } {11 2 33} 397 398 do_test auth-1.47 { 399 proc auth {code arg1 arg2 arg3 arg4} { 400 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 401 return SQLITE_DENY 402 } 403 return SQLITE_OK 404 } 405 catchsql {DELETE FROM t2 WHERE a=11} 406 } {1 {not authorized}} 407 do_test auth-1.48 { 408 execsql {SELECT * FROM t2} 409 } {11 2 33} 410 do_test auth-1.49 { 411 proc auth {code arg1 arg2 arg3 arg4} { 412 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 413 return SQLITE_IGNORE 414 } 415 return SQLITE_OK 416 } 417 catchsql {DELETE FROM t2 WHERE a=11} 418 } {0 {}} 419 do_test auth-1.50 { 420 execsql {SELECT * FROM t2} 421 } {} 422 do_test auth-1.50.2 { 423 execsql {INSERT INTO t2 VALUES(11, 2, 33)} 424 } {} 425 426 do_test auth-1.51 { 427 proc auth {code arg1 arg2 arg3 arg4} { 428 if {$code=="SQLITE_SELECT"} { 429 return SQLITE_DENY 430 } 431 return SQLITE_OK 432 } 433 catchsql {SELECT * FROM t2} 434 } {1 {not authorized}} 435 do_test auth-1.52 { 436 proc auth {code arg1 arg2 arg3 arg4} { 437 if {$code=="SQLITE_SELECT"} { 438 return SQLITE_IGNORE 439 } 440 return SQLITE_OK 441 } 442 catchsql {SELECT * FROM t2} 443 } {0 {}} 444 do_test auth-1.53 { 445 proc auth {code arg1 arg2 arg3 arg4} { 446 if {$code=="SQLITE_SELECT"} { 447 return SQLITE_OK 448 } 449 return SQLITE_OK 450 } 451 catchsql {SELECT * FROM t2} 452 } {0 {11 2 33}} 453 454 # Update for version 3: There used to be a handful of test here that 455 # tested the authorisation callback with the COPY command. The following 456 # test makes the same database modifications as they used to. 457 do_test auth-1.54 { 458 execsql {INSERT INTO t2 VALUES(7, 8, 9);} 459 } {} 460 do_test auth-1.55 { 461 execsql {SELECT * FROM t2} 462 } {11 2 33 7 8 9} 463 464 do_test auth-1.63 { 465 proc auth {code arg1 arg2 arg3 arg4} { 466 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 467 return SQLITE_DENY 468 } 469 return SQLITE_OK 470 } 471 catchsql {DROP TABLE t2} 472 } {1 {not authorized}} 473 do_test auth-1.64 { 474 execsql {SELECT name FROM sqlite_master} 475 } {t2} 476 do_test auth-1.65 { 477 proc auth {code arg1 arg2 arg3 arg4} { 478 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 479 return SQLITE_DENY 480 } 481 return SQLITE_OK 482 } 483 catchsql {DROP TABLE t2} 484 } {1 {not authorized}} 485 do_test auth-1.66 { 486 execsql {SELECT name FROM sqlite_master} 487 } {t2} 488 489 ifcapable tempdb { 490 do_test auth-1.67 { 491 proc auth {code arg1 arg2 arg3 arg4} { 492 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 493 return SQLITE_DENY 494 } 495 return SQLITE_OK 496 } 497 catchsql {DROP TABLE t1} 498 } {1 {not authorized}} 499 do_test auth-1.68 { 500 execsql {SELECT name FROM sqlite_temp_master} 501 } {t1} 502 do_test auth-1.69 { 503 proc auth {code arg1 arg2 arg3 arg4} { 504 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 505 return SQLITE_DENY 506 } 507 return SQLITE_OK 508 } 509 catchsql {DROP TABLE t1} 510 } {1 {not authorized}} 511 do_test auth-1.70 { 512 execsql {SELECT name FROM sqlite_temp_master} 513 } {t1} 514 } 515 516 do_test auth-1.71 { 517 proc auth {code arg1 arg2 arg3 arg4} { 518 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 519 return SQLITE_IGNORE 520 } 521 return SQLITE_OK 522 } 523 catchsql {DROP TABLE t2} 524 } {0 {}} 525 do_test auth-1.72 { 526 execsql {SELECT name FROM sqlite_master} 527 } {t2} 528 do_test auth-1.73 { 529 proc auth {code arg1 arg2 arg3 arg4} { 530 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 531 return SQLITE_IGNORE 532 } 533 return SQLITE_OK 534 } 535 catchsql {DROP TABLE t2} 536 } {0 {}} 537 do_test auth-1.74 { 538 execsql {SELECT name FROM sqlite_master} 539 } {t2} 540 541 ifcapable tempdb { 542 do_test auth-1.75 { 543 proc auth {code arg1 arg2 arg3 arg4} { 544 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 545 return SQLITE_IGNORE 546 } 547 return SQLITE_OK 548 } 549 catchsql {DROP TABLE t1} 550 } {0 {}} 551 do_test auth-1.76 { 552 execsql {SELECT name FROM sqlite_temp_master} 553 } {t1} 554 do_test auth-1.77 { 555 proc auth {code arg1 arg2 arg3 arg4} { 556 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 557 return SQLITE_IGNORE 558 } 559 return SQLITE_OK 560 } 561 catchsql {DROP TABLE t1} 562 } {0 {}} 563 do_test auth-1.78 { 564 execsql {SELECT name FROM sqlite_temp_master} 565 } {t1} 566 } 567 568 # Test cases auth-1.79 to auth-1.124 test creating and dropping views. 569 # Omit these if the library was compiled with views omitted. 570 ifcapable view { 571 do_test auth-1.79 { 572 proc auth {code arg1 arg2 arg3 arg4} { 573 if {$code=="SQLITE_CREATE_VIEW"} { 574 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 575 return SQLITE_DENY 576 } 577 return SQLITE_OK 578 } 579 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 580 } {1 {not authorized}} 581 do_test auth-1.80 { 582 set ::authargs 583 } {v1 {} main {}} 584 do_test auth-1.81 { 585 execsql {SELECT name FROM sqlite_master} 586 } {t2} 587 do_test auth-1.82 { 588 proc auth {code arg1 arg2 arg3 arg4} { 589 if {$code=="SQLITE_CREATE_VIEW"} { 590 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 591 return SQLITE_IGNORE 592 } 593 return SQLITE_OK 594 } 595 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 596 } {0 {}} 597 do_test auth-1.83 { 598 set ::authargs 599 } {v1 {} main {}} 600 do_test auth-1.84 { 601 execsql {SELECT name FROM sqlite_master} 602 } {t2} 603 604 ifcapable tempdb { 605 do_test auth-1.85 { 606 proc auth {code arg1 arg2 arg3 arg4} { 607 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 608 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 609 return SQLITE_DENY 610 } 611 return SQLITE_OK 612 } 613 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 614 } {1 {not authorized}} 615 do_test auth-1.86 { 616 set ::authargs 617 } {v1 {} temp {}} 618 do_test auth-1.87 { 619 execsql {SELECT name FROM sqlite_temp_master} 620 } {t1} 621 do_test auth-1.88 { 622 proc auth {code arg1 arg2 arg3 arg4} { 623 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 624 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 625 return SQLITE_IGNORE 626 } 627 return SQLITE_OK 628 } 629 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 630 } {0 {}} 631 do_test auth-1.89 { 632 set ::authargs 633 } {v1 {} temp {}} 634 do_test auth-1.90 { 635 execsql {SELECT name FROM sqlite_temp_master} 636 } {t1} 637 } 638 639 do_test auth-1.91 { 640 proc auth {code arg1 arg2 arg3 arg4} { 641 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 642 return SQLITE_DENY 643 } 644 return SQLITE_OK 645 } 646 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 647 } {1 {not authorized}} 648 do_test auth-1.92 { 649 execsql {SELECT name FROM sqlite_master} 650 } {t2} 651 do_test auth-1.93 { 652 proc auth {code arg1 arg2 arg3 arg4} { 653 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 654 return SQLITE_IGNORE 655 } 656 return SQLITE_OK 657 } 658 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 659 } {0 {}} 660 do_test auth-1.94 { 661 execsql {SELECT name FROM sqlite_master} 662 } {t2} 663 664 ifcapable tempdb { 665 do_test auth-1.95 { 666 proc auth {code arg1 arg2 arg3 arg4} { 667 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 668 return SQLITE_DENY 669 } 670 return SQLITE_OK 671 } 672 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 673 } {1 {not authorized}} 674 do_test auth-1.96 { 675 execsql {SELECT name FROM sqlite_temp_master} 676 } {t1} 677 do_test auth-1.97 { 678 proc auth {code arg1 arg2 arg3 arg4} { 679 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 680 return SQLITE_IGNORE 681 } 682 return SQLITE_OK 683 } 684 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 685 } {0 {}} 686 do_test auth-1.98 { 687 execsql {SELECT name FROM sqlite_temp_master} 688 } {t1} 689 } 690 691 do_test auth-1.99 { 692 proc auth {code arg1 arg2 arg3 arg4} { 693 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 694 return SQLITE_DENY 695 } 696 return SQLITE_OK 697 } 698 catchsql { 699 CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2; 700 DROP VIEW v2 701 } 702 } {1 {not authorized}} 703 do_test auth-1.100 { 704 execsql {SELECT name FROM sqlite_master} 705 } {t2 v2} 706 do_test auth-1.101 { 707 proc auth {code arg1 arg2 arg3 arg4} { 708 if {$code=="SQLITE_DROP_VIEW"} { 709 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 710 return SQLITE_DENY 711 } 712 return SQLITE_OK 713 } 714 catchsql {DROP VIEW v2} 715 } {1 {not authorized}} 716 do_test auth-1.102 { 717 set ::authargs 718 } {v2 {} main {}} 719 do_test auth-1.103 { 720 execsql {SELECT name FROM sqlite_master} 721 } {t2 v2} 722 do_test auth-1.104 { 723 proc auth {code arg1 arg2 arg3 arg4} { 724 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 725 return SQLITE_IGNORE 726 } 727 return SQLITE_OK 728 } 729 catchsql {DROP VIEW v2} 730 } {0 {}} 731 do_test auth-1.105 { 732 execsql {SELECT name FROM sqlite_master} 733 } {t2 v2} 734 do_test auth-1.106 { 735 proc auth {code arg1 arg2 arg3 arg4} { 736 if {$code=="SQLITE_DROP_VIEW"} { 737 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 738 return SQLITE_IGNORE 739 } 740 return SQLITE_OK 741 } 742 catchsql {DROP VIEW v2} 743 } {0 {}} 744 do_test auth-1.107 { 745 set ::authargs 746 } {v2 {} main {}} 747 do_test auth-1.108 { 748 execsql {SELECT name FROM sqlite_master} 749 } {t2 v2} 750 do_test auth-1.109 { 751 proc auth {code arg1 arg2 arg3 arg4} { 752 if {$code=="SQLITE_DROP_VIEW"} { 753 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 754 return SQLITE_OK 755 } 756 return SQLITE_OK 757 } 758 catchsql {DROP VIEW v2} 759 } {0 {}} 760 do_test auth-1.110 { 761 set ::authargs 762 } {v2 {} main {}} 763 do_test auth-1.111 { 764 execsql {SELECT name FROM sqlite_master} 765 } {t2} 766 767 768 ifcapable tempdb { 769 do_test auth-1.112 { 770 proc auth {code arg1 arg2 arg3 arg4} { 771 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 772 return SQLITE_DENY 773 } 774 return SQLITE_OK 775 } 776 catchsql { 777 CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1; 778 DROP VIEW v1 779 } 780 } {1 {not authorized}} 781 do_test auth-1.113 { 782 execsql {SELECT name FROM sqlite_temp_master} 783 } {t1 v1} 784 do_test auth-1.114 { 785 proc auth {code arg1 arg2 arg3 arg4} { 786 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 787 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 788 return SQLITE_DENY 789 } 790 return SQLITE_OK 791 } 792 catchsql {DROP VIEW v1} 793 } {1 {not authorized}} 794 do_test auth-1.115 { 795 set ::authargs 796 } {v1 {} temp {}} 797 do_test auth-1.116 { 798 execsql {SELECT name FROM sqlite_temp_master} 799 } {t1 v1} 800 do_test auth-1.117 { 801 proc auth {code arg1 arg2 arg3 arg4} { 802 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 803 return SQLITE_IGNORE 804 } 805 return SQLITE_OK 806 } 807 catchsql {DROP VIEW v1} 808 } {0 {}} 809 do_test auth-1.118 { 810 execsql {SELECT name FROM sqlite_temp_master} 811 } {t1 v1} 812 do_test auth-1.119 { 813 proc auth {code arg1 arg2 arg3 arg4} { 814 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 815 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 816 return SQLITE_IGNORE 817 } 818 return SQLITE_OK 819 } 820 catchsql {DROP VIEW v1} 821 } {0 {}} 822 do_test auth-1.120 { 823 set ::authargs 824 } {v1 {} temp {}} 825 do_test auth-1.121 { 826 execsql {SELECT name FROM sqlite_temp_master} 827 } {t1 v1} 828 do_test auth-1.122 { 829 proc auth {code arg1 arg2 arg3 arg4} { 830 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 831 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 832 return SQLITE_OK 833 } 834 return SQLITE_OK 835 } 836 catchsql {DROP VIEW v1} 837 } {0 {}} 838 do_test auth-1.123 { 839 set ::authargs 840 } {v1 {} temp {}} 841 do_test auth-1.124 { 842 execsql {SELECT name FROM sqlite_temp_master} 843 } {t1} 844 } 845 } ;# ifcapable view 846 847 # Test cases auth-1.125 to auth-1.176 test creating and dropping triggers. 848 # Omit these if the library was compiled with triggers omitted. 849 # 850 ifcapable trigger&&tempdb { 851 do_test auth-1.125 { 852 proc auth {code arg1 arg2 arg3 arg4} { 853 if {$code=="SQLITE_CREATE_TRIGGER"} { 854 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 855 return SQLITE_DENY 856 } 857 return SQLITE_OK 858 } 859 catchsql { 860 CREATE TRIGGER r2 DELETE on t2 BEGIN 861 SELECT NULL; 862 END; 863 } 864 } {1 {not authorized}} 865 do_test auth-1.126 { 866 set ::authargs 867 } {r2 t2 main {}} 868 do_test auth-1.127 { 869 execsql {SELECT name FROM sqlite_master} 870 } {t2} 871 do_test auth-1.128 { 872 proc auth {code arg1 arg2 arg3 arg4} { 873 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 874 return SQLITE_DENY 875 } 876 return SQLITE_OK 877 } 878 catchsql { 879 CREATE TRIGGER r2 DELETE on t2 BEGIN 880 SELECT NULL; 881 END; 882 } 883 } {1 {not authorized}} 884 do_test auth-1.129 { 885 execsql {SELECT name FROM sqlite_master} 886 } {t2} 887 do_test auth-1.130 { 888 proc auth {code arg1 arg2 arg3 arg4} { 889 if {$code=="SQLITE_CREATE_TRIGGER"} { 890 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 891 return SQLITE_IGNORE 892 } 893 return SQLITE_OK 894 } 895 catchsql { 896 CREATE TRIGGER r2 DELETE on t2 BEGIN 897 SELECT NULL; 898 END; 899 } 900 } {0 {}} 901 do_test auth-1.131 { 902 set ::authargs 903 } {r2 t2 main {}} 904 do_test auth-1.132 { 905 execsql {SELECT name FROM sqlite_master} 906 } {t2} 907 do_test auth-1.133 { 908 proc auth {code arg1 arg2 arg3 arg4} { 909 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 910 return SQLITE_IGNORE 911 } 912 return SQLITE_OK 913 } 914 catchsql { 915 CREATE TRIGGER r2 DELETE on t2 BEGIN 916 SELECT NULL; 917 END; 918 } 919 } {0 {}} 920 do_test auth-1.134 { 921 execsql {SELECT name FROM sqlite_master} 922 } {t2} 923 do_test auth-1.135 { 924 proc auth {code arg1 arg2 arg3 arg4} { 925 if {$code=="SQLITE_CREATE_TRIGGER"} { 926 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 927 return SQLITE_OK 928 } 929 return SQLITE_OK 930 } 931 catchsql { 932 CREATE TABLE tx(id); 933 CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN 934 INSERT INTO tx VALUES(NEW.rowid); 935 END; 936 } 937 } {0 {}} 938 do_test auth-1.136.1 { 939 set ::authargs 940 } {r2 t2 main {}} 941 do_test auth-1.136.2 { 942 execsql { 943 SELECT name FROM sqlite_master WHERE type='trigger' 944 } 945 } {r2} 946 do_test auth-1.136.3 { 947 proc auth {code arg1 arg2 arg3 arg4} { 948 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 949 return SQLITE_OK 950 } 951 set ::authargs {} 952 execsql { 953 INSERT INTO t2 VALUES(1,2,3); 954 } 955 set ::authargs 956 } {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2} 957 do_test auth-1.136.4 { 958 execsql { 959 SELECT * FROM tx; 960 } 961 } {3} 962 do_test auth-1.137 { 963 execsql {SELECT name FROM sqlite_master} 964 } {t2 tx r2} 965 do_test auth-1.138 { 966 proc auth {code arg1 arg2 arg3 arg4} { 967 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 968 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 969 return SQLITE_DENY 970 } 971 return SQLITE_OK 972 } 973 catchsql { 974 CREATE TRIGGER r1 DELETE on t1 BEGIN 975 SELECT NULL; 976 END; 977 } 978 } {1 {not authorized}} 979 do_test auth-1.139 { 980 set ::authargs 981 } {r1 t1 temp {}} 982 do_test auth-1.140 { 983 execsql {SELECT name FROM sqlite_temp_master} 984 } {t1} 985 do_test auth-1.141 { 986 proc auth {code arg1 arg2 arg3 arg4} { 987 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 988 return SQLITE_DENY 989 } 990 return SQLITE_OK 991 } 992 catchsql { 993 CREATE TRIGGER r1 DELETE on t1 BEGIN 994 SELECT NULL; 995 END; 996 } 997 } {1 {not authorized}} 998 do_test auth-1.142 { 999 execsql {SELECT name FROM sqlite_temp_master} 1000 } {t1} 1001 do_test auth-1.143 { 1002 proc auth {code arg1 arg2 arg3 arg4} { 1003 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 1004 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1005 return SQLITE_IGNORE 1006 } 1007 return SQLITE_OK 1008 } 1009 catchsql { 1010 CREATE TRIGGER r1 DELETE on t1 BEGIN 1011 SELECT NULL; 1012 END; 1013 } 1014 } {0 {}} 1015 do_test auth-1.144 { 1016 set ::authargs 1017 } {r1 t1 temp {}} 1018 do_test auth-1.145 { 1019 execsql {SELECT name FROM sqlite_temp_master} 1020 } {t1} 1021 do_test auth-1.146 { 1022 proc auth {code arg1 arg2 arg3 arg4} { 1023 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1024 return SQLITE_IGNORE 1025 } 1026 return SQLITE_OK 1027 } 1028 catchsql { 1029 CREATE TRIGGER r1 DELETE on t1 BEGIN 1030 SELECT NULL; 1031 END; 1032 } 1033 } {0 {}} 1034 do_test auth-1.147 { 1035 execsql {SELECT name FROM sqlite_temp_master} 1036 } {t1} 1037 do_test auth-1.148 { 1038 proc auth {code arg1 arg2 arg3 arg4} { 1039 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 1040 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1041 return SQLITE_OK 1042 } 1043 return SQLITE_OK 1044 } 1045 catchsql { 1046 CREATE TRIGGER r1 DELETE on t1 BEGIN 1047 SELECT NULL; 1048 END; 1049 } 1050 } {0 {}} 1051 do_test auth-1.149 { 1052 set ::authargs 1053 } {r1 t1 temp {}} 1054 do_test auth-1.150 { 1055 execsql {SELECT name FROM sqlite_temp_master} 1056 } {t1 r1} 1057 1058 do_test auth-1.151 { 1059 proc auth {code arg1 arg2 arg3 arg4} { 1060 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1061 return SQLITE_DENY 1062 } 1063 return SQLITE_OK 1064 } 1065 catchsql {DROP TRIGGER r2} 1066 } {1 {not authorized}} 1067 do_test auth-1.152 { 1068 execsql {SELECT name FROM sqlite_master} 1069 } {t2 tx r2} 1070 do_test auth-1.153 { 1071 proc auth {code arg1 arg2 arg3 arg4} { 1072 if {$code=="SQLITE_DROP_TRIGGER"} { 1073 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1074 return SQLITE_DENY 1075 } 1076 return SQLITE_OK 1077 } 1078 catchsql {DROP TRIGGER r2} 1079 } {1 {not authorized}} 1080 do_test auth-1.154 { 1081 set ::authargs 1082 } {r2 t2 main {}} 1083 do_test auth-1.155 { 1084 execsql {SELECT name FROM sqlite_master} 1085 } {t2 tx r2} 1086 do_test auth-1.156 { 1087 proc auth {code arg1 arg2 arg3 arg4} { 1088 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1089 return SQLITE_IGNORE 1090 } 1091 return SQLITE_OK 1092 } 1093 catchsql {DROP TRIGGER r2} 1094 } {0 {}} 1095 do_test auth-1.157 { 1096 execsql {SELECT name FROM sqlite_master} 1097 } {t2 tx r2} 1098 do_test auth-1.158 { 1099 proc auth {code arg1 arg2 arg3 arg4} { 1100 if {$code=="SQLITE_DROP_TRIGGER"} { 1101 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1102 return SQLITE_IGNORE 1103 } 1104 return SQLITE_OK 1105 } 1106 catchsql {DROP TRIGGER r2} 1107 } {0 {}} 1108 do_test auth-1.159 { 1109 set ::authargs 1110 } {r2 t2 main {}} 1111 do_test auth-1.160 { 1112 execsql {SELECT name FROM sqlite_master} 1113 } {t2 tx r2} 1114 do_test auth-1.161 { 1115 proc auth {code arg1 arg2 arg3 arg4} { 1116 if {$code=="SQLITE_DROP_TRIGGER"} { 1117 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1118 return SQLITE_OK 1119 } 1120 return SQLITE_OK 1121 } 1122 catchsql {DROP TRIGGER r2} 1123 } {0 {}} 1124 do_test auth-1.162 { 1125 set ::authargs 1126 } {r2 t2 main {}} 1127 do_test auth-1.163 { 1128 execsql { 1129 DROP TABLE tx; 1130 DELETE FROM t2 WHERE a=1 AND b=2 AND c=3; 1131 SELECT name FROM sqlite_master; 1132 } 1133 } {t2} 1134 1135 do_test auth-1.164 { 1136 proc auth {code arg1 arg2 arg3 arg4} { 1137 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1138 return SQLITE_DENY 1139 } 1140 return SQLITE_OK 1141 } 1142 catchsql {DROP TRIGGER r1} 1143 } {1 {not authorized}} 1144 do_test auth-1.165 { 1145 execsql {SELECT name FROM sqlite_temp_master} 1146 } {t1 r1} 1147 do_test auth-1.166 { 1148 proc auth {code arg1 arg2 arg3 arg4} { 1149 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1150 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1151 return SQLITE_DENY 1152 } 1153 return SQLITE_OK 1154 } 1155 catchsql {DROP TRIGGER r1} 1156 } {1 {not authorized}} 1157 do_test auth-1.167 { 1158 set ::authargs 1159 } {r1 t1 temp {}} 1160 do_test auth-1.168 { 1161 execsql {SELECT name FROM sqlite_temp_master} 1162 } {t1 r1} 1163 do_test auth-1.169 { 1164 proc auth {code arg1 arg2 arg3 arg4} { 1165 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1166 return SQLITE_IGNORE 1167 } 1168 return SQLITE_OK 1169 } 1170 catchsql {DROP TRIGGER r1} 1171 } {0 {}} 1172 do_test auth-1.170 { 1173 execsql {SELECT name FROM sqlite_temp_master} 1174 } {t1 r1} 1175 do_test auth-1.171 { 1176 proc auth {code arg1 arg2 arg3 arg4} { 1177 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1178 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1179 return SQLITE_IGNORE 1180 } 1181 return SQLITE_OK 1182 } 1183 catchsql {DROP TRIGGER r1} 1184 } {0 {}} 1185 do_test auth-1.172 { 1186 set ::authargs 1187 } {r1 t1 temp {}} 1188 do_test auth-1.173 { 1189 execsql {SELECT name FROM sqlite_temp_master} 1190 } {t1 r1} 1191 do_test auth-1.174 { 1192 proc auth {code arg1 arg2 arg3 arg4} { 1193 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1194 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1195 return SQLITE_OK 1196 } 1197 return SQLITE_OK 1198 } 1199 catchsql {DROP TRIGGER r1} 1200 } {0 {}} 1201 do_test auth-1.175 { 1202 set ::authargs 1203 } {r1 t1 temp {}} 1204 do_test auth-1.176 { 1205 execsql {SELECT name FROM sqlite_temp_master} 1206 } {t1} 1207 } ;# ifcapable trigger 1208 1209 do_test auth-1.177 { 1210 proc auth {code arg1 arg2 arg3 arg4} { 1211 if {$code=="SQLITE_CREATE_INDEX"} { 1212 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1213 return SQLITE_DENY 1214 } 1215 return SQLITE_OK 1216 } 1217 catchsql {CREATE INDEX i2 ON t2(a)} 1218 } {1 {not authorized}} 1219 do_test auth-1.178 { 1220 set ::authargs 1221 } {i2 t2 main {}} 1222 do_test auth-1.179 { 1223 execsql {SELECT name FROM sqlite_master} 1224 } {t2} 1225 do_test auth-1.180 { 1226 proc auth {code arg1 arg2 arg3 arg4} { 1227 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 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.181 { 1235 execsql {SELECT name FROM sqlite_master} 1236 } {t2} 1237 do_test auth-1.182 { 1238 proc auth {code arg1 arg2 arg3 arg4} { 1239 if {$code=="SQLITE_CREATE_INDEX"} { 1240 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1241 return SQLITE_IGNORE 1242 } 1243 return SQLITE_OK 1244 } 1245 catchsql {CREATE INDEX i2 ON t2(b)} 1246 } {0 {}} 1247 do_test auth-1.183 { 1248 set ::authargs 1249 } {i2 t2 main {}} 1250 do_test auth-1.184 { 1251 execsql {SELECT name FROM sqlite_master} 1252 } {t2} 1253 do_test auth-1.185 { 1254 proc auth {code arg1 arg2 arg3 arg4} { 1255 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 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.186 { 1263 execsql {SELECT name FROM sqlite_master} 1264 } {t2} 1265 do_test auth-1.187 { 1266 proc auth {code arg1 arg2 arg3 arg4} { 1267 if {$code=="SQLITE_CREATE_INDEX"} { 1268 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1269 return SQLITE_OK 1270 } 1271 return SQLITE_OK 1272 } 1273 catchsql {CREATE INDEX i2 ON t2(a)} 1274 } {0 {}} 1275 do_test auth-1.188 { 1276 set ::authargs 1277 } {i2 t2 main {}} 1278 do_test auth-1.189 { 1279 execsql {SELECT name FROM sqlite_master} 1280 } {t2 i2} 1281 1282 ifcapable tempdb { 1283 do_test auth-1.190 { 1284 proc auth {code arg1 arg2 arg3 arg4} { 1285 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1286 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1287 return SQLITE_DENY 1288 } 1289 return SQLITE_OK 1290 } 1291 catchsql {CREATE INDEX i1 ON t1(a)} 1292 } {1 {not authorized}} 1293 do_test auth-1.191 { 1294 set ::authargs 1295 } {i1 t1 temp {}} 1296 do_test auth-1.192 { 1297 execsql {SELECT name FROM sqlite_temp_master} 1298 } {t1} 1299 do_test auth-1.193 { 1300 proc auth {code arg1 arg2 arg3 arg4} { 1301 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1302 return SQLITE_DENY 1303 } 1304 return SQLITE_OK 1305 } 1306 catchsql {CREATE INDEX i1 ON t1(b)} 1307 } {1 {not authorized}} 1308 do_test auth-1.194 { 1309 execsql {SELECT name FROM sqlite_temp_master} 1310 } {t1} 1311 do_test auth-1.195 { 1312 proc auth {code arg1 arg2 arg3 arg4} { 1313 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1314 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1315 return SQLITE_IGNORE 1316 } 1317 return SQLITE_OK 1318 } 1319 catchsql {CREATE INDEX i1 ON t1(b)} 1320 } {0 {}} 1321 do_test auth-1.196 { 1322 set ::authargs 1323 } {i1 t1 temp {}} 1324 do_test auth-1.197 { 1325 execsql {SELECT name FROM sqlite_temp_master} 1326 } {t1} 1327 do_test auth-1.198 { 1328 proc auth {code arg1 arg2 arg3 arg4} { 1329 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1330 return SQLITE_IGNORE 1331 } 1332 return SQLITE_OK 1333 } 1334 catchsql {CREATE INDEX i1 ON t1(c)} 1335 } {0 {}} 1336 do_test auth-1.199 { 1337 execsql {SELECT name FROM sqlite_temp_master} 1338 } {t1} 1339 do_test auth-1.200 { 1340 proc auth {code arg1 arg2 arg3 arg4} { 1341 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1342 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1343 return SQLITE_OK 1344 } 1345 return SQLITE_OK 1346 } 1347 catchsql {CREATE INDEX i1 ON t1(a)} 1348 } {0 {}} 1349 do_test auth-1.201 { 1350 set ::authargs 1351 } {i1 t1 temp {}} 1352 do_test auth-1.202 { 1353 execsql {SELECT name FROM sqlite_temp_master} 1354 } {t1 i1} 1355 } 1356 1357 do_test auth-1.203 { 1358 proc auth {code arg1 arg2 arg3 arg4} { 1359 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1360 return SQLITE_DENY 1361 } 1362 return SQLITE_OK 1363 } 1364 catchsql {DROP INDEX i2} 1365 } {1 {not authorized}} 1366 do_test auth-1.204 { 1367 execsql {SELECT name FROM sqlite_master} 1368 } {t2 i2} 1369 do_test auth-1.205 { 1370 proc auth {code arg1 arg2 arg3 arg4} { 1371 if {$code=="SQLITE_DROP_INDEX"} { 1372 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1373 return SQLITE_DENY 1374 } 1375 return SQLITE_OK 1376 } 1377 catchsql {DROP INDEX i2} 1378 } {1 {not authorized}} 1379 do_test auth-1.206 { 1380 set ::authargs 1381 } {i2 t2 main {}} 1382 do_test auth-1.207 { 1383 execsql {SELECT name FROM sqlite_master} 1384 } {t2 i2} 1385 do_test auth-1.208 { 1386 proc auth {code arg1 arg2 arg3 arg4} { 1387 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1388 return SQLITE_IGNORE 1389 } 1390 return SQLITE_OK 1391 } 1392 catchsql {DROP INDEX i2} 1393 } {0 {}} 1394 do_test auth-1.209 { 1395 execsql {SELECT name FROM sqlite_master} 1396 } {t2 i2} 1397 do_test auth-1.210 { 1398 proc auth {code arg1 arg2 arg3 arg4} { 1399 if {$code=="SQLITE_DROP_INDEX"} { 1400 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1401 return SQLITE_IGNORE 1402 } 1403 return SQLITE_OK 1404 } 1405 catchsql {DROP INDEX i2} 1406 } {0 {}} 1407 do_test auth-1.211 { 1408 set ::authargs 1409 } {i2 t2 main {}} 1410 do_test auth-1.212 { 1411 execsql {SELECT name FROM sqlite_master} 1412 } {t2 i2} 1413 do_test auth-1.213 { 1414 proc auth {code arg1 arg2 arg3 arg4} { 1415 if {$code=="SQLITE_DROP_INDEX"} { 1416 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1417 return SQLITE_OK 1418 } 1419 return SQLITE_OK 1420 } 1421 catchsql {DROP INDEX i2} 1422 } {0 {}} 1423 do_test auth-1.214 { 1424 set ::authargs 1425 } {i2 t2 main {}} 1426 do_test auth-1.215 { 1427 execsql {SELECT name FROM sqlite_master} 1428 } {t2} 1429 1430 ifcapable tempdb { 1431 do_test auth-1.216 { 1432 proc auth {code arg1 arg2 arg3 arg4} { 1433 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1434 return SQLITE_DENY 1435 } 1436 return SQLITE_OK 1437 } 1438 catchsql {DROP INDEX i1} 1439 } {1 {not authorized}} 1440 do_test auth-1.217 { 1441 execsql {SELECT name FROM sqlite_temp_master} 1442 } {t1 i1} 1443 do_test auth-1.218 { 1444 proc auth {code arg1 arg2 arg3 arg4} { 1445 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1446 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1447 return SQLITE_DENY 1448 } 1449 return SQLITE_OK 1450 } 1451 catchsql {DROP INDEX i1} 1452 } {1 {not authorized}} 1453 do_test auth-1.219 { 1454 set ::authargs 1455 } {i1 t1 temp {}} 1456 do_test auth-1.220 { 1457 execsql {SELECT name FROM sqlite_temp_master} 1458 } {t1 i1} 1459 do_test auth-1.221 { 1460 proc auth {code arg1 arg2 arg3 arg4} { 1461 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1462 return SQLITE_IGNORE 1463 } 1464 return SQLITE_OK 1465 } 1466 catchsql {DROP INDEX i1} 1467 } {0 {}} 1468 do_test auth-1.222 { 1469 execsql {SELECT name FROM sqlite_temp_master} 1470 } {t1 i1} 1471 do_test auth-1.223 { 1472 proc auth {code arg1 arg2 arg3 arg4} { 1473 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1474 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1475 return SQLITE_IGNORE 1476 } 1477 return SQLITE_OK 1478 } 1479 catchsql {DROP INDEX i1} 1480 } {0 {}} 1481 do_test auth-1.224 { 1482 set ::authargs 1483 } {i1 t1 temp {}} 1484 do_test auth-1.225 { 1485 execsql {SELECT name FROM sqlite_temp_master} 1486 } {t1 i1} 1487 do_test auth-1.226 { 1488 proc auth {code arg1 arg2 arg3 arg4} { 1489 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1490 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1491 return SQLITE_OK 1492 } 1493 return SQLITE_OK 1494 } 1495 catchsql {DROP INDEX i1} 1496 } {0 {}} 1497 do_test auth-1.227 { 1498 set ::authargs 1499 } {i1 t1 temp {}} 1500 do_test auth-1.228 { 1501 execsql {SELECT name FROM sqlite_temp_master} 1502 } {t1} 1503 } 1504 1505 do_test auth-1.229 { 1506 proc auth {code arg1 arg2 arg3 arg4} { 1507 if {$code=="SQLITE_PRAGMA"} { 1508 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1509 return SQLITE_DENY 1510 } 1511 return SQLITE_OK 1512 } 1513 catchsql {PRAGMA full_column_names=on} 1514 } {1 {not authorized}} 1515 do_test auth-1.230 { 1516 set ::authargs 1517 } {full_column_names on {} {}} 1518 do_test auth-1.231 { 1519 execsql2 {SELECT a FROM t2} 1520 } {a 11 a 7} 1521 do_test auth-1.232 { 1522 proc auth {code arg1 arg2 arg3 arg4} { 1523 if {$code=="SQLITE_PRAGMA"} { 1524 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1525 return SQLITE_IGNORE 1526 } 1527 return SQLITE_OK 1528 } 1529 catchsql {PRAGMA full_column_names=on} 1530 } {0 {}} 1531 do_test auth-1.233 { 1532 set ::authargs 1533 } {full_column_names on {} {}} 1534 do_test auth-1.234 { 1535 execsql2 {SELECT a FROM t2} 1536 } {a 11 a 7} 1537 do_test auth-1.235 { 1538 proc auth {code arg1 arg2 arg3 arg4} { 1539 if {$code=="SQLITE_PRAGMA"} { 1540 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1541 return SQLITE_OK 1542 } 1543 return SQLITE_OK 1544 } 1545 catchsql {PRAGMA full_column_names=on} 1546 } {0 {}} 1547 do_test auth-1.236 { 1548 execsql2 {SELECT a FROM t2} 1549 } {t2.a 11 t2.a 7} 1550 do_test auth-1.237 { 1551 proc auth {code arg1 arg2 arg3 arg4} { 1552 if {$code=="SQLITE_PRAGMA"} { 1553 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1554 return SQLITE_OK 1555 } 1556 return SQLITE_OK 1557 } 1558 catchsql {PRAGMA full_column_names=OFF} 1559 } {0 {}} 1560 do_test auth-1.238 { 1561 set ::authargs 1562 } {full_column_names OFF {} {}} 1563 do_test auth-1.239 { 1564 execsql2 {SELECT a FROM t2} 1565 } {a 11 a 7} 1566 1567 do_test auth-1.240 { 1568 proc auth {code arg1 arg2 arg3 arg4} { 1569 if {$code=="SQLITE_TRANSACTION"} { 1570 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1571 return SQLITE_DENY 1572 } 1573 return SQLITE_OK 1574 } 1575 catchsql {BEGIN} 1576 } {1 {not authorized}} 1577 do_test auth-1.241 { 1578 set ::authargs 1579 } {BEGIN {} {} {}} 1580 do_test auth-1.242 { 1581 proc auth {code arg1 arg2 arg3 arg4} { 1582 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} { 1583 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1584 return SQLITE_DENY 1585 } 1586 return SQLITE_OK 1587 } 1588 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT} 1589 } {1 {not authorized}} 1590 do_test auth-1.243 { 1591 set ::authargs 1592 } {COMMIT {} {} {}} 1593 do_test auth-1.244 { 1594 execsql {SELECT * FROM t2} 1595 } {11 2 33 7 8 9 44 55 66} 1596 do_test auth-1.245 { 1597 catchsql {ROLLBACK} 1598 } {1 {not authorized}} 1599 do_test auth-1.246 { 1600 set ::authargs 1601 } {ROLLBACK {} {} {}} 1602 do_test auth-1.247 { 1603 catchsql {END TRANSACTION} 1604 } {1 {not authorized}} 1605 do_test auth-1.248 { 1606 set ::authargs 1607 } {COMMIT {} {} {}} 1608 do_test auth-1.249 { 1609 db authorizer {} 1610 catchsql {ROLLBACK} 1611 } {0 {}} 1612 do_test auth-1.250 { 1613 execsql {SELECT * FROM t2} 1614 } {11 2 33 7 8 9} 1615 1616 # ticket #340 - authorization for ATTACH and DETACH. 1617 # 1618 ifcapable attach { 1619 do_test auth-1.251 { 1620 db authorizer ::auth 1621 proc auth {code arg1 arg2 arg3 arg4} { 1622 if {$code=="SQLITE_ATTACH"} { 1623 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1624 } 1625 return SQLITE_OK 1626 } 1627 catchsql { 1628 ATTACH DATABASE ':memory:' AS test1 1629 } 1630 } {0 {}} 1631 do_test auth-1.252a { 1632 set ::authargs 1633 } {:memory: {} {} {}} 1634 do_test auth-1.252b { 1635 db eval {DETACH test1} 1636 set ::attachfilename :memory: 1637 db eval {ATTACH $::attachfilename AS test1} 1638 set ::authargs 1639 } {{} {} {} {}} 1640 do_test auth-1.252c { 1641 db eval {DETACH test1} 1642 db eval {ATTACH ':mem' || 'ory:' AS test1} 1643 set ::authargs 1644 } {{} {} {} {}} 1645 do_test auth-1.253 { 1646 catchsql {DETACH DATABASE test1} 1647 proc auth {code arg1 arg2 arg3 arg4} { 1648 if {$code=="SQLITE_ATTACH"} { 1649 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1650 return SQLITE_DENY 1651 } 1652 return SQLITE_OK 1653 } 1654 catchsql { 1655 ATTACH DATABASE ':memory:' AS test1; 1656 } 1657 } {1 {not authorized}} 1658 do_test auth-1.254 { 1659 lindex [execsql {PRAGMA database_list}] 7 1660 } {} 1661 do_test auth-1.255 { 1662 catchsql {DETACH DATABASE test1} 1663 proc auth {code arg1 arg2 arg3 arg4} { 1664 if {$code=="SQLITE_ATTACH"} { 1665 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1666 return SQLITE_IGNORE 1667 } 1668 return SQLITE_OK 1669 } 1670 catchsql { 1671 ATTACH DATABASE ':memory:' AS test1; 1672 } 1673 } {0 {}} 1674 do_test auth-1.256 { 1675 lindex [execsql {PRAGMA database_list}] 7 1676 } {} 1677 do_test auth-1.257 { 1678 proc auth {code arg1 arg2 arg3 arg4} { 1679 if {$code=="SQLITE_DETACH"} { 1680 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1681 return SQLITE_OK 1682 } 1683 return SQLITE_OK 1684 } 1685 execsql {ATTACH DATABASE ':memory:' AS test1} 1686 catchsql { 1687 DETACH DATABASE test1; 1688 } 1689 } {0 {}} 1690 do_test auth-1.258 { 1691 lindex [execsql {PRAGMA database_list}] 7 1692 } {} 1693 do_test auth-1.259 { 1694 execsql {ATTACH DATABASE ':memory:' AS test1} 1695 proc auth {code arg1 arg2 arg3 arg4} { 1696 if {$code=="SQLITE_DETACH"} { 1697 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1698 return SQLITE_IGNORE 1699 } 1700 return SQLITE_OK 1701 } 1702 catchsql { 1703 DETACH DATABASE test1; 1704 } 1705 } {0 {}} 1706 ifcapable tempdb { 1707 ifcapable schema_pragmas { 1708 do_test auth-1.260 { 1709 lindex [execsql {PRAGMA database_list}] 7 1710 } {test1} 1711 } ;# ifcapable schema_pragmas 1712 do_test auth-1.261 { 1713 proc auth {code arg1 arg2 arg3 arg4} { 1714 if {$code=="SQLITE_DETACH"} { 1715 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1716 return SQLITE_DENY 1717 } 1718 return SQLITE_OK 1719 } 1720 catchsql { 1721 DETACH DATABASE test1; 1722 } 1723 } {1 {not authorized}} 1724 ifcapable schema_pragmas { 1725 do_test auth-1.262 { 1726 lindex [execsql {PRAGMA database_list}] 7 1727 } {test1} 1728 } ;# ifcapable schema_pragmas 1729 db authorizer {} 1730 execsql {DETACH DATABASE test1} 1731 db authorizer ::auth 1732 1733 # Authorization for ALTER TABLE. These tests are omitted if the library 1734 # was built without ALTER TABLE support. 1735 ifcapable altertable { 1736 1737 do_test auth-1.263 { 1738 proc auth {code arg1 arg2 arg3 arg4} { 1739 if {$code=="SQLITE_ALTER_TABLE"} { 1740 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1741 return SQLITE_OK 1742 } 1743 return SQLITE_OK 1744 } 1745 catchsql { 1746 ALTER TABLE t1 RENAME TO t1x 1747 } 1748 } {0 {}} 1749 do_test auth-1.264 { 1750 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1751 } {t1x} 1752 do_test auth-1.265 { 1753 set authargs 1754 } {temp t1 {} {}} 1755 do_test auth-1.266 { 1756 proc auth {code arg1 arg2 arg3 arg4} { 1757 if {$code=="SQLITE_ALTER_TABLE"} { 1758 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1759 return SQLITE_IGNORE 1760 } 1761 return SQLITE_OK 1762 } 1763 catchsql { 1764 ALTER TABLE t1x RENAME TO t1 1765 } 1766 } {0 {}} 1767 do_test auth-1.267 { 1768 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1769 } {t1x} 1770 do_test auth-1.268 { 1771 set authargs 1772 } {temp t1x {} {}} 1773 do_test auth-1.269 { 1774 proc auth {code arg1 arg2 arg3 arg4} { 1775 if {$code=="SQLITE_ALTER_TABLE"} { 1776 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1777 return SQLITE_DENY 1778 } 1779 return SQLITE_OK 1780 } 1781 catchsql { 1782 ALTER TABLE t1x RENAME TO t1 1783 } 1784 } {1 {not authorized}} 1785 do_test auth-1.270 { 1786 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1787 } {t1x} 1788 1789 do_test auth-1.271 { 1790 set authargs 1791 } {temp t1x {} {}} 1792 } ;# ifcapable altertable 1793 1794 } else { 1795 db authorizer {} 1796 db eval { 1797 DETACH DATABASE test1; 1798 } 1799 } 1800 } 1801 1802 ifcapable altertable { 1803 db authorizer {} 1804 catchsql {ALTER TABLE t1x RENAME TO t1} 1805 db authorizer ::auth 1806 do_test auth-1.272 { 1807 proc auth {code arg1 arg2 arg3 arg4} { 1808 if {$code=="SQLITE_ALTER_TABLE"} { 1809 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1810 return SQLITE_OK 1811 } 1812 return SQLITE_OK 1813 } 1814 catchsql { 1815 ALTER TABLE t2 RENAME TO t2x 1816 } 1817 } {0 {}} 1818 do_test auth-1.273 { 1819 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1820 } {t2x} 1821 do_test auth-1.274 { 1822 set authargs 1823 } {main t2 {} {}} 1824 do_test auth-1.275 { 1825 proc auth {code arg1 arg2 arg3 arg4} { 1826 if {$code=="SQLITE_ALTER_TABLE"} { 1827 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1828 return SQLITE_IGNORE 1829 } 1830 return SQLITE_OK 1831 } 1832 catchsql { 1833 ALTER TABLE t2x RENAME TO t2 1834 } 1835 } {0 {}} 1836 do_test auth-1.276 { 1837 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1838 } {t2x} 1839 do_test auth-1.277 { 1840 set authargs 1841 } {main t2x {} {}} 1842 do_test auth-1.278 { 1843 proc auth {code arg1 arg2 arg3 arg4} { 1844 if {$code=="SQLITE_ALTER_TABLE"} { 1845 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1846 return SQLITE_DENY 1847 } 1848 return SQLITE_OK 1849 } 1850 catchsql { 1851 ALTER TABLE t2x RENAME TO t2 1852 } 1853 } {1 {not authorized}} 1854 do_test auth-1.279 { 1855 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1856 } {t2x} 1857 do_test auth-1.280 { 1858 set authargs 1859 } {main t2x {} {}} 1860 db authorizer {} 1861 catchsql {ALTER TABLE t2x RENAME TO t2} 1862 1863 } ;# ifcapable altertable 1864 1865 # Test the authorization callbacks for the REINDEX command. 1866 ifcapable reindex { 1867 1868 proc auth {code args} { 1869 if {$code=="SQLITE_REINDEX"} { 1870 set ::authargs [concat $::authargs $args] 1871 } 1872 return SQLITE_OK 1873 } 1874 db authorizer auth 1875 do_test auth-1.281 { 1876 execsql { 1877 CREATE TABLE t3(a PRIMARY KEY, b, c); 1878 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1879 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1880 } 1881 } {} 1882 do_test auth-1.282 { 1883 set ::authargs {} 1884 execsql { 1885 REINDEX t3_idx1; 1886 } 1887 set ::authargs 1888 } {t3_idx1 {} main {}} 1889 do_test auth-1.283 { 1890 set ::authargs {} 1891 execsql { 1892 REINDEX BINARY; 1893 } 1894 set ::authargs 1895 } {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1896 do_test auth-1.284 { 1897 set ::authargs {} 1898 execsql { 1899 REINDEX NOCASE; 1900 } 1901 set ::authargs 1902 } {t3_idx2 {} main {}} 1903 do_test auth-1.285 { 1904 set ::authargs {} 1905 execsql { 1906 REINDEX t3; 1907 } 1908 set ::authargs 1909 } {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1910 do_test auth-1.286 { 1911 execsql { 1912 DROP TABLE t3; 1913 } 1914 } {} 1915 ifcapable tempdb { 1916 do_test auth-1.287 { 1917 execsql { 1918 CREATE TEMP TABLE t3(a PRIMARY KEY, b, c); 1919 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1920 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1921 } 1922 } {} 1923 do_test auth-1.288 { 1924 set ::authargs {} 1925 execsql { 1926 REINDEX temp.t3_idx1; 1927 } 1928 set ::authargs 1929 } {t3_idx1 {} temp {}} 1930 do_test auth-1.289 { 1931 set ::authargs {} 1932 execsql { 1933 REINDEX BINARY; 1934 } 1935 set ::authargs 1936 } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1937 do_test auth-1.290 { 1938 set ::authargs {} 1939 execsql { 1940 REINDEX NOCASE; 1941 } 1942 set ::authargs 1943 } {t3_idx2 {} temp {}} 1944 do_test auth-1.291 { 1945 set ::authargs {} 1946 execsql { 1947 REINDEX temp.t3; 1948 } 1949 set ::authargs 1950 } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1951 proc auth {code args} { 1952 if {$code=="SQLITE_REINDEX"} { 1953 set ::authargs [concat $::authargs $args] 1954 return SQLITE_DENY 1955 } 1956 return SQLITE_OK 1957 } 1958 do_test auth-1.292 { 1959 set ::authargs {} 1960 catchsql { 1961 REINDEX temp.t3; 1962 } 1963 } {1 {not authorized}} 1964 do_test auth-1.293 { 1965 execsql { 1966 DROP TABLE t3; 1967 } 1968 } {} 1969 } 1970 1971 } ;# ifcapable reindex 1972 1973 ifcapable analyze { 1974 proc auth {code args} { 1975 if {$code=="SQLITE_ANALYZE"} { 1976 set ::authargs [concat $::authargs $args] 1977 } 1978 return SQLITE_OK 1979 } 1980 do_test auth-1.294 { 1981 set ::authargs {} 1982 execsql { 1983 CREATE TABLE t4(a,b,c); 1984 CREATE INDEX t4i1 ON t4(a); 1985 CREATE INDEX t4i2 ON t4(b,a,c); 1986 INSERT INTO t4 VALUES(1,2,3); 1987 ANALYZE; 1988 } 1989 set ::authargs 1990 } {t4 {} main {} t2 {} main {}} 1991 do_test auth-1.295 { 1992 execsql { 1993 SELECT count(*) FROM sqlite_stat1; 1994 } 1995 } 3 1996 proc auth {code args} { 1997 if {$code=="SQLITE_ANALYZE"} { 1998 set ::authargs [concat $::authargs $args] 1999 return SQLITE_DENY 2000 } 2001 return SQLITE_OK 2002 } 2003 do_test auth-1.296 { 2004 set ::authargs {} 2005 catchsql { 2006 ANALYZE; 2007 } 2008 } {1 {not authorized}} 2009 do_test auth-1.297 { 2010 execsql { 2011 SELECT count(*) FROM sqlite_stat1; 2012 } 2013 } 3 2014 } ;# ifcapable analyze 2015 2016 2017 # Authorization for ALTER TABLE ADD COLUMN. 2018 # These tests are omitted if the library 2019 # was built without ALTER TABLE support. 2020 ifcapable {altertable} { 2021 do_test auth-1.300 { 2022 execsql {CREATE TABLE t5(x)} 2023 proc auth {code arg1 arg2 arg3 arg4} { 2024 if {$code=="SQLITE_ALTER_TABLE"} { 2025 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2026 return SQLITE_OK 2027 } 2028 return SQLITE_OK 2029 } 2030 catchsql { 2031 ALTER TABLE t5 ADD COLUMN new_col_1; 2032 } 2033 } {0 {}} 2034 do_test auth-1.301 { 2035 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2036 regexp new_col_1 $x 2037 } {1} 2038 do_test auth-1.302 { 2039 set authargs 2040 } {main t5 {} {}} 2041 do_test auth-1.303 { 2042 proc auth {code arg1 arg2 arg3 arg4} { 2043 if {$code=="SQLITE_ALTER_TABLE"} { 2044 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2045 return SQLITE_IGNORE 2046 } 2047 return SQLITE_OK 2048 } 2049 catchsql { 2050 ALTER TABLE t5 ADD COLUMN new_col_2; 2051 } 2052 } {0 {}} 2053 do_test auth-1.304 { 2054 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2055 regexp new_col_2 $x 2056 } {0} 2057 do_test auth-1.305 { 2058 set authargs 2059 } {main t5 {} {}} 2060 do_test auth-1.306 { 2061 proc auth {code arg1 arg2 arg3 arg4} { 2062 if {$code=="SQLITE_ALTER_TABLE"} { 2063 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2064 return SQLITE_DENY 2065 } 2066 return SQLITE_OK 2067 } 2068 catchsql { 2069 ALTER TABLE t5 ADD COLUMN new_col_3 2070 } 2071 } {1 {not authorized}} 2072 do_test auth-1.307 { 2073 set x [execsql {SELECT sql FROM sqlite_temp_master WHERE type='t5'}] 2074 regexp new_col_3 $x 2075 } {0} 2076 2077 do_test auth-1.308 { 2078 set authargs 2079 } {main t5 {} {}} 2080 execsql {DROP TABLE t5} 2081 } ;# ifcapable altertable 2082 2083 do_test auth-2.1 { 2084 proc auth {code arg1 arg2 arg3 arg4} { 2085 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2086 return SQLITE_DENY 2087 } 2088 return SQLITE_OK 2089 } 2090 db authorizer ::auth 2091 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)} 2092 catchsql {SELECT * FROM t3} 2093 } {1 {access to t3.x is prohibited}} 2094 do_test auth-2.1 { 2095 catchsql {SELECT y,z FROM t3} 2096 } {0 {}} 2097 do_test auth-2.2 { 2098 catchsql {SELECT ROWID,y,z FROM t3} 2099 } {1 {access to t3.x is prohibited}} 2100 do_test auth-2.3 { 2101 catchsql {SELECT OID,y,z FROM t3} 2102 } {1 {access to t3.x is prohibited}} 2103 do_test auth-2.4 { 2104 proc auth {code arg1 arg2 arg3 arg4} { 2105 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2106 return SQLITE_IGNORE 2107 } 2108 return SQLITE_OK 2109 } 2110 execsql {INSERT INTO t3 VALUES(44,55,66)} 2111 catchsql {SELECT * FROM t3} 2112 } {0 {{} 55 66}} 2113 do_test auth-2.5 { 2114 catchsql {SELECT rowid,y,z FROM t3} 2115 } {0 {{} 55 66}} 2116 do_test auth-2.6 { 2117 proc auth {code arg1 arg2 arg3 arg4} { 2118 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} { 2119 return SQLITE_IGNORE 2120 } 2121 return SQLITE_OK 2122 } 2123 catchsql {SELECT * FROM t3} 2124 } {0 {44 55 66}} 2125 do_test auth-2.7 { 2126 catchsql {SELECT ROWID,y,z FROM t3} 2127 } {0 {44 55 66}} 2128 do_test auth-2.8 { 2129 proc auth {code arg1 arg2 arg3 arg4} { 2130 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2131 return SQLITE_IGNORE 2132 } 2133 return SQLITE_OK 2134 } 2135 catchsql {SELECT ROWID,b,c FROM t2} 2136 } {0 {{} 2 33 {} 8 9}} 2137 do_test auth-2.9.1 { 2138 # We have to flush the cache here in case the Tcl interface tries to 2139 # reuse a statement compiled with sqlite3_prepare_v2(). In this case, 2140 # the first error encountered is an SQLITE_SCHEMA error. Then, when 2141 # trying to recompile the statement, the authorization error is encountered. 2142 # If we do not flush the cache, the correct error message is returned, but 2143 # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test 2144 # case after this one. 2145 # 2146 db cache flush 2147 2148 proc auth {code arg1 arg2 arg3 arg4} { 2149 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2150 return bogus 2151 } 2152 return SQLITE_OK 2153 } 2154 catchsql {SELECT ROWID,b,c FROM t2} 2155 } {1 {authorizer malfunction}} 2156 do_test auth-2.9.2 { 2157 db errorcode 2158 } {1} 2159 do_test auth-2.10 { 2160 proc auth {code arg1 arg2 arg3 arg4} { 2161 if {$code=="SQLITE_SELECT"} { 2162 return bogus 2163 } 2164 return SQLITE_OK 2165 } 2166 catchsql {SELECT ROWID,b,c FROM t2} 2167 } {1 {authorizer malfunction}} 2168 do_test auth-2.11.1 { 2169 proc auth {code arg1 arg2 arg3 arg4} { 2170 if {$code=="SQLITE_READ" && $arg2=="a"} { 2171 return SQLITE_IGNORE 2172 } 2173 return SQLITE_OK 2174 } 2175 catchsql {SELECT * FROM t2, t3} 2176 } {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}} 2177 do_test auth-2.11.2 { 2178 proc auth {code arg1 arg2 arg3 arg4} { 2179 if {$code=="SQLITE_READ" && $arg2=="x"} { 2180 return SQLITE_IGNORE 2181 } 2182 return SQLITE_OK 2183 } 2184 catchsql {SELECT * FROM t2, t3} 2185 } {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}} 2186 2187 # Make sure the OLD and NEW pseudo-tables of a trigger get authorized. 2188 # 2189 ifcapable trigger { 2190 do_test auth-3.1 { 2191 proc auth {code arg1 arg2 arg3 arg4} { 2192 return SQLITE_OK 2193 } 2194 execsql { 2195 CREATE TABLE tx(a1,a2,b1,b2,c1,c2); 2196 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 2197 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c); 2198 END; 2199 UPDATE t2 SET a=a+1; 2200 SELECT * FROM tx; 2201 } 2202 } {11 12 2 2 33 33 7 8 8 8 9 9} 2203 do_test auth-3.2 { 2204 proc auth {code arg1 arg2 arg3 arg4} { 2205 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} { 2206 return SQLITE_IGNORE 2207 } 2208 return SQLITE_OK 2209 } 2210 execsql { 2211 DELETE FROM tx; 2212 UPDATE t2 SET a=a+100; 2213 SELECT * FROM tx; 2214 } 2215 } {12 112 2 2 {} {} 8 108 8 8 {} {}} 2216 } ;# ifcapable trigger 2217 2218 # Make sure the names of views and triggers are passed on on arg4. 2219 # 2220 ifcapable trigger { 2221 do_test auth-4.1 { 2222 proc auth {code arg1 arg2 arg3 arg4} { 2223 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 2224 return SQLITE_OK 2225 } 2226 set authargs {} 2227 execsql { 2228 UPDATE t2 SET a=a+1; 2229 } 2230 set authargs 2231 } [list \ 2232 SQLITE_READ t2 a main {} \ 2233 SQLITE_UPDATE t2 a main {} \ 2234 SQLITE_INSERT tx {} main r1 \ 2235 SQLITE_READ t2 a main r1 \ 2236 SQLITE_READ t2 a main r1 \ 2237 SQLITE_READ t2 b main r1 \ 2238 SQLITE_READ t2 b main r1 \ 2239 SQLITE_READ t2 c main r1 \ 2240 SQLITE_READ t2 c main r1] 2241 } 2242 2243 ifcapable {view && trigger} { 2244 do_test auth-4.2 { 2245 execsql { 2246 CREATE VIEW v1 AS SELECT a+b AS x FROM t2; 2247 CREATE TABLE v1chng(x1,x2); 2248 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN 2249 INSERT INTO v1chng VALUES(OLD.x,NEW.x); 2250 END; 2251 SELECT * FROM v1; 2252 } 2253 } {115 117} 2254 do_test auth-4.3 { 2255 set authargs {} 2256 execsql { 2257 UPDATE v1 SET x=1 WHERE x=117 2258 } 2259 set authargs 2260 } [list \ 2261 SQLITE_UPDATE v1 x main {} \ 2262 SQLITE_SELECT {} {} {} v1 \ 2263 SQLITE_READ t2 a main v1 \ 2264 SQLITE_READ t2 b main v1 \ 2265 SQLITE_SELECT {} {} {} {} \ 2266 SQLITE_READ v1 x main v1 \ 2267 SQLITE_INSERT v1chng {} main r2 \ 2268 SQLITE_READ v1 x main r2 \ 2269 SQLITE_READ v1 x main r2 \ 2270 ] 2271 2272 do_test auth-4.4 { 2273 execsql { 2274 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN 2275 INSERT INTO v1chng VALUES(OLD.x,NULL); 2276 END; 2277 SELECT * FROM v1; 2278 } 2279 } {115 117} 2280 do_test auth-4.5 { 2281 set authargs {} 2282 execsql { 2283 DELETE FROM v1 WHERE x=117 2284 } 2285 set authargs 2286 } [list \ 2287 SQLITE_DELETE v1 {} main {} \ 2288 SQLITE_SELECT {} {} {} v1 \ 2289 SQLITE_READ t2 a main v1 \ 2290 SQLITE_READ t2 b main v1 \ 2291 SQLITE_SELECT {} {} {} {} \ 2292 SQLITE_READ v1 x main v1 \ 2293 SQLITE_INSERT v1chng {} main r3 \ 2294 SQLITE_READ v1 x main r3 \ 2295 ] 2296 2297 } ;# ifcapable view && trigger 2298 2299 # Ticket #1338: Make sure authentication works in the presence of an AS 2300 # clause. 2301 # 2302 do_test auth-5.1 { 2303 proc auth {code arg1 arg2 arg3 arg4} { 2304 return SQLITE_OK 2305 } 2306 execsql { 2307 SELECT count(a) AS cnt FROM t4 ORDER BY cnt 2308 } 2309 } {1} 2310 2311 # Ticket #1607 2312 # 2313 ifcapable compound&&subquery { 2314 ifcapable trigger { 2315 execsql { 2316 DROP TABLE tx; 2317 } 2318 ifcapable view { 2319 execsql { 2320 DROP TABLE v1chng; 2321 } 2322 } 2323 } 2324 ifcapable stat2 { 2325 set stat2 "sqlite_stat2 " 2326 } else { 2327 set stat2 "" 2328 } 2329 do_test auth-5.2 { 2330 execsql { 2331 SELECT name FROM ( 2332 SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) 2333 WHERE type='table' 2334 ORDER BY name 2335 } 2336 } "sqlite_stat1 ${stat2}t1 t2 t3 t4" 2337 } 2338 2339 # Ticket #3944 2340 # 2341 ifcapable trigger { 2342 do_test auth-5.3.1 { 2343 execsql { 2344 CREATE TABLE t5 ( x ); 2345 CREATE TRIGGER t5_tr1 AFTER INSERT ON t5 BEGIN 2346 UPDATE t5 SET x = 1 WHERE NEW.x = 0; 2347 END; 2348 } 2349 } {} 2350 set ::authargs [list] 2351 proc auth {args} { 2352 eval lappend ::authargs $args 2353 return SQLITE_OK 2354 } 2355 do_test auth-5.3.2 { 2356 execsql { INSERT INTO t5 (x) values(0) } 2357 set ::authargs 2358 } [list SQLITE_INSERT t5 {} main {} \ 2359 SQLITE_UPDATE t5 x main t5_tr1 \ 2360 SQLITE_READ t5 x main t5_tr1 \ 2361 ] 2362 do_test auth-5.3.2 { 2363 execsql { SELECT * FROM t5 } 2364 } {1} 2365 } 2366 2367 2368 rename proc {} 2369 rename proc_real proc 2370 2371 2372 finish_test 2373
