ADDED ext/userauth/sqlite3userauth.h Index: ext/userauth/sqlite3userauth.h ================================================================== --- ext/userauth/sqlite3userauth.h +++ ext/userauth/sqlite3userauth.h @@ -0,0 +1,88 @@ +/* +** 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 + +/* +** 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 */ + int nPW, /* Number of bytes in aPW[] */ + const char *aPW /* Password or credentials */ +); + +/* +** 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 */ + int isAdmin, /* True to give new user admin privilege */ + int nPW, /* Number of bytes in aPW[] */ + const char *aPW /* Password or credentials */ +); + +/* +** 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 */ + int isAdmin, /* Modified admin privilege for the user */ + int nPW, /* Number of bytes in aPW[] */ + const char *aPW /* Modified password or credentials */ +); + +/* +** 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 */ +); + +#endif /* SQLITE_USER_AUTHENTICATION */ Index: ext/userauth/userauth.c ================================================================== --- ext/userauth/userauth.c +++ ext/userauth/userauth.c @@ -21,10 +21,11 @@ ** compile-time option. See the user-auth.txt file in the same source ** directory as this file for additional information. */ #ifdef SQLITE_USER_AUTHENTICATION #include "sqliteInt.h" +#include "sqlite3userauth.h" /* ** 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. @@ -49,10 +50,23 @@ 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); + 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 @@ -65,24 +79,12 @@ ){ sqlite3_stmt *pStmt; int rc; *peAuth = UAUTH_Unknown; - pStmt = sqlite3UserAuthPrepare(db, - "SELECT 1 FROM \"%w\".sqlite_master " - " WHERE name='sqlite_user' AND type='table'", zDb); - if( pStmt==0 ){ - return SQLITE_NOMEM; - } - rc = sqlite3_step(pStmt); - sqlite3_finalize(pStmt); - if( rc==SQLITE_DONE ){ + if( !userTableExists(db, "main") ){ *peAuth = UAUTH_Admin; /* No sqlite_user table. Everybody is admin. */ - return SQLITE_OK; - } - if( rc!=SQLITE_ROW ){ - return rc; } if( db->auth.zAuthUser==0 ){ *peAuth = UAUTH_Fail; return SQLITE_OK; } @@ -113,10 +115,46 @@ rc = userAuthCheckLogin(db, zDb, peAuth); db->auth.authLevel = savedAuthLevel; return rc; } +/* +** 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; iiauth.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); if( rc ){ return rc; /* OOM error, I/O error, etc. */ } if( authLevelauth.authLevelauth.authLevelauth.zAuthUser==0 ){ + assert( isAdmin!=0 ); + sqlite3_user_authenticate(db, zUsername, nPW, aPW); + } return SQLITE_OK; } /* ** The sqlite3_user_change() interface can be used to change a users @@ -188,15 +255,15 @@ int sqlite3_user_change( sqlite3 *db, /* Database connection */ const char *zUsername, /* Username to change */ int isAdmin, /* Modified admin privilege for the user */ int nPW, /* Number of bytes in aPW[] */ - const void *aPW /* Modified password or credentials */ + const char *aPW /* Modified password or credentials */ ){ - if( db->auth.authLevelauth.authLevelauth.zAuthUser, zUsername)!=0 - && db->auth.authLevelauth.authLevelauth.authLevelauth.authLevelauth.zAuthUser, zUsername)==0 ) return SQLITE_AUTH; return SQLITE_OK; } #endif /* SQLITE_USER_AUTHENTICATION */ DELETED ext/userauth/userauth.h Index: ext/userauth/userauth.h ================================================================== --- ext/userauth/userauth.h +++ ext/userauth/userauth.h @@ -1,88 +0,0 @@ -/* -** 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 - -/* -** 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 */ - int nPW, /* Number of bytes in aPW[] */ - const void *aPW /* Password or credentials */ -); - -/* -** 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 */ - int isAdmin, /* True to give new user admin privilege */ - int nPW, /* Number of bytes in aPW[] */ - const void *aPW /* Password or credentials */ -); - -/* -** 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 */ - int isAdmin, /* Modified admin privilege for the user */ - int nPW, /* Number of bytes in aPW[] */ - const void *aPW /* Modified password or credentials */ -); - -/* -** 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 */ -); - -#endif /* SQLITE_USER_AUTHENTICATION */ Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -44,11 +44,11 @@ # This is how we compile # TCCX = $(TCC) $(OPTS) -I. -I$(TOP)/src -I$(TOP) TCCX += -I$(TOP)/ext/rtree -I$(TOP)/ext/icu -I$(TOP)/ext/fts3 -TCCX += -I$(TOP)/ext/async +TCCX += -I$(TOP)/ext/async -I$(TOP)/ext/userauth # Object files for the SQLite library. # LIBOBJ+= vdbe.o parse.o \ alter.o analyze.o attach.o auth.o \ @@ -214,11 +214,11 @@ $(TOP)/ext/rtree/sqlite3rtree.h \ $(TOP)/ext/rtree/rtree.h \ $(TOP)/ext/rtree/rtree.c SRC += \ $(TOP)/ext/userauth/userauth.c \ - $(TOP)/ext/userauth/userauth.h + $(TOP)/ext/userauth/sqlite3userauth.h # Generated source code files # SRC += \ keywordhash.h \ @@ -378,11 +378,11 @@ EXTHDR += \ $(TOP)/ext/rtree/rtree.h EXTHDR += \ $(TOP)/ext/icu/sqliteicu.h EXTHDR += \ - $(TOP)/ext/userauth/userauth.h + $(TOP)/ext/userauth/sqlite3userauth.h # This is the default Makefile target. The objects listed here # are what get build when you type just "make" with no arguments. # all: sqlite3.h libsqlite3.a sqlite3$(EXE) Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -1693,10 +1693,13 @@ VFUNCTION(randomblob, 1, 0, 0, randomBlob ), FUNCTION(nullif, 2, 0, 1, nullifFunc ), FUNCTION(sqlite_version, 0, 0, 0, versionFunc ), FUNCTION(sqlite_source_id, 0, 0, 0, sourceidFunc ), FUNCTION(sqlite_log, 2, 0, 0, errlogFunc ), +#if SQLITE_USER_AUTHENTICATION + FUNCTION(sqlite_crypt, 2, 0, 0, sqlite3CryptFunc ), +#endif #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc ), FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc ), #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ FUNCTION(quote, 1, 0, 0, quoteFunc ), Index: src/prepare.c ================================================================== --- src/prepare.c +++ src/prepare.c @@ -720,11 +720,11 @@ u8 authLevel = UAUTH_Fail; sqlite3UserAuthCheckLogin(db, "main", &authLevel); db->auth.authLevel = authLevel; } if( db->auth.authLevelmutex); return SQLITE_ERROR; } } #endif Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -31,10 +31,13 @@ #include #include #include #include #include "sqlite3.h" +#if SQLITE_USER_AUTHENTICATION +# include "sqlite3userauth.h" +#endif #include #include #if !defined(_WIN32) && !defined(WIN32) # include @@ -3433,10 +3436,71 @@ sqlite3_trace(p->db, sql_trace_callback, p->traceOut); } #endif }else +#if SQLITE_USER_AUTHENTICATION + if( c=='u' && strncmp(azArg[0], "user", n)==0 ){ + if( nArg<2 ){ + fprintf(stderr, "Usage: .user SUBCOMMAND ...\n"); + rc = 1; + goto meta_command_exit; + } + if( strcmp(azArg[1],"login")==0 ){ + if( nArg!=4 ){ + fprintf(stderr, "Usage: .user login USER PASSWORD\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_authenticate(p->db, azArg[2], (int)strlen(azArg[3]), azArg[3]); + if( rc ){ + fprintf(stderr, "Authentication failed for user %s\n", azArg[2]); + rc = 1; + } + }else if( strcmp(azArg[1],"add")==0 ){ + if( nArg!=5 ){ + fprintf(stderr, "Usage: .user add USER ISADMIN PASSWORD\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_add(p->db, azArg[2], booleanValue(azArg[3]), + (int)strlen(azArg[4]), azArg[4]); + if( rc ){ + fprintf(stderr, "User-Add failed: %d\n", rc); + rc = 1; + } + }else if( strcmp(azArg[1],"edit")==0 ){ + if( nArg!=5 ){ + fprintf(stderr, "Usage: .user edit USER ISADMIN PASSWORD\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_change(p->db, azArg[2], booleanValue(azArg[3]), + (int)strlen(azArg[4]), azArg[4]); + if( rc ){ + fprintf(stderr, "User-Edit failed: %d\n", rc); + rc = 1; + } + }else if( strcmp(azArg[1],"delete")==0 ){ + if( nArg!=3 ){ + fprintf(stderr, "Usage: .user delete USER\n"); + rc = 1; + goto meta_command_exit; + } + rc = sqlite3_user_delete(p->db, azArg[2]); + if( rc ){ + fprintf(stderr, "User-Delete failed: %d\n", rc); + rc = 1; + } + }else{ + fprintf(stderr, "Usage: .user login|add|edit|delete ...\n"); + rc = 1; + goto meta_command_exit; + } + }else +#endif /* SQLITE_USER_AUTHENTICATION */ + if( c=='v' && strncmp(azArg[0], "version", n)==0 ){ fprintf(p->out, "SQLite %s %s\n" /*extra-version-info*/, sqlite3_libversion(), sqlite3_sourceid()); }else Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -490,10 +490,11 @@ #define SQLITE_CONSTRAINT_VTAB (SQLITE_CONSTRAINT | (9<<8)) #define SQLITE_CONSTRAINT_ROWID (SQLITE_CONSTRAINT |(10<<8)) #define SQLITE_NOTICE_RECOVER_WAL (SQLITE_NOTICE | (1<<8)) #define SQLITE_NOTICE_RECOVER_ROLLBACK (SQLITE_NOTICE | (2<<8)) #define SQLITE_WARNING_AUTOINDEX (SQLITE_WARNING | (1<<8)) +#define SQLITE_AUTH_USER (SQLITE_AUTH | (1<<8)) /* ** CAPI3REF: Flags For File Open Operations ** ** These bit values are intended for use in the Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1008,10 +1008,12 @@ #define UAUTH_Admin 3 /* Authenticated as an administrator */ /* Functions used only by user authorization logic */ int sqlite3UserAuthTable(const char*); int sqlite3UserAuthCheckLogin(sqlite3*,const char*,u8*); +void sqlite3CryptFunc(sqlite3_context*,int,sqlite3_value**); + #endif /* SQLITE_USER_AUTHENTICATION */ /*