SQLite Database Authorization and Access Control with Python
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.
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
- SQLITE_IGNORE (
2) — treat the column as NULL (for granular column access).
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_TEMP_INDEX||3||Index name||Table name|
|SQLITE_CREATE_TEMP_TRIGGER||5||Trigger name||Table name|
|SQLITE_CREATE_TRIGGER||7||Trigger name||Table name|
|SQLITE_DROP_INDEX||10||Index name||Table name|
|SQLITE_DROP_TEMP_INDEX||12||Index name||Table name|
|SQLITE_DROP_TEMP_TRIGGER||14||Trigger name||Table name|
|SQLITE_DROP_TRIGGER||16||Trigger name||Table name|
|SQLITE_PRAGMA||19||Pragma name||1st arg or NULL|
|SQLITE_READ||20||Table name||Column name|
|SQLITE_UPDATE||23||Table name||Column name|
|SQLITE_ALTER_TABLE||26||Database name||Table name|
|SQLITE_CREATE_VTABLE||29||Table name||Module name|
|SQLITE_DROP_VTABLE||30||Table name||Module name|
For Python code you can copy and paste, check out this gist.
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
- Prevent deletion of a user record by raising an exception. (
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
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
If you're curious about working with encrypted SQLite databases from Python, you might enjoy these posts:
- Dear Diary, an Encrypted Command-Line Diary with Python
- Encrypted SQLite Databases with Python and SQLCipher
Commenting has been closed, but please feel free to contact me