gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/userauth01.test (about) 1 # 2014-09-10 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 # 12 # This file implements tests of the SQLITE_USER_AUTHENTICATION extension. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix userauth01 18 19 ifcapable !userauth { 20 finish_test 21 return 22 } 23 24 # Create a no-authentication-required database 25 # 26 do_execsql_test userauth01-1.0 { 27 CREATE TABLE t1(x); 28 INSERT INTO t1 VALUES(1),(2.5),('three'),(x'4444'),(NULL); 29 SELECT quote(x) FROM t1 ORDER BY x; 30 SELECT name FROM sqlite_master; 31 } {NULL 1 2.5 'three' X'4444' t1} 32 33 # Calling sqlite3_user_authenticate() on a no-authentication-required 34 # database connection is a harmless no-op. 35 # 36 do_test userauth01-1.1 { 37 sqlite3_user_authenticate db alice pw-4-alice 38 execsql { 39 SELECT quote(x) FROM t1 ORDER BY x; 40 SELECT name FROM sqlite_master; 41 } 42 } {NULL 1 2.5 'three' X'4444' t1} 43 44 # If sqlite3_user_add(D,U,P,N,A) is called on a no-authentication-required 45 # database and A is false, then the call fails with an SQLITE_AUTH error. 46 # 47 do_test userauth01-1.2 { 48 sqlite3_user_add db bob pw-4-bob 0 49 } {SQLITE_AUTH} 50 do_test userauth01-1.3 { 51 execsql { 52 SELECT quote(x) FROM t1 ORDER BY x; 53 SELECT name FROM sqlite_master; 54 } 55 } {NULL 1 2.5 'three' X'4444' t1} 56 57 # When called on a no-authentication-required 58 # database and when A is true, the sqlite3_user_add(D,U,P,N,A) routine 59 # converts the database into an authentication-required database and 60 # logs the database connection D in using user U with password P,N. 61 # 62 do_test userauth01-1.4 { 63 sqlite3_user_add db alice pw-4-alice 1 64 } {SQLITE_OK} 65 do_test userauth01-1.5 { 66 execsql { 67 SELECT quote(x) FROM t1 ORDER BY x; 68 SELECT uname, isadmin FROM sqlite_user ORDER BY uname; 69 SELECT name FROM sqlite_master ORDER BY name; 70 } 71 } {NULL 1 2.5 'three' X'4444' alice 1 sqlite_user t1} 72 73 # The sqlite3_user_add() interface can be used (by an admin user only) 74 # to create a new user. 75 # 76 do_test userauth01-1.6 { 77 sqlite3_user_add db bob pw-4-bob 0 78 sqlite3_user_add db cindy pw-4-cindy 0 79 sqlite3_user_add db david pw-4-david 0 80 execsql { 81 SELECT uname, isadmin FROM sqlite_user ORDER BY uname; 82 } 83 } {alice 1 bob 0 cindy 0 david 0} 84 85 # The sqlite_user table is inaccessible (unreadable and unwriteable) to 86 # non-admin users and is read-only for admin users. However, if the same 87 # 88 do_test userauth01-1.7 { 89 sqlite3 db2 test.db 90 sqlite3_user_authenticate db2 cindy pw-4-cindy 91 db2 eval { 92 SELECT quote(x) FROM t1 ORDER BY x; 93 SELECT name FROM sqlite_master ORDER BY name; 94 } 95 } {NULL 1 2.5 'three' X'4444' sqlite_user t1} 96 do_test userauth01-1.8 { 97 catchsql { 98 SELECT uname, isadmin FROM sqlite_user ORDER BY uname; 99 } db2 100 } {1 {no such table: sqlite_user}} 101 102 # Any user can change their own password. 103 # 104 do_test userauth01-1.9 { 105 sqlite3_user_change db2 cindy xyzzy-cindy 0 106 } {SQLITE_OK} 107 do_test userauth01-1.10 { 108 sqlite3_user_authenticate db2 cindy pw-4-cindy 109 } {SQLITE_AUTH} 110 do_test userauth01-1.11 { 111 sqlite3_user_authenticate db2 cindy xyzzy-cindy 112 } {SQLITE_OK} 113 do_test userauth01-1.12 { 114 sqlite3_user_change db alice xyzzy-alice 1 115 } {SQLITE_OK} 116 do_test userauth01-1.13 { 117 sqlite3_user_authenticate db alice pw-4-alice 118 } {SQLITE_AUTH} 119 do_test userauth01-1.14 { 120 sqlite3_user_authenticate db alice xyzzy-alice 121 } {SQLITE_OK} 122 123 # No user may change their own admin privilege setting. 124 # 125 do_test userauth01-1.15 { 126 sqlite3_user_change db alice xyzzy-alice 0 127 } {SQLITE_AUTH} 128 do_test userauth01-1.16 { 129 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 130 } {alice 1 bob 0 cindy 0 david 0} 131 do_test userauth01-1.17 { 132 sqlite3_user_change db2 cindy xyzzy-cindy 1 133 } {SQLITE_AUTH} 134 do_test userauth01-1.18 { 135 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 136 } {alice 1 bob 0 cindy 0 david 0} 137 138 # The sqlite3_user_change() interface can be used to change a users 139 # login credentials or admin privilege. 140 # 141 do_test userauth01-1.20 { 142 sqlite3_user_change db david xyzzy-david 1 143 } {SQLITE_OK} 144 do_test userauth01-1.21 { 145 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 146 } {alice 1 bob 0 cindy 0 david 1} 147 do_test userauth01-1.22 { 148 sqlite3_user_authenticate db2 david xyzzy-david 149 } {SQLITE_OK} 150 do_test userauth01-1.23 { 151 db2 eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 152 } {alice 1 bob 0 cindy 0 david 1} 153 do_test userauth01-1.24 { 154 sqlite3_user_change db david pw-4-david 0 155 } {SQLITE_OK} 156 do_test userauth01-1.25 { 157 sqlite3_user_authenticate db2 david pw-4-david 158 } {SQLITE_OK} 159 do_test userauth01-1.26 { 160 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 161 } {alice 1 bob 0 cindy 0 david 0} 162 do_test userauth01-1.27 { 163 catchsql {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} db2 164 } {1 {no such table: sqlite_user}} 165 166 # Only an admin user can change another users login 167 # credentials or admin privilege setting. 168 # 169 do_test userauth01-1.30 { 170 sqlite3_user_change db2 bob xyzzy-bob 1 171 } {SQLITE_AUTH} 172 do_test userauth01-1.31 { 173 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 174 } {alice 1 bob 0 cindy 0 david 0} 175 176 # The sqlite3_user_delete() interface can be used (by an admin user only) 177 # to delete a user. 178 # 179 do_test userauth01-1.40 { 180 sqlite3_user_delete db bob 181 } {SQLITE_OK} 182 do_test userauth01-1.41 { 183 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 184 } {alice 1 cindy 0 david 0} 185 do_test userauth01-1.42 { 186 sqlite3_user_delete db2 cindy 187 } {SQLITE_AUTH} 188 do_test userauth01-1.43 { 189 sqlite3_user_delete db2 alice 190 } {SQLITE_AUTH} 191 do_test userauth01-1.44 { 192 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 193 } {alice 1 cindy 0 david 0} 194 195 # The currently logged-in user cannot be deleted 196 # 197 do_test userauth01-1.50 { 198 sqlite3_user_delete db alice 199 } {SQLITE_AUTH} 200 do_test userauth01-1.51 { 201 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 202 } {alice 1 cindy 0 david 0} 203 204 # When ATTACH-ing new database files to a connection, each newly attached 205 # database that is an authentication-required database is checked using 206 # the same username and password as supplied to the main database. If that 207 # check fails, then the ATTACH command fails with an SQLITE_AUTH error. 208 # 209 do_test userauth01-1.60 { 210 forcedelete test3.db 211 sqlite3 db3 test3.db 212 sqlite3_user_add db3 alice xyzzy-alice 1 213 } {SQLITE_OK} 214 do_test userauth01-1.61 { 215 db3 eval { 216 CREATE TABLE t3(a,b,c); INSERT INTO t3 VALUES(1,2,3); 217 SELECT * FROM t3; 218 } 219 } {1 2 3} 220 do_test userauth01-1.62 { 221 db eval { 222 ATTACH 'test3.db' AS aux; 223 SELECT * FROM t1, t3 ORDER BY x LIMIT 1; 224 DETACH aux; 225 } 226 } {{} 1 2 3} 227 do_test userauth01-1.63 { 228 sqlite3_user_change db alice pw-4-alice 1 229 sqlite3_user_authenticate db alice pw-4-alice 230 catchsql { 231 ATTACH 'test3.db' AS aux; 232 } 233 } {1 {unable to open database: test3.db}} 234 do_test userauth01-1.64 { 235 sqlite3_extended_errcode db 236 } {SQLITE_AUTH} 237 do_test userauth01-1.65 { 238 db eval {PRAGMA database_list} 239 } {~/test3.db/} 240 241 # The sqlite3_set_authorizer() callback is modified to take a 7th parameter 242 # which is the username of the currently logged in user, or NULL for a 243 # no-authentication-required database. 244 # 245 proc auth {args} { 246 lappend ::authargs $args 247 return SQLITE_OK 248 } 249 do_test authuser01-2.1 { 250 unset -nocomplain ::authargs 251 db auth auth 252 db eval {SELECT x FROM t1} 253 set ::authargs 254 } {/SQLITE_SELECT {} {} {} {} alice/} 255 256 257 finish_test