Notes Related to User Authentication in LumoSQL

Lessons from the BDB Port

User authentication is part of standard SQLite but not many people know about it. This is not part of the encryption API whose implementation is only available via third party patches or as closed source. LumoSQL has to make a decision as to whether to mandate encryption if user authentication is required.

The SQLite mechanism is a bit ragged around the edges see https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt . For example "Calling sqlite3_user_authenticate() on a no-authentication-required database connection is a harmless no-op.", which is not acceptable security practice.

According to the aims of LumoSQL we want to be as close to 100% compatible across all backends as possible (and of course definitely 100% compatible when the original backend is in use.)

Oracle invested in bdb-sql and chose to clean up authentication a bit and made some modestly incompatible changes as per https://docs.oracle.com/database/bdb181/html/bdb-sql/user_authentication.html .

These changes include this sensible justification for always insisting that encryption is enabled if user authentication is enabled:

Security Considerations

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