/ 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 Unified Diffs Ignore Whitespace Patch

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

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
...
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
   );

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 prepare
SQL statements (using sqlite3_prepare_v2(), for example) 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().

................................................................................

    CREATE TABLE sqlite_user(
      uname TEXT PRIMARY KEY,
      isAdmin BOOLEAN,
      pw BLOB
    ) WITHOUT ROWID;

This 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







|
<
|
|
|
|







 







|
|
|
|
|
|







40
41
42
43
44
45
46
47

48
49
50
51
52
53
54
55
56
57
58
...
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
   );

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().

................................................................................

    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

Changes to ext/userauth/userauth.c.

264
265
266
267
268
269
270




271
272
273
274
275
276
277
278
279
280
281
282
283

284
285
286
287
288
289
290
291


292
293
294




295
296
297
298
299
300
301
  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;




  if( db->auth.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!=(db->auth.authLevel==UAUTH_Admin) ){
    /* Cannot change the isAdmin setting for self */
    return SQLITE_AUTH;
  }

  if( !userTableExists(db, "main") ){
    /* This routine is a no-op if the user to be modified does not exist */
    return SQLITE_OK;
  }
  pStmt = sqlite3UserAuthPrepare(db,
            "UPDATE sqlite_user SET isAdmin=%d, pw=sqlite_crypt(?1,NULL)"
            " WHERE uname=%Q", isAdmin, zUsername);
  if( pStmt==0 ) return SQLITE_NOMEM;


  sqlite3_bind_blob(pStmt, 1, aPW, nPW, SQLITE_STATIC);
  sqlite3_step(pStmt);
  return sqlite3_finalize(pStmt);




}

/*
** 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







>
>
>
>
|








|



>


<
|
|
|
|
|
>
>
|
|
|
>
>
>
>







264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
  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

Changes to test/userauth01.test.

65
66
67
68
69
70
71
72


73








































74
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








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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}

# The sqlite3_user_add() interface can be used (by an admin user only)
# to create a new user.
#
do_test userauth01-1.6 {
  sqlite3_user_add db bob pw-4-bob 0
  sqlite3_user_add db cindy pw-4-cindy 0
  sqlite3_user_add db david pw-4-david 0
  execsql {
    SELECT uname, isadmin FROM sqlite_user ORDER BY uname;
  }
} {alice 1 bob 0 cindy 0 david 0}

# The sqlite_user table is inaccessible (unreadable and unwriteable) to
# non-admin users and is read-only for admin users.  However, if the same
#
do_test userauth01-1.7 {
  sqlite3 db2 test.db
  sqlite3_user_authenticate db2 cindy pw-4-cindy
  db2 eval {
    SELECT quote(x) FROM t1 ORDER BY x;
    SELECT name FROM sqlite_master ORDER BY name;
  }
} {NULL 1 2.5 'three' X'4444' sqlite_user t1}
do_test userauth01-1.8 {
  catchsql {
    SELECT uname, isadmin FROM sqlite_user ORDER BY uname;
  } db2
} {1 {no such table: sqlite_user}}

# Any user can change their own password.  
#
do_test userauth01-1.9 {
  sqlite3_user_change db2 cindy xyzzy-cindy 0
} {SQLITE_OK}
do_test userauth01-1.10 {
  sqlite3_user_authenticate db2 cindy pw-4-cindy
} {SQLITE_AUTH}
do_test userauth01-1.11 {
  sqlite3_user_authenticate db2 cindy xyzzy-cindy
} {SQLITE_OK}



finish_test