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