SQLite User Permissions

By

Published · Updated

Tagged: Database

Understand why SQLite has no user permissions: GRANT and REVOKE are not available because the database is a single file, so handle access in your app.

I covered user permissions in MySQL and PostgreSQL.

One thing to note about SQLite is that permissions management, using GRANT and REVOKE, is not available.

It’s not available because it’s not possible.

The reason is that an SQLite database is self-contained in a single file.

This is due to the SQLite architecture. There’s no server process sitting between you and the data. Your app opens the file and reads it directly, so there’s nobody to check who you are.

Anything with access to SQLite file can access anything inside the database.

There is no way to give permissions at the database level.

There are a few things you can do, though.

Use file permissions

The SQLite file is a regular file, so the operating system decides who can read or write it. On Linux and macOS:

chmod 600 app.db   # only the owner can read and write
chmod 644 app.db   # others can read, only the owner can write
chmod 444 app.db   # nobody can write

Writing to a file you can only read fails with:

attempt to write a readonly database

SQLite also creates temporary files next to the database while it writes, like app.db-journal, or app.db-wal and app.db-shm in WAL mode. The process that writes needs write permission on the folder too, not just on the file.

This is all or nothing per file: a user can read everything or nothing. If you need some tables to be private, one option is to put them in a separate database file with stricter permissions, and ATTACH it only where it’s needed.

Open the database read-only

When a part of your app only needs to read, open the database read-only. Then no bug in that code can change the data.

In the sqlite3 command line tool:

sqlite3 -readonly app.db

Or with a URI filename, which works in most SQLite libraries:

sqlite3 'file:app.db?mode=ro'

In Python:

import sqlite3

con = sqlite3.connect('file:app.db?mode=ro', uri=True)

In Node.js with the built-in node:sqlite module:

import { DatabaseSync } from 'node:sqlite'

const db = new DatabaseSync('app.db', { readOnly: true })

Any INSERT, UPDATE or DELETE on these connections fails with “attempt to write a readonly database”.

There’s also PRAGMA query_only = ON, which blocks writes on a connection. But any code on that connection can turn it back off with PRAGMA query_only = OFF, so treat it as a safety net, not as security.

Use an authorizer callback

SQLite has a C API function, sqlite3_set_authorizer(), that lets you register a function SQLite calls while it compiles each SQL statement. For every action (read a column, insert into a table, run a pragma…) your function answers:

It’s meant for running SQL that comes from untrusted sources. Many language bindings expose it. Here’s Python, hiding a column and blocking writes:

import sqlite3

con = sqlite3.connect('app.db')

def authorizer(action, arg1, arg2, db_name, source):
    # hide the password_hash column
    if action == sqlite3.SQLITE_READ and arg1 == 'users' and arg2 == 'password_hash':
        return sqlite3.SQLITE_IGNORE
    # block writes and pragmas
    if action in (sqlite3.SQLITE_INSERT, sqlite3.SQLITE_UPDATE,
                  sqlite3.SQLITE_DELETE, sqlite3.SQLITE_PRAGMA):
        return sqlite3.SQLITE_DENY
    return sqlite3.SQLITE_OK

con.set_authorizer(authorizer)

con.execute('SELECT email, password_hash FROM users').fetchall()
# [('[email protected]', None)]

con.execute('DELETE FROM users')
# sqlite3.DatabaseError: not authorized

Keep in mind this runs inside your own app. It protects you from SQL you don’t control, like queries typed by users. It does nothing against someone who can open the file with another program.

Handle permissions in your app

If your application needs to implement user permissions, you can do so at an application level, for example in your API server. Your users never touch the database. They call your API, your code checks who they are and what they’re allowed to do, and only then runs the query.

This is how most web apps work anyway, even with PostgreSQL or MySQL behind them. The app often connects as a single database user and does its own permission checks.

When to pick another database

If your app must need user permissions in the database itself, like several people or services connecting directly with different rights, you could also reconsider your DBMS choice and prefer PostgreSQL or MySQL/MariaDB instead.

Want me to talk about your product? You can sponsor this site.

Related posts about database: