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