diff options
Diffstat (limited to 'ext/userauth')
| -rw-r--r-- | ext/userauth/sqlite3userauth.h | 96 | ||||
| -rw-r--r-- | ext/userauth/user-auth.txt | 164 | ||||
| -rw-r--r-- | ext/userauth/userauth.c | 355 |
3 files changed, 615 insertions, 0 deletions
diff --git a/ext/userauth/sqlite3userauth.h b/ext/userauth/sqlite3userauth.h new file mode 100644 index 0000000..c7d23b9 --- /dev/null +++ b/ext/userauth/sqlite3userauth.h @@ -0,0 +1,96 @@ +/* +** 2014-09-08 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +************************************************************************* +** +** This file contains the application interface definitions for the +** user-authentication extension feature. +** +** To compile with the user-authentication feature, append this file to +** end of an SQLite amalgamation header file ("sqlite3.h"), then add +** the SQLITE_USER_AUTHENTICATION compile-time option. See the +** user-auth.txt file in the same source directory as this file for +** additional information. +*/ +#ifdef SQLITE_USER_AUTHENTICATION + +#ifdef __cplusplus +extern "C" { +#endif + +/* +** If a database contains the SQLITE_USER table, then the +** sqlite3_user_authenticate() interface must be invoked with an +** appropriate username and password prior to enable read and write +** access to the database. +** +** Return SQLITE_OK on success or SQLITE_ERROR if the username/password +** combination is incorrect or unknown. +** +** If the SQLITE_USER table is not present in the database file, then +** this interface is a harmless no-op returnning SQLITE_OK. +*/ +int sqlite3_user_authenticate( + sqlite3 *db, /* The database connection */ + const char *zUsername, /* Username */ + const char *aPW, /* Password or credentials */ + int nPW /* Number of bytes in aPW[] */ +); + +/* +** The sqlite3_user_add() interface can be used (by an admin user only) +** to create a new user. When called on a no-authentication-required +** database, this routine converts the database into an authentication- +** required database, automatically makes the added user an +** administrator, and logs in the current connection as that user. +** The sqlite3_user_add() interface only works for the "main" database, not +** for any ATTACH-ed databases. Any call to sqlite3_user_add() by a +** non-admin user results in an error. +*/ +int sqlite3_user_add( + sqlite3 *db, /* Database connection */ + const char *zUsername, /* Username to be added */ + const char *aPW, /* Password or credentials */ + int nPW, /* Number of bytes in aPW[] */ + int isAdmin /* True to give new user admin privilege */ +); + +/* +** The sqlite3_user_change() interface can be used to change a users +** login credentials or admin privilege. Any user can change their own +** login credentials. Only an admin user can change another users login +** credentials or admin privilege setting. No user may change their own +** admin privilege setting. +*/ +int sqlite3_user_change( + sqlite3 *db, /* Database connection */ + const char *zUsername, /* Username to change */ + const char *aPW, /* New password or credentials */ + int nPW, /* Number of bytes in aPW[] */ + int isAdmin /* Modified admin privilege for the user */ +); + +/* +** The sqlite3_user_delete() interface can be used (by an admin user only) +** to delete a user. The currently logged-in user cannot be deleted, +** which guarantees that there is always an admin user and hence that +** the database cannot be converted into a no-authentication-required +** database. +*/ +int sqlite3_user_delete( + sqlite3 *db, /* Database connection */ + const char *zUsername /* Username to remove */ +); + +#ifdef __cplusplus +} /* end of the 'extern "C"' block */ +#endif + +#endif /* SQLITE_USER_AUTHENTICATION */ diff --git a/ext/userauth/user-auth.txt b/ext/userauth/user-auth.txt new file mode 100644 index 0000000..ba4eabc --- /dev/null +++ b/ext/userauth/user-auth.txt @@ -0,0 +1,164 @@ +Activate the user authentication logic by including the +ext/userauth/userauth.c source code file in the build and +adding the -DSQLITE_USER_AUTHENTICATION compile-time option. +The ext/userauth/sqlite3userauth.h header file is available to +applications to define the interface. + +When using the SQLite amalgamation, it is sufficient to append +the ext/userauth/userauth.c source file onto the end of the +amalgamation. + +The following new APIs are available when user authentication is +activated: + + int sqlite3_user_authenticate( + sqlite3 *db, /* The database connection */ + const char *zUsername, /* Username */ + const char *aPW, /* Password or credentials */ + int nPW /* Number of bytes in aPW[] */ + ); + + int sqlite3_user_add( + sqlite3 *db, /* Database connection */ + const char *zUsername, /* Username to be added */ + const char *aPW, /* Password or credentials */ + int nPW, /* Number of bytes in aPW[] */ + int isAdmin /* True to give new user admin privilege */ + ); + + int sqlite3_user_change( + sqlite3 *db, /* Database connection */ + const char *zUsername, /* Username to change */ + const void *aPW, /* Modified password or credentials */ + int nPW, /* Number of bytes in aPW[] */ + int isAdmin /* Modified admin privilege for the user */ + ); + + int sqlite3_user_delete( + sqlite3 *db, /* Database connection */ + const char *zUsername /* Username to remove */ + ); + +With this extension, a database can be marked as requiring authentication. +By default a database does not require authentication. + +The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces +work as before: they open a new database connection. However, if the +database being opened requires authentication, then attempts to read +or write from the database will fail with an SQLITE_AUTH error until +after sqlite3_user_authenticate() has been called successfully. The +sqlite3_user_authenticate() call will return SQLITE_OK if the +authentication credentials are accepted and SQLITE_ERROR if not. + +Calling sqlite3_user_authenticate() on a no-authentication-required +database connection is a harmless no-op. + +If the database is encrypted, then sqlite3_key_v2() must be called first, +with the correct decryption key, prior to invoking sqlite3_user_authenticate(). + +To recapitulate: When opening an existing unencrypted authentication- +required database, the call sequence is: + + sqlite3_open_v2() + sqlite3_user_authenticate(); + /* Database is now usable */ + +To open an existing, encrypted, authentication-required database, the +call sequence is: + + sqlite3_open_v2(); + sqlite3_key_v2(); + sqlite3_user_authenticate(); + /* Database is now usable */ + +When opening a no-authentication-required database, the database +connection is treated as if it was authenticated as an admin user. + +When ATTACH-ing new database files to a connection, each newly attached +database that is an authentication-required database is checked using +the same username and password as supplied to the main database. If that +check fails, then the ATTACH command fails with an SQLITE_AUTH error. + +The sqlite3_user_add() interface can be used (by an admin user only) +to create a new user. When called on a no-authentication-required +database and when A is true, the sqlite3_user_add(D,U,P,N,A) routine +converts the database into an authentication-required database and +logs in the database connection D as user U with password P,N. +To convert a no-authentication-required database into an authentication- +required database, the isAdmin parameter must be true. If +sqlite3_user_add(D,U,P,N,A) is called on a no-authentication-required +database and A is false, then the call fails with an SQLITE_AUTH error. + +Any call to sqlite3_user_add() by a non-admin user results in an error. + +Hence, to create a new, unencrypted, authentication-required database, +the call sequence is: + + sqlite3_open_v2(); + sqlite3_user_add(); + +And to create a new, encrypted, authentication-required database, the call +sequence is: + + sqlite3_open_v2(); + sqlite3_key_v2(); + sqlite3_user_add(); + +The sqlite3_user_delete() interface can be used (by an admin user only) +to delete a user. The currently logged-in user cannot be deleted, +which guarantees that there is always an admin user and hence that +the database cannot be converted into a no-authentication-required +database. + +The sqlite3_user_change() interface can be used to change a users +login credentials or admin privilege. Any user can change their own +password. Only an admin user can change another users login +credentials or admin privilege setting. No user may change their own +admin privilege setting. + +The sqlite3_set_authorizer() callback is modified to take a 7th parameter +which is the username of the currently logged in user, or NULL for a +no-authentication-required database. + +----------------------------------------------------------------------------- +Implementation notes: + +An authentication-required database is identified by the presence of a +new table: + + CREATE TABLE sqlite_user( + uname TEXT PRIMARY KEY, + isAdmin BOOLEAN, + pw BLOB + ) WITHOUT ROWID; + +The sqlite_user table is inaccessible (unreadable and unwriteable) to +non-admin users and is read-only for admin users. However, if the same +database file is opened by a version of SQLite that omits +the -DSQLITE_USER_AUTHENTICATION compile-time option, then the sqlite_user +table will be readable by anybody and writeable by anybody if +the "PRAGMA writable_schema=ON" statement is run first. + +The sqlite_user.pw field is encoded by a built-in SQL function +"sqlite_crypt(X,Y)". The two arguments are both BLOBs. The first argument +is the plaintext password supplied to the sqlite3_user_authenticate() +interface. The second argument is the sqlite_user.pw value and is supplied +so that the function can extract the "salt" used by the password encoder. +The result of sqlite_crypt(X,Y) is another blob which is the value that +ends up being stored in sqlite_user.pw. To verify credentials X supplied +by the sqlite3_user_authenticate() routine, SQLite runs: + + sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw) + +To compute an appropriate sqlite_user.pw value from a new or modified +password X, sqlite_crypt(X,NULL) is run. A new random salt is selected +when the second argument is NULL. + +The built-in version of of sqlite_crypt() uses a simple Ceasar-cypher +which prevents passwords from being revealed by searching the raw database +for ASCII text, but is otherwise trivally broken. For better password +security, the database should be encrypted using the SQLite Encryption +Extension or similar technology. Or, the application can use the +sqlite3_create_function() interface to provide an alternative +implementation of sqlite_crypt() that computes a stronger password hash, +perhaps using a cryptographic hash function like SHA1. diff --git a/ext/userauth/userauth.c b/ext/userauth/userauth.c new file mode 100644 index 0000000..fa70851 --- /dev/null +++ b/ext/userauth/userauth.c @@ -0,0 +1,355 @@ +/* +** 2014-09-08 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +************************************************************************* +** +** This file contains the bulk of the implementation of the +** user-authentication extension feature. Some parts of the user- +** authentication code are contained within the SQLite core (in the +** src/ subdirectory of the main source code tree) but those parts +** that could reasonable be separated out are moved into this file. +** +** To compile with the user-authentication feature, append this file to +** end of an SQLite amalgamation, then add the SQLITE_USER_AUTHENTICATION +** compile-time option. See the user-auth.txt file in the same source +** directory as this file for additional information. +*/ +#ifdef SQLITE_USER_AUTHENTICATION +#ifndef SQLITEINT_H +# include "sqliteInt.h" +#endif + +/* +** Prepare an SQL statement for use by the user authentication logic. +** Return a pointer to the prepared statement on success. Return a +** NULL pointer if there is an error of any kind. +*/ +static sqlite3_stmt *sqlite3UserAuthPrepare( + sqlite3 *db, + const char *zFormat, + ... +){ + sqlite3_stmt *pStmt; + char *zSql; + int rc; + va_list ap; + u64 savedFlags = db->flags; + + va_start(ap, zFormat); + zSql = sqlite3_vmprintf(zFormat, ap); + va_end(ap); + if( zSql==0 ) return 0; + db->flags |= SQLITE_WriteSchema; + rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0); + db->flags = savedFlags; + sqlite3_free(zSql); + if( rc ){ + sqlite3_finalize(pStmt); + pStmt = 0; + } + return pStmt; +} + +/* +** Check to see if the sqlite_user table exists in database zDb. +*/ +static int userTableExists(sqlite3 *db, const char *zDb){ + int rc; + sqlite3_mutex_enter(db->mutex); + sqlite3BtreeEnterAll(db); + if( db->init.busy==0 ){ + char *zErr = 0; + sqlite3Init(db, &zErr); + sqlite3DbFree(db, zErr); + } + rc = sqlite3FindTable(db, "sqlite_user", zDb)!=0; + sqlite3BtreeLeaveAll(db); + sqlite3_mutex_leave(db->mutex); + return rc; +} + +/* +** Check to see if database zDb has a "sqlite_user" table and if it does +** whether that table can authenticate zUser with nPw,zPw. Write one of +** the UAUTH_* user authorization level codes into *peAuth and return a +** result code. +*/ +static int userAuthCheckLogin( + sqlite3 *db, /* The database connection to check */ + const char *zDb, /* Name of specific database to check */ + u8 *peAuth /* OUT: One of UAUTH_* constants */ +){ + sqlite3_stmt *pStmt; + int rc; + + *peAuth = UAUTH_Unknown; + if( !userTableExists(db, "main") ){ + *peAuth = UAUTH_Admin; /* No sqlite_user table. Everybody is admin. */ + return SQLITE_OK; + } + if( db->auth.zAuthUser==0 ){ + *peAuth = UAUTH_Fail; + return SQLITE_OK; + } + pStmt = sqlite3UserAuthPrepare(db, + "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM \"%w\".sqlite_user" + " WHERE uname=?2", zDb); + if( pStmt==0 ) return SQLITE_NOMEM; + sqlite3_bind_blob(pStmt, 1, db->auth.zAuthPW, db->auth.nAuthPW,SQLITE_STATIC); + sqlite3_bind_text(pStmt, 2, db->auth.zAuthUser, -1, SQLITE_STATIC); + rc = sqlite3_step(pStmt); + if( rc==SQLITE_ROW && sqlite3_column_int(pStmt,0) ){ + *peAuth = sqlite3_column_int(pStmt, 1) + UAUTH_User; + }else{ + *peAuth = UAUTH_Fail; + } + return sqlite3_finalize(pStmt); +} +int sqlite3UserAuthCheckLogin( + sqlite3 *db, /* The database connection to check */ + const char *zDb, /* Name of specific database to check */ + u8 *peAuth /* OUT: One of UAUTH_* constants */ +){ + int rc; + u8 savedAuthLevel; + assert( zDb!=0 ); + assert( peAuth!=0 ); + savedAuthLevel = db->auth.authLevel; + db->auth.authLevel = UAUTH_Admin; + rc = userAuthCheckLogin(db, zDb, peAuth); + db->auth.authLevel = savedAuthLevel; + return rc; +} + +/* +** If the current authLevel is UAUTH_Unknown, the take actions to figure +** out what authLevel should be +*/ +void sqlite3UserAuthInit(sqlite3 *db){ + if( db->auth.authLevel==UAUTH_Unknown ){ + u8 authLevel = UAUTH_Fail; + sqlite3UserAuthCheckLogin(db, "main", &authLevel); + db->auth.authLevel = authLevel; + if( authLevel<UAUTH_Admin ) db->flags &= ~SQLITE_WriteSchema; + } +} + +/* +** Implementation of the sqlite_crypt(X,Y) function. +** +** If Y is NULL then generate a new hash for password X and return that +** hash. If Y is not null, then generate a hash for password X using the +** same salt as the previous hash Y and return the new hash. +*/ +void sqlite3CryptFunc( + sqlite3_context *context, + int NotUsed, + sqlite3_value **argv +){ + const char *zIn; + int nIn, ii; + u8 *zOut; + char zSalt[8]; + zIn = sqlite3_value_blob(argv[0]); + nIn = sqlite3_value_bytes(argv[0]); + if( sqlite3_value_type(argv[1])==SQLITE_BLOB + && sqlite3_value_bytes(argv[1])==nIn+sizeof(zSalt) + ){ + memcpy(zSalt, sqlite3_value_blob(argv[1]), sizeof(zSalt)); + }else{ + sqlite3_randomness(sizeof(zSalt), zSalt); + } + zOut = sqlite3_malloc( nIn+sizeof(zSalt) ); + if( zOut==0 ){ + sqlite3_result_error_nomem(context); + }else{ + memcpy(zOut, zSalt, sizeof(zSalt)); + for(ii=0; ii<nIn; ii++){ + zOut[ii+sizeof(zSalt)] = zIn[ii]^zSalt[ii&0x7]; + } + sqlite3_result_blob(context, zOut, nIn+sizeof(zSalt), sqlite3_free); + } +} + +/* +** If a database contains the SQLITE_USER table, then the +** sqlite3_user_authenticate() interface must be invoked with an +** appropriate username and password prior to enable read and write +** access to the database. +** +** Return SQLITE_OK on success or SQLITE_ERROR if the username/password +** combination is incorrect or unknown. +** +** If the SQLITE_USER table is not present in the database file, then +** this interface is a harmless no-op returnning SQLITE_OK. +*/ +int sqlite3_user_authenticate( + sqlite3 *db, /* The database connection */ + const char *zUsername, /* Username */ + const char *zPW, /* Password or credentials */ + int nPW /* Number of bytes in aPW[] */ +){ + int rc; + u8 authLevel = UAUTH_Fail; + db->auth.authLevel = UAUTH_Unknown; + sqlite3_free(db->auth.zAuthUser); + sqlite3_free(db->auth.zAuthPW); + memset(&db->auth, 0, sizeof(db->auth)); + db->auth.zAuthUser = sqlite3_mprintf("%s", zUsername); + if( db->auth.zAuthUser==0 ) return SQLITE_NOMEM; + db->auth.zAuthPW = sqlite3_malloc( nPW+1 ); + if( db->auth.zAuthPW==0 ) return SQLITE_NOMEM; + memcpy(db->auth.zAuthPW,zPW,nPW); + db->auth.nAuthPW = nPW; + rc = sqlite3UserAuthCheckLogin(db, "main", &authLevel); + db->auth.authLevel = authLevel; + sqlite3ExpirePreparedStatements(db, 0); + if( rc ){ + return rc; /* OOM error, I/O error, etc. */ + } + if( authLevel<UAUTH_User ){ + return SQLITE_AUTH; /* Incorrect username and/or password */ + } + return SQLITE_OK; /* Successful login */ +} + +/* +** The sqlite3_user_add() interface can be used (by an admin user only) +** to create a new user. When called on a no-authentication-required +** database, this routine converts the database into an authentication- +** required database, automatically makes the added user an +** administrator, and logs in the current connection as that user. +** The sqlite3_user_add() interface only works for the "main" database, not +** for any ATTACH-ed databases. Any call to sqlite3_user_add() by a +** non-admin user results in an error. +*/ +int sqlite3_user_add( + sqlite3 *db, /* Database connection */ + const char *zUsername, /* Username to be added */ + const char *aPW, /* Password or credentials */ + int nPW, /* Number of bytes in aPW[] */ + int isAdmin /* True to give new user admin privilege */ +){ + sqlite3_stmt *pStmt; + int rc; + sqlite3UserAuthInit(db); + if( db->auth.authLevel<UAUTH_Admin ) return SQLITE_AUTH; + if( !userTableExists(db, "main") ){ + if( !isAdmin ) return SQLITE_AUTH; + pStmt = sqlite3UserAuthPrepare(db, + "CREATE TABLE sqlite_user(\n" + " uname TEXT PRIMARY KEY,\n" + " isAdmin BOOLEAN,\n" + " pw BLOB\n" + ") WITHOUT ROWID;"); + if( pStmt==0 ) return SQLITE_NOMEM; + sqlite3_step(pStmt); + rc = sqlite3_finalize(pStmt); + if( rc ) return rc; + } + pStmt = sqlite3UserAuthPrepare(db, + "INSERT INTO sqlite_user(uname,isAdmin,pw)" + " VALUES(%Q,%d,sqlite_crypt(?1,NULL))", + zUsername, isAdmin!=0); + if( pStmt==0 ) return SQLITE_NOMEM; + sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC); + sqlite3_step(pStmt); + rc = sqlite3_finalize(pStmt); + if( rc ) return rc; + if( db->auth.zAuthUser==0 ){ + assert( isAdmin!=0 ); + sqlite3_user_authenticate(db, zUsername, aPW, nPW); + } + return SQLITE_OK; +} + +/* +** The sqlite3_user_change() interface can be used to change a users +** login credentials or admin privilege. Any user can change their own +** login credentials. Only an admin user can change another users login +** credentials or admin privilege setting. No user may change their own +** admin privilege setting. +*/ +int sqlite3_user_change( + sqlite3 *db, /* Database connection */ + const char *zUsername, /* Username to change */ + const char *aPW, /* Modified password or credentials */ + int nPW, /* Number of bytes in aPW[] */ + int isAdmin /* Modified admin privilege for the user */ +){ + sqlite3_stmt *pStmt; + int rc; + u8 authLevel; + + authLevel = db->auth.authLevel; + if( authLevel<UAUTH_User ){ + /* Must be logged in to make a change */ + return SQLITE_AUTH; + } + if( strcmp(db->auth.zAuthUser, zUsername)!=0 ){ + if( db->auth.authLevel<UAUTH_Admin ){ + /* Must be an administrator to change a different user */ + return SQLITE_AUTH; + } + }else if( isAdmin!=(authLevel==UAUTH_Admin) ){ + /* Cannot change the isAdmin setting for self */ + return SQLITE_AUTH; + } + db->auth.authLevel = UAUTH_Admin; + if( !userTableExists(db, "main") ){ + /* This routine is a no-op if the user to be modified does not exist */ + }else{ + pStmt = sqlite3UserAuthPrepare(db, + "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)" + " WHERE uname=%Q", isAdmin, zUsername); + if( pStmt==0 ){ + rc = SQLITE_NOMEM; + }else{ + sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC); + sqlite3_step(pStmt); + rc = sqlite3_finalize(pStmt); + } + } + db->auth.authLevel = authLevel; + return rc; +} + +/* +** The sqlite3_user_delete() interface can be used (by an admin user only) +** to delete a user. The currently logged-in user cannot be deleted, +** which guarantees that there is always an admin user and hence that +** the database cannot be converted into a no-authentication-required +** database. +*/ +int sqlite3_user_delete( + sqlite3 *db, /* Database connection */ + const char *zUsername /* Username to remove */ +){ + sqlite3_stmt *pStmt; + if( db->auth.authLevel<UAUTH_Admin ){ + /* Must be an administrator to delete a user */ + return SQLITE_AUTH; + } + if( strcmp(db->auth.zAuthUser, zUsername)==0 ){ + /* Cannot delete self */ + return SQLITE_AUTH; + } + if( !userTableExists(db, "main") ){ + /* This routine is a no-op if the user to be deleted does not exist */ + return SQLITE_OK; + } + pStmt = sqlite3UserAuthPrepare(db, + "DELETE FROM sqlite_user WHERE uname=%Q", zUsername); + if( pStmt==0 ) return SQLITE_NOMEM; + sqlite3_step(pStmt); + return sqlite3_finalize(pStmt); +} + +#endif /* SQLITE_USER_AUTHENTICATION */ |