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