Index: ext/userauth/sqlite3userauth.h ================================================================== --- ext/userauth/sqlite3userauth.h +++ ext/userauth/sqlite3userauth.h @@ -34,12 +34,12 @@ ** 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 */ + 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 @@ -51,13 +51,13 @@ ** 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 */ + const char *aPW, /* Password or credentials */ int nPW, /* Number of bytes in aPW[] */ - const char *aPW /* Password or credentials */ + 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 @@ -66,13 +66,13 @@ ** 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 */ + const char *aPW, /* New password or credentials */ int nPW, /* Number of bytes in aPW[] */ - const char *aPW /* Modified password or credentials */ + 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, Index: ext/userauth/user-auth.txt ================================================================== --- ext/userauth/user-auth.txt +++ ext/userauth/user-auth.txt @@ -5,28 +5,28 @@ activated: 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 */ + 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 */ - int isAdmin, /* True to give new user admin privilege */ + const char *aPW, /* Password or credentials */ int nPW, /* Number of bytes in aPW[] */ - const void *aPW /* Password or credentials */ + int isAdmin /* True to give new user admin privilege */ ); int sqlite3_user_change( sqlite3 *db, /* Database connection */ const char *zUsername, /* Username to change */ - int isAdmin, /* Modified admin privilege for the user */ + const void *aPW, /* Modified password or credentials */ int nPW, /* Number of bytes in aPW[] */ - const void *aPW /* Modified password or credentials */ + int isAdmin /* Modified admin privilege for the user */ ); int sqlite3_user_delete( sqlite3 *db, /* Database connection */ const char *zUsername /* Username to remove */ @@ -69,16 +69,19 @@ the same username and password as supplied to the main database. If that check fails, then the ATTACH-ed database is unreadable [1g]. 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 [3], automatically makes the added user an -administrator [1b], and logs in the current connection as that user [1a]. -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. +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 the database connection D in using 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(); Index: ext/userauth/userauth.c ================================================================== --- ext/userauth/userauth.c +++ ext/userauth/userauth.c @@ -175,12 +175,12 @@ ** 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 *zPW /* Password or credentials */ + 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); @@ -215,13 +215,13 @@ ** 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 */ + const char *aPW, /* Password or credentials */ int nPW, /* Number of bytes in aPW[] */ - const char *aPW /* Password or credentials */ + int isAdmin /* True to give new user admin privilege */ ){ sqlite3_stmt *pStmt; int rc; if( db->auth.authLevelauth.zAuthUser==0 ){ assert( isAdmin!=0 ); - sqlite3_user_authenticate(db, zUsername, nPW, aPW); + sqlite3_user_authenticate(db, zUsername, aPW, nPW); } return SQLITE_OK; } /* @@ -261,13 +261,13 @@ ** 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 */ + const char *aPW, /* Modified password or credentials */ int nPW, /* Number of bytes in aPW[] */ - const char *aPW /* Modified password or credentials */ + int isAdmin /* Modified admin privilege for the user */ ){ sqlite3_stmt *pStmt; if( db->auth.authLeveldb, azArg[2], (int)strlen(azArg[3]), azArg[3]); + rc = sqlite3_user_authenticate(p->db, azArg[2], azArg[3], + (int)strlen(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"); + fprintf(stderr, "Usage: .user add USER PASSWORD ISADMIN\n"); rc = 1; goto meta_command_exit; } - rc = sqlite3_user_add(p->db, azArg[2], booleanValue(azArg[3]), - (int)strlen(azArg[4]), azArg[4]); + rc = sqlite3_user_add(p->db, azArg[2], + azArg[3], (int)strlen(azArg[3]), + booleanValue(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"); + fprintf(stderr, "Usage: .user edit USER PASSWORD ISADMIN\n"); rc = 1; goto meta_command_exit; } - rc = sqlite3_user_change(p->db, azArg[2], booleanValue(azArg[3]), - (int)strlen(azArg[4]), azArg[4]); + rc = sqlite3_user_change(p->db, azArg[2], + azArg[3], (int)strlen(azArg[3]), + booleanValue(azArg[4])); if( rc ){ fprintf(stderr, "User-Edit failed: %d\n", rc); rc = 1; } }else if( strcmp(azArg[1],"delete")==0 ){ Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -6495,10 +6495,136 @@ Tcl_AppendResult(interp, "sql error: ", sqlite3_errmsg(db), 0); return TCL_ERROR; } +#ifdef SQLITE_USER_AUTHENTICATION +#include "sqlite3userauth.h" +/* +** tclcmd: sqlite3_user_authenticate DB USERNAME PASSWORD +*/ +static int test_user_authenticate( + ClientData clientData, /* Unused */ + Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ + int objc, /* Number of arguments */ + Tcl_Obj *CONST objv[] /* Command arguments */ +){ + char *zUser = 0; + char *zPasswd = 0; + int nPasswd = 0; + sqlite3 *db; + int rc; + + if( objc!=4 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB USERNAME PASSWORD"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ){ + return TCL_ERROR; + } + zUser = Tcl_GetString(objv[2]); + zPasswd = Tcl_GetStringFromObj(objv[3], &nPasswd); + rc = sqlite3_user_authenticate(db, zUser, zPasswd, nPasswd); + Tcl_SetResult(interp, (char *)t1ErrorName(rc), TCL_STATIC); + return TCL_OK; +} +#endif /* SQLITE_USER_AUTHENTICATION */ + +#ifdef SQLITE_USER_AUTHENTICATION +/* +** tclcmd: sqlite3_user_add DB USERNAME PASSWORD ISADMIN +*/ +static int test_user_add( + ClientData clientData, /* Unused */ + Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ + int objc, /* Number of arguments */ + Tcl_Obj *CONST objv[] /* Command arguments */ +){ + char *zUser = 0; + char *zPasswd = 0; + int nPasswd = 0; + int isAdmin = 0; + sqlite3 *db; + int rc; + + if( objc!=5 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB USERNAME PASSWORD ISADMIN"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ){ + return TCL_ERROR; + } + zUser = Tcl_GetString(objv[2]); + zPasswd = Tcl_GetStringFromObj(objv[3], &nPasswd); + Tcl_GetBooleanFromObj(interp, objv[4], &isAdmin); + rc = sqlite3_user_add(db, zUser, zPasswd, nPasswd, isAdmin); + Tcl_SetResult(interp, (char *)t1ErrorName(rc), TCL_STATIC); + return TCL_OK; +} +#endif /* SQLITE_USER_AUTHENTICATION */ + +#ifdef SQLITE_USER_AUTHENTICATION +/* +** tclcmd: sqlite3_user_change DB USERNAME PASSWORD ISADMIN +*/ +static int test_user_change( + ClientData clientData, /* Unused */ + Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ + int objc, /* Number of arguments */ + Tcl_Obj *CONST objv[] /* Command arguments */ +){ + char *zUser = 0; + char *zPasswd = 0; + int nPasswd = 0; + int isAdmin = 0; + sqlite3 *db; + int rc; + + if( objc!=5 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB USERNAME PASSWORD ISADMIN"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ){ + return TCL_ERROR; + } + zUser = Tcl_GetString(objv[2]); + zPasswd = Tcl_GetStringFromObj(objv[3], &nPasswd); + Tcl_GetBooleanFromObj(interp, objv[4], &isAdmin); + rc = sqlite3_user_change(db, zUser, zPasswd, nPasswd, isAdmin); + Tcl_SetResult(interp, (char *)t1ErrorName(rc), TCL_STATIC); + return TCL_OK; +} +#endif /* SQLITE_USER_AUTHENTICATION */ + +#ifdef SQLITE_USER_AUTHENTICATION +/* +** tclcmd: sqlite3_user_delete DB USERNAME +*/ +static int test_user_delete( + ClientData clientData, /* Unused */ + Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ + int objc, /* Number of arguments */ + Tcl_Obj *CONST objv[] /* Command arguments */ +){ + char *zUser = 0; + sqlite3 *db; + int rc; + + if( objc!=3 ){ + Tcl_WrongNumArgs(interp, 1, objv, "DB USERNAME"); + return TCL_ERROR; + } + if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ){ + return TCL_ERROR; + } + zUser = Tcl_GetString(objv[2]); + rc = sqlite3_user_delete(db, zUser); + Tcl_SetResult(interp, (char *)t1ErrorName(rc), TCL_STATIC); + return TCL_OK; +} +#endif /* SQLITE_USER_AUTHENTICATION */ + /* ** Register commands with the TCL interpreter. */ int Sqlitetest1_Init(Tcl_Interp *interp){ extern int sqlite3_search_count; @@ -6732,10 +6858,17 @@ { "getrusage", test_getrusage }, #endif { "load_static_extension", tclLoadStaticExtensionCmd }, { "sorter_test_fakeheap", sorter_test_fakeheap }, { "sorter_test_sort4_helper", sorter_test_sort4_helper }, +#ifdef SQLITE_USER_AUTHENTICATION + { "sqlite3_user_authenticate", test_user_authenticate, 0 }, + { "sqlite3_user_add", test_user_add, 0 }, + { "sqlite3_user_change", test_user_change, 0 }, + { "sqlite3_user_delete", test_user_delete, 0 }, +#endif + }; static int bitmask_size = sizeof(Bitmask)*8; int i; extern int sqlite3_sync_count, sqlite3_fullsync_count; extern int sqlite3_opentemp_count; ADDED test/userauth01.test Index: test/userauth01.test ================================================================== --- test/userauth01.test +++ test/userauth01.test @@ -0,0 +1,74 @@ +# 2014-09-10 +# +# 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 implements tests of the SQLITE_USER_AUTHENTICATION extension. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix userauth01 + +ifcapable !userauth { + finish_test + return +} + +# Create a no-authentication-required database +# +do_execsql_test userauth01-1.0 { + CREATE TABLE t1(x); + INSERT INTO t1 VALUES(1),(2.5),('three'),(x'4444'),(NULL); + SELECT quote(x) FROM t1 ORDER BY x; + SELECT name FROM sqlite_master; +} {NULL 1 2.5 'three' X'4444' t1} + +# Calling sqlite3_user_authenticate() on a no-authentication-required +# database connection is a harmless no-op. +# +do_test userauth01-1.1 { + sqlite3_user_authenticate db alice pw-4-alice + execsql { + SELECT quote(x) FROM t1 ORDER BY x; + SELECT name FROM sqlite_master; + } +} {NULL 1 2.5 'three' X'4444' t1} + +# 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. +# +do_test userauth01-1.2 { + sqlite3_user_add db bob pw-4-bob 0 +} {SQLITE_AUTH} +do_test userauth01-1.3 { + execsql { + SELECT quote(x) FROM t1 ORDER BY x; + SELECT name FROM sqlite_master; + } +} {NULL 1 2.5 'three' X'4444' t1} + +# 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 the database connection D in using user U with password P,N. +# +do_test userauth01-1.4 { + sqlite3_user_add db alice pw-4-alice 1 +} {SQLITE_OK} +do_test userauth01-1.5 { + execsql { + SELECT quote(x) FROM t1 ORDER BY x; + SELECT uname, isadmin FROM sqlite_user ORDER BY uname; + SELECT name FROM sqlite_master ORDER BY name; + } +} {NULL 1 2.5 'three' X'4444' alice 1 sqlite_user t1} + + +finish_test