Authenticating Berkeley DB SQL User (without Keystore)

With the Berkeley DB user authentication extension, a database can be marked as requiring authentication.

To view an authentication-required Berkeley DB database, an authenticated user must be logged into the database connection first. Once you mark a Berkeley DB database as authentication-required, it cannot be converted into a no-authentication-required database. Encryption is mandatory if you activate user authentication.

By default a database does not require authentication. You can add the -DBDBSQL_USER_AUTHENTICATION compile-time option to activate the Berkeley DB user authentication module.

The client application must add lang/sql/generated/sqlite3.h to work with Berkeley DB user authentication. Berkeley DB user authentication is based on SQLite user authentication. For further details, read the sections below.

The Interface

You can use the following three ways to work with Berkeley DB 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 Berkeley DB 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 Berkeley DB 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 a user into the database connection. Calling sqlite3_user_authenticate() in a no-authentication-required database connection results in an error. This is different from original SQLite behavior.

  • sqlite3_user_add()/sqlite3_user_delete() interfaces to add/delete a user. Calling sqlite3_user_add()/sqlite3_user_delete() in an authentication-required database connection without an administrative user logged in results in an error. The currently logged-in user cannot be deleted.

  • sqlite3_user_change() interface to change a user’s 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 user’s login credentials or administrative privilege setting.

Modify the sqlite3_set_authorizer() callback to take a 7th parameter, which is the username of the currently logged in user, or NULL for a no-authentication-required database.

When attaching 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 in the main database. If that check fails, then the ATTACH command fails with a SQLITE_AUTH error.

Bootstrap

A no-authentication-required database becomes an authentication-required database when the first user is added into the Berkeley DB database. This is called user authentication bootstrap. In bootstrap, you must set the isAdmin parameter of the sqlite3_user_add() to true. After bootstrap, the first added user is logged into the database connection.  

Transaction

Berkeley DB user authentication APIs sqlite3_user_add()/sqlite3_user_change()/sqlite3_user_delete() work in their own transaction. Calling these APIs inside a transaction results in an error.

Security Considerations

A Berkeley DB database is not considered to be secure if it has only Berkeley DB 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 Berkeley DB database requires no authentication if opened by a version of Berkeley DB that omits the user authentication compile-time option.

Due to these issues, Berkeley DB requires encryption as a prerequisite for user authentication. Call sqlite3_key_v2() first with the correct decryption key prior to invoking sqlite3_user_authenticate()/ sqlite3_user_add().

  1. 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 */
  2. To create a new, encrypted, authentication-required database, the call sequence is: 
    sqlite3_open_v2();
sqlite3_key_v2();
sqlite3_user_add();

Related Topics