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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix userauth01 18 19ifcapable !userauth { 20 finish_test 21 return 22} 23 24# Create a no-authentication-required database 25# 26do_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# 36do_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# 47do_test userauth01-1.2 { 48 sqlite3_user_add db bob pw-4-bob 0 49} {SQLITE_AUTH} 50do_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# 62do_test userauth01-1.4 { 63 sqlite3_user_add db alice pw-4-alice 1 64} {SQLITE_OK} 65do_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# 76do_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# 88do_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} 96do_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# 104do_test userauth01-1.9 { 105 sqlite3_user_change db2 cindy xyzzy-cindy 0 106} {SQLITE_OK} 107do_test userauth01-1.10 { 108 sqlite3_user_authenticate db2 cindy pw-4-cindy 109} {SQLITE_AUTH} 110do_test userauth01-1.11 { 111 sqlite3_user_authenticate db2 cindy xyzzy-cindy 112} {SQLITE_OK} 113do_test userauth01-1.12 { 114 sqlite3_user_change db alice xyzzy-alice 1 115} {SQLITE_OK} 116do_test userauth01-1.13 { 117 sqlite3_user_authenticate db alice pw-4-alice 118} {SQLITE_AUTH} 119do_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# 125do_test userauth01-1.15 { 126 sqlite3_user_change db alice xyzzy-alice 0 127} {SQLITE_AUTH} 128do_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} 131do_test userauth01-1.17 { 132 sqlite3_user_change db2 cindy xyzzy-cindy 1 133} {SQLITE_AUTH} 134do_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# 141do_test userauth01-1.20 { 142 sqlite3_user_change db david xyzzy-david 1 143} {SQLITE_OK} 144do_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} 147do_test userauth01-1.22 { 148 sqlite3_user_authenticate db2 david xyzzy-david 149} {SQLITE_OK} 150do_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} 153do_test userauth01-1.24 { 154 sqlite3_user_change db david pw-4-david 0 155} {SQLITE_OK} 156do_test userauth01-1.25 { 157 sqlite3_user_authenticate db2 david pw-4-david 158} {SQLITE_OK} 159do_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} 162do_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# 169do_test userauth01-1.30 { 170 sqlite3_user_change db2 bob xyzzy-bob 1 171} {SQLITE_AUTH} 172do_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# 179do_test userauth01-1.40 { 180 sqlite3_user_delete db bob 181} {SQLITE_OK} 182do_test userauth01-1.41 { 183 db eval {SELECT uname, isadmin FROM sqlite_user ORDER BY uname} 184} {alice 1 cindy 0 david 0} 185do_test userauth01-1.42 { 186 sqlite3_user_delete db2 cindy 187} {SQLITE_AUTH} 188do_test userauth01-1.43 { 189 sqlite3_user_delete db2 alice 190} {SQLITE_AUTH} 191do_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# 197do_test userauth01-1.50 { 198 sqlite3_user_delete db alice 199} {SQLITE_AUTH} 200do_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# 209do_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} 214do_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} 220do_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} 227do_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}} 234do_test userauth01-1.64 { 235 sqlite3_extended_errcode db 236} {SQLITE_AUTH} 237do_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# 245proc auth {args} { 246 lappend ::authargs $args 247 return SQLITE_OK 248} 249do_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 257finish_test 258