/ Check-in [52d440c7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the sqlite3_user_change() interface so that it does allow a non-admin user to change their own password.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | user-auth
Files: files | file ages | folders
SHA1: 52d440c7e1b07fc03f14ed5fa4cc4c89a75cd430
User & Date: drh 2014-09-11 14:40:27
Context
2014-09-11
14:56
Get the sqlite3_user_delete() interface working. check-in: 974a9c65 user: drh tags: user-auth
14:40
Fix the sqlite3_user_change() interface so that it does allow a non-admin user to change their own password. check-in: 52d440c7 user: drh tags: user-auth
14:01
Move user authentication blocking from sqlite3_prepare() over to the table lock generator, thus allowing SQL statements (like "PRAGMA locking_mode") that do not touch database content to run prior to authentication. check-in: 70121e7c user: drh tags: user-auth
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/userauth/user-auth.txt.

    40     40      );
    41     41   
    42     42   With this extension, a database can be marked as requiring authentication.
    43     43   By default a database does not require authentication.
    44     44   
    45     45   The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces
    46     46   work as before: they open a new database connection.  However, if the
    47         -database being opened requires authentication, then attempts to prepare
    48         -SQL statements (using sqlite3_prepare_v2(), for example) will fail
    49         -with an SQLITE_AUTH error until after sqlite3_user_authenticate()
    50         -has been called successfully.  The sqlite3_user_authenticate() call
    51         -will return SQLITE_OK if the authentication credentials are accepted
    52         -and SQLITE_ERROR if not.
           47  +database being opened requires authentication, then attempts to read
           48  +or write from the database will fail with an SQLITE_AUTH error until 
           49  +after sqlite3_user_authenticate() has been called successfully.  The 
           50  +sqlite3_user_authenticate() call will return SQLITE_OK if the 
           51  +authentication credentials are accepted and SQLITE_ERROR if not.
    53     52   
    54     53   Calling sqlite3_user_authenticate() on a no-authentication-required
    55     54   database connection is a harmless no-op.  
    56     55   
    57     56   If the database is encrypted, then sqlite3_key_v2() must be called first,
    58     57   with the correct decryption key, prior to invoking sqlite3_user_authenticate().
    59     58   
................................................................................
   129    128   
   130    129       CREATE TABLE sqlite_user(
   131    130         uname TEXT PRIMARY KEY,
   132    131         isAdmin BOOLEAN,
   133    132         pw BLOB
   134    133       ) WITHOUT ROWID;
   135    134   
   136         -This table is inaccessible (unreadable and unwriteable) to non-admin users
   137         -and is read-only for admin users.  However, if the same database file is
   138         -opened by a version of SQLite that omits the -DSQLITE_USER_AUTHENTICATION
   139         -compile-time option, then the sqlite_user table will be readable by
   140         -anybody and writeable by anybody if the "PRAGMA writable_schema=ON"
   141         -statement is run first.
          135  +The sqlite_user table is inaccessible (unreadable and unwriteable) to
          136  +non-admin users and is read-only for admin users.  However, if the same
          137  +database file is opened by a version of SQLite that omits
          138  +the -DSQLITE_USER_AUTHENTICATION compile-time option, then the sqlite_user
          139  +table will be readable by anybody and writeable by anybody if
          140  +the "PRAGMA writable_schema=ON" statement is run first.
   142    141   
   143    142   The sqlite_user.pw field is encoded by a built-in SQL function
   144    143   "sqlite_crypt(X,Y)".  The two arguments are both BLOBs.  The first argument
   145    144   is the plaintext password supplied to the sqlite3_user_authenticate()
   146    145   interface.  The second argument is the sqlite_user.pw value and is supplied
   147    146   so that the function can extract the "salt" used by the password encoder.
   148    147   The result of sqlite_crypt(X,Y) is another blob which is the value that

Changes to ext/userauth/userauth.c.

   264    264     sqlite3 *db,           /* Database connection */
   265    265     const char *zUsername, /* Username to change */
   266    266     const char *aPW,       /* Modified password or credentials */
   267    267     int nPW,               /* Number of bytes in aPW[] */
   268    268     int isAdmin            /* Modified admin privilege for the user */
   269    269   ){
   270    270     sqlite3_stmt *pStmt;
   271         -  if( db->auth.authLevel<UAUTH_User ){
          271  +  int rc;
          272  +  u8 authLevel;
          273  +
          274  +  authLevel = db->auth.authLevel;
          275  +  if( authLevel<UAUTH_User ){
   272    276       /* Must be logged in to make a change */
   273    277       return SQLITE_AUTH;
   274    278     }
   275    279     if( strcmp(db->auth.zAuthUser, zUsername)!=0 ){
   276    280       if( db->auth.authLevel<UAUTH_Admin ){
   277    281         /* Must be an administrator to change a different user */
   278    282         return SQLITE_AUTH;
   279    283       }
   280         -  }else if( isAdmin!=(db->auth.authLevel==UAUTH_Admin) ){
          284  +  }else if( isAdmin!=(authLevel==UAUTH_Admin) ){
   281    285       /* Cannot change the isAdmin setting for self */
   282    286       return SQLITE_AUTH;
   283    287     }
          288  +  db->auth.authLevel = UAUTH_Admin;
   284    289     if( !userTableExists(db, "main") ){
   285    290       /* This routine is a no-op if the user to be modified does not exist */
   286         -    return SQLITE_OK;
          291  +  }else{
          292  +    pStmt = sqlite3UserAuthPrepare(db,
          293  +              "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
          294  +              " WHERE uname=%Q", isAdmin, zUsername);
          295  +    if( pStmt==0 ){
          296  +      rc = SQLITE_NOMEM;
          297  +    }else{
          298  +      sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
          299  +      sqlite3_step(pStmt);
          300  +      rc = sqlite3_finalize(pStmt);
          301  +    }
   287    302     }
   288         -  pStmt = sqlite3UserAuthPrepare(db,
   289         -            "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
   290         -            " WHERE uname=%Q", isAdmin, zUsername);
   291         -  if( pStmt==0 ) return SQLITE_NOMEM;
   292         -  sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
   293         -  sqlite3_step(pStmt);
   294         -  return sqlite3_finalize(pStmt);
          303  +  db->auth.authLevel = authLevel;
          304  +  return rc;
   295    305   }
   296    306   
   297    307   /*
   298    308   ** The sqlite3_user_delete() interface can be used (by an admin user only)
   299    309   ** to delete a user.  The currently logged-in user cannot be deleted,
   300    310   ** which guarantees that there is always an admin user and hence that
   301    311   ** the database cannot be converted into a no-authentication-required

Changes to test/userauth01.test.

    65     65   do_test userauth01-1.5 {
    66     66     execsql {
    67     67       SELECT quote(x) FROM t1 ORDER BY x;
    68     68       SELECT uname, isadmin FROM sqlite_user ORDER BY uname;
    69     69       SELECT name FROM sqlite_master ORDER BY name;
    70     70     }
    71     71   } {NULL 1 2.5 'three' X'4444' alice 1 sqlite_user t1}
           72  +
           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}
          113  +
    72    114   
    73    115   
    74    116   finish_test