SQLite Basics: Reject SQL Actions with a Connection Authorizer

An application can reject SQL operations while a statement is being prepared. This exercise installs a narrow allowlist on one connection after creating a toy table, then proves that three different modifications are refused.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party Python packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Run the example

Save this as demonstrate-authorizer.py:

import sqlite3

connection = sqlite3.connect(":memory:")

def permit_simple_reads(action, argument1, argument2, database, trigger):
    if action in {sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ}:
        return sqlite3.SQLITE_OK
    return sqlite3.SQLITE_DENY

try:
    connection.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, quantity INTEGER)")
    connection.execute("INSERT INTO stock VALUES (?, ?)", ("pen", 10))
    connection.commit()
    connection.set_authorizer(permit_simple_reads)

    row = connection.execute("SELECT sku, quantity FROM stock").fetchone()
    assert row == ("pen", 10)
    print("Allowed read:", row[0], row[1])
    statements = [
        ("UPDATE", "UPDATE stock SET quantity = 0"),
        ("DELETE", "DELETE FROM stock"),
        ("CREATE", "CREATE TABLE unwanted (value TEXT)"),
    ]
    for label, sql in statements:
        try:
            connection.execute(sql)
        except sqlite3.DatabaseError as error:
            assert error.sqlite_errorcode == sqlite3.SQLITE_AUTH
            print("Denied:", label)
        else:
            raise AssertionError(f"Unexpectedly allowed {label}")

    assert connection.execute("SELECT quantity FROM stock").fetchone()[0] == 10
    print("Quantity unchanged: 10")
finally:
    connection.set_authorizer(None)
    connection.close()

Run it:

python3 -B demonstrate-authorizer.py

Observed output:

Allowed read: pen 10
Denied: UPDATE
Denied: DELETE
Denied: CREATE
Quantity unchanged: 10

Read the result

The read requires SELECT and READ actions, which the callback allows. Each modification requires an action outside that set and fails with SQLITE_AUTH. The last query confirms that the inventory quantity did not change.

Apply it carefully

This is intentionally a very limited query policy, not a complete sandbox for arbitrary SQL. It also rejects functions, PRAGMA statements, and other actions that legitimate queries may require. It does not restrict which table or column a read may access, limit query cost, or govern other connections. Code that owns the connection can remove the callback. Keep filesystem permissions, parameter binding, resource limits, and application authorization as separate controls. For a broader policy, inspect documented action codes and design explicit rules; do not simply permit every unrecognized action.

The official reference documents the API behavior. These checks exercise the controlled fixture above.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- demonstrate-authorizer.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.