(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.
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);
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.
This SQLite userauth will be activated in Berkeley DB SQL layer with -
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 */
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:
This enhanced keystore based userauth will be activated with - DBDBSQL_USER_AUTHENTICATION_KEYSTORE compile option.
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:
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.
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.
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.