SQLite Database Authorization and Access Control with Python

Sqlite Logo

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:

The return value is one of:

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:

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.