Why does SQLite have no CREATE USER, GRANT or REVOKE, and what decides who can read a SQLite database file?
answer
- a library, not a server
- nothing to log in to
- an ordinary file on disk
- file permissions are the gate
- the authorizer limits SQL, not people
basics
~20 sSQLite runs inside the application and reads an ordinary file, so there is no server login and no GRANT or REVOKE. Anyone the operating system lets open the file can read every table in it.
solid answer
~50 sSQLite is a library linked into the application, reading and writing one ordinary file, so there is no server to authenticate against and no catalogue of users or roles. Its documentation lists `GRANT` and `REVOKE` as deliberately omitted: the only permissions that apply are the operating system's file permissions. Whoever can open the file can read every table, and with write access change any row. On a phone the app's private storage is the boundary, so anything that copies the file out, such as a device backup or a rooted device, exposes everything. Inside the app you can narrow what one connection may do with `sqlite3_set_authorizer()`, a read-only open or `PRAGMA query_only`, but those restrain your own code or untrusted SQL, not someone holding the file. Keeping data secret from that person needs file-level encryption, which the public-domain library does not include.
go deeper
Recall that SQLite is a library reading one file: no server, no logins, no GRANT. Say plainly that file permissions decide access and that a copy of the file exposes every table.
Explain what the in-library controls do: the authorizer callback, read-only opens, query_only and sqlite3_limit, and that each restrains SQL on one connection rather than identifying a caller.
Show the threat model: backups, exported files and rooted devices carry the whole database, deleted rows may linger in free pages, and only file-level encryption protects against someone holding a copy.
Frame the boundary decision: when per-user rights are a real requirement, the answer is a service in front of the file or a client-server database, not access control bolted onto an embedded library.
## Why there is nothing to log in to A client-server database runs as its own process. Clients connect over a socket, present a name and a password, and the server decides from its own catalogue what each **login** may touch. That catalogue is what `CREATE USER`, `GRANT` and `REVOKE` edit. **SQLite** has no such process. It is a library compiled into the application, and a query is a function call that reads and writes the pages of one ordinary file. There is no second party to authenticate to, so nobody exists to enforce a privilege. SQLite's own list of omitted SQL features names `GRANT` and `REVOKE`, explaining that the only access permissions that can be applied are the normal file permissions of the operating system, and that the commands would be meaningless for an embedded engine. They are not accepted and ignored: the parser rejects `GRANT SELECT ON message TO reporting` with `near "GRANT": syntax error`, and `CREATE USER` fails the same way. ## What actually decides who can read the file - **File permissions.** The operating system decides which processes may open the file for reading or writing. A process that can read it can read every table, index and schema entry; no row, column or table is hidden from it. - **Where the file lives.** On a phone, the app's private storage keeps other apps out. For most apps that directory is the whole security boundary. - **Every copy of the file.** A device backup, an exported debug bundle or a file pulled off a rooted device carries the complete database, and anyone can open an unencrypted copy with the `sqlite3` command-line tool. - **Deleted content.** With `PRAGMA secure_delete` off, which is the normal default, deleted rows can survive in free pages until they are reused. Turning it on makes SQLite overwrite deleted content with zeros, at some I/O cost. ## What the library can restrict, and what it cannot SQLite does offer controls, but every one of them acts on SQL running through a particular connection. None of them identifies a caller. | Mechanism | What it limits | What it does not stop | |---|---|---| | `sqlite3_set_authorizer()` callback | which actions SQL prepared on that connection may perform, such as `ATTACH`, `DROP TABLE` or reading a column; it answers `SQLITE_OK`, `SQLITE_IGNORE` or `SQLITE_DENY` | another process, or a person, opening the file directly | | Read-only open (`SQLITE_OPEN_READONLY`, or `mode=ro` in a URI filename) | writes through that connection | writes through any other connection | | `PRAGMA query_only=ON` | `CREATE`, `DELETE`, `DROP`, `INSERT` and `UPDATE` on that connection, which fail with `SQLITE_READONLY` | checkpoints and commits, which still run, and other connections | | `sqlite3_limit()` | how long or complex accepted SQL may be | reading the file at all | These are useful when the app runs SQL it did not write, for instance a power-user query box, or when you want a reporting connection that cannot change data even if a bug tries. They are defences against your own code and untrusted statements. They are not identity: SQLite never asks who is calling. ## Keeping the contents secret If the threat is someone who obtains a copy of the file, only the file's bytes matter: 1. Keep the database in the app's private storage, never in a shared or world-readable location. 2. Decide deliberately whether the file should be included in device backups; a backup is a copy. 3. Encrypt the file if its contents must stay secret from someone holding it. The public-domain library does not encrypt. The SQLite developers sell a drop-in encrypting version, the SQLite Encryption Extension, and other encrypting builds exist. 4. Keep the key outside the database file and outside the app's plain resources, or encryption only moves the problem. ## Where server-style access control belongs Per-user rights are a real requirement in some systems, and an embedded file cannot provide them. Two honest shapes exist: - **A service in front of the file.** One process owns the SQLite file, authenticates its clients and decides what each may do. The file is reachable only by that process. - **A client-server database.** When many people or services need different rights to the same data, a server with real logins and privileges is the right tool; how `GRANT`, roles and revocation work there is covered with the general material on relational access control. ## Common confusions - Believing `GRANT` is silently ignored. It is a syntax error, which at least fails loudly. - Treating the authorizer callback as authentication. It filters statements on one connection and knows nothing about users. - Assuming a read-only connection protects the data. It protects against writes through that handle only. - Assuming private storage means private forever. Every backup or exported copy carries the whole database, deleted content possibly included.
- If a SQLite app lets power users type their own queries, how do you stop those queries from attaching other files or dropping tables?Register an authorizer with `sqlite3_set_authorizer()` on that connection and return `SQLITE_DENY` for `SQLITE_ATTACH`, `SQLITE_DROP_TABLE` and any other action the feature does not need; the statement is then rejected with an error when it is prepared. Lower limits such as `SQLITE_LIMIT_ATTACHED` with `sqlite3_limit()`, and run the feature on a read-only connection or with `PRAGMA query_only=ON`.
- Does deleting a row in SQLite remove its text from the database file?Not necessarily. With `PRAGMA secure_delete` off, the normal default, freed content can remain in free pages until SQLite reuses them, so someone with a copy of the file may recover deleted messages with a page-level tool. Turning `secure_delete` on overwrites deleted content with zeros at some I/O cost, and `VACUUM` rebuilds the file without its free pages.
A SQLite file is like a paper notebook rather than a library with a desk clerk: nobody checks a card at the door, so whoever holds the notebook can read every page, and protection comes only from where you keep it or from writing it in cipher.
- SQLite users are created with CREATE USER, as on a database server.
- GRANT statements in SQLite are accepted and silently ignored.
- Only the app that created a SQLite file is able to open it.
- SQLite encrypts every database file by default.
- sqlite3_set_authorizer() authenticates who is connecting to the database.
- PRAGMA query_only stops other processes from writing the file.