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, but please feel free to contact me