4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
13 ** This file contains the bulk of the implementation of the
14 ** user-authentication extension feature. Some parts of the user-
15 ** authentication code are contained within the SQLite core (in the
16 ** src/ subdirectory of the main source code tree) but those parts
17 ** that could reasonable be separated out are moved into this file.
19 ** To compile with the user-authentication feature, append this file to
20 ** end of an SQLite amalgamation, then add the SQLITE_USER_AUTHENTICATION
21 ** compile-time option. See the user-auth.txt file in the same source
22 ** directory as this file for additional information.
24 #ifdef SQLITE_USER_AUTHENTICATION
26 # include "sqliteInt.h"
30 ** Prepare an SQL statement for use by the user authentication logic.
31 ** Return a pointer to the prepared statement on success. Return a
32 ** NULL pointer if there is an error of any kind.
34 static sqlite3_stmt
*sqlite3UserAuthPrepare(
43 int savedFlags
= db
->flags
;
45 va_start(ap
, zFormat
);
46 zSql
= sqlite3_vmprintf(zFormat
, ap
);
48 if( zSql
==0 ) return 0;
49 db
->flags
|= SQLITE_WriteSchema
;
50 rc
= sqlite3_prepare_v2(db
, zSql
, -1, &pStmt
, 0);
51 db
->flags
= savedFlags
;
54 sqlite3_finalize(pStmt
);
61 ** Check to see if the sqlite_user table exists in database zDb.
63 static int userTableExists(sqlite3
*db
, const char *zDb
){
65 sqlite3_mutex_enter(db
->mutex
);
66 sqlite3BtreeEnterAll(db
);
67 if( db
->init
.busy
==0 ){
69 sqlite3Init(db
, &zErr
);
70 sqlite3DbFree(db
, zErr
);
72 rc
= sqlite3FindTable(db
, "sqlite_user", zDb
)!=0;
73 sqlite3BtreeLeaveAll(db
);
74 sqlite3_mutex_leave(db
->mutex
);
79 ** Check to see if database zDb has a "sqlite_user" table and if it does
80 ** whether that table can authenticate zUser with nPw,zPw. Write one of
81 ** the UAUTH_* user authorization level codes into *peAuth and return a
84 static int userAuthCheckLogin(
85 sqlite3
*db
, /* The database connection to check */
86 const char *zDb
, /* Name of specific database to check */
87 u8
*peAuth
/* OUT: One of UAUTH_* constants */
92 *peAuth
= UAUTH_Unknown
;
93 if( !userTableExists(db
, "main") ){
94 *peAuth
= UAUTH_Admin
; /* No sqlite_user table. Everybody is admin. */
97 if( db
->auth
.zAuthUser
==0 ){
101 pStmt
= sqlite3UserAuthPrepare(db
,
102 "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM \"%w\".sqlite_user"
103 " WHERE uname=?2", zDb
);
104 if( pStmt
==0 ) return SQLITE_NOMEM
;
105 sqlite3_bind_blob(pStmt
, 1, db
->auth
.zAuthPW
, db
->auth
.nAuthPW
,SQLITE_STATIC
);
106 sqlite3_bind_text(pStmt
, 2, db
->auth
.zAuthUser
, -1, SQLITE_STATIC
);
107 rc
= sqlite3_step(pStmt
);
108 if( rc
==SQLITE_ROW
&& sqlite3_column_int(pStmt
,0) ){
109 *peAuth
= sqlite3_column_int(pStmt
, 1) + UAUTH_User
;
111 *peAuth
= UAUTH_Fail
;
113 return sqlite3_finalize(pStmt
);
115 int sqlite3UserAuthCheckLogin(
116 sqlite3
*db
, /* The database connection to check */
117 const char *zDb
, /* Name of specific database to check */
118 u8
*peAuth
/* OUT: One of UAUTH_* constants */
124 savedAuthLevel
= db
->auth
.authLevel
;
125 db
->auth
.authLevel
= UAUTH_Admin
;
126 rc
= userAuthCheckLogin(db
, zDb
, peAuth
);
127 db
->auth
.authLevel
= savedAuthLevel
;
132 ** If the current authLevel is UAUTH_Unknown, the take actions to figure
133 ** out what authLevel should be
135 void sqlite3UserAuthInit(sqlite3
*db
){
136 if( db
->auth
.authLevel
==UAUTH_Unknown
){
137 u8 authLevel
= UAUTH_Fail
;
138 sqlite3UserAuthCheckLogin(db
, "main", &authLevel
);
139 db
->auth
.authLevel
= authLevel
;
140 if( authLevel
<UAUTH_Admin
) db
->flags
&= ~SQLITE_WriteSchema
;
145 ** Implementation of the sqlite_crypt(X,Y) function.
147 ** If Y is NULL then generate a new hash for password X and return that
148 ** hash. If Y is not null, then generate a hash for password X using the
149 ** same salt as the previous hash Y and return the new hash.
151 void sqlite3CryptFunc(
152 sqlite3_context
*context
,
160 zIn
= sqlite3_value_blob(argv
[0]);
161 nIn
= sqlite3_value_bytes(argv
[0]);
162 if( sqlite3_value_type(argv
[1])==SQLITE_BLOB
163 && sqlite3_value_bytes(argv
[1])==nIn
+sizeof(zSalt
)
165 memcpy(zSalt
, sqlite3_value_blob(argv
[1]), sizeof(zSalt
));
167 sqlite3_randomness(sizeof(zSalt
), zSalt
);
169 zOut
= sqlite3_malloc( nIn
+sizeof(zSalt
) );
171 sqlite3_result_error_nomem(context
);
173 memcpy(zOut
, zSalt
, sizeof(zSalt
));
174 for(ii
=0; ii
<nIn
; ii
++){
175 zOut
[ii
+sizeof(zSalt
)] = zIn
[ii
]^zSalt
[ii
&0x7];
177 sqlite3_result_blob(context
, zOut
, nIn
+sizeof(zSalt
), sqlite3_free
);
182 ** If a database contains the SQLITE_USER table, then the
183 ** sqlite3_user_authenticate() interface must be invoked with an
184 ** appropriate username and password prior to enable read and write
185 ** access to the database.
187 ** Return SQLITE_OK on success or SQLITE_ERROR if the username/password
188 ** combination is incorrect or unknown.
190 ** If the SQLITE_USER table is not present in the database file, then
191 ** this interface is a harmless no-op returnning SQLITE_OK.
193 int sqlite3_user_authenticate(
194 sqlite3
*db
, /* The database connection */
195 const char *zUsername
, /* Username */
196 const char *zPW
, /* Password or credentials */
197 int nPW
/* Number of bytes in aPW[] */
200 u8 authLevel
= UAUTH_Fail
;
201 db
->auth
.authLevel
= UAUTH_Unknown
;
202 sqlite3_free(db
->auth
.zAuthUser
);
203 sqlite3_free(db
->auth
.zAuthPW
);
204 memset(&db
->auth
, 0, sizeof(db
->auth
));
205 db
->auth
.zAuthUser
= sqlite3_mprintf("%s", zUsername
);
206 if( db
->auth
.zAuthUser
==0 ) return SQLITE_NOMEM
;
207 db
->auth
.zAuthPW
= sqlite3_malloc( nPW
+1 );
208 if( db
->auth
.zAuthPW
==0 ) return SQLITE_NOMEM
;
209 memcpy(db
->auth
.zAuthPW
,zPW
,nPW
);
210 db
->auth
.nAuthPW
= nPW
;
211 rc
= sqlite3UserAuthCheckLogin(db
, "main", &authLevel
);
212 db
->auth
.authLevel
= authLevel
;
213 sqlite3ExpirePreparedStatements(db
);
215 return rc
; /* OOM error, I/O error, etc. */
217 if( authLevel
<UAUTH_User
){
218 return SQLITE_AUTH
; /* Incorrect username and/or password */
220 return SQLITE_OK
; /* Successful login */
224 ** The sqlite3_user_add() interface can be used (by an admin user only)
225 ** to create a new user. When called on a no-authentication-required
226 ** database, this routine converts the database into an authentication-
227 ** required database, automatically makes the added user an
228 ** administrator, and logs in the current connection as that user.
229 ** The sqlite3_user_add() interface only works for the "main" database, not
230 ** for any ATTACH-ed databases. Any call to sqlite3_user_add() by a
231 ** non-admin user results in an error.
233 int sqlite3_user_add(
234 sqlite3
*db
, /* Database connection */
235 const char *zUsername
, /* Username to be added */
236 const char *aPW
, /* Password or credentials */
237 int nPW
, /* Number of bytes in aPW[] */
238 int isAdmin
/* True to give new user admin privilege */
242 sqlite3UserAuthInit(db
);
243 if( db
->auth
.authLevel
<UAUTH_Admin
) return SQLITE_AUTH
;
244 if( !userTableExists(db
, "main") ){
245 if( !isAdmin
) return SQLITE_AUTH
;
246 pStmt
= sqlite3UserAuthPrepare(db
,
247 "CREATE TABLE sqlite_user(\n"
248 " uname TEXT PRIMARY KEY,\n"
249 " isAdmin BOOLEAN,\n"
252 if( pStmt
==0 ) return SQLITE_NOMEM
;
254 rc
= sqlite3_finalize(pStmt
);
257 pStmt
= sqlite3UserAuthPrepare(db
,
258 "INSERT INTO sqlite_user(uname,isAdmin,pw)"
259 " VALUES(%Q,%d,sqlite_crypt(?1,NULL))",
260 zUsername
, isAdmin
!=0);
261 if( pStmt
==0 ) return SQLITE_NOMEM
;
262 sqlite3_bind_blob(pStmt
, 1, aPW
, nPW
, SQLITE_STATIC
);
264 rc
= sqlite3_finalize(pStmt
);
266 if( db
->auth
.zAuthUser
==0 ){
267 assert( isAdmin
!=0 );
268 sqlite3_user_authenticate(db
, zUsername
, aPW
, nPW
);
274 ** The sqlite3_user_change() interface can be used to change a users
275 ** login credentials or admin privilege. Any user can change their own
276 ** login credentials. Only an admin user can change another users login
277 ** credentials or admin privilege setting. No user may change their own
278 ** admin privilege setting.
280 int sqlite3_user_change(
281 sqlite3
*db
, /* Database connection */
282 const char *zUsername
, /* Username to change */
283 const char *aPW
, /* Modified password or credentials */
284 int nPW
, /* Number of bytes in aPW[] */
285 int isAdmin
/* Modified admin privilege for the user */
291 authLevel
= db
->auth
.authLevel
;
292 if( authLevel
<UAUTH_User
){
293 /* Must be logged in to make a change */
296 if( strcmp(db
->auth
.zAuthUser
, zUsername
)!=0 ){
297 if( db
->auth
.authLevel
<UAUTH_Admin
){
298 /* Must be an administrator to change a different user */
301 }else if( isAdmin
!=(authLevel
==UAUTH_Admin
) ){
302 /* Cannot change the isAdmin setting for self */
305 db
->auth
.authLevel
= UAUTH_Admin
;
306 if( !userTableExists(db
, "main") ){
307 /* This routine is a no-op if the user to be modified does not exist */
309 pStmt
= sqlite3UserAuthPrepare(db
,
310 "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
311 " WHERE uname=%Q", isAdmin
, zUsername
);
315 sqlite3_bind_blob(pStmt
, 1, aPW
, nPW
, SQLITE_STATIC
);
317 rc
= sqlite3_finalize(pStmt
);
320 db
->auth
.authLevel
= authLevel
;
325 ** The sqlite3_user_delete() interface can be used (by an admin user only)
326 ** to delete a user. The currently logged-in user cannot be deleted,
327 ** which guarantees that there is always an admin user and hence that
328 ** the database cannot be converted into a no-authentication-required
331 int sqlite3_user_delete(
332 sqlite3
*db
, /* Database connection */
333 const char *zUsername
/* Username to remove */
336 if( db
->auth
.authLevel
<UAUTH_Admin
){
337 /* Must be an administrator to delete a user */
340 if( strcmp(db
->auth
.zAuthUser
, zUsername
)==0 ){
341 /* Cannot delete self */
344 if( !userTableExists(db
, "main") ){
345 /* This routine is a no-op if the user to be deleted does not exist */
348 pStmt
= sqlite3UserAuthPrepare(db
,
349 "DELETE FROM sqlite_user WHERE uname=%Q", zUsername
);
350 if( pStmt
==0 ) return SQLITE_NOMEM
;
352 return sqlite3_finalize(pStmt
);
355 #endif /* SQLITE_USER_AUTHENTICATION */