1# 2# 2003 April 4 3# 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# This file implements regression tests for SQLite library. The 13# focus of this script is testing the ATTACH and DETACH commands 14# and related functionality. 15# 16# $Id: auth.test,v 1.12 2003/12/07 00:24:35 drh Exp $ 17# 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is 23# defined during compilation. 24 25do_test auth-1.1.1 { 26 db close 27 set ::DB [sqlite db test.db] 28 proc auth {code arg1 arg2 arg3 arg4} { 29 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 30 return SQLITE_DENY 31 } 32 return SQLITE_OK 33 } 34 db authorizer ::auth 35 catchsql {CREATE TABLE t1(a,b,c)} 36} {1 {not authorized}} 37do_test auth-1.1.2 { 38 db errorcode 39} {23} 40do_test auth-1.2 { 41 execsql {SELECT name FROM sqlite_master} 42} {} 43do_test auth-1.3.1 { 44 proc auth {code arg1 arg2 arg3 arg4} { 45 if {$code=="SQLITE_CREATE_TABLE"} { 46 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 47 return SQLITE_DENY 48 } 49 return SQLITE_OK 50 } 51 catchsql {CREATE TABLE t1(a,b,c)} 52} {1 {not authorized}} 53do_test auth-1.3.2 { 54 db errorcode 55} {23} 56do_test auth-1.3.3 { 57 set ::authargs 58} {t1 {} main {}} 59do_test auth-1.4 { 60 execsql {SELECT name FROM sqlite_master} 61} {} 62 63do_test auth-1.5 { 64 proc auth {code arg1 arg2 arg3 arg4} { 65 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 66 return SQLITE_DENY 67 } 68 return SQLITE_OK 69 } 70 catchsql {CREATE TEMP TABLE t1(a,b,c)} 71} {1 {not authorized}} 72do_test auth-1.6 { 73 execsql {SELECT name FROM sqlite_temp_master} 74} {} 75do_test auth-1.7.1 { 76 proc auth {code arg1 arg2 arg3 arg4} { 77 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 78 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 79 return SQLITE_DENY 80 } 81 return SQLITE_OK 82 } 83 catchsql {CREATE TEMP TABLE t1(a,b,c)} 84} {1 {not authorized}} 85do_test auth-1.7.2 { 86 set ::authargs 87} {t1 {} temp {}} 88do_test auth-1.8 { 89 execsql {SELECT name FROM sqlite_temp_master} 90} {} 91 92do_test auth-1.9 { 93 proc auth {code arg1 arg2 arg3 arg4} { 94 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 95 return SQLITE_IGNORE 96 } 97 return SQLITE_OK 98 } 99 catchsql {CREATE TABLE t1(a,b,c)} 100} {0 {}} 101do_test auth-1.10 { 102 execsql {SELECT name FROM sqlite_master} 103} {} 104do_test auth-1.11 { 105 proc auth {code arg1 arg2 arg3 arg4} { 106 if {$code=="SQLITE_CREATE_TABLE"} { 107 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 108 return SQLITE_IGNORE 109 } 110 return SQLITE_OK 111 } 112 catchsql {CREATE TABLE t1(a,b,c)} 113} {0 {}} 114do_test auth-1.12 { 115 execsql {SELECT name FROM sqlite_master} 116} {} 117do_test auth-1.13 { 118 proc auth {code arg1 arg2 arg3 arg4} { 119 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 120 return SQLITE_IGNORE 121 } 122 return SQLITE_OK 123 } 124 catchsql {CREATE TEMP TABLE t1(a,b,c)} 125} {0 {}} 126do_test auth-1.14 { 127 execsql {SELECT name FROM sqlite_temp_master} 128} {} 129do_test auth-1.15 { 130 proc auth {code arg1 arg2 arg3 arg4} { 131 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 132 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 133 return SQLITE_IGNORE 134 } 135 return SQLITE_OK 136 } 137 catchsql {CREATE TEMP TABLE t1(a,b,c)} 138} {0 {}} 139do_test auth-1.16 { 140 execsql {SELECT name FROM sqlite_temp_master} 141} {} 142 143do_test auth-1.17 { 144 proc auth {code arg1 arg2 arg3 arg4} { 145 if {$code=="SQLITE_CREATE_TABLE"} { 146 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 147 return SQLITE_DENY 148 } 149 return SQLITE_OK 150 } 151 catchsql {CREATE TEMP TABLE t1(a,b,c)} 152} {0 {}} 153do_test auth-1.18 { 154 execsql {SELECT name FROM sqlite_temp_master} 155} {t1} 156do_test auth-1.19.1 { 157 set ::authargs {} 158 proc auth {code arg1 arg2 arg3 arg4} { 159 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 160 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 161 return SQLITE_DENY 162 } 163 return SQLITE_OK 164 } 165 catchsql {CREATE TABLE t2(a,b,c)} 166} {0 {}} 167do_test auth-1.19.2 { 168 set ::authargs 169} {} 170do_test auth-1.20 { 171 execsql {SELECT name FROM sqlite_master} 172} {t2} 173 174do_test auth-1.21.1 { 175 proc auth {code arg1 arg2 arg3 arg4} { 176 if {$code=="SQLITE_DROP_TABLE"} { 177 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 178 return SQLITE_DENY 179 } 180 return SQLITE_OK 181 } 182 catchsql {DROP TABLE t2} 183} {1 {not authorized}} 184do_test auth-1.21.2 { 185 set ::authargs 186} {t2 {} main {}} 187do_test auth-1.22 { 188 execsql {SELECT name FROM sqlite_master} 189} {t2} 190do_test auth-1.23.1 { 191 proc auth {code arg1 arg2 arg3 arg4} { 192 if {$code=="SQLITE_DROP_TABLE"} { 193 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 194 return SQLITE_IGNORE 195 } 196 return SQLITE_OK 197 } 198 catchsql {DROP TABLE t2} 199} {0 {}} 200do_test auth-1.23.2 { 201 set ::authargs 202} {t2 {} main {}} 203do_test auth-1.24 { 204 execsql {SELECT name FROM sqlite_master} 205} {t2} 206 207do_test auth-1.25 { 208 proc auth {code arg1 arg2 arg3 arg4} { 209 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 210 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 211 return SQLITE_DENY 212 } 213 return SQLITE_OK 214 } 215 catchsql {DROP TABLE t1} 216} {1 {not authorized}} 217do_test auth-1.26 { 218 execsql {SELECT name FROM sqlite_temp_master} 219} {t1} 220do_test auth-1.27 { 221 proc auth {code arg1 arg2 arg3 arg4} { 222 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 223 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 224 return SQLITE_IGNORE 225 } 226 return SQLITE_OK 227 } 228 catchsql {DROP TABLE t1} 229} {0 {}} 230do_test auth-1.28 { 231 execsql {SELECT name FROM sqlite_temp_master} 232} {t1} 233 234do_test auth-1.29 { 235 proc auth {code arg1 arg2 arg3 arg4} { 236 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 237 return SQLITE_DENY 238 } 239 return SQLITE_OK 240 } 241 catchsql {INSERT INTO t2 VALUES(1,2,3)} 242} {1 {not authorized}} 243do_test auth-1.30 { 244 execsql {SELECT * FROM t2} 245} {} 246do_test auth-1.31 { 247 proc auth {code arg1 arg2 arg3 arg4} { 248 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 249 return SQLITE_IGNORE 250 } 251 return SQLITE_OK 252 } 253 catchsql {INSERT INTO t2 VALUES(1,2,3)} 254} {0 {}} 255do_test auth-1.32 { 256 execsql {SELECT * FROM t2} 257} {} 258do_test auth-1.33 { 259 proc auth {code arg1 arg2 arg3 arg4} { 260 if {$code=="SQLITE_INSERT" && $arg1=="t1"} { 261 return SQLITE_IGNORE 262 } 263 return SQLITE_OK 264 } 265 catchsql {INSERT INTO t2 VALUES(1,2,3)} 266} {0 {}} 267do_test auth-1.34 { 268 execsql {SELECT * FROM t2} 269} {1 2 3} 270 271do_test auth-1.35.1 { 272 proc auth {code arg1 arg2 arg3 arg4} { 273 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 274 return SQLITE_DENY 275 } 276 return SQLITE_OK 277 } 278 catchsql {SELECT * FROM t2} 279} {1 {access to t2.b is prohibited}} 280do_test auth-1.35.2 { 281 execsql {ATTACH DATABASE 'test.db' AS two} 282 catchsql {SELECT * FROM two.t2} 283} {1 {access to two.t2.b is prohibited}} 284execsql {DETACH DATABASE two} 285do_test auth-1.36 { 286 proc auth {code arg1 arg2 arg3 arg4} { 287 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 288 return SQLITE_IGNORE 289 } 290 return SQLITE_OK 291 } 292 catchsql {SELECT * FROM t2} 293} {0 {1 {} 3}} 294do_test auth-1.37 { 295 proc auth {code arg1 arg2 arg3 arg4} { 296 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 297 return SQLITE_IGNORE 298 } 299 return SQLITE_OK 300 } 301 catchsql {SELECT * FROM t2 WHERE b=2} 302} {0 {}} 303do_test auth-1.38 { 304 proc auth {code arg1 arg2 arg3 arg4} { 305 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} { 306 return SQLITE_IGNORE 307 } 308 return SQLITE_OK 309 } 310 catchsql {SELECT * FROM t2 WHERE b=2} 311} {0 {{} 2 3}} 312do_test auth-1.39 { 313 proc auth {code arg1 arg2 arg3 arg4} { 314 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 315 return SQLITE_IGNORE 316 } 317 return SQLITE_OK 318 } 319 catchsql {SELECT * FROM t2 WHERE b IS NULL} 320} {0 {1 {} 3}} 321do_test auth-1.40 { 322 proc auth {code arg1 arg2 arg3 arg4} { 323 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 324 return SQLITE_DENY 325 } 326 return SQLITE_OK 327 } 328 catchsql {SELECT a,c FROM t2 WHERE b IS NULL} 329} {1 {access to t2.b is prohibited}} 330 331do_test auth-1.41 { 332 proc auth {code arg1 arg2 arg3 arg4} { 333 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 334 return SQLITE_DENY 335 } 336 return SQLITE_OK 337 } 338 catchsql {UPDATE t2 SET a=11} 339} {0 {}} 340do_test auth-1.42 { 341 execsql {SELECT * FROM t2} 342} {11 2 3} 343do_test auth-1.43 { 344 proc auth {code arg1 arg2 arg3 arg4} { 345 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 346 return SQLITE_DENY 347 } 348 return SQLITE_OK 349 } 350 catchsql {UPDATE t2 SET b=22, c=33} 351} {1 {not authorized}} 352do_test auth-1.44 { 353 execsql {SELECT * FROM t2} 354} {11 2 3} 355do_test auth-1.45 { 356 proc auth {code arg1 arg2 arg3 arg4} { 357 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 358 return SQLITE_IGNORE 359 } 360 return SQLITE_OK 361 } 362 catchsql {UPDATE t2 SET b=22, c=33} 363} {0 {}} 364do_test auth-1.46 { 365 execsql {SELECT * FROM t2} 366} {11 2 33} 367 368do_test auth-1.47 { 369 proc auth {code arg1 arg2 arg3 arg4} { 370 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 371 return SQLITE_DENY 372 } 373 return SQLITE_OK 374 } 375 catchsql {DELETE FROM t2 WHERE a=11} 376} {1 {not authorized}} 377do_test auth-1.48 { 378 execsql {SELECT * FROM t2} 379} {11 2 33} 380do_test auth-1.49 { 381 proc auth {code arg1 arg2 arg3 arg4} { 382 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 383 return SQLITE_IGNORE 384 } 385 return SQLITE_OK 386 } 387 catchsql {DELETE FROM t2 WHERE a=11} 388} {0 {}} 389do_test auth-1.50 { 390 execsql {SELECT * FROM t2} 391} {11 2 33} 392 393do_test auth-1.51 { 394 proc auth {code arg1 arg2 arg3 arg4} { 395 if {$code=="SQLITE_SELECT"} { 396 return SQLITE_DENY 397 } 398 return SQLITE_OK 399 } 400 catchsql {SELECT * FROM t2} 401} {1 {not authorized}} 402do_test auth-1.52 { 403 proc auth {code arg1 arg2 arg3 arg4} { 404 if {$code=="SQLITE_SELECT"} { 405 return SQLITE_IGNORE 406 } 407 return SQLITE_OK 408 } 409 catchsql {SELECT * FROM t2} 410} {0 {}} 411do_test auth-1.53 { 412 proc auth {code arg1 arg2 arg3 arg4} { 413 if {$code=="SQLITE_SELECT"} { 414 return SQLITE_OK 415 } 416 return SQLITE_OK 417 } 418 catchsql {SELECT * FROM t2} 419} {0 {11 2 33}} 420 421set f [open data1.txt w] 422puts $f "7:8:9" 423close $f 424do_test auth-1.54 { 425 proc auth {code arg1 arg2 arg3 arg4} { 426 if {$code=="SQLITE_COPY"} { 427 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 428 return SQLITE_DENY 429 } 430 return SQLITE_OK 431 } 432 catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'} 433} {1 {not authorized}} 434do_test auth-1.55 { 435 set ::authargs 436} {t2 data1.txt main {}} 437do_test auth-1.56 { 438 execsql {SELECT * FROM t2} 439} {11 2 33} 440do_test auth-1.57 { 441 proc auth {code arg1 arg2 arg3 arg4} { 442 if {$code=="SQLITE_COPY"} { 443 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 444 return SQLITE_IGNORE 445 } 446 return SQLITE_OK 447 } 448 catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'} 449} {0 {}} 450do_test auth-1.58 { 451 set ::authargs 452} {t2 data1.txt main {}} 453do_test auth-1.59 { 454 execsql {SELECT * FROM t2} 455} {11 2 33} 456do_test auth-1.60 { 457 proc auth {code arg1 arg2 arg3 arg4} { 458 if {$code=="SQLITE_COPY"} { 459 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 460 return SQLITE_OK 461 } 462 return SQLITE_OK 463 } 464 catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'} 465} {0 {}} 466do_test auth-1.61 { 467 set ::authargs 468} {t2 data1.txt main {}} 469do_test auth-1.62 { 470 execsql {SELECT * FROM t2} 471} {11 2 33 7 8 9} 472 473do_test auth-1.63 { 474 proc auth {code arg1 arg2 arg3 arg4} { 475 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 476 return SQLITE_DENY 477 } 478 return SQLITE_OK 479 } 480 catchsql {DROP TABLE t2} 481} {1 {not authorized}} 482do_test auth-1.64 { 483 execsql {SELECT name FROM sqlite_master} 484} {t2} 485do_test auth-1.65 { 486 proc auth {code arg1 arg2 arg3 arg4} { 487 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 488 return SQLITE_DENY 489 } 490 return SQLITE_OK 491 } 492 catchsql {DROP TABLE t2} 493} {1 {not authorized}} 494do_test auth-1.66 { 495 execsql {SELECT name FROM sqlite_master} 496} {t2} 497do_test auth-1.67 { 498 proc auth {code arg1 arg2 arg3 arg4} { 499 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 500 return SQLITE_DENY 501 } 502 return SQLITE_OK 503 } 504 catchsql {DROP TABLE t1} 505} {1 {not authorized}} 506do_test auth-1.68 { 507 execsql {SELECT name FROM sqlite_temp_master} 508} {t1} 509do_test auth-1.69 { 510 proc auth {code arg1 arg2 arg3 arg4} { 511 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 512 return SQLITE_DENY 513 } 514 return SQLITE_OK 515 } 516 catchsql {DROP TABLE t1} 517} {1 {not authorized}} 518do_test auth-1.70 { 519 execsql {SELECT name FROM sqlite_temp_master} 520} {t1} 521 522do_test auth-1.71 { 523 proc auth {code arg1 arg2 arg3 arg4} { 524 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 525 return SQLITE_IGNORE 526 } 527 return SQLITE_OK 528 } 529 catchsql {DROP TABLE t2} 530} {0 {}} 531do_test auth-1.72 { 532 execsql {SELECT name FROM sqlite_master} 533} {t2} 534do_test auth-1.73 { 535 proc auth {code arg1 arg2 arg3 arg4} { 536 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 537 return SQLITE_IGNORE 538 } 539 return SQLITE_OK 540 } 541 catchsql {DROP TABLE t2} 542} {0 {}} 543do_test auth-1.74 { 544 execsql {SELECT name FROM sqlite_master} 545} {t2} 546do_test auth-1.75 { 547 proc auth {code arg1 arg2 arg3 arg4} { 548 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 549 return SQLITE_IGNORE 550 } 551 return SQLITE_OK 552 } 553 catchsql {DROP TABLE t1} 554} {0 {}} 555do_test auth-1.76 { 556 execsql {SELECT name FROM sqlite_temp_master} 557} {t1} 558do_test auth-1.77 { 559 proc auth {code arg1 arg2 arg3 arg4} { 560 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 561 return SQLITE_IGNORE 562 } 563 return SQLITE_OK 564 } 565 catchsql {DROP TABLE t1} 566} {0 {}} 567do_test auth-1.78 { 568 execsql {SELECT name FROM sqlite_temp_master} 569} {t1} 570 571do_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}} 581do_test auth-1.80 { 582 set ::authargs 583} {v1 {} main {}} 584do_test auth-1.81 { 585 execsql {SELECT name FROM sqlite_master} 586} {t2} 587do_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 {}} 597do_test auth-1.83 { 598 set ::authargs 599} {v1 {} main {}} 600do_test auth-1.84 { 601 execsql {SELECT name FROM sqlite_master} 602} {t2} 603 604do_test auth-1.85 { 605 proc auth {code arg1 arg2 arg3 arg4} { 606 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 607 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 608 return SQLITE_DENY 609 } 610 return SQLITE_OK 611 } 612 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 613} {1 {not authorized}} 614do_test auth-1.86 { 615 set ::authargs 616} {v1 {} temp {}} 617do_test auth-1.87 { 618 execsql {SELECT name FROM sqlite_temp_master} 619} {t1} 620do_test auth-1.88 { 621 proc auth {code arg1 arg2 arg3 arg4} { 622 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 623 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 624 return SQLITE_IGNORE 625 } 626 return SQLITE_OK 627 } 628 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 629} {0 {}} 630do_test auth-1.89 { 631 set ::authargs 632} {v1 {} temp {}} 633do_test auth-1.90 { 634 execsql {SELECT name FROM sqlite_temp_master} 635} {t1} 636 637do_test auth-1.91 { 638 proc auth {code arg1 arg2 arg3 arg4} { 639 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 640 return SQLITE_DENY 641 } 642 return SQLITE_OK 643 } 644 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 645} {1 {not authorized}} 646do_test auth-1.92 { 647 execsql {SELECT name FROM sqlite_master} 648} {t2} 649do_test auth-1.93 { 650 proc auth {code arg1 arg2 arg3 arg4} { 651 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 652 return SQLITE_IGNORE 653 } 654 return SQLITE_OK 655 } 656 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 657} {0 {}} 658do_test auth-1.94 { 659 execsql {SELECT name FROM sqlite_master} 660} {t2} 661 662do_test auth-1.95 { 663 proc auth {code arg1 arg2 arg3 arg4} { 664 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 665 return SQLITE_DENY 666 } 667 return SQLITE_OK 668 } 669 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 670} {1 {not authorized}} 671do_test auth-1.96 { 672 execsql {SELECT name FROM sqlite_temp_master} 673} {t1} 674do_test auth-1.97 { 675 proc auth {code arg1 arg2 arg3 arg4} { 676 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 677 return SQLITE_IGNORE 678 } 679 return SQLITE_OK 680 } 681 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 682} {0 {}} 683do_test auth-1.98 { 684 execsql {SELECT name FROM sqlite_temp_master} 685} {t1} 686 687do_test auth-1.99 { 688 proc auth {code arg1 arg2 arg3 arg4} { 689 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 690 return SQLITE_DENY 691 } 692 return SQLITE_OK 693 } 694 catchsql { 695 CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2; 696 DROP VIEW v2 697 } 698} {1 {not authorized}} 699do_test auth-1.100 { 700 execsql {SELECT name FROM sqlite_master} 701} {t2 v2} 702do_test auth-1.101 { 703 proc auth {code arg1 arg2 arg3 arg4} { 704 if {$code=="SQLITE_DROP_VIEW"} { 705 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 706 return SQLITE_DENY 707 } 708 return SQLITE_OK 709 } 710 catchsql {DROP VIEW v2} 711} {1 {not authorized}} 712do_test auth-1.102 { 713 set ::authargs 714} {v2 {} main {}} 715do_test auth-1.103 { 716 execsql {SELECT name FROM sqlite_master} 717} {t2 v2} 718do_test auth-1.104 { 719 proc auth {code arg1 arg2 arg3 arg4} { 720 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 721 return SQLITE_IGNORE 722 } 723 return SQLITE_OK 724 } 725 catchsql {DROP VIEW v2} 726} {0 {}} 727do_test auth-1.105 { 728 execsql {SELECT name FROM sqlite_master} 729} {t2 v2} 730do_test auth-1.106 { 731 proc auth {code arg1 arg2 arg3 arg4} { 732 if {$code=="SQLITE_DROP_VIEW"} { 733 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 734 return SQLITE_IGNORE 735 } 736 return SQLITE_OK 737 } 738 catchsql {DROP VIEW v2} 739} {0 {}} 740do_test auth-1.107 { 741 set ::authargs 742} {v2 {} main {}} 743do_test auth-1.108 { 744 execsql {SELECT name FROM sqlite_master} 745} {t2 v2} 746do_test auth-1.109 { 747 proc auth {code arg1 arg2 arg3 arg4} { 748 if {$code=="SQLITE_DROP_VIEW"} { 749 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 750 return SQLITE_OK 751 } 752 return SQLITE_OK 753 } 754 catchsql {DROP VIEW v2} 755} {0 {}} 756do_test auth-1.110 { 757 set ::authargs 758} {v2 {} main {}} 759do_test auth-1.111 { 760 execsql {SELECT name FROM sqlite_master} 761} {t2} 762 763 764do_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}} 776do_test auth-1.113 { 777 execsql {SELECT name FROM sqlite_temp_master} 778} {t1 v1} 779do_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}} 789do_test auth-1.115 { 790 set ::authargs 791} {v1 {} temp {}} 792do_test auth-1.116 { 793 execsql {SELECT name FROM sqlite_temp_master} 794} {t1 v1} 795do_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 {}} 804do_test auth-1.118 { 805 execsql {SELECT name FROM sqlite_temp_master} 806} {t1 v1} 807do_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 {}} 817do_test auth-1.120 { 818 set ::authargs 819} {v1 {} temp {}} 820do_test auth-1.121 { 821 execsql {SELECT name FROM sqlite_temp_master} 822} {t1 v1} 823do_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 {}} 833do_test auth-1.123 { 834 set ::authargs 835} {v1 {} temp {}} 836do_test auth-1.124 { 837 execsql {SELECT name FROM sqlite_temp_master} 838} {t1} 839 840do_test auth-1.125 { 841 proc auth {code arg1 arg2 arg3 arg4} { 842 if {$code=="SQLITE_CREATE_TRIGGER"} { 843 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 844 return SQLITE_DENY 845 } 846 return SQLITE_OK 847 } 848 catchsql { 849 CREATE TRIGGER r2 DELETE on t2 BEGIN 850 SELECT NULL; 851 END; 852 } 853} {1 {not authorized}} 854do_test auth-1.126 { 855 set ::authargs 856} {r2 t2 main {}} 857do_test auth-1.127 { 858 execsql {SELECT name FROM sqlite_master} 859} {t2} 860do_test auth-1.128 { 861 proc auth {code arg1 arg2 arg3 arg4} { 862 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 863 return SQLITE_DENY 864 } 865 return SQLITE_OK 866 } 867 catchsql { 868 CREATE TRIGGER r2 DELETE on t2 BEGIN 869 SELECT NULL; 870 END; 871 } 872} {1 {not authorized}} 873do_test auth-1.129 { 874 execsql {SELECT name FROM sqlite_master} 875} {t2} 876do_test auth-1.130 { 877 proc auth {code arg1 arg2 arg3 arg4} { 878 if {$code=="SQLITE_CREATE_TRIGGER"} { 879 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 880 return SQLITE_IGNORE 881 } 882 return SQLITE_OK 883 } 884 catchsql { 885 CREATE TRIGGER r2 DELETE on t2 BEGIN 886 SELECT NULL; 887 END; 888 } 889} {0 {}} 890do_test auth-1.131 { 891 set ::authargs 892} {r2 t2 main {}} 893do_test auth-1.132 { 894 execsql {SELECT name FROM sqlite_master} 895} {t2} 896do_test auth-1.133 { 897 proc auth {code arg1 arg2 arg3 arg4} { 898 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 899 return SQLITE_IGNORE 900 } 901 return SQLITE_OK 902 } 903 catchsql { 904 CREATE TRIGGER r2 DELETE on t2 BEGIN 905 SELECT NULL; 906 END; 907 } 908} {0 {}} 909do_test auth-1.134 { 910 execsql {SELECT name FROM sqlite_master} 911} {t2} 912do_test auth-1.135 { 913 proc auth {code arg1 arg2 arg3 arg4} { 914 if {$code=="SQLITE_CREATE_TRIGGER"} { 915 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 916 return SQLITE_OK 917 } 918 return SQLITE_OK 919 } 920 catchsql { 921 CREATE TABLE tx(id); 922 CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN 923 INSERT INTO tx VALUES(NEW.rowid); 924 END; 925 } 926} {0 {}} 927do_test auth-1.136.1 { 928 set ::authargs 929} {r2 t2 main {}} 930do_test auth-1.136.2 { 931 execsql { 932 SELECT name FROM sqlite_master WHERE type='trigger' 933 } 934} {r2} 935do_test auth-1.136.3 { 936 proc auth {code arg1 arg2 arg3 arg4} { 937 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 938 return SQLITE_OK 939 } 940 set ::authargs {} 941 execsql { 942 INSERT INTO t2 VALUES(1,2,3); 943 } 944 set ::authargs 945} {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2} 946do_test auth-1.136.4 { 947 execsql { 948 SELECT * FROM tx; 949 } 950} {3} 951do_test auth-1.137 { 952 execsql {SELECT name FROM sqlite_master} 953} {t2 tx r2} 954do_test auth-1.138 { 955 proc auth {code arg1 arg2 arg3 arg4} { 956 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 957 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 958 return SQLITE_DENY 959 } 960 return SQLITE_OK 961 } 962 catchsql { 963 CREATE TRIGGER r1 DELETE on t1 BEGIN 964 SELECT NULL; 965 END; 966 } 967} {1 {not authorized}} 968do_test auth-1.139 { 969 set ::authargs 970} {r1 t1 temp {}} 971do_test auth-1.140 { 972 execsql {SELECT name FROM sqlite_temp_master} 973} {t1} 974do_test auth-1.141 { 975 proc auth {code arg1 arg2 arg3 arg4} { 976 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 977 return SQLITE_DENY 978 } 979 return SQLITE_OK 980 } 981 catchsql { 982 CREATE TRIGGER r1 DELETE on t1 BEGIN 983 SELECT NULL; 984 END; 985 } 986} {1 {not authorized}} 987do_test auth-1.142 { 988 execsql {SELECT name FROM sqlite_temp_master} 989} {t1} 990do_test auth-1.143 { 991 proc auth {code arg1 arg2 arg3 arg4} { 992 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 993 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 994 return SQLITE_IGNORE 995 } 996 return SQLITE_OK 997 } 998 catchsql { 999 CREATE TRIGGER r1 DELETE on t1 BEGIN 1000 SELECT NULL; 1001 END; 1002 } 1003} {0 {}} 1004do_test auth-1.144 { 1005 set ::authargs 1006} {r1 t1 temp {}} 1007do_test auth-1.145 { 1008 execsql {SELECT name FROM sqlite_temp_master} 1009} {t1} 1010do_test auth-1.146 { 1011 proc auth {code arg1 arg2 arg3 arg4} { 1012 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1013 return SQLITE_IGNORE 1014 } 1015 return SQLITE_OK 1016 } 1017 catchsql { 1018 CREATE TRIGGER r1 DELETE on t1 BEGIN 1019 SELECT NULL; 1020 END; 1021 } 1022} {0 {}} 1023do_test auth-1.147 { 1024 execsql {SELECT name FROM sqlite_temp_master} 1025} {t1} 1026do_test auth-1.148 { 1027 proc auth {code arg1 arg2 arg3 arg4} { 1028 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 1029 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1030 return SQLITE_OK 1031 } 1032 return SQLITE_OK 1033 } 1034 catchsql { 1035 CREATE TRIGGER r1 DELETE on t1 BEGIN 1036 SELECT NULL; 1037 END; 1038 } 1039} {0 {}} 1040do_test auth-1.149 { 1041 set ::authargs 1042} {r1 t1 temp {}} 1043do_test auth-1.150 { 1044 execsql {SELECT name FROM sqlite_temp_master} 1045} {t1 r1} 1046 1047do_test auth-1.151 { 1048 proc auth {code arg1 arg2 arg3 arg4} { 1049 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1050 return SQLITE_DENY 1051 } 1052 return SQLITE_OK 1053 } 1054 catchsql {DROP TRIGGER r2} 1055} {1 {not authorized}} 1056do_test auth-1.152 { 1057 execsql {SELECT name FROM sqlite_master} 1058} {t2 tx r2} 1059do_test auth-1.153 { 1060 proc auth {code arg1 arg2 arg3 arg4} { 1061 if {$code=="SQLITE_DROP_TRIGGER"} { 1062 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1063 return SQLITE_DENY 1064 } 1065 return SQLITE_OK 1066 } 1067 catchsql {DROP TRIGGER r2} 1068} {1 {not authorized}} 1069do_test auth-1.154 { 1070 set ::authargs 1071} {r2 t2 main {}} 1072do_test auth-1.155 { 1073 execsql {SELECT name FROM sqlite_master} 1074} {t2 tx r2} 1075do_test auth-1.156 { 1076 proc auth {code arg1 arg2 arg3 arg4} { 1077 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1078 return SQLITE_IGNORE 1079 } 1080 return SQLITE_OK 1081 } 1082 catchsql {DROP TRIGGER r2} 1083} {0 {}} 1084do_test auth-1.157 { 1085 execsql {SELECT name FROM sqlite_master} 1086} {t2 tx r2} 1087do_test auth-1.158 { 1088 proc auth {code arg1 arg2 arg3 arg4} { 1089 if {$code=="SQLITE_DROP_TRIGGER"} { 1090 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1091 return SQLITE_IGNORE 1092 } 1093 return SQLITE_OK 1094 } 1095 catchsql {DROP TRIGGER r2} 1096} {0 {}} 1097do_test auth-1.159 { 1098 set ::authargs 1099} {r2 t2 main {}} 1100do_test auth-1.160 { 1101 execsql {SELECT name FROM sqlite_master} 1102} {t2 tx r2} 1103do_test auth-1.161 { 1104 proc auth {code arg1 arg2 arg3 arg4} { 1105 if {$code=="SQLITE_DROP_TRIGGER"} { 1106 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1107 return SQLITE_OK 1108 } 1109 return SQLITE_OK 1110 } 1111 catchsql {DROP TRIGGER r2} 1112} {0 {}} 1113do_test auth-1.162 { 1114 set ::authargs 1115} {r2 t2 main {}} 1116do_test auth-1.163 { 1117 execsql { 1118 DROP TABLE tx; 1119 DELETE FROM t2 WHERE a=1 AND b=2 AND c=3; 1120 SELECT name FROM sqlite_master; 1121 } 1122} {t2} 1123 1124do_test auth-1.164 { 1125 proc auth {code arg1 arg2 arg3 arg4} { 1126 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1127 return SQLITE_DENY 1128 } 1129 return SQLITE_OK 1130 } 1131 catchsql {DROP TRIGGER r1} 1132} {1 {not authorized}} 1133do_test auth-1.165 { 1134 execsql {SELECT name FROM sqlite_temp_master} 1135} {t1 r1} 1136do_test auth-1.166 { 1137 proc auth {code arg1 arg2 arg3 arg4} { 1138 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1139 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1140 return SQLITE_DENY 1141 } 1142 return SQLITE_OK 1143 } 1144 catchsql {DROP TRIGGER r1} 1145} {1 {not authorized}} 1146do_test auth-1.167 { 1147 set ::authargs 1148} {r1 t1 temp {}} 1149do_test auth-1.168 { 1150 execsql {SELECT name FROM sqlite_temp_master} 1151} {t1 r1} 1152do_test auth-1.169 { 1153 proc auth {code arg1 arg2 arg3 arg4} { 1154 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1155 return SQLITE_IGNORE 1156 } 1157 return SQLITE_OK 1158 } 1159 catchsql {DROP TRIGGER r1} 1160} {0 {}} 1161do_test auth-1.170 { 1162 execsql {SELECT name FROM sqlite_temp_master} 1163} {t1 r1} 1164do_test auth-1.171 { 1165 proc auth {code arg1 arg2 arg3 arg4} { 1166 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1167 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1168 return SQLITE_IGNORE 1169 } 1170 return SQLITE_OK 1171 } 1172 catchsql {DROP TRIGGER r1} 1173} {0 {}} 1174do_test auth-1.172 { 1175 set ::authargs 1176} {r1 t1 temp {}} 1177do_test auth-1.173 { 1178 execsql {SELECT name FROM sqlite_temp_master} 1179} {t1 r1} 1180do_test auth-1.174 { 1181 proc auth {code arg1 arg2 arg3 arg4} { 1182 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1183 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1184 return SQLITE_OK 1185 } 1186 return SQLITE_OK 1187 } 1188 catchsql {DROP TRIGGER r1} 1189} {0 {}} 1190do_test auth-1.175 { 1191 set ::authargs 1192} {r1 t1 temp {}} 1193do_test auth-1.176 { 1194 execsql {SELECT name FROM sqlite_temp_master} 1195} {t1} 1196 1197do_test auth-1.177 { 1198 proc auth {code arg1 arg2 arg3 arg4} { 1199 if {$code=="SQLITE_CREATE_INDEX"} { 1200 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1201 return SQLITE_DENY 1202 } 1203 return SQLITE_OK 1204 } 1205 catchsql {CREATE INDEX i2 ON t2(a)} 1206} {1 {not authorized}} 1207do_test auth-1.178 { 1208 set ::authargs 1209} {i2 t2 main {}} 1210do_test auth-1.179 { 1211 execsql {SELECT name FROM sqlite_master} 1212} {t2} 1213do_test auth-1.180 { 1214 proc auth {code arg1 arg2 arg3 arg4} { 1215 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 1216 return SQLITE_DENY 1217 } 1218 return SQLITE_OK 1219 } 1220 catchsql {CREATE INDEX i2 ON t2(a)} 1221} {1 {not authorized}} 1222do_test auth-1.181 { 1223 execsql {SELECT name FROM sqlite_master} 1224} {t2} 1225do_test auth-1.182 { 1226 proc auth {code arg1 arg2 arg3 arg4} { 1227 if {$code=="SQLITE_CREATE_INDEX"} { 1228 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1229 return SQLITE_IGNORE 1230 } 1231 return SQLITE_OK 1232 } 1233 catchsql {CREATE INDEX i2 ON t2(b)} 1234} {0 {}} 1235do_test auth-1.183 { 1236 set ::authargs 1237} {i2 t2 main {}} 1238do_test auth-1.184 { 1239 execsql {SELECT name FROM sqlite_master} 1240} {t2} 1241do_test auth-1.185 { 1242 proc auth {code arg1 arg2 arg3 arg4} { 1243 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 1244 return SQLITE_IGNORE 1245 } 1246 return SQLITE_OK 1247 } 1248 catchsql {CREATE INDEX i2 ON t2(b)} 1249} {0 {}} 1250do_test auth-1.186 { 1251 execsql {SELECT name FROM sqlite_master} 1252} {t2} 1253do_test auth-1.187 { 1254 proc auth {code arg1 arg2 arg3 arg4} { 1255 if {$code=="SQLITE_CREATE_INDEX"} { 1256 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1257 return SQLITE_OK 1258 } 1259 return SQLITE_OK 1260 } 1261 catchsql {CREATE INDEX i2 ON t2(a)} 1262} {0 {}} 1263do_test auth-1.188 { 1264 set ::authargs 1265} {i2 t2 main {}} 1266do_test auth-1.189 { 1267 execsql {SELECT name FROM sqlite_master} 1268} {t2 i2} 1269 1270do_test auth-1.190 { 1271 proc auth {code arg1 arg2 arg3 arg4} { 1272 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1273 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1274 return SQLITE_DENY 1275 } 1276 return SQLITE_OK 1277 } 1278 catchsql {CREATE INDEX i1 ON t1(a)} 1279} {1 {not authorized}} 1280do_test auth-1.191 { 1281 set ::authargs 1282} {i1 t1 temp {}} 1283do_test auth-1.192 { 1284 execsql {SELECT name FROM sqlite_temp_master} 1285} {t1} 1286do_test auth-1.193 { 1287 proc auth {code arg1 arg2 arg3 arg4} { 1288 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1289 return SQLITE_DENY 1290 } 1291 return SQLITE_OK 1292 } 1293 catchsql {CREATE INDEX i1 ON t1(b)} 1294} {1 {not authorized}} 1295do_test auth-1.194 { 1296 execsql {SELECT name FROM sqlite_temp_master} 1297} {t1} 1298do_test auth-1.195 { 1299 proc auth {code arg1 arg2 arg3 arg4} { 1300 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1301 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1302 return SQLITE_IGNORE 1303 } 1304 return SQLITE_OK 1305 } 1306 catchsql {CREATE INDEX i1 ON t1(b)} 1307} {0 {}} 1308do_test auth-1.196 { 1309 set ::authargs 1310} {i1 t1 temp {}} 1311do_test auth-1.197 { 1312 execsql {SELECT name FROM sqlite_temp_master} 1313} {t1} 1314do_test auth-1.198 { 1315 proc auth {code arg1 arg2 arg3 arg4} { 1316 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1317 return SQLITE_IGNORE 1318 } 1319 return SQLITE_OK 1320 } 1321 catchsql {CREATE INDEX i1 ON t1(c)} 1322} {0 {}} 1323do_test auth-1.199 { 1324 execsql {SELECT name FROM sqlite_temp_master} 1325} {t1} 1326do_test auth-1.200 { 1327 proc auth {code arg1 arg2 arg3 arg4} { 1328 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1329 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1330 return SQLITE_OK 1331 } 1332 return SQLITE_OK 1333 } 1334 catchsql {CREATE INDEX i1 ON t1(a)} 1335} {0 {}} 1336do_test auth-1.201 { 1337 set ::authargs 1338} {i1 t1 temp {}} 1339do_test auth-1.202 { 1340 execsql {SELECT name FROM sqlite_temp_master} 1341} {t1 i1} 1342 1343do_test auth-1.203 { 1344 proc auth {code arg1 arg2 arg3 arg4} { 1345 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1346 return SQLITE_DENY 1347 } 1348 return SQLITE_OK 1349 } 1350 catchsql {DROP INDEX i2} 1351} {1 {not authorized}} 1352do_test auth-1.204 { 1353 execsql {SELECT name FROM sqlite_master} 1354} {t2 i2} 1355do_test auth-1.205 { 1356 proc auth {code arg1 arg2 arg3 arg4} { 1357 if {$code=="SQLITE_DROP_INDEX"} { 1358 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1359 return SQLITE_DENY 1360 } 1361 return SQLITE_OK 1362 } 1363 catchsql {DROP INDEX i2} 1364} {1 {not authorized}} 1365do_test auth-1.206 { 1366 set ::authargs 1367} {i2 t2 main {}} 1368do_test auth-1.207 { 1369 execsql {SELECT name FROM sqlite_master} 1370} {t2 i2} 1371do_test auth-1.208 { 1372 proc auth {code arg1 arg2 arg3 arg4} { 1373 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1374 return SQLITE_IGNORE 1375 } 1376 return SQLITE_OK 1377 } 1378 catchsql {DROP INDEX i2} 1379} {0 {}} 1380do_test auth-1.209 { 1381 execsql {SELECT name FROM sqlite_master} 1382} {t2 i2} 1383do_test auth-1.210 { 1384 proc auth {code arg1 arg2 arg3 arg4} { 1385 if {$code=="SQLITE_DROP_INDEX"} { 1386 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1387 return SQLITE_IGNORE 1388 } 1389 return SQLITE_OK 1390 } 1391 catchsql {DROP INDEX i2} 1392} {0 {}} 1393do_test auth-1.211 { 1394 set ::authargs 1395} {i2 t2 main {}} 1396do_test auth-1.212 { 1397 execsql {SELECT name FROM sqlite_master} 1398} {t2 i2} 1399do_test auth-1.213 { 1400 proc auth {code arg1 arg2 arg3 arg4} { 1401 if {$code=="SQLITE_DROP_INDEX"} { 1402 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1403 return SQLITE_OK 1404 } 1405 return SQLITE_OK 1406 } 1407 catchsql {DROP INDEX i2} 1408} {0 {}} 1409do_test auth-1.214 { 1410 set ::authargs 1411} {i2 t2 main {}} 1412do_test auth-1.215 { 1413 execsql {SELECT name FROM sqlite_master} 1414} {t2} 1415 1416do_test auth-1.216 { 1417 proc auth {code arg1 arg2 arg3 arg4} { 1418 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1419 return SQLITE_DENY 1420 } 1421 return SQLITE_OK 1422 } 1423 catchsql {DROP INDEX i1} 1424} {1 {not authorized}} 1425do_test auth-1.217 { 1426 execsql {SELECT name FROM sqlite_temp_master} 1427} {t1 i1} 1428do_test auth-1.218 { 1429 proc auth {code arg1 arg2 arg3 arg4} { 1430 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1431 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1432 return SQLITE_DENY 1433 } 1434 return SQLITE_OK 1435 } 1436 catchsql {DROP INDEX i1} 1437} {1 {not authorized}} 1438do_test auth-1.219 { 1439 set ::authargs 1440} {i1 t1 temp {}} 1441do_test auth-1.220 { 1442 execsql {SELECT name FROM sqlite_temp_master} 1443} {t1 i1} 1444do_test auth-1.221 { 1445 proc auth {code arg1 arg2 arg3 arg4} { 1446 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1447 return SQLITE_IGNORE 1448 } 1449 return SQLITE_OK 1450 } 1451 catchsql {DROP INDEX i1} 1452} {0 {}} 1453do_test auth-1.222 { 1454 execsql {SELECT name FROM sqlite_temp_master} 1455} {t1 i1} 1456do_test auth-1.223 { 1457 proc auth {code arg1 arg2 arg3 arg4} { 1458 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1459 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1460 return SQLITE_IGNORE 1461 } 1462 return SQLITE_OK 1463 } 1464 catchsql {DROP INDEX i1} 1465} {0 {}} 1466do_test auth-1.224 { 1467 set ::authargs 1468} {i1 t1 temp {}} 1469do_test auth-1.225 { 1470 execsql {SELECT name FROM sqlite_temp_master} 1471} {t1 i1} 1472do_test auth-1.226 { 1473 proc auth {code arg1 arg2 arg3 arg4} { 1474 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1475 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1476 return SQLITE_OK 1477 } 1478 return SQLITE_OK 1479 } 1480 catchsql {DROP INDEX i1} 1481} {0 {}} 1482do_test auth-1.227 { 1483 set ::authargs 1484} {i1 t1 temp {}} 1485do_test auth-1.228 { 1486 execsql {SELECT name FROM sqlite_temp_master} 1487} {t1} 1488 1489do_test auth-1.229 { 1490 proc auth {code arg1 arg2 arg3 arg4} { 1491 if {$code=="SQLITE_PRAGMA"} { 1492 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1493 return SQLITE_DENY 1494 } 1495 return SQLITE_OK 1496 } 1497 catchsql {PRAGMA full_column_names=on} 1498} {1 {not authorized}} 1499do_test auth-1.230 { 1500 set ::authargs 1501} {full_column_names on {} {}} 1502do_test auth-1.231 { 1503 execsql2 {SELECT a FROM t2} 1504} {a 11 a 7} 1505do_test auth-1.232 { 1506 proc auth {code arg1 arg2 arg3 arg4} { 1507 if {$code=="SQLITE_PRAGMA"} { 1508 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1509 return SQLITE_IGNORE 1510 } 1511 return SQLITE_OK 1512 } 1513 catchsql {PRAGMA full_column_names=on} 1514} {0 {}} 1515do_test auth-1.233 { 1516 set ::authargs 1517} {full_column_names on {} {}} 1518do_test auth-1.234 { 1519 execsql2 {SELECT a FROM t2} 1520} {a 11 a 7} 1521do_test auth-1.235 { 1522 proc auth {code arg1 arg2 arg3 arg4} { 1523 if {$code=="SQLITE_PRAGMA"} { 1524 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1525 return SQLITE_OK 1526 } 1527 return SQLITE_OK 1528 } 1529 catchsql {PRAGMA full_column_names=on} 1530} {0 {}} 1531do_test auth-1.236 { 1532 execsql2 {SELECT a FROM t2} 1533} {t2.a 11 t2.a 7} 1534do_test auth-1.237 { 1535 proc auth {code arg1 arg2 arg3 arg4} { 1536 if {$code=="SQLITE_PRAGMA"} { 1537 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1538 return SQLITE_OK 1539 } 1540 return SQLITE_OK 1541 } 1542 catchsql {PRAGMA full_column_names=OFF} 1543} {0 {}} 1544do_test auth-1.238 { 1545 set ::authargs 1546} {full_column_names OFF {} {}} 1547do_test auth-1.239 { 1548 execsql2 {SELECT a FROM t2} 1549} {a 11 a 7} 1550 1551do_test auth-1.240 { 1552 proc auth {code arg1 arg2 arg3 arg4} { 1553 if {$code=="SQLITE_TRANSACTION"} { 1554 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1555 return SQLITE_DENY 1556 } 1557 return SQLITE_OK 1558 } 1559 catchsql {BEGIN} 1560} {1 {not authorized}} 1561do_test auth-1.241 { 1562 set ::authargs 1563} {BEGIN {} {} {}} 1564do_test auth-1.242 { 1565 proc auth {code arg1 arg2 arg3 arg4} { 1566 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} { 1567 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1568 return SQLITE_DENY 1569 } 1570 return SQLITE_OK 1571 } 1572 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT} 1573} {1 {not authorized}} 1574do_test auth-1.243 { 1575 set ::authargs 1576} {COMMIT {} {} {}} 1577do_test auth-1.244 { 1578 execsql {SELECT * FROM t2} 1579} {11 2 33 7 8 9 44 55 66} 1580do_test auth-1.245 { 1581 catchsql {ROLLBACK} 1582} {1 {not authorized}} 1583do_test auth-1.246 { 1584 set ::authargs 1585} {ROLLBACK {} {} {}} 1586do_test auth-1.247 { 1587 catchsql {END TRANSACTION} 1588} {1 {not authorized}} 1589do_test auth-1.248 { 1590 set ::authargs 1591} {COMMIT {} {} {}} 1592do_test auth-1.249 { 1593 db authorizer {} 1594 catchsql {ROLLBACK} 1595} {0 {}} 1596do_test auth-1.250 { 1597 execsql {SELECT * FROM t2} 1598} {11 2 33 7 8 9} 1599 1600# ticket #340 - authorization for ATTACH and DETACH. 1601# 1602do_test auth-1.251 { 1603 db authorizer ::auth 1604 proc auth {code arg1 arg2 arg3 arg4} { 1605 if {$code=="SQLITE_ATTACH"} { 1606 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1607 } 1608 return SQLITE_OK 1609 } 1610 catchsql { 1611 ATTACH DATABASE ':memory:' AS test1 1612 } 1613} {0 {}} 1614do_test auth-1.252 { 1615 set ::authargs 1616} {:memory: {} {} {}} 1617do_test auth-1.253 { 1618 catchsql {DETACH DATABASE test1} 1619 proc auth {code arg1 arg2 arg3 arg4} { 1620 if {$code=="SQLITE_ATTACH"} { 1621 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1622 return SQLITE_DENY 1623 } 1624 return SQLITE_OK 1625 } 1626 catchsql { 1627 ATTACH DATABASE ':memory:' AS test1; 1628 } 1629} {1 {not authorized}} 1630do_test auth-1.254 { 1631 lindex [execsql {PRAGMA database_list}] 7 1632} {} 1633do_test auth-1.255 { 1634 catchsql {DETACH DATABASE test1} 1635 proc auth {code arg1 arg2 arg3 arg4} { 1636 if {$code=="SQLITE_ATTACH"} { 1637 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1638 return SQLITE_IGNORE 1639 } 1640 return SQLITE_OK 1641 } 1642 catchsql { 1643 ATTACH DATABASE ':memory:' AS test1; 1644 } 1645} {0 {}} 1646do_test auth-1.256 { 1647 lindex [execsql {PRAGMA database_list}] 7 1648} {} 1649do_test auth-1.257 { 1650 proc auth {code arg1 arg2 arg3 arg4} { 1651 if {$code=="SQLITE_DETACH"} { 1652 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1653 return SQLITE_OK 1654 } 1655 return SQLITE_OK 1656 } 1657 execsql {ATTACH DATABASE ':memory:' AS test1} 1658 catchsql { 1659 DETACH DATABASE test1; 1660 } 1661} {0 {}} 1662do_test auth-1.258 { 1663 lindex [execsql {PRAGMA database_list}] 7 1664} {} 1665do_test auth-1.259 { 1666 execsql {ATTACH DATABASE ':memory:' AS test1} 1667 proc auth {code arg1 arg2 arg3 arg4} { 1668 if {$code=="SQLITE_DETACH"} { 1669 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1670 return SQLITE_IGNORE 1671 } 1672 return SQLITE_OK 1673 } 1674 catchsql { 1675 DETACH DATABASE test1; 1676 } 1677} {0 {}} 1678do_test auth-1.260 { 1679 lindex [execsql {PRAGMA database_list}] 7 1680} {test1} 1681do_test auth-1.261 { 1682 proc auth {code arg1 arg2 arg3 arg4} { 1683 if {$code=="SQLITE_DETACH"} { 1684 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1685 return SQLITE_DENY 1686 } 1687 return SQLITE_OK 1688 } 1689 catchsql { 1690 DETACH DATABASE test1; 1691 } 1692} {1 {not authorized}} 1693do_test auth-1.262 { 1694 lindex [execsql {PRAGMA database_list}] 7 1695} {test1} 1696db authorizer {} 1697execsql {DETACH DATABASE test1} 1698 1699 1700do_test auth-2.1 { 1701 proc auth {code arg1 arg2 arg3 arg4} { 1702 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 1703 return SQLITE_DENY 1704 } 1705 return SQLITE_OK 1706 } 1707 db authorizer ::auth 1708 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)} 1709 catchsql {SELECT * FROM t3} 1710} {1 {access to t3.x is prohibited}} 1711do_test auth-2.1 { 1712 catchsql {SELECT y,z FROM t3} 1713} {0 {}} 1714do_test auth-2.2 { 1715 catchsql {SELECT ROWID,y,z FROM t3} 1716} {1 {access to t3.x is prohibited}} 1717do_test auth-2.3 { 1718 catchsql {SELECT OID,y,z FROM t3} 1719} {1 {access to t3.x is prohibited}} 1720do_test auth-2.4 { 1721 proc auth {code arg1 arg2 arg3 arg4} { 1722 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 1723 return SQLITE_IGNORE 1724 } 1725 return SQLITE_OK 1726 } 1727 execsql {INSERT INTO t3 VALUES(44,55,66)} 1728 catchsql {SELECT * FROM t3} 1729} {0 {{} 55 66}} 1730do_test auth-2.5 { 1731 catchsql {SELECT rowid,y,z FROM t3} 1732} {0 {{} 55 66}} 1733do_test auth-2.6 { 1734 proc auth {code arg1 arg2 arg3 arg4} { 1735 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} { 1736 return SQLITE_IGNORE 1737 } 1738 return SQLITE_OK 1739 } 1740 catchsql {SELECT * FROM t3} 1741} {0 {44 55 66}} 1742do_test auth-2.7 { 1743 catchsql {SELECT ROWID,y,z FROM t3} 1744} {0 {44 55 66}} 1745do_test auth-2.8 { 1746 proc auth {code arg1 arg2 arg3 arg4} { 1747 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 1748 return SQLITE_IGNORE 1749 } 1750 return SQLITE_OK 1751 } 1752 catchsql {SELECT ROWID,b,c FROM t2} 1753} {0 {{} 2 33 {} 8 9}} 1754do_test auth-2.9.1 { 1755 proc auth {code arg1 arg2 arg3 arg4} { 1756 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 1757 return bogus 1758 } 1759 return SQLITE_OK 1760 } 1761 catchsql {SELECT ROWID,b,c FROM t2} 1762} {1 {illegal return value (999) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}} 1763do_test auth-2.9.2 { 1764 db errorcode 1765} {21} 1766do_test auth-2.10 { 1767 proc auth {code arg1 arg2 arg3 arg4} { 1768 if {$code=="SQLITE_SELECT"} { 1769 return bogus 1770 } 1771 return SQLITE_OK 1772 } 1773 catchsql {SELECT ROWID,b,c FROM t2} 1774} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}} 1775do_test auth-2.11.1 { 1776 proc auth {code arg1 arg2 arg3 arg4} { 1777 if {$code=="SQLITE_READ" && $arg2=="a"} { 1778 return SQLITE_IGNORE 1779 } 1780 return SQLITE_OK 1781 } 1782 catchsql {SELECT * FROM t2, t3} 1783} {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}} 1784do_test auth-2.11.2 { 1785 proc auth {code arg1 arg2 arg3 arg4} { 1786 if {$code=="SQLITE_READ" && $arg2=="x"} { 1787 return SQLITE_IGNORE 1788 } 1789 return SQLITE_OK 1790 } 1791 catchsql {SELECT * FROM t2, t3} 1792} {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}} 1793 1794# Make sure the OLD and NEW pseudo-tables of a trigger get authorized. 1795# 1796do_test auth-3.1 { 1797 proc auth {code arg1 arg2 arg3 arg4} { 1798 return SQLITE_OK 1799 } 1800 execsql { 1801 CREATE TABLE tx(a1,a2,b1,b2,c1,c2); 1802 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 1803 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c); 1804 END; 1805 UPDATE t2 SET a=a+1; 1806 SELECT * FROM tx; 1807 } 1808} {11 12 2 2 33 33 7 8 8 8 9 9} 1809do_test auth-3.2 { 1810 proc auth {code arg1 arg2 arg3 arg4} { 1811 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} { 1812 return SQLITE_IGNORE 1813 } 1814 return SQLITE_OK 1815 } 1816 execsql { 1817 DELETE FROM tx; 1818 UPDATE t2 SET a=a+100; 1819 SELECT * FROM tx; 1820 } 1821} {12 112 2 2 {} {} 8 108 8 8 {} {}} 1822 1823# Make sure the names of views and triggers are passed on on arg4. 1824# 1825do_test auth-4.1 { 1826 proc auth {code arg1 arg2 arg3 arg4} { 1827 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 1828 return SQLITE_OK 1829 } 1830 set authargs {} 1831 execsql { 1832 UPDATE t2 SET a=a+1; 1833 } 1834 set authargs 1835} [list \ 1836 SQLITE_READ t2 a main {} \ 1837 SQLITE_UPDATE t2 a main {} \ 1838 SQLITE_INSERT tx {} main r1 \ 1839 SQLITE_READ t2 a main r1 \ 1840 SQLITE_READ t2 a main r1 \ 1841 SQLITE_READ t2 b main r1 \ 1842 SQLITE_READ t2 b main r1 \ 1843 SQLITE_READ t2 c main r1 \ 1844 SQLITE_READ t2 c main r1] 1845do_test auth-4.2 { 1846 execsql { 1847 CREATE VIEW v1 AS SELECT a+b AS x FROM t2; 1848 CREATE TABLE v1chng(x1,x2); 1849 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN 1850 INSERT INTO v1chng VALUES(OLD.x,NEW.x); 1851 END; 1852 SELECT * FROM v1; 1853 } 1854} {115 117} 1855do_test auth-4.3 { 1856 set authargs {} 1857 execsql { 1858 UPDATE v1 SET x=1 WHERE x=117 1859 } 1860 set authargs 1861} [list \ 1862 SQLITE_UPDATE v1 x main {} \ 1863 SQLITE_READ v1 x main {} \ 1864 SQLITE_SELECT {} {} {} v1 \ 1865 SQLITE_READ t2 a main v1 \ 1866 SQLITE_READ t2 b main v1 \ 1867 SQLITE_INSERT v1chng {} main r2 \ 1868 SQLITE_READ v1 x main r2 \ 1869 SQLITE_READ v1 x main r2] 1870do_test auth-4.4 { 1871 execsql { 1872 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN 1873 INSERT INTO v1chng VALUES(OLD.x,NULL); 1874 END; 1875 SELECT * FROM v1; 1876 } 1877} {115 117} 1878do_test auth-4.5 { 1879 set authargs {} 1880 execsql { 1881 DELETE FROM v1 WHERE x=117 1882 } 1883 set authargs 1884} [list \ 1885 SQLITE_DELETE v1 {} main {} \ 1886 SQLITE_READ v1 x main {} \ 1887 SQLITE_SELECT {} {} {} v1 \ 1888 SQLITE_READ t2 a main v1 \ 1889 SQLITE_READ t2 b main v1 \ 1890 SQLITE_INSERT v1chng {} main r3 \ 1891 SQLITE_READ v1 x main r3] 1892 1893finish_test 1894