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 sqlite3_set_authorizer() API 13# and related functionality. 14# 15# $Id: auth.test,v 1.41 2008/02/12 16:52:14 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. 23if {[catch {db auth {}} msg]} { 24 finish_test 25 return 26} 27 28rename proc proc_real 29proc_real proc {name arguments script} { 30 proc_real $name $arguments $script 31 if {$name=="auth"} { 32 db authorizer ::auth 33 } 34} 35 36do_test auth-1.1.1 { 37 db close 38 set ::DB [sqlite3 db test.db] 39 proc auth {code arg1 arg2 arg3 arg4} { 40 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 41 return SQLITE_DENY 42 } 43 return SQLITE_OK 44 } 45 db authorizer ::auth 46 catchsql {CREATE TABLE t1(a,b,c)} 47} {1 {not authorized}} 48do_test auth-1.1.2 { 49 db errorcode 50} {23} 51do_test auth-1.1.3 { 52 db authorizer 53} {::auth} 54do_test auth-1.1.4 { 55 # Ticket #896. 56 catchsql { 57 SELECT x; 58 } 59} {1 {no such column: x}} 60do_test auth-1.2 { 61 execsql {SELECT name FROM sqlite_master} 62} {} 63do_test auth-1.3.1 { 64 proc auth {code arg1 arg2 arg3 arg4} { 65 if {$code=="SQLITE_CREATE_TABLE"} { 66 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 67 return SQLITE_DENY 68 } 69 return SQLITE_OK 70 } 71 catchsql {CREATE TABLE t1(a,b,c)} 72} {1 {not authorized}} 73do_test auth-1.3.2 { 74 db errorcode 75} {23} 76do_test auth-1.3.3 { 77 set ::authargs 78} {t1 {} main {}} 79do_test auth-1.4 { 80 execsql {SELECT name FROM sqlite_master} 81} {} 82 83ifcapable tempdb { 84 do_test auth-1.5 { 85 proc auth {code arg1 arg2 arg3 arg4} { 86 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 87 return SQLITE_DENY 88 } 89 return SQLITE_OK 90 } 91 catchsql {CREATE TEMP TABLE t1(a,b,c)} 92 } {1 {not authorized}} 93 do_test auth-1.6 { 94 execsql {SELECT name FROM sqlite_temp_master} 95 } {} 96 do_test auth-1.7.1 { 97 proc auth {code arg1 arg2 arg3 arg4} { 98 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 99 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 100 return SQLITE_DENY 101 } 102 return SQLITE_OK 103 } 104 catchsql {CREATE TEMP TABLE t1(a,b,c)} 105 } {1 {not authorized}} 106 do_test auth-1.7.2 { 107 set ::authargs 108 } {t1 {} temp {}} 109 do_test auth-1.8 { 110 execsql {SELECT name FROM sqlite_temp_master} 111 } {} 112} 113 114do_test auth-1.9 { 115 proc auth {code arg1 arg2 arg3 arg4} { 116 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 117 return SQLITE_IGNORE 118 } 119 return SQLITE_OK 120 } 121 catchsql {CREATE TABLE t1(a,b,c)} 122} {0 {}} 123do_test auth-1.10 { 124 execsql {SELECT name FROM sqlite_master} 125} {} 126do_test auth-1.11 { 127 proc auth {code arg1 arg2 arg3 arg4} { 128 if {$code=="SQLITE_CREATE_TABLE"} { 129 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 130 return SQLITE_IGNORE 131 } 132 return SQLITE_OK 133 } 134 catchsql {CREATE TABLE t1(a,b,c)} 135} {0 {}} 136do_test auth-1.12 { 137 execsql {SELECT name FROM sqlite_master} 138} {} 139 140ifcapable tempdb { 141 do_test auth-1.13 { 142 proc auth {code arg1 arg2 arg3 arg4} { 143 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 144 return SQLITE_IGNORE 145 } 146 return SQLITE_OK 147 } 148 catchsql {CREATE TEMP TABLE t1(a,b,c)} 149 } {0 {}} 150 do_test auth-1.14 { 151 execsql {SELECT name FROM sqlite_temp_master} 152 } {} 153 do_test auth-1.15 { 154 proc auth {code arg1 arg2 arg3 arg4} { 155 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 156 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 157 return SQLITE_IGNORE 158 } 159 return SQLITE_OK 160 } 161 catchsql {CREATE TEMP TABLE t1(a,b,c)} 162 } {0 {}} 163 do_test auth-1.16 { 164 execsql {SELECT name FROM sqlite_temp_master} 165 } {} 166 167 do_test auth-1.17 { 168 proc auth {code arg1 arg2 arg3 arg4} { 169 if {$code=="SQLITE_CREATE_TABLE"} { 170 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 171 return SQLITE_DENY 172 } 173 return SQLITE_OK 174 } 175 catchsql {CREATE TEMP TABLE t1(a,b,c)} 176 } {0 {}} 177 do_test auth-1.18 { 178 execsql {SELECT name FROM sqlite_temp_master} 179 } {t1} 180} 181 182do_test auth-1.19.1 { 183 set ::authargs {} 184 proc auth {code arg1 arg2 arg3 arg4} { 185 if {$code=="SQLITE_CREATE_TEMP_TABLE"} { 186 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 187 return SQLITE_DENY 188 } 189 return SQLITE_OK 190 } 191 catchsql {CREATE TABLE t2(a,b,c)} 192} {0 {}} 193do_test auth-1.19.2 { 194 set ::authargs 195} {} 196do_test auth-1.20 { 197 execsql {SELECT name FROM sqlite_master} 198} {t2} 199 200do_test auth-1.21.1 { 201 proc auth {code arg1 arg2 arg3 arg4} { 202 if {$code=="SQLITE_DROP_TABLE"} { 203 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 204 return SQLITE_DENY 205 } 206 return SQLITE_OK 207 } 208 catchsql {DROP TABLE t2} 209} {1 {not authorized}} 210do_test auth-1.21.2 { 211 set ::authargs 212} {t2 {} main {}} 213do_test auth-1.22 { 214 execsql {SELECT name FROM sqlite_master} 215} {t2} 216do_test auth-1.23.1 { 217 proc auth {code arg1 arg2 arg3 arg4} { 218 if {$code=="SQLITE_DROP_TABLE"} { 219 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 220 return SQLITE_IGNORE 221 } 222 return SQLITE_OK 223 } 224 catchsql {DROP TABLE t2} 225} {0 {}} 226do_test auth-1.23.2 { 227 set ::authargs 228} {t2 {} main {}} 229do_test auth-1.24 { 230 execsql {SELECT name FROM sqlite_master} 231} {t2} 232 233ifcapable tempdb { 234 do_test auth-1.25 { 235 proc auth {code arg1 arg2 arg3 arg4} { 236 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 237 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 238 return SQLITE_DENY 239 } 240 return SQLITE_OK 241 } 242 catchsql {DROP TABLE t1} 243 } {1 {not authorized}} 244 do_test auth-1.26 { 245 execsql {SELECT name FROM sqlite_temp_master} 246 } {t1} 247 do_test auth-1.27 { 248 proc auth {code arg1 arg2 arg3 arg4} { 249 if {$code=="SQLITE_DROP_TEMP_TABLE"} { 250 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 251 return SQLITE_IGNORE 252 } 253 return SQLITE_OK 254 } 255 catchsql {DROP TABLE t1} 256 } {0 {}} 257 do_test auth-1.28 { 258 execsql {SELECT name FROM sqlite_temp_master} 259 } {t1} 260} 261 262do_test auth-1.29 { 263 proc auth {code arg1 arg2 arg3 arg4} { 264 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 265 return SQLITE_DENY 266 } 267 return SQLITE_OK 268 } 269 catchsql {INSERT INTO t2 VALUES(1,2,3)} 270} {1 {not authorized}} 271do_test auth-1.30 { 272 execsql {SELECT * FROM t2} 273} {} 274do_test auth-1.31 { 275 proc auth {code arg1 arg2 arg3 arg4} { 276 if {$code=="SQLITE_INSERT" && $arg1=="t2"} { 277 return SQLITE_IGNORE 278 } 279 return SQLITE_OK 280 } 281 catchsql {INSERT INTO t2 VALUES(1,2,3)} 282} {0 {}} 283do_test auth-1.32 { 284 execsql {SELECT * FROM t2} 285} {} 286do_test auth-1.33 { 287 proc auth {code arg1 arg2 arg3 arg4} { 288 if {$code=="SQLITE_INSERT" && $arg1=="t1"} { 289 return SQLITE_IGNORE 290 } 291 return SQLITE_OK 292 } 293 catchsql {INSERT INTO t2 VALUES(1,2,3)} 294} {0 {}} 295do_test auth-1.34 { 296 execsql {SELECT * FROM t2} 297} {1 2 3} 298 299do_test auth-1.35.1 { 300 proc auth {code arg1 arg2 arg3 arg4} { 301 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 302 return SQLITE_DENY 303 } 304 return SQLITE_OK 305 } 306 catchsql {SELECT * FROM t2} 307} {1 {access to t2.b is prohibited}} 308ifcapable attach { 309 do_test auth-1.35.2 { 310 execsql {ATTACH DATABASE 'test.db' AS two} 311 catchsql {SELECT * FROM two.t2} 312 } {1 {access to two.t2.b is prohibited}} 313 execsql {DETACH DATABASE two} 314} 315do_test auth-1.36 { 316 proc auth {code arg1 arg2 arg3 arg4} { 317 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 318 return SQLITE_IGNORE 319 } 320 return SQLITE_OK 321 } 322 catchsql {SELECT * FROM t2} 323} {0 {1 {} 3}} 324do_test auth-1.37 { 325 proc auth {code arg1 arg2 arg3 arg4} { 326 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 327 return SQLITE_IGNORE 328 } 329 return SQLITE_OK 330 } 331 catchsql {SELECT * FROM t2 WHERE b=2} 332} {0 {}} 333do_test auth-1.38 { 334 proc auth {code arg1 arg2 arg3 arg4} { 335 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="a"} { 336 return SQLITE_IGNORE 337 } 338 return SQLITE_OK 339 } 340 catchsql {SELECT * FROM t2 WHERE b=2} 341} {0 {{} 2 3}} 342do_test auth-1.39 { 343 proc auth {code arg1 arg2 arg3 arg4} { 344 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 345 return SQLITE_IGNORE 346 } 347 return SQLITE_OK 348 } 349 catchsql {SELECT * FROM t2 WHERE b IS NULL} 350} {0 {1 {} 3}} 351do_test auth-1.40 { 352 proc auth {code arg1 arg2 arg3 arg4} { 353 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="b"} { 354 return SQLITE_DENY 355 } 356 return SQLITE_OK 357 } 358 catchsql {SELECT a,c FROM t2 WHERE b IS NULL} 359} {1 {access to t2.b is prohibited}} 360 361do_test auth-1.41 { 362 proc auth {code arg1 arg2 arg3 arg4} { 363 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 364 return SQLITE_DENY 365 } 366 return SQLITE_OK 367 } 368 catchsql {UPDATE t2 SET a=11} 369} {0 {}} 370do_test auth-1.42 { 371 execsql {SELECT * FROM t2} 372} {11 2 3} 373do_test auth-1.43 { 374 proc auth {code arg1 arg2 arg3 arg4} { 375 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 376 return SQLITE_DENY 377 } 378 return SQLITE_OK 379 } 380 catchsql {UPDATE t2 SET b=22, c=33} 381} {1 {not authorized}} 382do_test auth-1.44 { 383 execsql {SELECT * FROM t2} 384} {11 2 3} 385do_test auth-1.45 { 386 proc auth {code arg1 arg2 arg3 arg4} { 387 if {$code=="SQLITE_UPDATE" && $arg1=="t2" && $arg2=="b"} { 388 return SQLITE_IGNORE 389 } 390 return SQLITE_OK 391 } 392 catchsql {UPDATE t2 SET b=22, c=33} 393} {0 {}} 394do_test auth-1.46 { 395 execsql {SELECT * FROM t2} 396} {11 2 33} 397 398do_test auth-1.47 { 399 proc auth {code arg1 arg2 arg3 arg4} { 400 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 401 return SQLITE_DENY 402 } 403 return SQLITE_OK 404 } 405 catchsql {DELETE FROM t2 WHERE a=11} 406} {1 {not authorized}} 407do_test auth-1.48 { 408 execsql {SELECT * FROM t2} 409} {11 2 33} 410do_test auth-1.49 { 411 proc auth {code arg1 arg2 arg3 arg4} { 412 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 413 return SQLITE_IGNORE 414 } 415 return SQLITE_OK 416 } 417 catchsql {DELETE FROM t2 WHERE a=11} 418} {0 {}} 419do_test auth-1.50 { 420 execsql {SELECT * FROM t2} 421} {11 2 33} 422 423do_test auth-1.51 { 424 proc auth {code arg1 arg2 arg3 arg4} { 425 if {$code=="SQLITE_SELECT"} { 426 return SQLITE_DENY 427 } 428 return SQLITE_OK 429 } 430 catchsql {SELECT * FROM t2} 431} {1 {not authorized}} 432do_test auth-1.52 { 433 proc auth {code arg1 arg2 arg3 arg4} { 434 if {$code=="SQLITE_SELECT"} { 435 return SQLITE_IGNORE 436 } 437 return SQLITE_OK 438 } 439 catchsql {SELECT * FROM t2} 440} {0 {}} 441do_test auth-1.53 { 442 proc auth {code arg1 arg2 arg3 arg4} { 443 if {$code=="SQLITE_SELECT"} { 444 return SQLITE_OK 445 } 446 return SQLITE_OK 447 } 448 catchsql {SELECT * FROM t2} 449} {0 {11 2 33}} 450 451# Update for version 3: There used to be a handful of test here that 452# tested the authorisation callback with the COPY command. The following 453# test makes the same database modifications as they used to. 454do_test auth-1.54 { 455 execsql {INSERT INTO t2 VALUES(7, 8, 9);} 456} {} 457do_test auth-1.55 { 458 execsql {SELECT * FROM t2} 459} {11 2 33 7 8 9} 460 461do_test auth-1.63 { 462 proc auth {code arg1 arg2 arg3 arg4} { 463 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 464 return SQLITE_DENY 465 } 466 return SQLITE_OK 467 } 468 catchsql {DROP TABLE t2} 469} {1 {not authorized}} 470do_test auth-1.64 { 471 execsql {SELECT name FROM sqlite_master} 472} {t2} 473do_test auth-1.65 { 474 proc auth {code arg1 arg2 arg3 arg4} { 475 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 476 return SQLITE_DENY 477 } 478 return SQLITE_OK 479 } 480 catchsql {DROP TABLE t2} 481} {1 {not authorized}} 482do_test auth-1.66 { 483 execsql {SELECT name FROM sqlite_master} 484} {t2} 485 486ifcapable tempdb { 487 do_test auth-1.67 { 488 proc auth {code arg1 arg2 arg3 arg4} { 489 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 490 return SQLITE_DENY 491 } 492 return SQLITE_OK 493 } 494 catchsql {DROP TABLE t1} 495 } {1 {not authorized}} 496 do_test auth-1.68 { 497 execsql {SELECT name FROM sqlite_temp_master} 498 } {t1} 499 do_test auth-1.69 { 500 proc auth {code arg1 arg2 arg3 arg4} { 501 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 502 return SQLITE_DENY 503 } 504 return SQLITE_OK 505 } 506 catchsql {DROP TABLE t1} 507 } {1 {not authorized}} 508 do_test auth-1.70 { 509 execsql {SELECT name FROM sqlite_temp_master} 510 } {t1} 511} 512 513do_test auth-1.71 { 514 proc auth {code arg1 arg2 arg3 arg4} { 515 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 516 return SQLITE_IGNORE 517 } 518 return SQLITE_OK 519 } 520 catchsql {DROP TABLE t2} 521} {0 {}} 522do_test auth-1.72 { 523 execsql {SELECT name FROM sqlite_master} 524} {t2} 525do_test auth-1.73 { 526 proc auth {code arg1 arg2 arg3 arg4} { 527 if {$code=="SQLITE_DELETE" && $arg1=="t2"} { 528 return SQLITE_IGNORE 529 } 530 return SQLITE_OK 531 } 532 catchsql {DROP TABLE t2} 533} {0 {}} 534do_test auth-1.74 { 535 execsql {SELECT name FROM sqlite_master} 536} {t2} 537 538ifcapable tempdb { 539 do_test auth-1.75 { 540 proc auth {code arg1 arg2 arg3 arg4} { 541 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 542 return SQLITE_IGNORE 543 } 544 return SQLITE_OK 545 } 546 catchsql {DROP TABLE t1} 547 } {0 {}} 548 do_test auth-1.76 { 549 execsql {SELECT name FROM sqlite_temp_master} 550 } {t1} 551 do_test auth-1.77 { 552 proc auth {code arg1 arg2 arg3 arg4} { 553 if {$code=="SQLITE_DELETE" && $arg1=="t1"} { 554 return SQLITE_IGNORE 555 } 556 return SQLITE_OK 557 } 558 catchsql {DROP TABLE t1} 559 } {0 {}} 560 do_test auth-1.78 { 561 execsql {SELECT name FROM sqlite_temp_master} 562 } {t1} 563} 564 565# Test cases auth-1.79 to auth-1.124 test creating and dropping views. 566# Omit these if the library was compiled with views omitted. 567ifcapable view { 568do_test auth-1.79 { 569 proc auth {code arg1 arg2 arg3 arg4} { 570 if {$code=="SQLITE_CREATE_VIEW"} { 571 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 572 return SQLITE_DENY 573 } 574 return SQLITE_OK 575 } 576 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 577} {1 {not authorized}} 578do_test auth-1.80 { 579 set ::authargs 580} {v1 {} main {}} 581do_test auth-1.81 { 582 execsql {SELECT name FROM sqlite_master} 583} {t2} 584do_test auth-1.82 { 585 proc auth {code arg1 arg2 arg3 arg4} { 586 if {$code=="SQLITE_CREATE_VIEW"} { 587 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 588 return SQLITE_IGNORE 589 } 590 return SQLITE_OK 591 } 592 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 593} {0 {}} 594do_test auth-1.83 { 595 set ::authargs 596} {v1 {} main {}} 597do_test auth-1.84 { 598 execsql {SELECT name FROM sqlite_master} 599} {t2} 600 601ifcapable tempdb { 602 do_test auth-1.85 { 603 proc auth {code arg1 arg2 arg3 arg4} { 604 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 605 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 606 return SQLITE_DENY 607 } 608 return SQLITE_OK 609 } 610 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 611 } {1 {not authorized}} 612 do_test auth-1.86 { 613 set ::authargs 614 } {v1 {} temp {}} 615 do_test auth-1.87 { 616 execsql {SELECT name FROM sqlite_temp_master} 617 } {t1} 618 do_test auth-1.88 { 619 proc auth {code arg1 arg2 arg3 arg4} { 620 if {$code=="SQLITE_CREATE_TEMP_VIEW"} { 621 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 622 return SQLITE_IGNORE 623 } 624 return SQLITE_OK 625 } 626 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 627 } {0 {}} 628 do_test auth-1.89 { 629 set ::authargs 630 } {v1 {} temp {}} 631 do_test auth-1.90 { 632 execsql {SELECT name FROM sqlite_temp_master} 633 } {t1} 634} 635 636do_test auth-1.91 { 637 proc auth {code arg1 arg2 arg3 arg4} { 638 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 639 return SQLITE_DENY 640 } 641 return SQLITE_OK 642 } 643 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 644} {1 {not authorized}} 645do_test auth-1.92 { 646 execsql {SELECT name FROM sqlite_master} 647} {t2} 648do_test auth-1.93 { 649 proc auth {code arg1 arg2 arg3 arg4} { 650 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 651 return SQLITE_IGNORE 652 } 653 return SQLITE_OK 654 } 655 catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2} 656} {0 {}} 657do_test auth-1.94 { 658 execsql {SELECT name FROM sqlite_master} 659} {t2} 660 661ifcapable tempdb { 662 do_test auth-1.95 { 663 proc auth {code arg1 arg2 arg3 arg4} { 664 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 665 return SQLITE_DENY 666 } 667 return SQLITE_OK 668 } 669 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 670 } {1 {not authorized}} 671 do_test auth-1.96 { 672 execsql {SELECT name FROM sqlite_temp_master} 673 } {t1} 674 do_test auth-1.97 { 675 proc auth {code arg1 arg2 arg3 arg4} { 676 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 677 return SQLITE_IGNORE 678 } 679 return SQLITE_OK 680 } 681 catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2} 682 } {0 {}} 683 do_test auth-1.98 { 684 execsql {SELECT name FROM sqlite_temp_master} 685 } {t1} 686} 687 688do_test auth-1.99 { 689 proc auth {code arg1 arg2 arg3 arg4} { 690 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 691 return SQLITE_DENY 692 } 693 return SQLITE_OK 694 } 695 catchsql { 696 CREATE VIEW v2 AS SELECT a+1,b+1 FROM t2; 697 DROP VIEW v2 698 } 699} {1 {not authorized}} 700do_test auth-1.100 { 701 execsql {SELECT name FROM sqlite_master} 702} {t2 v2} 703do_test auth-1.101 { 704 proc auth {code arg1 arg2 arg3 arg4} { 705 if {$code=="SQLITE_DROP_VIEW"} { 706 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 707 return SQLITE_DENY 708 } 709 return SQLITE_OK 710 } 711 catchsql {DROP VIEW v2} 712} {1 {not authorized}} 713do_test auth-1.102 { 714 set ::authargs 715} {v2 {} main {}} 716do_test auth-1.103 { 717 execsql {SELECT name FROM sqlite_master} 718} {t2 v2} 719do_test auth-1.104 { 720 proc auth {code arg1 arg2 arg3 arg4} { 721 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 722 return SQLITE_IGNORE 723 } 724 return SQLITE_OK 725 } 726 catchsql {DROP VIEW v2} 727} {0 {}} 728do_test auth-1.105 { 729 execsql {SELECT name FROM sqlite_master} 730} {t2 v2} 731do_test auth-1.106 { 732 proc auth {code arg1 arg2 arg3 arg4} { 733 if {$code=="SQLITE_DROP_VIEW"} { 734 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 735 return SQLITE_IGNORE 736 } 737 return SQLITE_OK 738 } 739 catchsql {DROP VIEW v2} 740} {0 {}} 741do_test auth-1.107 { 742 set ::authargs 743} {v2 {} main {}} 744do_test auth-1.108 { 745 execsql {SELECT name FROM sqlite_master} 746} {t2 v2} 747do_test auth-1.109 { 748 proc auth {code arg1 arg2 arg3 arg4} { 749 if {$code=="SQLITE_DROP_VIEW"} { 750 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 751 return SQLITE_OK 752 } 753 return SQLITE_OK 754 } 755 catchsql {DROP VIEW v2} 756} {0 {}} 757do_test auth-1.110 { 758 set ::authargs 759} {v2 {} main {}} 760do_test auth-1.111 { 761 execsql {SELECT name FROM sqlite_master} 762} {t2} 763 764 765ifcapable tempdb { 766 do_test auth-1.112 { 767 proc auth {code arg1 arg2 arg3 arg4} { 768 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 769 return SQLITE_DENY 770 } 771 return SQLITE_OK 772 } 773 catchsql { 774 CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1; 775 DROP VIEW v1 776 } 777 } {1 {not authorized}} 778 do_test auth-1.113 { 779 execsql {SELECT name FROM sqlite_temp_master} 780 } {t1 v1} 781 do_test auth-1.114 { 782 proc auth {code arg1 arg2 arg3 arg4} { 783 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 784 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 785 return SQLITE_DENY 786 } 787 return SQLITE_OK 788 } 789 catchsql {DROP VIEW v1} 790 } {1 {not authorized}} 791 do_test auth-1.115 { 792 set ::authargs 793 } {v1 {} temp {}} 794 do_test auth-1.116 { 795 execsql {SELECT name FROM sqlite_temp_master} 796 } {t1 v1} 797 do_test auth-1.117 { 798 proc auth {code arg1 arg2 arg3 arg4} { 799 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 800 return SQLITE_IGNORE 801 } 802 return SQLITE_OK 803 } 804 catchsql {DROP VIEW v1} 805 } {0 {}} 806 do_test auth-1.118 { 807 execsql {SELECT name FROM sqlite_temp_master} 808 } {t1 v1} 809 do_test auth-1.119 { 810 proc auth {code arg1 arg2 arg3 arg4} { 811 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 812 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 813 return SQLITE_IGNORE 814 } 815 return SQLITE_OK 816 } 817 catchsql {DROP VIEW v1} 818 } {0 {}} 819 do_test auth-1.120 { 820 set ::authargs 821 } {v1 {} temp {}} 822 do_test auth-1.121 { 823 execsql {SELECT name FROM sqlite_temp_master} 824 } {t1 v1} 825 do_test auth-1.122 { 826 proc auth {code arg1 arg2 arg3 arg4} { 827 if {$code=="SQLITE_DROP_TEMP_VIEW"} { 828 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 829 return SQLITE_OK 830 } 831 return SQLITE_OK 832 } 833 catchsql {DROP VIEW v1} 834 } {0 {}} 835 do_test auth-1.123 { 836 set ::authargs 837 } {v1 {} temp {}} 838 do_test auth-1.124 { 839 execsql {SELECT name FROM sqlite_temp_master} 840 } {t1} 841} 842} ;# ifcapable view 843 844# Test cases auth-1.125 to auth-1.176 test creating and dropping triggers. 845# Omit these if the library was compiled with triggers omitted. 846# 847ifcapable trigger&&tempdb { 848do_test auth-1.125 { 849 proc auth {code arg1 arg2 arg3 arg4} { 850 if {$code=="SQLITE_CREATE_TRIGGER"} { 851 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 852 return SQLITE_DENY 853 } 854 return SQLITE_OK 855 } 856 catchsql { 857 CREATE TRIGGER r2 DELETE on t2 BEGIN 858 SELECT NULL; 859 END; 860 } 861} {1 {not authorized}} 862do_test auth-1.126 { 863 set ::authargs 864} {r2 t2 main {}} 865do_test auth-1.127 { 866 execsql {SELECT name FROM sqlite_master} 867} {t2} 868do_test auth-1.128 { 869 proc auth {code arg1 arg2 arg3 arg4} { 870 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 871 return SQLITE_DENY 872 } 873 return SQLITE_OK 874 } 875 catchsql { 876 CREATE TRIGGER r2 DELETE on t2 BEGIN 877 SELECT NULL; 878 END; 879 } 880} {1 {not authorized}} 881do_test auth-1.129 { 882 execsql {SELECT name FROM sqlite_master} 883} {t2} 884do_test auth-1.130 { 885 proc auth {code arg1 arg2 arg3 arg4} { 886 if {$code=="SQLITE_CREATE_TRIGGER"} { 887 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 888 return SQLITE_IGNORE 889 } 890 return SQLITE_OK 891 } 892 catchsql { 893 CREATE TRIGGER r2 DELETE on t2 BEGIN 894 SELECT NULL; 895 END; 896 } 897} {0 {}} 898do_test auth-1.131 { 899 set ::authargs 900} {r2 t2 main {}} 901do_test auth-1.132 { 902 execsql {SELECT name FROM sqlite_master} 903} {t2} 904do_test auth-1.133 { 905 proc auth {code arg1 arg2 arg3 arg4} { 906 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 907 return SQLITE_IGNORE 908 } 909 return SQLITE_OK 910 } 911 catchsql { 912 CREATE TRIGGER r2 DELETE on t2 BEGIN 913 SELECT NULL; 914 END; 915 } 916} {0 {}} 917do_test auth-1.134 { 918 execsql {SELECT name FROM sqlite_master} 919} {t2} 920do_test auth-1.135 { 921 proc auth {code arg1 arg2 arg3 arg4} { 922 if {$code=="SQLITE_CREATE_TRIGGER"} { 923 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 924 return SQLITE_OK 925 } 926 return SQLITE_OK 927 } 928 catchsql { 929 CREATE TABLE tx(id); 930 CREATE TRIGGER r2 AFTER INSERT ON t2 BEGIN 931 INSERT INTO tx VALUES(NEW.rowid); 932 END; 933 } 934} {0 {}} 935do_test auth-1.136.1 { 936 set ::authargs 937} {r2 t2 main {}} 938do_test auth-1.136.2 { 939 execsql { 940 SELECT name FROM sqlite_master WHERE type='trigger' 941 } 942} {r2} 943do_test auth-1.136.3 { 944 proc auth {code arg1 arg2 arg3 arg4} { 945 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 946 return SQLITE_OK 947 } 948 set ::authargs {} 949 execsql { 950 INSERT INTO t2 VALUES(1,2,3); 951 } 952 set ::authargs 953} {SQLITE_INSERT t2 {} main {} SQLITE_INSERT tx {} main r2 SQLITE_READ t2 ROWID main r2} 954do_test auth-1.136.4 { 955 execsql { 956 SELECT * FROM tx; 957 } 958} {3} 959do_test auth-1.137 { 960 execsql {SELECT name FROM sqlite_master} 961} {t2 tx r2} 962do_test auth-1.138 { 963 proc auth {code arg1 arg2 arg3 arg4} { 964 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 965 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 966 return SQLITE_DENY 967 } 968 return SQLITE_OK 969 } 970 catchsql { 971 CREATE TRIGGER r1 DELETE on t1 BEGIN 972 SELECT NULL; 973 END; 974 } 975} {1 {not authorized}} 976do_test auth-1.139 { 977 set ::authargs 978} {r1 t1 temp {}} 979do_test auth-1.140 { 980 execsql {SELECT name FROM sqlite_temp_master} 981} {t1} 982do_test auth-1.141 { 983 proc auth {code arg1 arg2 arg3 arg4} { 984 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 985 return SQLITE_DENY 986 } 987 return SQLITE_OK 988 } 989 catchsql { 990 CREATE TRIGGER r1 DELETE on t1 BEGIN 991 SELECT NULL; 992 END; 993 } 994} {1 {not authorized}} 995do_test auth-1.142 { 996 execsql {SELECT name FROM sqlite_temp_master} 997} {t1} 998do_test auth-1.143 { 999 proc auth {code arg1 arg2 arg3 arg4} { 1000 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 1001 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1002 return SQLITE_IGNORE 1003 } 1004 return SQLITE_OK 1005 } 1006 catchsql { 1007 CREATE TRIGGER r1 DELETE on t1 BEGIN 1008 SELECT NULL; 1009 END; 1010 } 1011} {0 {}} 1012do_test auth-1.144 { 1013 set ::authargs 1014} {r1 t1 temp {}} 1015do_test auth-1.145 { 1016 execsql {SELECT name FROM sqlite_temp_master} 1017} {t1} 1018do_test auth-1.146 { 1019 proc auth {code arg1 arg2 arg3 arg4} { 1020 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1021 return SQLITE_IGNORE 1022 } 1023 return SQLITE_OK 1024 } 1025 catchsql { 1026 CREATE TRIGGER r1 DELETE on t1 BEGIN 1027 SELECT NULL; 1028 END; 1029 } 1030} {0 {}} 1031do_test auth-1.147 { 1032 execsql {SELECT name FROM sqlite_temp_master} 1033} {t1} 1034do_test auth-1.148 { 1035 proc auth {code arg1 arg2 arg3 arg4} { 1036 if {$code=="SQLITE_CREATE_TEMP_TRIGGER"} { 1037 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1038 return SQLITE_OK 1039 } 1040 return SQLITE_OK 1041 } 1042 catchsql { 1043 CREATE TRIGGER r1 DELETE on t1 BEGIN 1044 SELECT NULL; 1045 END; 1046 } 1047} {0 {}} 1048do_test auth-1.149 { 1049 set ::authargs 1050} {r1 t1 temp {}} 1051do_test auth-1.150 { 1052 execsql {SELECT name FROM sqlite_temp_master} 1053} {t1 r1} 1054 1055do_test auth-1.151 { 1056 proc auth {code arg1 arg2 arg3 arg4} { 1057 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1058 return SQLITE_DENY 1059 } 1060 return SQLITE_OK 1061 } 1062 catchsql {DROP TRIGGER r2} 1063} {1 {not authorized}} 1064do_test auth-1.152 { 1065 execsql {SELECT name FROM sqlite_master} 1066} {t2 tx r2} 1067do_test auth-1.153 { 1068 proc auth {code arg1 arg2 arg3 arg4} { 1069 if {$code=="SQLITE_DROP_TRIGGER"} { 1070 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1071 return SQLITE_DENY 1072 } 1073 return SQLITE_OK 1074 } 1075 catchsql {DROP TRIGGER r2} 1076} {1 {not authorized}} 1077do_test auth-1.154 { 1078 set ::authargs 1079} {r2 t2 main {}} 1080do_test auth-1.155 { 1081 execsql {SELECT name FROM sqlite_master} 1082} {t2 tx r2} 1083do_test auth-1.156 { 1084 proc auth {code arg1 arg2 arg3 arg4} { 1085 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1086 return SQLITE_IGNORE 1087 } 1088 return SQLITE_OK 1089 } 1090 catchsql {DROP TRIGGER r2} 1091} {0 {}} 1092do_test auth-1.157 { 1093 execsql {SELECT name FROM sqlite_master} 1094} {t2 tx r2} 1095do_test auth-1.158 { 1096 proc auth {code arg1 arg2 arg3 arg4} { 1097 if {$code=="SQLITE_DROP_TRIGGER"} { 1098 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1099 return SQLITE_IGNORE 1100 } 1101 return SQLITE_OK 1102 } 1103 catchsql {DROP TRIGGER r2} 1104} {0 {}} 1105do_test auth-1.159 { 1106 set ::authargs 1107} {r2 t2 main {}} 1108do_test auth-1.160 { 1109 execsql {SELECT name FROM sqlite_master} 1110} {t2 tx r2} 1111do_test auth-1.161 { 1112 proc auth {code arg1 arg2 arg3 arg4} { 1113 if {$code=="SQLITE_DROP_TRIGGER"} { 1114 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1115 return SQLITE_OK 1116 } 1117 return SQLITE_OK 1118 } 1119 catchsql {DROP TRIGGER r2} 1120} {0 {}} 1121do_test auth-1.162 { 1122 set ::authargs 1123} {r2 t2 main {}} 1124do_test auth-1.163 { 1125 execsql { 1126 DROP TABLE tx; 1127 DELETE FROM t2 WHERE a=1 AND b=2 AND c=3; 1128 SELECT name FROM sqlite_master; 1129 } 1130} {t2} 1131 1132do_test auth-1.164 { 1133 proc auth {code arg1 arg2 arg3 arg4} { 1134 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1135 return SQLITE_DENY 1136 } 1137 return SQLITE_OK 1138 } 1139 catchsql {DROP TRIGGER r1} 1140} {1 {not authorized}} 1141do_test auth-1.165 { 1142 execsql {SELECT name FROM sqlite_temp_master} 1143} {t1 r1} 1144do_test auth-1.166 { 1145 proc auth {code arg1 arg2 arg3 arg4} { 1146 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1147 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1148 return SQLITE_DENY 1149 } 1150 return SQLITE_OK 1151 } 1152 catchsql {DROP TRIGGER r1} 1153} {1 {not authorized}} 1154do_test auth-1.167 { 1155 set ::authargs 1156} {r1 t1 temp {}} 1157do_test auth-1.168 { 1158 execsql {SELECT name FROM sqlite_temp_master} 1159} {t1 r1} 1160do_test auth-1.169 { 1161 proc auth {code arg1 arg2 arg3 arg4} { 1162 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1163 return SQLITE_IGNORE 1164 } 1165 return SQLITE_OK 1166 } 1167 catchsql {DROP TRIGGER r1} 1168} {0 {}} 1169do_test auth-1.170 { 1170 execsql {SELECT name FROM sqlite_temp_master} 1171} {t1 r1} 1172do_test auth-1.171 { 1173 proc auth {code arg1 arg2 arg3 arg4} { 1174 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1175 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1176 return SQLITE_IGNORE 1177 } 1178 return SQLITE_OK 1179 } 1180 catchsql {DROP TRIGGER r1} 1181} {0 {}} 1182do_test auth-1.172 { 1183 set ::authargs 1184} {r1 t1 temp {}} 1185do_test auth-1.173 { 1186 execsql {SELECT name FROM sqlite_temp_master} 1187} {t1 r1} 1188do_test auth-1.174 { 1189 proc auth {code arg1 arg2 arg3 arg4} { 1190 if {$code=="SQLITE_DROP_TEMP_TRIGGER"} { 1191 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1192 return SQLITE_OK 1193 } 1194 return SQLITE_OK 1195 } 1196 catchsql {DROP TRIGGER r1} 1197} {0 {}} 1198do_test auth-1.175 { 1199 set ::authargs 1200} {r1 t1 temp {}} 1201do_test auth-1.176 { 1202 execsql {SELECT name FROM sqlite_temp_master} 1203} {t1} 1204} ;# ifcapable trigger 1205 1206do_test auth-1.177 { 1207 proc auth {code arg1 arg2 arg3 arg4} { 1208 if {$code=="SQLITE_CREATE_INDEX"} { 1209 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 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.178 { 1217 set ::authargs 1218} {i2 t2 main {}} 1219do_test auth-1.179 { 1220 execsql {SELECT name FROM sqlite_master} 1221} {t2} 1222do_test auth-1.180 { 1223 proc auth {code arg1 arg2 arg3 arg4} { 1224 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 1225 return SQLITE_DENY 1226 } 1227 return SQLITE_OK 1228 } 1229 catchsql {CREATE INDEX i2 ON t2(a)} 1230} {1 {not authorized}} 1231do_test auth-1.181 { 1232 execsql {SELECT name FROM sqlite_master} 1233} {t2} 1234do_test auth-1.182 { 1235 proc auth {code arg1 arg2 arg3 arg4} { 1236 if {$code=="SQLITE_CREATE_INDEX"} { 1237 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1238 return SQLITE_IGNORE 1239 } 1240 return SQLITE_OK 1241 } 1242 catchsql {CREATE INDEX i2 ON t2(b)} 1243} {0 {}} 1244do_test auth-1.183 { 1245 set ::authargs 1246} {i2 t2 main {}} 1247do_test auth-1.184 { 1248 execsql {SELECT name FROM sqlite_master} 1249} {t2} 1250do_test auth-1.185 { 1251 proc auth {code arg1 arg2 arg3 arg4} { 1252 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} { 1253 return SQLITE_IGNORE 1254 } 1255 return SQLITE_OK 1256 } 1257 catchsql {CREATE INDEX i2 ON t2(b)} 1258} {0 {}} 1259do_test auth-1.186 { 1260 execsql {SELECT name FROM sqlite_master} 1261} {t2} 1262do_test auth-1.187 { 1263 proc auth {code arg1 arg2 arg3 arg4} { 1264 if {$code=="SQLITE_CREATE_INDEX"} { 1265 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1266 return SQLITE_OK 1267 } 1268 return SQLITE_OK 1269 } 1270 catchsql {CREATE INDEX i2 ON t2(a)} 1271} {0 {}} 1272do_test auth-1.188 { 1273 set ::authargs 1274} {i2 t2 main {}} 1275do_test auth-1.189 { 1276 execsql {SELECT name FROM sqlite_master} 1277} {t2 i2} 1278 1279ifcapable tempdb { 1280 do_test auth-1.190 { 1281 proc auth {code arg1 arg2 arg3 arg4} { 1282 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1283 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1284 return SQLITE_DENY 1285 } 1286 return SQLITE_OK 1287 } 1288 catchsql {CREATE INDEX i1 ON t1(a)} 1289 } {1 {not authorized}} 1290 do_test auth-1.191 { 1291 set ::authargs 1292 } {i1 t1 temp {}} 1293 do_test auth-1.192 { 1294 execsql {SELECT name FROM sqlite_temp_master} 1295 } {t1} 1296 do_test auth-1.193 { 1297 proc auth {code arg1 arg2 arg3 arg4} { 1298 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1299 return SQLITE_DENY 1300 } 1301 return SQLITE_OK 1302 } 1303 catchsql {CREATE INDEX i1 ON t1(b)} 1304 } {1 {not authorized}} 1305 do_test auth-1.194 { 1306 execsql {SELECT name FROM sqlite_temp_master} 1307 } {t1} 1308 do_test auth-1.195 { 1309 proc auth {code arg1 arg2 arg3 arg4} { 1310 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1311 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1312 return SQLITE_IGNORE 1313 } 1314 return SQLITE_OK 1315 } 1316 catchsql {CREATE INDEX i1 ON t1(b)} 1317 } {0 {}} 1318 do_test auth-1.196 { 1319 set ::authargs 1320 } {i1 t1 temp {}} 1321 do_test auth-1.197 { 1322 execsql {SELECT name FROM sqlite_temp_master} 1323 } {t1} 1324 do_test auth-1.198 { 1325 proc auth {code arg1 arg2 arg3 arg4} { 1326 if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} { 1327 return SQLITE_IGNORE 1328 } 1329 return SQLITE_OK 1330 } 1331 catchsql {CREATE INDEX i1 ON t1(c)} 1332 } {0 {}} 1333 do_test auth-1.199 { 1334 execsql {SELECT name FROM sqlite_temp_master} 1335 } {t1} 1336 do_test auth-1.200 { 1337 proc auth {code arg1 arg2 arg3 arg4} { 1338 if {$code=="SQLITE_CREATE_TEMP_INDEX"} { 1339 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1340 return SQLITE_OK 1341 } 1342 return SQLITE_OK 1343 } 1344 catchsql {CREATE INDEX i1 ON t1(a)} 1345 } {0 {}} 1346 do_test auth-1.201 { 1347 set ::authargs 1348 } {i1 t1 temp {}} 1349 do_test auth-1.202 { 1350 execsql {SELECT name FROM sqlite_temp_master} 1351 } {t1 i1} 1352} 1353 1354do_test auth-1.203 { 1355 proc auth {code arg1 arg2 arg3 arg4} { 1356 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1357 return SQLITE_DENY 1358 } 1359 return SQLITE_OK 1360 } 1361 catchsql {DROP INDEX i2} 1362} {1 {not authorized}} 1363do_test auth-1.204 { 1364 execsql {SELECT name FROM sqlite_master} 1365} {t2 i2} 1366do_test auth-1.205 { 1367 proc auth {code arg1 arg2 arg3 arg4} { 1368 if {$code=="SQLITE_DROP_INDEX"} { 1369 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1370 return SQLITE_DENY 1371 } 1372 return SQLITE_OK 1373 } 1374 catchsql {DROP INDEX i2} 1375} {1 {not authorized}} 1376do_test auth-1.206 { 1377 set ::authargs 1378} {i2 t2 main {}} 1379do_test auth-1.207 { 1380 execsql {SELECT name FROM sqlite_master} 1381} {t2 i2} 1382do_test auth-1.208 { 1383 proc auth {code arg1 arg2 arg3 arg4} { 1384 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} { 1385 return SQLITE_IGNORE 1386 } 1387 return SQLITE_OK 1388 } 1389 catchsql {DROP INDEX i2} 1390} {0 {}} 1391do_test auth-1.209 { 1392 execsql {SELECT name FROM sqlite_master} 1393} {t2 i2} 1394do_test auth-1.210 { 1395 proc auth {code arg1 arg2 arg3 arg4} { 1396 if {$code=="SQLITE_DROP_INDEX"} { 1397 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1398 return SQLITE_IGNORE 1399 } 1400 return SQLITE_OK 1401 } 1402 catchsql {DROP INDEX i2} 1403} {0 {}} 1404do_test auth-1.211 { 1405 set ::authargs 1406} {i2 t2 main {}} 1407do_test auth-1.212 { 1408 execsql {SELECT name FROM sqlite_master} 1409} {t2 i2} 1410do_test auth-1.213 { 1411 proc auth {code arg1 arg2 arg3 arg4} { 1412 if {$code=="SQLITE_DROP_INDEX"} { 1413 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1414 return SQLITE_OK 1415 } 1416 return SQLITE_OK 1417 } 1418 catchsql {DROP INDEX i2} 1419} {0 {}} 1420do_test auth-1.214 { 1421 set ::authargs 1422} {i2 t2 main {}} 1423do_test auth-1.215 { 1424 execsql {SELECT name FROM sqlite_master} 1425} {t2} 1426 1427ifcapable tempdb { 1428 do_test auth-1.216 { 1429 proc auth {code arg1 arg2 arg3 arg4} { 1430 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1431 return SQLITE_DENY 1432 } 1433 return SQLITE_OK 1434 } 1435 catchsql {DROP INDEX i1} 1436 } {1 {not authorized}} 1437 do_test auth-1.217 { 1438 execsql {SELECT name FROM sqlite_temp_master} 1439 } {t1 i1} 1440 do_test auth-1.218 { 1441 proc auth {code arg1 arg2 arg3 arg4} { 1442 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1443 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1444 return SQLITE_DENY 1445 } 1446 return SQLITE_OK 1447 } 1448 catchsql {DROP INDEX i1} 1449 } {1 {not authorized}} 1450 do_test auth-1.219 { 1451 set ::authargs 1452 } {i1 t1 temp {}} 1453 do_test auth-1.220 { 1454 execsql {SELECT name FROM sqlite_temp_master} 1455 } {t1 i1} 1456 do_test auth-1.221 { 1457 proc auth {code arg1 arg2 arg3 arg4} { 1458 if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} { 1459 return SQLITE_IGNORE 1460 } 1461 return SQLITE_OK 1462 } 1463 catchsql {DROP INDEX i1} 1464 } {0 {}} 1465 do_test auth-1.222 { 1466 execsql {SELECT name FROM sqlite_temp_master} 1467 } {t1 i1} 1468 do_test auth-1.223 { 1469 proc auth {code arg1 arg2 arg3 arg4} { 1470 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1471 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1472 return SQLITE_IGNORE 1473 } 1474 return SQLITE_OK 1475 } 1476 catchsql {DROP INDEX i1} 1477 } {0 {}} 1478 do_test auth-1.224 { 1479 set ::authargs 1480 } {i1 t1 temp {}} 1481 do_test auth-1.225 { 1482 execsql {SELECT name FROM sqlite_temp_master} 1483 } {t1 i1} 1484 do_test auth-1.226 { 1485 proc auth {code arg1 arg2 arg3 arg4} { 1486 if {$code=="SQLITE_DROP_TEMP_INDEX"} { 1487 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1488 return SQLITE_OK 1489 } 1490 return SQLITE_OK 1491 } 1492 catchsql {DROP INDEX i1} 1493 } {0 {}} 1494 do_test auth-1.227 { 1495 set ::authargs 1496 } {i1 t1 temp {}} 1497 do_test auth-1.228 { 1498 execsql {SELECT name FROM sqlite_temp_master} 1499 } {t1} 1500} 1501 1502do_test auth-1.229 { 1503 proc auth {code arg1 arg2 arg3 arg4} { 1504 if {$code=="SQLITE_PRAGMA"} { 1505 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1506 return SQLITE_DENY 1507 } 1508 return SQLITE_OK 1509 } 1510 catchsql {PRAGMA full_column_names=on} 1511} {1 {not authorized}} 1512do_test auth-1.230 { 1513 set ::authargs 1514} {full_column_names on {} {}} 1515do_test auth-1.231 { 1516 execsql2 {SELECT a FROM t2} 1517} {a 11 a 7} 1518do_test auth-1.232 { 1519 proc auth {code arg1 arg2 arg3 arg4} { 1520 if {$code=="SQLITE_PRAGMA"} { 1521 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1522 return SQLITE_IGNORE 1523 } 1524 return SQLITE_OK 1525 } 1526 catchsql {PRAGMA full_column_names=on} 1527} {0 {}} 1528do_test auth-1.233 { 1529 set ::authargs 1530} {full_column_names on {} {}} 1531do_test auth-1.234 { 1532 execsql2 {SELECT a FROM t2} 1533} {a 11 a 7} 1534do_test auth-1.235 { 1535 proc auth {code arg1 arg2 arg3 arg4} { 1536 if {$code=="SQLITE_PRAGMA"} { 1537 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1538 return SQLITE_OK 1539 } 1540 return SQLITE_OK 1541 } 1542 catchsql {PRAGMA full_column_names=on} 1543} {0 {}} 1544do_test auth-1.236 { 1545 execsql2 {SELECT a FROM t2} 1546} {t2.a 11 t2.a 7} 1547do_test auth-1.237 { 1548 proc auth {code arg1 arg2 arg3 arg4} { 1549 if {$code=="SQLITE_PRAGMA"} { 1550 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1551 return SQLITE_OK 1552 } 1553 return SQLITE_OK 1554 } 1555 catchsql {PRAGMA full_column_names=OFF} 1556} {0 {}} 1557do_test auth-1.238 { 1558 set ::authargs 1559} {full_column_names OFF {} {}} 1560do_test auth-1.239 { 1561 execsql2 {SELECT a FROM t2} 1562} {a 11 a 7} 1563 1564do_test auth-1.240 { 1565 proc auth {code arg1 arg2 arg3 arg4} { 1566 if {$code=="SQLITE_TRANSACTION"} { 1567 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1568 return SQLITE_DENY 1569 } 1570 return SQLITE_OK 1571 } 1572 catchsql {BEGIN} 1573} {1 {not authorized}} 1574do_test auth-1.241 { 1575 set ::authargs 1576} {BEGIN {} {} {}} 1577do_test auth-1.242 { 1578 proc auth {code arg1 arg2 arg3 arg4} { 1579 if {$code=="SQLITE_TRANSACTION" && $arg1!="BEGIN"} { 1580 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1581 return SQLITE_DENY 1582 } 1583 return SQLITE_OK 1584 } 1585 catchsql {BEGIN; INSERT INTO t2 VALUES(44,55,66); COMMIT} 1586} {1 {not authorized}} 1587do_test auth-1.243 { 1588 set ::authargs 1589} {COMMIT {} {} {}} 1590do_test auth-1.244 { 1591 execsql {SELECT * FROM t2} 1592} {11 2 33 7 8 9 44 55 66} 1593do_test auth-1.245 { 1594 catchsql {ROLLBACK} 1595} {1 {not authorized}} 1596do_test auth-1.246 { 1597 set ::authargs 1598} {ROLLBACK {} {} {}} 1599do_test auth-1.247 { 1600 catchsql {END TRANSACTION} 1601} {1 {not authorized}} 1602do_test auth-1.248 { 1603 set ::authargs 1604} {COMMIT {} {} {}} 1605do_test auth-1.249 { 1606 db authorizer {} 1607 catchsql {ROLLBACK} 1608} {0 {}} 1609do_test auth-1.250 { 1610 execsql {SELECT * FROM t2} 1611} {11 2 33 7 8 9} 1612 1613# ticket #340 - authorization for ATTACH and DETACH. 1614# 1615ifcapable attach { 1616 do_test auth-1.251 { 1617 db authorizer ::auth 1618 proc auth {code arg1 arg2 arg3 arg4} { 1619 if {$code=="SQLITE_ATTACH"} { 1620 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1621 } 1622 return SQLITE_OK 1623 } 1624 catchsql { 1625 ATTACH DATABASE ':memory:' AS test1 1626 } 1627 } {0 {}} 1628 do_test auth-1.252 { 1629 set ::authargs 1630 } {:memory: {} {} {}} 1631 do_test auth-1.253 { 1632 catchsql {DETACH DATABASE test1} 1633 proc auth {code arg1 arg2 arg3 arg4} { 1634 if {$code=="SQLITE_ATTACH"} { 1635 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1636 return SQLITE_DENY 1637 } 1638 return SQLITE_OK 1639 } 1640 catchsql { 1641 ATTACH DATABASE ':memory:' AS test1; 1642 } 1643 } {1 {not authorized}} 1644 do_test auth-1.254 { 1645 lindex [execsql {PRAGMA database_list}] 7 1646 } {} 1647 do_test auth-1.255 { 1648 catchsql {DETACH DATABASE test1} 1649 proc auth {code arg1 arg2 arg3 arg4} { 1650 if {$code=="SQLITE_ATTACH"} { 1651 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1652 return SQLITE_IGNORE 1653 } 1654 return SQLITE_OK 1655 } 1656 catchsql { 1657 ATTACH DATABASE ':memory:' AS test1; 1658 } 1659 } {0 {}} 1660 do_test auth-1.256 { 1661 lindex [execsql {PRAGMA database_list}] 7 1662 } {} 1663 do_test auth-1.257 { 1664 proc auth {code arg1 arg2 arg3 arg4} { 1665 if {$code=="SQLITE_DETACH"} { 1666 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1667 return SQLITE_OK 1668 } 1669 return SQLITE_OK 1670 } 1671 execsql {ATTACH DATABASE ':memory:' AS test1} 1672 catchsql { 1673 DETACH DATABASE test1; 1674 } 1675 } {0 {}} 1676 do_test auth-1.258 { 1677 lindex [execsql {PRAGMA database_list}] 7 1678 } {} 1679 do_test auth-1.259 { 1680 execsql {ATTACH DATABASE ':memory:' AS test1} 1681 proc auth {code arg1 arg2 arg3 arg4} { 1682 if {$code=="SQLITE_DETACH"} { 1683 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1684 return SQLITE_IGNORE 1685 } 1686 return SQLITE_OK 1687 } 1688 catchsql { 1689 DETACH DATABASE test1; 1690 } 1691 } {0 {}} 1692 ifcapable tempdb { 1693 ifcapable schema_pragmas { 1694 do_test auth-1.260 { 1695 lindex [execsql {PRAGMA database_list}] 7 1696 } {test1} 1697 } ;# ifcapable schema_pragmas 1698 do_test auth-1.261 { 1699 proc auth {code arg1 arg2 arg3 arg4} { 1700 if {$code=="SQLITE_DETACH"} { 1701 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1702 return SQLITE_DENY 1703 } 1704 return SQLITE_OK 1705 } 1706 catchsql { 1707 DETACH DATABASE test1; 1708 } 1709 } {1 {not authorized}} 1710 ifcapable schema_pragmas { 1711 do_test auth-1.262 { 1712 lindex [execsql {PRAGMA database_list}] 7 1713 } {test1} 1714 } ;# ifcapable schema_pragmas 1715 db authorizer {} 1716 execsql {DETACH DATABASE test1} 1717 db authorizer ::auth 1718 1719 # Authorization for ALTER TABLE. These tests are omitted if the library 1720 # was built without ALTER TABLE support. 1721 ifcapable altertable { 1722 1723 do_test auth-1.263 { 1724 proc auth {code arg1 arg2 arg3 arg4} { 1725 if {$code=="SQLITE_ALTER_TABLE"} { 1726 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1727 return SQLITE_OK 1728 } 1729 return SQLITE_OK 1730 } 1731 catchsql { 1732 ALTER TABLE t1 RENAME TO t1x 1733 } 1734 } {0 {}} 1735 do_test auth-1.264 { 1736 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1737 } {t1x} 1738 do_test auth-1.265 { 1739 set authargs 1740 } {temp t1 {} {}} 1741 do_test auth-1.266 { 1742 proc auth {code arg1 arg2 arg3 arg4} { 1743 if {$code=="SQLITE_ALTER_TABLE"} { 1744 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1745 return SQLITE_IGNORE 1746 } 1747 return SQLITE_OK 1748 } 1749 catchsql { 1750 ALTER TABLE t1x RENAME TO t1 1751 } 1752 } {0 {}} 1753 do_test auth-1.267 { 1754 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1755 } {t1x} 1756 do_test auth-1.268 { 1757 set authargs 1758 } {temp t1x {} {}} 1759 do_test auth-1.269 { 1760 proc auth {code arg1 arg2 arg3 arg4} { 1761 if {$code=="SQLITE_ALTER_TABLE"} { 1762 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1763 return SQLITE_DENY 1764 } 1765 return SQLITE_OK 1766 } 1767 catchsql { 1768 ALTER TABLE t1x RENAME TO t1 1769 } 1770 } {1 {not authorized}} 1771 do_test auth-1.270 { 1772 execsql {SELECT name FROM sqlite_temp_master WHERE type='table'} 1773 } {t1x} 1774 1775 do_test auth-1.271 { 1776 set authargs 1777 } {temp t1x {} {}} 1778 } ;# ifcapable altertable 1779 1780 } else { 1781 db authorizer {} 1782 db eval { 1783 DETACH DATABASE test1; 1784 } 1785 } 1786} 1787 1788ifcapable altertable { 1789db authorizer {} 1790catchsql {ALTER TABLE t1x RENAME TO t1} 1791db authorizer ::auth 1792do_test auth-1.272 { 1793 proc auth {code arg1 arg2 arg3 arg4} { 1794 if {$code=="SQLITE_ALTER_TABLE"} { 1795 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1796 return SQLITE_OK 1797 } 1798 return SQLITE_OK 1799 } 1800 catchsql { 1801 ALTER TABLE t2 RENAME TO t2x 1802 } 1803} {0 {}} 1804do_test auth-1.273 { 1805 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1806} {t2x} 1807do_test auth-1.274 { 1808 set authargs 1809} {main t2 {} {}} 1810do_test auth-1.275 { 1811 proc auth {code arg1 arg2 arg3 arg4} { 1812 if {$code=="SQLITE_ALTER_TABLE"} { 1813 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1814 return SQLITE_IGNORE 1815 } 1816 return SQLITE_OK 1817 } 1818 catchsql { 1819 ALTER TABLE t2x RENAME TO t2 1820 } 1821} {0 {}} 1822do_test auth-1.276 { 1823 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1824} {t2x} 1825do_test auth-1.277 { 1826 set authargs 1827} {main t2x {} {}} 1828do_test auth-1.278 { 1829 proc auth {code arg1 arg2 arg3 arg4} { 1830 if {$code=="SQLITE_ALTER_TABLE"} { 1831 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 1832 return SQLITE_DENY 1833 } 1834 return SQLITE_OK 1835 } 1836 catchsql { 1837 ALTER TABLE t2x RENAME TO t2 1838 } 1839} {1 {not authorized}} 1840do_test auth-1.279 { 1841 execsql {SELECT name FROM sqlite_master WHERE type='table'} 1842} {t2x} 1843do_test auth-1.280 { 1844 set authargs 1845} {main t2x {} {}} 1846db authorizer {} 1847catchsql {ALTER TABLE t2x RENAME TO t2} 1848 1849} ;# ifcapable altertable 1850 1851# Test the authorization callbacks for the REINDEX command. 1852ifcapable reindex { 1853 1854proc auth {code args} { 1855 if {$code=="SQLITE_REINDEX"} { 1856 set ::authargs [concat $::authargs $args] 1857 } 1858 return SQLITE_OK 1859} 1860db authorizer auth 1861do_test auth-1.281 { 1862 execsql { 1863 CREATE TABLE t3(a PRIMARY KEY, b, c); 1864 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1865 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1866 } 1867} {} 1868do_test auth-1.282 { 1869 set ::authargs {} 1870 execsql { 1871 REINDEX t3_idx1; 1872 } 1873 set ::authargs 1874} {t3_idx1 {} main {}} 1875do_test auth-1.283 { 1876 set ::authargs {} 1877 execsql { 1878 REINDEX BINARY; 1879 } 1880 set ::authargs 1881} {t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1882do_test auth-1.284 { 1883 set ::authargs {} 1884 execsql { 1885 REINDEX NOCASE; 1886 } 1887 set ::authargs 1888} {t3_idx2 {} main {}} 1889do_test auth-1.285 { 1890 set ::authargs {} 1891 execsql { 1892 REINDEX t3; 1893 } 1894 set ::authargs 1895} {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}} 1896do_test auth-1.286 { 1897 execsql { 1898 DROP TABLE t3; 1899 } 1900} {} 1901ifcapable tempdb { 1902 do_test auth-1.287 { 1903 execsql { 1904 CREATE TEMP TABLE t3(a PRIMARY KEY, b, c); 1905 CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY); 1906 CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE); 1907 } 1908 } {} 1909 do_test auth-1.288 { 1910 set ::authargs {} 1911 execsql { 1912 REINDEX temp.t3_idx1; 1913 } 1914 set ::authargs 1915 } {t3_idx1 {} temp {}} 1916 do_test auth-1.289 { 1917 set ::authargs {} 1918 execsql { 1919 REINDEX BINARY; 1920 } 1921 set ::authargs 1922 } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1923 do_test auth-1.290 { 1924 set ::authargs {} 1925 execsql { 1926 REINDEX NOCASE; 1927 } 1928 set ::authargs 1929 } {t3_idx2 {} temp {}} 1930 do_test auth-1.291 { 1931 set ::authargs {} 1932 execsql { 1933 REINDEX temp.t3; 1934 } 1935 set ::authargs 1936 } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}} 1937 proc auth {code args} { 1938 if {$code=="SQLITE_REINDEX"} { 1939 set ::authargs [concat $::authargs $args] 1940 return SQLITE_DENY 1941 } 1942 return SQLITE_OK 1943 } 1944 do_test auth-1.292 { 1945 set ::authargs {} 1946 catchsql { 1947 REINDEX temp.t3; 1948 } 1949 } {1 {not authorized}} 1950 do_test auth-1.293 { 1951 execsql { 1952 DROP TABLE t3; 1953 } 1954 } {} 1955} 1956 1957} ;# ifcapable reindex 1958 1959ifcapable analyze { 1960 proc auth {code args} { 1961 if {$code=="SQLITE_ANALYZE"} { 1962 set ::authargs [concat $::authargs $args] 1963 } 1964 return SQLITE_OK 1965 } 1966 do_test auth-1.294 { 1967 set ::authargs {} 1968 execsql { 1969 CREATE TABLE t4(a,b,c); 1970 CREATE INDEX t4i1 ON t4(a); 1971 CREATE INDEX t4i2 ON t4(b,a,c); 1972 INSERT INTO t4 VALUES(1,2,3); 1973 ANALYZE; 1974 } 1975 set ::authargs 1976 } {t4 {} main {}} 1977 do_test auth-1.295 { 1978 execsql { 1979 SELECT count(*) FROM sqlite_stat1; 1980 } 1981 } 2 1982 proc auth {code args} { 1983 if {$code=="SQLITE_ANALYZE"} { 1984 set ::authargs [concat $::authargs $args] 1985 return SQLITE_DENY 1986 } 1987 return SQLITE_OK 1988 } 1989 do_test auth-1.296 { 1990 set ::authargs {} 1991 catchsql { 1992 ANALYZE; 1993 } 1994 } {1 {not authorized}} 1995 do_test auth-1.297 { 1996 execsql { 1997 SELECT count(*) FROM sqlite_stat1; 1998 } 1999 } 2 2000} ;# ifcapable analyze 2001 2002 2003# Authorization for ALTER TABLE ADD COLUMN. 2004# These tests are omitted if the library 2005# was built without ALTER TABLE support. 2006ifcapable {altertable} { 2007 do_test auth-1.300 { 2008 execsql {CREATE TABLE t5(x)} 2009 proc auth {code arg1 arg2 arg3 arg4} { 2010 if {$code=="SQLITE_ALTER_TABLE"} { 2011 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2012 return SQLITE_OK 2013 } 2014 return SQLITE_OK 2015 } 2016 catchsql { 2017 ALTER TABLE t5 ADD COLUMN new_col_1; 2018 } 2019 } {0 {}} 2020 do_test auth-1.301 { 2021 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2022 regexp new_col_1 $x 2023 } {1} 2024 do_test auth-1.302 { 2025 set authargs 2026 } {main t5 {} {}} 2027 do_test auth-1.303 { 2028 proc auth {code arg1 arg2 arg3 arg4} { 2029 if {$code=="SQLITE_ALTER_TABLE"} { 2030 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2031 return SQLITE_IGNORE 2032 } 2033 return SQLITE_OK 2034 } 2035 catchsql { 2036 ALTER TABLE t5 ADD COLUMN new_col_2; 2037 } 2038 } {0 {}} 2039 do_test auth-1.304 { 2040 set x [execsql {SELECT sql FROM sqlite_master WHERE name='t5'}] 2041 regexp new_col_2 $x 2042 } {0} 2043 do_test auth-1.305 { 2044 set authargs 2045 } {main t5 {} {}} 2046 do_test auth-1.306 { 2047 proc auth {code arg1 arg2 arg3 arg4} { 2048 if {$code=="SQLITE_ALTER_TABLE"} { 2049 set ::authargs [list $arg1 $arg2 $arg3 $arg4] 2050 return SQLITE_DENY 2051 } 2052 return SQLITE_OK 2053 } 2054 catchsql { 2055 ALTER TABLE t5 ADD COLUMN new_col_3 2056 } 2057 } {1 {not authorized}} 2058 do_test auth-1.307 { 2059 set x [execsql {SELECT sql FROM sqlite_temp_master WHERE type='t5'}] 2060 regexp new_col_3 $x 2061 } {0} 2062 2063 do_test auth-1.308 { 2064 set authargs 2065 } {main t5 {} {}} 2066 execsql {DROP TABLE t5} 2067} ;# ifcapable altertable 2068 2069do_test auth-2.1 { 2070 proc auth {code arg1 arg2 arg3 arg4} { 2071 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2072 return SQLITE_DENY 2073 } 2074 return SQLITE_OK 2075 } 2076 db authorizer ::auth 2077 execsql {CREATE TABLE t3(x INTEGER PRIMARY KEY, y, z)} 2078 catchsql {SELECT * FROM t3} 2079} {1 {access to t3.x is prohibited}} 2080do_test auth-2.1 { 2081 catchsql {SELECT y,z FROM t3} 2082} {0 {}} 2083do_test auth-2.2 { 2084 catchsql {SELECT ROWID,y,z FROM t3} 2085} {1 {access to t3.x is prohibited}} 2086do_test auth-2.3 { 2087 catchsql {SELECT OID,y,z FROM t3} 2088} {1 {access to t3.x is prohibited}} 2089do_test auth-2.4 { 2090 proc auth {code arg1 arg2 arg3 arg4} { 2091 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} { 2092 return SQLITE_IGNORE 2093 } 2094 return SQLITE_OK 2095 } 2096 execsql {INSERT INTO t3 VALUES(44,55,66)} 2097 catchsql {SELECT * FROM t3} 2098} {0 {{} 55 66}} 2099do_test auth-2.5 { 2100 catchsql {SELECT rowid,y,z FROM t3} 2101} {0 {{} 55 66}} 2102do_test auth-2.6 { 2103 proc auth {code arg1 arg2 arg3 arg4} { 2104 if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="ROWID"} { 2105 return SQLITE_IGNORE 2106 } 2107 return SQLITE_OK 2108 } 2109 catchsql {SELECT * FROM t3} 2110} {0 {44 55 66}} 2111do_test auth-2.7 { 2112 catchsql {SELECT ROWID,y,z FROM t3} 2113} {0 {44 55 66}} 2114do_test auth-2.8 { 2115 proc auth {code arg1 arg2 arg3 arg4} { 2116 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2117 return SQLITE_IGNORE 2118 } 2119 return SQLITE_OK 2120 } 2121 catchsql {SELECT ROWID,b,c FROM t2} 2122} {0 {{} 2 33 {} 8 9}} 2123do_test auth-2.9.1 { 2124 # We have to flush the cache here in case the Tcl interface tries to 2125 # reuse a statement compiled with sqlite3_prepare_v2(). In this case, 2126 # the first error encountered is an SQLITE_SCHEMA error. Then, when 2127 # trying to recompile the statement, the authorization error is encountered. 2128 # If we do not flush the cache, the correct error message is returned, but 2129 # the error code is SQLITE_SCHEMA, not SQLITE_ERROR as required by the test 2130 # case after this one. 2131 # 2132 db cache flush 2133 2134 proc auth {code arg1 arg2 arg3 arg4} { 2135 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="ROWID"} { 2136 return bogus 2137 } 2138 return SQLITE_OK 2139 } 2140 catchsql {SELECT ROWID,b,c FROM t2} 2141} {1 {illegal return value (999) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}} 2142do_test auth-2.9.2 { 2143 db errorcode 2144} {1} 2145do_test auth-2.10 { 2146 proc auth {code arg1 arg2 arg3 arg4} { 2147 if {$code=="SQLITE_SELECT"} { 2148 return bogus 2149 } 2150 return SQLITE_OK 2151 } 2152 catchsql {SELECT ROWID,b,c FROM t2} 2153} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}} 2154do_test auth-2.11.1 { 2155 proc auth {code arg1 arg2 arg3 arg4} { 2156 if {$code=="SQLITE_READ" && $arg2=="a"} { 2157 return SQLITE_IGNORE 2158 } 2159 return SQLITE_OK 2160 } 2161 catchsql {SELECT * FROM t2, t3} 2162} {0 {{} 2 33 44 55 66 {} 8 9 44 55 66}} 2163do_test auth-2.11.2 { 2164 proc auth {code arg1 arg2 arg3 arg4} { 2165 if {$code=="SQLITE_READ" && $arg2=="x"} { 2166 return SQLITE_IGNORE 2167 } 2168 return SQLITE_OK 2169 } 2170 catchsql {SELECT * FROM t2, t3} 2171} {0 {11 2 33 {} 55 66 7 8 9 {} 55 66}} 2172 2173# Make sure the OLD and NEW pseudo-tables of a trigger get authorized. 2174# 2175ifcapable trigger { 2176 do_test auth-3.1 { 2177 proc auth {code arg1 arg2 arg3 arg4} { 2178 return SQLITE_OK 2179 } 2180 execsql { 2181 CREATE TABLE tx(a1,a2,b1,b2,c1,c2); 2182 CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN 2183 INSERT INTO tx VALUES(OLD.a,NEW.a,OLD.b,NEW.b,OLD.c,NEW.c); 2184 END; 2185 UPDATE t2 SET a=a+1; 2186 SELECT * FROM tx; 2187 } 2188 } {11 12 2 2 33 33 7 8 8 8 9 9} 2189 do_test auth-3.2 { 2190 proc auth {code arg1 arg2 arg3 arg4} { 2191 if {$code=="SQLITE_READ" && $arg1=="t2" && $arg2=="c"} { 2192 return SQLITE_IGNORE 2193 } 2194 return SQLITE_OK 2195 } 2196 execsql { 2197 DELETE FROM tx; 2198 UPDATE t2 SET a=a+100; 2199 SELECT * FROM tx; 2200 } 2201 } {12 112 2 2 {} {} 8 108 8 8 {} {}} 2202} ;# ifcapable trigger 2203 2204# Make sure the names of views and triggers are passed on on arg4. 2205# 2206ifcapable trigger { 2207do_test auth-4.1 { 2208 proc auth {code arg1 arg2 arg3 arg4} { 2209 lappend ::authargs $code $arg1 $arg2 $arg3 $arg4 2210 return SQLITE_OK 2211 } 2212 set authargs {} 2213 execsql { 2214 UPDATE t2 SET a=a+1; 2215 } 2216 set authargs 2217} [list \ 2218 SQLITE_READ t2 a main {} \ 2219 SQLITE_UPDATE t2 a main {} \ 2220 SQLITE_INSERT tx {} main r1 \ 2221 SQLITE_READ t2 a main r1 \ 2222 SQLITE_READ t2 a main r1 \ 2223 SQLITE_READ t2 b main r1 \ 2224 SQLITE_READ t2 b main r1 \ 2225 SQLITE_READ t2 c main r1 \ 2226 SQLITE_READ t2 c main r1] 2227} 2228 2229ifcapable {view && trigger} { 2230do_test auth-4.2 { 2231 execsql { 2232 CREATE VIEW v1 AS SELECT a+b AS x FROM t2; 2233 CREATE TABLE v1chng(x1,x2); 2234 CREATE TRIGGER r2 INSTEAD OF UPDATE ON v1 BEGIN 2235 INSERT INTO v1chng VALUES(OLD.x,NEW.x); 2236 END; 2237 SELECT * FROM v1; 2238 } 2239} {115 117} 2240do_test auth-4.3 { 2241 set authargs {} 2242 execsql { 2243 UPDATE v1 SET x=1 WHERE x=117 2244 } 2245 set authargs 2246} [list \ 2247 SQLITE_UPDATE v1 x main {} \ 2248 SQLITE_INSERT v1chng {} main r2 \ 2249 SQLITE_READ v1 x main r2 \ 2250 SQLITE_READ v1 x main r2 \ 2251 SQLITE_READ t2 a main v1 \ 2252 SQLITE_READ t2 b main v1 \ 2253 SQLITE_SELECT {} {} {} v1 \ 2254 SQLITE_SELECT {} {} {} v1 \ 2255 SQLITE_READ v1 x main v1 \ 2256] 2257do_test auth-4.4 { 2258 execsql { 2259 CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN 2260 INSERT INTO v1chng VALUES(OLD.x,NULL); 2261 END; 2262 SELECT * FROM v1; 2263 } 2264} {115 117} 2265do_test auth-4.5 { 2266 set authargs {} 2267 execsql { 2268 DELETE FROM v1 WHERE x=117 2269 } 2270 set authargs 2271} [list \ 2272 SQLITE_DELETE v1 {} main {} \ 2273 SQLITE_INSERT v1chng {} main r3 \ 2274 SQLITE_READ v1 x main r3 \ 2275 SQLITE_READ t2 a main v1 \ 2276 SQLITE_READ t2 b main v1 \ 2277 SQLITE_SELECT {} {} {} v1 \ 2278 SQLITE_SELECT {} {} {} v1 \ 2279 SQLITE_READ v1 x main v1 \ 2280] 2281 2282} ;# ifcapable view && trigger 2283 2284# Ticket #1338: Make sure authentication works in the presence of an AS 2285# clause. 2286# 2287do_test auth-5.1 { 2288 proc auth {code arg1 arg2 arg3 arg4} { 2289 return SQLITE_OK 2290 } 2291 execsql { 2292 SELECT count(a) AS cnt FROM t4 ORDER BY cnt 2293 } 2294} {1} 2295 2296# Ticket #1607 2297# 2298ifcapable compound&&subquery { 2299 ifcapable trigger { 2300 execsql { 2301 DROP TABLE tx; 2302 } 2303 ifcapable view { 2304 execsql { 2305 DROP TABLE v1chng; 2306 } 2307 } 2308 } 2309 do_test auth-5.2 { 2310 execsql { 2311 SELECT name FROM ( 2312 SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) 2313 WHERE type='table' 2314 ORDER BY name 2315 } 2316 } {sqlite_stat1 t1 t2 t3 t4} 2317} 2318 2319 2320rename proc {} 2321rename proc_real proc 2322 2323 2324finish_test 2325