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