| OLD | NEW |
|---|
| (Empty) |
| 1 Activate the user authentication logic by including the | |
| 2 ext/userauth/userauth.c source code file in the build and | |
| 3 adding the -DSQLITE_USER_AUTHENTICATION compile-time option. | |
| 4 The ext/userauth/sqlite3userauth.h header file is available to | |
| 5 applications to define the interface. | |
| 6 | |
| 7 When using the SQLite amalgamation, it is sufficient to append | |
| 8 the ext/userauth/userauth.c source file onto the end of the | |
| 9 amalgamation. | |
| 10 | |
| 11 The following new APIs are available when user authentication is | |
| 12 activated: | |
| 13 | |
| 14 int sqlite3_user_authenticate( | |
| 15 sqlite3 *db, /* The database connection */ | |
| 16 const char *zUsername, /* Username */ | |
| 17 const char *aPW, /* Password or credentials */ | |
| 18 int nPW /* Number of bytes in aPW[] */ | |
| 19 ); | |
| 20 | |
| 21 int sqlite3_user_add( | |
| 22 sqlite3 *db, /* Database connection */ | |
| 23 const char *zUsername, /* Username to be added */ | |
| 24 const char *aPW, /* Password or credentials */ | |
| 25 int nPW, /* Number of bytes in aPW[] */ | |
| 26 int isAdmin /* True to give new user admin privilege */ | |
| 27 ); | |
| 28 | |
| 29 int sqlite3_user_change( | |
| 30 sqlite3 *db, /* Database connection */ | |
| 31 const char *zUsername, /* Username to change */ | |
| 32 const void *aPW, /* Modified password or credentials */ | |
| 33 int nPW, /* Number of bytes in aPW[] */ | |
| 34 int isAdmin /* Modified admin privilege for the user */ | |
| 35 ); | |
| 36 | |
| 37 int sqlite3_user_delete( | |
| 38 sqlite3 *db, /* Database connection */ | |
| 39 const char *zUsername /* Username to remove */ | |
| 40 ); | |
| 41 | |
| 42 With this extension, a database can be marked as requiring authentication. | |
| 43 By default a database does not require authentication. | |
| 44 | |
| 45 The sqlite3_open(), sqlite3_open16(), and sqlite3_open_v2() interfaces | |
| 46 work as before: they open a new database connection. However, if the | |
| 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. | |
| 52 | |
| 53 Calling sqlite3_user_authenticate() on a no-authentication-required | |
| 54 database connection is a harmless no-op. | |
| 55 | |
| 56 If the database is encrypted, then sqlite3_key_v2() must be called first, | |
| 57 with the correct decryption key, prior to invoking sqlite3_user_authenticate(). | |
| 58 | |
| 59 To recapitulate: When opening an existing unencrypted authentication- | |
| 60 required database, the call sequence is: | |
| 61 | |
| 62 sqlite3_open_v2() | |
| 63 sqlite3_user_authenticate(); | |
| 64 /* Database is now usable */ | |
| 65 | |
| 66 To open an existing, encrypted, authentication-required database, the | |
| 67 call sequence is: | |
| 68 | |
| 69 sqlite3_open_v2(); | |
| 70 sqlite3_key_v2(); | |
| 71 sqlite3_user_authenticate(); | |
| 72 /* Database is now usable */ | |
| 73 | |
| 74 When opening a no-authentication-required database, the database | |
| 75 connection is treated as if it was authenticated as an admin user. | |
| 76 | |
| 77 When ATTACH-ing new database files to a connection, each newly attached | |
| 78 database that is an authentication-required database is checked using | |
| 79 the same username and password as supplied to the main database. If that | |
| 80 check fails, then the ATTACH command fails with an SQLITE_AUTH error. | |
| 81 | |
| 82 The sqlite3_user_add() interface can be used (by an admin user only) | |
| 83 to create a new user. When called on a no-authentication-required | |
| 84 database and when A is true, the sqlite3_user_add(D,U,P,N,A) routine | |
| 85 converts the database into an authentication-required database and | |
| 86 logs in the database connection D as user U with password P,N. | |
| 87 To convert a no-authentication-required database into an authentication- | |
| 88 required database, the isAdmin parameter must be true. If | |
| 89 sqlite3_user_add(D,U,P,N,A) is called on a no-authentication-required | |
| 90 database and A is false, then the call fails with an SQLITE_AUTH error. | |
| 91 | |
| 92 Any call to sqlite3_user_add() by a non-admin user results in an error. | |
| 93 | |
| 94 Hence, to create a new, unencrypted, authentication-required database, | |
| 95 the call sequence is: | |
| 96 | |
| 97 sqlite3_open_v2(); | |
| 98 sqlite3_user_add(); | |
| 99 | |
| 100 And to create a new, encrypted, authentication-required database, the call | |
| 101 sequence is: | |
| 102 | |
| 103 sqlite3_open_v2(); | |
| 104 sqlite3_key_v2(); | |
| 105 sqlite3_user_add(); | |
| 106 | |
| 107 The sqlite3_user_delete() interface can be used (by an admin user only) | |
| 108 to delete a user. The currently logged-in user cannot be deleted, | |
| 109 which guarantees that there is always an admin user and hence that | |
| 110 the database cannot be converted into a no-authentication-required | |
| 111 database. | |
| 112 | |
| 113 The sqlite3_user_change() interface can be used to change a users | |
| 114 login credentials or admin privilege. Any user can change their own | |
| 115 password. Only an admin user can change another users login | |
| 116 credentials or admin privilege setting. No user may change their own | |
| 117 admin privilege setting. | |
| 118 | |
| 119 The sqlite3_set_authorizer() callback is modified to take a 7th parameter | |
| 120 which is the username of the currently logged in user, or NULL for a | |
| 121 no-authentication-required database. | |
| 122 | |
| 123 ----------------------------------------------------------------------------- | |
| 124 Implementation notes: | |
| 125 | |
| 126 An authentication-required database is identified by the presence of a | |
| 127 new table: | |
| 128 | |
| 129 CREATE TABLE sqlite_user( | |
| 130 uname TEXT PRIMARY KEY, | |
| 131 isAdmin BOOLEAN, | |
| 132 pw BLOB | |
| 133 ) WITHOUT ROWID; | |
| 134 | |
| 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. | |
| 141 | |
| 142 The sqlite_user.pw field is encoded by a built-in SQL function | |
| 143 "sqlite_crypt(X,Y)". The two arguments are both BLOBs. The first argument | |
| 144 is the plaintext password supplied to the sqlite3_user_authenticate() | |
| 145 interface. The second argument is the sqlite_user.pw value and is supplied | |
| 146 so that the function can extract the "salt" used by the password encoder. | |
| 147 The result of sqlite_crypt(X,Y) is another blob which is the value that | |
| 148 ends up being stored in sqlite_user.pw. To verify credentials X supplied | |
| 149 by the sqlite3_user_authenticate() routine, SQLite runs: | |
| 150 | |
| 151 sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw) | |
| 152 | |
| 153 To compute an appropriate sqlite_user.pw value from a new or modified | |
| 154 password X, sqlite_crypt(X,NULL) is run. A new random salt is selected | |
| 155 when the second argument is NULL. | |
| 156 | |
| 157 The built-in version of of sqlite_crypt() uses a simple Ceasar-cypher | |
| 158 which prevents passwords from being revealed by searching the raw database | |
| 159 for ASCII text, but is otherwise trivally broken. For better password | |
| 160 security, the database should be encrypted using the SQLite Encryption | |
| 161 Extension or similar technology. Or, the application can use the | |
| 162 sqlite3_create_function() interface to provide an alternative | |
| 163 implementation of sqlite_crypt() that computes a stronger password hash, | |
| 164 perhaps using a cryptographic hash function like SHA1. | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
