drh | d39c40f | 2014-09-11 00:27:53 +0000 | [diff] [blame] | 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 | |
drh | 570f187 | 2014-09-11 14:40:27 +0000 | [diff] [blame] | 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} |
drh | 9d5b0df | 2014-09-11 14:56:45 +0000 | [diff] [blame] | 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} |
drh | 570f187 | 2014-09-11 14:40:27 +0000 | [diff] [blame] | 122 | |
drh | 9d5b0df | 2014-09-11 14:56:45 +0000 | [diff] [blame] | 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} |
drh | 570f187 | 2014-09-11 14:40:27 +0000 | [diff] [blame] | 203 | |
drh | a000ca6 | 2014-09-11 15:25:02 +0000 | [diff] [blame] | 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 |
drh | 7883ecf | 2014-09-11 16:19:31 +0000 | [diff] [blame] | 212 | sqlite3_user_add db3 alice xyzzy-alice 1 |
| 213 | } {SQLITE_OK} |
| 214 | do_test userauth01-1.61 { |
drh | a000ca6 | 2014-09-11 15:25:02 +0000 | [diff] [blame] | 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} |
drh | a000ca6 | 2014-09-11 15:25:02 +0000 | [diff] [blame] | 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/} |
drh | d39c40f | 2014-09-11 00:27:53 +0000 | [diff] [blame] | 240 | |
drh | 7883ecf | 2014-09-11 16:19:31 +0000 | [diff] [blame] | 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 | |
drh | d39c40f | 2014-09-11 00:27:53 +0000 | [diff] [blame] | 257 | finish_test |