SQLite Database Authorization and Access Control with Python

January 23, 2018 17:02 / python sqlite / 0 comments

The Python standard library sqlite3 driver comes with a barely-documented hook for implementing basic authorization for SQLite databases. Using this hook, it is possible to register a callback that signals, via a return value, what data can be accessed by a connection.

SQLite databases are embedded in the same process as your application, so there is no master server process to act as a gatekeeper for the data stored in your database. Additionally, SQLite database files are readable by anyone with access to the database file itself (unless you are using an encryption library like sqlcipher or sqleet). Restricting access to a SQLite database, once a connection has been opened, is only possible through the use of an authorizer callback.

SQLite provides very granular settings for controlling access, along with two failure modes. Taken together, I think you'll be impressed by the degree of control that is possible.

Authorizer callback

The authorizer callback is a function you write and then register with the SQLite connection object, which subsequently is called for each operation on the database. Note that the authorizer is not global, it only is in effect for the duration of the connection it was registered with. The authorizer callback is a function that accepts 5 parameters and should return one of three different values, which instruct SQLite how to handle the access request.

The parameters are:

action (a constant defined in sqlite3.h)
argument 1 — value depends on action
argument 2 — value depends on action
database name
trigger name (if action is result of a trigger)

The return value is one of:

SQLITE_OK (0) — allow operation
SQLITE_DENY (1) — do not allow and raise a DatabaseError.
SQLITE_IGNORE (2) — treat the column as NULL (for granular column access).

Actions

The following table describes the various actions that the authorizer callback may be called with, as well as descriptions of the action-specific argument values.

Action name	Value	First parameter	Second parameter
SQLITE_CREATE_INDEX	1	Index name	Table name
SQLITE_CREATE_TABLE	2	Table name	NULL
SQLITE_CREATE_TEMP_INDEX	3	Index name	Table name
SQLITE_CREATE_TEMP_TABLE	4	Table name	NULL
SQLITE_CREATE_TEMP_TRIGGER	5	Trigger name	Table name
SQLITE_CREATE_TEMP_VIEW	6	View name	NULL
SQLITE_CREATE_TRIGGER	7	Trigger name	Table name
SQLITE_CREATE_VIEW	8	View name	NULL
SQLITE_DELETE	9	Table name	NULL
SQLITE_DROP_INDEX	10	Index name	Table name
SQLITE_DROP_TABLE	11	Table name	NULL
SQLITE_DROP_TEMP_INDEX	12	Index name	Table name
SQLITE_DROP_TEMP_TABLE	13	Table name	NULL
SQLITE_DROP_TEMP_TRIGGER	14	Trigger name	Table name
SQLITE_DROP_TEMP_VIEW	15	View name	NULL
SQLITE_DROP_TRIGGER	16	Trigger name	Table name
SQLITE_DROP_VIEW	17	View name	NULL
SQLITE_INSERT	18	Table name	NULL
SQLITE_PRAGMA	19	Pragma name	1st arg or NULL
SQLITE_READ	20	Table name	Column name
SQLITE_SELECT	21	NULL	NULL
SQLITE_TRANSACTION	22	Operation	NULL
SQLITE_UPDATE	23	Table name	Column name
SQLITE_ATTACH	24	Filename	NULL
SQLITE_DETACH	25	Database name	NULL
SQLITE_ALTER_TABLE	26	Database name	Table name
SQLITE_REINDEX	27	Index name	NULL
SQLITE_ANALYZE	28	Table name	NULL
SQLITE_CREATE_VTABLE	29	Table name	Module name
SQLITE_DROP_VTABLE	30	Table name	Module name
SQLITE_FUNCTION	31	NULL	Function name
SQLITE_SAVEPOINT	32	Operation	Savepoint name
SQLITE_RECURSIVE	33	NULL	NULL

For Python code you can copy and paste, check out this gist.

Example

Let's look at a simple example to see how we might use these hooks to:

Prevent a user's password from being read by replacing the password with NULL. (SQLITE_READ)
Prevent deletion of a user record by raising an exception. (SQLITE_DELETE)

To follow along, open an interactive terminal and run the following code to set up a database connection and create a table with sample data.

import sqlite3

db = sqlite3.connect('/tmp/auth-demo.db')
db.execute('CREATE TABLE users (username TEXT PRIMARY KEY, password TEXT)')
db.execute('INSERT INTO users (username, password) VALUES (?, ?), (?, ?)',
           ('huey', 'meow', 'mickey', 'woof'))

Now we'll declare an authorizer callback, implementing logic to ignore requests to read a user's password, and denying attempts to delete a user.

def authorizer(action, arg1, arg2, db_name, trigger_name):
    if action == SQLITE_DELETE and arg1 == 'users':
        return SQLITE_DENY  # 1
    elif action == SQLITE_READ and arg1 == 'users' and arg2 == 'password':
        return SQLITE_IGNORE  # 2
    return SQLITE_OK  # 0

Finally, we'll register the authorizer callback using the set_authorizer() method and see the effect it has on subsequent queries.

db.set_authorizer(authorizer)

cursor = db.execute('SELECT * FROM users;')
for username, password in cursor.fetchall():
    print(username, password)  # Password will be None (NULL).

# ('huey', None)
# ('mickey', None)

db.execute('DELETE FROM users WHERE username = ?', ('huey',))

# Triggers an exception:
# ------------------------------------------------------
# DatabaseError        Traceback (most recent call last)
# <ipython-input-10-04b65dd3e206> in <module>()
#       1 # Trying to delete a user will result in an error.
# ----> 2 db.execute('DELETE FROM users WHERE username '...)
#
# DatabaseError: not authorized

That's all there is to it!

Thanks for reading

Thanks for reading, I hope you found this post helpful. For fun, try implementing an authorizer callback that simply logs all writes to the database on a given connection (hint: use SQLITE_INSERT, SQLITE_UPDATE and SQLITE_DELETE).

If you're curious about working with encrypted SQLite databases from Python, you might enjoy these posts:

Comments (0)

Commenting has been closed.