Learn all about Oracle BerkeleyDB family of databases here. Scalable and high-performance data management services.

BDB User Authentication

Michael Schulman
Sr. Principal Product Manager

BDB User Authentication

(courtesy of  Brayden Zhang)


In Berkeley DB 6.2, user authentication extension is added into the SQL interface. The new user authentication layer is both secure and easy to use. In this article, we will introduce how to use the user authentication, and also explain some details.

The SQLite user authentication

User authentication was first included in SQLite 3.8.7, and was described well in the following page: http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt  There are four user authentication APIs:

sqlite3_user_authenticate(conn, username, pwd, nPwd); sqlite3_user_add(conn, username, pwd, nPwd, isAdmin);

sqlite3_user_change(conn, username, pwd, nPwd, isAdmin);

sqlite3_user_delete(conn, username);

 A database becomes a user authentication required database once the first user is added with a sqlite3_user_add() call. To visit an authentication-required SQLite database, an authenticated user must be logged into the database connection first; otherwise attempts to read or write from the database will fail with errors.

From the implementation view, SQLite stores user/password-hash in an internal sqlite_user table and verifies user login with that table.

This SQLite userauth will be activated in Berkeley DB SQL layer with -


Security considerations about the SQLite userauth

Let’s rethink about user authentication. When we are talking about user login, we mean we are putting the sensitive data into a safe zone. If a user need to access the data, we will ask the user for his name/password. Once we are sure the user is authenticated, we will let him into the safe zone. Thinking about a client/server Database, it provides the safe zone as the data is physically stored in a backend host.

Next let’s go back into the SQLite userauth, where is the safe zone? I guess the answer is: “it does not exist (yet).” It will be SQLite application’s resposibilly to provide it. In some cases it should be not so easy, such as an Android application. Anyone who has the access to this Android device could read the database file in binary editor; also if you built a SQLite without the userauth compile option, you could access an authentication-required database freely(no need for a login) 

Encryption should be a common(and neccesary) way to provide a safe zone for SQLite user authentication. In SQLite, to visit an encrypted authentication-required database, we need a call sequence as:


/* Database is now usable */ 

The next question is: who will provide the key?

  • The application could keep the key in somewhere and call sqlite3_key_v2() with the key. This means the application needs to implement some mechanism to protect the key;
  • Or the key will be remembered by every user, and he needs to provide it as well as his username/password. This sounds less attractive. 

Keystore-based userauth in Berkeley DB

It will be much better if an authentication-required database is encrypted, but a user could access the database with only his user name/password. We provide such an enhancement with a keystore file. Encryption is mandatory for a keystore based authentication-required database and we take the responsibly to protect the database encryption key. This is totally transparent to the end user. A user just does the login with a call sequence as:


/* Database is now usable */ 

In our enhanced sqlite3_user_authenticate() , we actually do things as below: 

1. fetch the database encryption key from keystore file using user name and password;
2. apply the encryption key to the database(that is, calling sqlite3_key_v2() );
3. verify user/password via sqlite_user table, as the orignal userauth does. 

This enhanced keystore based userauth will be activated with - DBDBSQL_USER_AUTHENTICATION_KEYSTORE compile option.

How the keystore file works

We store keystore entries in the keystore file. Every entry is the database encryption key which is encrypted with a salted hash value of user’s password. Say we have items below:
DEK - database encryptio key
EDEK - encrypted database encryption key
PWD - user's password
SALT - a randomly generated byte string
USERNAME - user's name
encrypt - Berkeley DB's encryption function;
decrypt - Berkeley DB's decryption function;
hash - Berkeley DB's SHA1 Secure Hash Algorithm function

When a user was added, we get the database encryption key from memory cache, and we compute 

EDEK = encrypt(DEK, hash(SALT, PWD))

 then we save a keystore entry as: (USERNAME, SALT, EDEK) . During a user login, to get the database encryption key back, first we find the keystore entry with user’s name, then we compute the database encryption key with:

DEK = decrypt(EDEK, hash(SALT, PWD)); 

Thus, by using hash/encryption, We ensure that:

  • The database encryption key in the keystore file is encrypted and could never be leaked from the keystore file;
  • Only user’s password hash is stored, so user’s password could never be leaked from the keystore file.

Make the Berkeley DB keystore userauth robust

If keystore file get corrupted, some of the end users will not be able to access the database. We try to make our keystore authentication as robust as possible.

Checksum and backup/restore

We enable checksum for the keystore file and have the checksum verified every time when keystore file was used. We also implement a simple backup/restore mechanism of the keystore file.

Locking file for the keystore

We expect the database be visited by multiple processes. To avoid keystore file from messed by multiple process writes, we introduce a lock file. Only when a process could have the .lck file
created(exclusively), it could access the keystore file; when it finished, it will remove the .lck file.

In rare cases, if user application fails when accessing the keystore file,  the locking file may not be cleaned. In this case, user needs to clean the .lck file under the database environment.

Treat updates of sqlite_user table and keystore file as an atomic operation

When adding/deleting/updating a user, we will first update the sqlite_user database table then the keystore file. We need to ensure that: Updates to sqlite_user table and keystore file will both succeed or both fail. This is guaranteed by database transactions. We put the updates of sqlite_user table and keystore file in a transaction and only commit when we succeed to update the keystore file; if we
failed to update the keystore file, we will rollback the database updates. Please note as we use transactions internally in userauth API, you should not call the userauth API within an outside transaction. If you do that, userauth API will return an error message.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.