See the following section for more information:
With the BDB user authentication extension, a database can be marked as requiring authentication. To visit an authentication-required BDB database, an authenticated user must be logged into the database connection first. Once a BDB database is marked as authentication-required, it cannot be converted back into a no-authentication-required database. Encryption is mandatory if user authentication is activated.
By default a database does not require authentication.
The BDB user authentication module will be activated
by adding the -DBDBSQL_USER_AUTHENTICATION
compile-time option. The client application must add
lang/sql/generated/sqlite3.h to work
with BDB user authentication. BDB user authentication
is based on
SQLite User Authentication.
See the following sections for more information:
The users can use the following 3 ways to work with BDB user authentication:
via C APIs
int sqlite3_user_authenticate(
sqlite3 *db, /* The database connection */
const char *zUsername, /* Username */
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 */
const char *aPW, /* Password or credentials */
int nPW, /* Number of bytes in aPW[] */
int isAdmin /* True to give new user admin privilege */
);
int sqlite3_user_change(
sqlite3 *db, /* Database connection */
const char *zUsername, /* Username to change */
const void *aPW, /* Modified password or credentials */
int nPW, /* Number of bytes in aPW[] */
int isAdmin /* Modified admin privilege for the user */
);
int sqlite3_user_delete(
sqlite3 *db, /* Database connection */
const char *zUsername /* Username to remove */
);
Via the BDB SQL user authentication PRAGMAs below:
PRAGMA bdbsql_user_login="{USER_NAME}:{USER_PWD}";
PRAGMA bdbsql_user_add="{USER_NAME}:{USER_PWD}:{IS_ADMIN}";
PRAGMA bdbsql_user_edit="{USER_NAME}:{USER_PWD}:{IS_ADMIN}";
PRAGMA bdbsql_user_delete="{USER_NAME}";
Via the BDB SQL shell commands as below:
.user login {USER_NAME} {USER_PWD}
.user add {USER_NAME} {USER_PWD} {IS_ADMIN}
.user edit {USER_NAME} {USER_PWD} {IS_ADMIN}
.user delete {USER_NAME}
You can use the sqlite3_user_authenticate() interface to log in a user into the database connection. Calling sqlite3_user_authenticate() on a no-authentication-required database connection will return an error. This is different from the original SQLite behavior.
You can use the sqlite3_user_add()/sqlite3_user_delete() interfaces to add/delete a user. It resuts in an error to call sqlite3_user_add()/sqlite3_user_delete() on an authentication-required database connection without an administrative user loged in. The currently logged-in user cannot be deleted.
You can use the sqlite3_user_change() interface to change a users login credentials or admin privilege. Any user can change their own password, but no user can change their own administrative privilege setting. Only an administrative user can change another users login credentials or administrative privilege setting.
The sqlite3_set_authorizer() callback is modified to take a 7th parameter which is the username of the currently logged in user, or NULL for a no-authentication-required database.
When ATTACH-ing new database files to a connection, each newly attached database that is an authentication-required database is checked using the same username and password as provided to the main database. If that check fails, then the ATTACH command fails with an SQLITE_AUTH error.
No-authentication-required database becomes an authentication-required database when the first user was added into the BDB database. This is called user authentication bootstrap. In bootstrap, the isAdmin parameter of the sqlite3_user_add() call must be true. After bootstrap, the first added user is logged into the database connection.
BDB user authentication APIs sqlite3_user_add()/sqlite3_user_change()/sqlite3_user_delete() work in their own transaction. It results in an error to call these APIs inside a transaction.
A BDB database is not considered as secure if it has only BDB user authentication applied status. The security issues are as follows:
Anyone with access to the device can just open the database file in binary editor to see and modify the data.
An authentication-required BDB database requires no authentication if opened by a version of BDB that omits the user authentication compile-time option
Due to the above issues BDB encryption has to be turned on when BDB user authentication is used. This requires the user to provide an encryption key before calling any of the authentication functions. If the database is encrypted, sqlite3_key_v2() must be called first, with the correct decryption key, prior to invoking sqlite3_user_authenticate()/sqlite3_user_add().
To open an existing, encrypted, authentication-required database, the call sequence is:
sqlite3_open_v2(); sqlite3_key_v2(); sqlite3_user_authenticate(); /* Database is now usable */
To create a new, encrypted, authentication-required database, the call sequence is:
sqlite3_open_v2(); sqlite3_key_v2(); sqlite3_user_add();
BDB SQL provides the key-store based user authentication to allow the user to work easily with encryption and user authentication together. In the key-store based user authentication, encryption becomes mandatory if user authentication is enabled, and user could just work with user authentication API only and without the knowledge of the encryption key.
You do this by storing the encryption key into a key-store file. The encryption key stored in the key-store file is encrypted. The key-store file, name ending with ".ks", is put under the same directory as the database environment. Each authenticated user has one entry in this key-store file. The entry contains the user's name and the encryption key. When sqlite3_user_authenticate() is called, if the encryption key is not applied to the database connection yet, BDB SQL will find the user's entry in the key-store file, compute the database encryption key with the user's password, then apply the encryption key to the database connection.
You can enable the key-store based user authentication
by adding -DBDBSQL_USER_AUTHENTICATION_KEYSTORE
compile option.
See the following sections for more information:
The key-store user authentication APIs work the same with the non-keystore user authentication.
No-authentication-required database becomes an authentication-required database until the first user is added into the BDB database. This is called user authentication bootstrap. In bootstrap, the isAdmin parameter of the sqlite3_user_add() call must be true. After bootstrap, the first added user is logged into the database connection. There are several cases related the the encryption key when doing key-store based user authentication bootstrap:
If the database file does not exist yet and user does not provide his encryption key, a random generated key will be applied to the database and be stored into the key-store file. The call sequence is:
sqlite3_open_v2(); sqlite3_user_add();
It is recommended for the user to backup the key-store file, especially when a generated random key is used.
If the database file does not exist yet and user provides the encryption key, the encryption key provided will be applied to the database and be stored into the key-store file. The call sequence is:
sqlite3_open_v2(); sqlite3_key_v2(); sqlite3_user_add();
If the database file already exists, the database is encrypted and the user provided the correct encryption key, the encryption key provided will be stored into the key-store file. The call sequence is:
sqlite3_open_v2(); sqlite3_key_v2(); sqlite3_user_add();
The bootstrap fails in case:
The database file already exists, the database is encrypted and the user provided an incorrect encryption key.
The database file already exists but the database is not encrypted.
As encryption key is already in the key-store file, the user only needs to provide the user/password details to work with the encrypted database. BDB SQL will compute the encryption key from the key-store file and apply it to the database connection. The call sequence is as below:
sqlite3_open_v2(); sqlite3_user_authenticate(); /* Database is now usable */
The user can also provide the encryption key before the sqlite3_user_authenticate() call. In this case, BDB SQL will not visit the key-store file for the encryption key. The call sequence is as below:
sqlite3_open_v2(); sqlite3_key_v2(); sqlite3_user_authenticate(); /* Database is now usable */
BDB user authentication API sqlite3_user_add()/sqlite3_user_change()/sqlite3_user_delete() works in their own transaction. It will result in an error if you call these APIs inside a transaction.
BDB key-store user authentication uses a locking file to ensure it behaves correctly in a multi-thread enviroment. In rare cases, if a system or application crash occurs while updating updating the key-store file, the locking file may not be cleaned and the next sqlite3_user_authenticate() call will be rejected. In this case, user needs to clean the .lck file under the database environment.