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