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