In this chapter
GreenMart designs a real roles-and-permissions plan — users, roles, GRANT/REVOKE, and the principle of least privilege — then confronts an honest limitation: SQLite has none of this built in, so access control has to live in the application instead.
The Problem in Real Life
GreenMart now has real staff across real cities — cashiers ringing up sales, city managers pulling reports, and Mike and Sarah with full access to everything. A new cashier asks an innocent question that stops Mike cold: "Can I see how much every customer owes across every city, or just mine?"
Mike realizes he genuinely doesn't know the answer, because right now, every single person who touches the database can see and change literally everything in it. There's no difference between a brand-new cashier and Mike himself, as far as the database is concerned.
Everyone who touches this database can see everything in it. That can't be right anymore.
Mike
One Person With Full Access vs. Many People With the Right Access
One database, many different jobs
A cashier, a city manager, and an admin all need genuinely different levels of access to the same data.
Least privilege limits the blast radius of a mistake
A cashier who technically can't run DELETE can't accidentally (or maliciously) run it either.
Roles bundle permissions so they scale
Granting permissions to a "Cashier" role once is far more maintainable than granting them to every individual cashier by hand.
SQLite genuinely can't enforce any of this
No users, no GRANT, no REVOKE — access control for a SQLite-backed app has to live entirely outside the database.
What Real Database Access Control Looks Like
Real, production database systems solve this with genuine access control built into the database itself: users (individual accounts the database recognizes), roles (named bundles of permissions, like "Cashier" or "Manager"), and GRANT / REVOKE statements that give or take away specific permissions — SELECT, INSERT, UPDATE, DELETE, or even the ability to change the schema — on specific tables, for specific users or roles.
The guiding principle is least privilege: give every person and every piece of software only the access it genuinely needs to do its job, nothing more. A cashier needs to record sales and look up a customer's balance — not drop a table, not see every city's numbers, not grant permissions to anyone else.
| Role | Customers | Orders | Products | Schema Changes |
|---|---|---|---|---|
| Cashier | SELECT, UPDATE (own city) | SELECT, INSERT (own city) | SELECT | None |
| City Manager | SELECT, UPDATE (own city) | SELECT, INSERT, UPDATE (own city) | SELECT, UPDATE | None |
| Admin | SELECT, INSERT, UPDATE, DELETE | SELECT, INSERT, UPDATE, DELETE | SELECT, INSERT, UPDATE, DELETE | Full access |
This is a real, GRANT/REVOKE-backed design in a system like PostgreSQL — a Cashier role would be technically incapable of running DELETE FROM Customers, not just discouraged from it.
Here's the honest limitation worth knowing directly: SQLite has none of this. There's no CREATE USER, no GRANT, no REVOKE, no roles — SQLite is a single embedded file, with exactly one level of access: whoever can open the file can do anything to it. Real access control for a SQLite-backed application has to live outside the database entirely — in the application's own code, or at the operating system's file-permission level.
There's one technique from Act 3 that still genuinely helps here even in SQLite: a view that only exposes certain columns ("Security through Views," from the SQL Views chapter) can hide sensitive data from whoever queries it. It's a real, useful trick — but it's not the same thing as a database actually knowing who's asking and refusing certain people outright. That distinction is the entire point of this chapter.
Key Takeaway
Real database systems enforce access control themselves, through users, roles, and GRANT/REVOKE — SQLite has none of this built in, so a SQLite-backed application has to build "who's allowed to do what" entirely into its own code instead of leaning on the database to refuse anyone.
Why This Matters
Understanding the difference between "the database enforces this" and "our application code enforces this" is critical the moment more than one type of user touches a system — a bug in application code can accidentally expose or corrupt data that a real GRANT/REVOKE setup would have made structurally impossible.
GreenMart now has a real plan for who should be allowed to do what — even though SQLite itself can't enforce it directly, the design is exactly what a move to a production database system would need. Next: what happens when the server holding all of this simply goes away.