Python SQLite authorizer: deny a statement on the connection that executes it

Last updated: 1 Oct 20265 min read

tutorial

A connection authorizer can reject selected SQL operations before they run.

Operation contract

A prepared in-memory ledger holds receipt 47. Its authorizer rejects DELETE and UPDATE on this connection while allowing a SELECT. The rejected DELETE raises a database error, and a following count confirms that the row remains.

Failure boundary

The callback is a connection-local policy aid, not a sandbox for hostile Python code. Other connections do not inherit it, and code able to replace the callback can remove it. Restrict accepted SQL, connection ownership, and file access separately. The fixture denies only two action classes, not every possible write route.

Working program

import sqlite3

connection = sqlite3.connect(":memory:")
try:
    connection.execute("CREATE TABLE receipts (receipt_id INTEGER PRIMARY KEY)")
    connection.execute("INSERT INTO receipts VALUES (?)", (47,))
    connection.commit()

    def authorize_receipt_read(action, first, second, database, trigger):
        if action in (sqlite3.SQLITE_DELETE, sqlite3.SQLITE_UPDATE):
            return sqlite3.SQLITE_DENY
        return sqlite3.SQLITE_OK

    connection.set_authorizer(authorize_receipt_read)
    try:
        connection.execute("DELETE FROM receipts WHERE receipt_id = ?", (47,))
    except sqlite3.DatabaseError:
        print("delete_rejected", True)
    print("remaining", connection.execute("SELECT count(*) FROM receipts").fetchone()[0])
finally:
    connection.set_authorizer(None)
    connection.close()

Output

delete_rejected True
remaining 1

Costs and limits

The callback adds work to statement preparation and authorization. Its cost depends on statement shape; keep its decisions cheap and avoid retaining raw SQL or sensitive arguments for logging.

Common Mistakes

Installing a callback on one connection does not configure another.
A two-action denylist is not a complete read-only policy.
An authorizer cannot isolate hostile code that controls the Python process.

Connected lessons

Test this contract.

python

sqlite-authorizer-policy