1 # 2006 Aug 24 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: auth2.test,v 1.3 2008/07/02 13:13:53 danielk1977 Exp $ 16 # 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 # disable this test if the SQLITE_OMIT_AUTHORIZATION macro is 22 # defined during compilation. 23 if {[catch {db auth {}} msg]} { 24 finish_test 25 return 26 } 27 28 do_test auth2-1.1 { 29 execsql { 30 CREATE TABLE t1(a,b,c); 31 INSERT INTO t1 VALUES(1,2,3); 32 } 33 set ::flist {} 34 proc auth {code arg1 arg2 arg3 arg4} { 35 if {$code=="SQLITE_FUNCTION"} { 36 lappend ::flist $arg2 37 if {$arg2=="max"} { 38 return SQLITE_DENY 39 } elseif {$arg2=="min"} { 40 return SQLITE_IGNORE 41 } else { 42 return SQLITE_OK 43 } 44 } 45 return SQLITE_OK 46 } 47 db authorizer ::auth 48 catchsql {SELECT max(a,b,c) FROM t1} 49 } {1 {not authorized to use function: max}} 50 do_test auth2-1.2 { 51 set ::flist 52 } max 53 do_test auth2-1.3 { 54 set ::flist {} 55 catchsql {SELECT min(a,b,c) FROM t1} 56 } {0 {{}}} 57 do_test auth2-1.4 { 58 set ::flist 59 } min 60 do_test auth2-1.5 { 61 set ::flist {} 62 catchsql {SELECT coalesce(min(a,b,c),999) FROM t1} 63 } {0 999} 64 do_test auth2-1.6 { 65 set ::flist 66 } {coalesce min} 67 do_test auth2-1.7 { 68 set ::flist {} 69 catchsql {SELECT coalesce(a,b,c) FROM t1} 70 } {0 1} 71 do_test auth2-1.8 { 72 set ::flist 73 } coalesce 74 75 # Make sure the authorizer is not called when parsing the schema 76 # and when computing the result set of a view. 77 # 78 db close 79 sqlite3 db test.db 80 sqlite3 db2 test.db 81 proc auth {args} { 82 global authargs 83 append authargs $args\n 84 return SQLITE_OK 85 } 86 db auth auth 87 do_test auth2-2.1 { 88 set ::authargs {} 89 db eval { 90 CREATE TABLE t2(x,y,z); 91 } 92 set ::authargs 93 } {SQLITE_INSERT sqlite_master {} main {} 94 SQLITE_CREATE_TABLE t2 {} main {} 95 SQLITE_UPDATE sqlite_master type main {} 96 SQLITE_UPDATE sqlite_master name main {} 97 SQLITE_UPDATE sqlite_master tbl_name main {} 98 SQLITE_UPDATE sqlite_master rootpage main {} 99 SQLITE_UPDATE sqlite_master sql main {} 100 SQLITE_READ sqlite_master ROWID main {} 101 SQLITE_READ sqlite_master name main {} 102 SQLITE_READ sqlite_master rootpage main {} 103 SQLITE_READ sqlite_master sql main {} 104 SQLITE_READ sqlite_master tbl_name main {} 105 SQLITE_READ sqlite_master ROWID main {} 106 } 107 do_test auth2-2.2 { 108 set ::authargs {} 109 db eval { 110 CREATE VIEW v2 AS SELECT x+y AS a, y+z AS b from t2; 111 } 112 set ::authargs 113 } {SQLITE_INSERT sqlite_master {} main {} 114 SQLITE_CREATE_VIEW v2 {} main {} 115 SQLITE_UPDATE sqlite_master type main {} 116 SQLITE_UPDATE sqlite_master name main {} 117 SQLITE_UPDATE sqlite_master tbl_name main {} 118 SQLITE_UPDATE sqlite_master rootpage main {} 119 SQLITE_UPDATE sqlite_master sql main {} 120 SQLITE_READ sqlite_master ROWID main {} 121 SQLITE_READ sqlite_master name main {} 122 SQLITE_READ sqlite_master rootpage main {} 123 SQLITE_READ sqlite_master sql main {} 124 SQLITE_READ sqlite_master tbl_name main {} 125 SQLITE_READ sqlite_master ROWID main {} 126 } 127 do_test auth2-2.3 { 128 set ::authargs {} 129 db eval { 130 SELECT a, b FROM v2; 131 } 132 set ::authargs 133 } {SQLITE_SELECT {} {} {} {} 134 SQLITE_READ v2 a main {} 135 SQLITE_READ v2 b main {} 136 SQLITE_READ t2 x main v2 137 SQLITE_READ t2 y main v2 138 SQLITE_READ t2 y main v2 139 SQLITE_READ t2 z main v2 140 SQLITE_SELECT {} {} {} v2 141 } 142 do_test auth2-2.4 { 143 db2 eval { 144 CREATE TABLE t3(p,q,r); 145 } 146 set ::authargs {} 147 db eval { 148 SELECT b, a FROM v2; 149 } 150 set ::authargs 151 } {SQLITE_SELECT {} {} {} {} 152 SQLITE_READ v2 b main {} 153 SQLITE_READ v2 a main {} 154 SQLITE_READ t2 x main v2 155 SQLITE_READ t2 y main v2 156 SQLITE_READ t2 y main v2 157 SQLITE_READ t2 z main v2 158 SQLITE_SELECT {} {} {} v2 159 SQLITE_SELECT {} {} {} {} 160 SQLITE_READ v2 b main {} 161 SQLITE_READ v2 a main {} 162 SQLITE_READ t2 x main v2 163 SQLITE_READ t2 y main v2 164 SQLITE_READ t2 y main v2 165 SQLITE_READ t2 z main v2 166 SQLITE_SELECT {} {} {} v2 167 } 168 db2 close 169 170 finish_test 171
