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