SovranCode
SQL: Query, Model, and Analyze Data Users, roles, and least privilege
This device
Course contentsUsers, roles, and least privilege · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONBackups, restores, and migrations
NEXT LESSONPreventing SQL injection
Security and production workflow · Lesson 29 155 min

Users, roles, and least privilege

Last lesson restored a catalog. This lesson asks who is allowed to restore, who may change cost, and who may only read the storefront. Least privilege is the rule: each login gets the smallest set of rights that still does the job. A leaked analyst password should not DROP products.

SQLite has no GRANT

PostgreSQL and MySQL create roles and GRANT SELECT ON products TO shop_readonly. This runner cannot. One row in session_staff means “this connection.” role_grants plus a WHERE EXISTS on UPDATE is the same idea in SQL you can run. On a server, prefer real GRANT and, when you need per-row rules, row-level security—not a hope that the app remembered a filter.

Least privilege is a small key ring

A physical shop does not give every cashier the safe combination. A database login is a key ring: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, CREATE, DROP, restore. Least privilege means the cashier key opens the till, not the office safe.

The website is a login. The analyst is a login. You (migrating backups) are a login. They must not share the owner password. When SQL injection appears next lesson, the damage equals whatever this login could already do.

A session is “who is connected”

PostgreSQL: you connect as clerk@shop. SQLite in the browser: you INSERT INTO session_staff VALUES (2) and join to staff. Everything else in the script should ask that table. If you forget, you are running as nobody—or as everyone, which is worse.

SQL BROWSER RUNNER

Connect as the clerk

One session row, join to staff, read email and role.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

INSERT INTO session_staff VALUES (2);

SELECT st.email, st.role
FROM session_staff AS s
JOIN staff AS st ON st.staff_id = s.staff_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which email is connected, and what would a missing session row mean for later UPDATE checks?

Hide columns the role must not see

cost_cents is not a storefront fact. Clerks need listed price and stock. A view catalog_store omits cost. PostgreSQL can also GRANT SELECT (sku, name, listed_cents, stock) ON products. Either way, SELECT * on the base table is an owner habit, not a clerk habit.

Last section's covering indexes were performance. This is secrecy. Same SQL shape: list the columns you mean.

SQL BROWSER RUNNER

Read cost from the table, then the storefront view

Same SKU, products versus catalog_store.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

INSERT INTO session_staff VALUES (2);

SELECT 'table' AS source, sku, cost_cents, listed_cents
FROM products
WHERE sku = 'NB-1';

SELECT 'view' AS source, sku, listed_cents
FROM catalog_store
WHERE sku = 'NB-1';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which result still shows 400 cost_cents, and which object should the clerk's database role be granted?

A role is a bundle of grants

Do not grant tables to each person. Grant to clerk, then attach people (and the app) to clerk. role_grants is that bundle in a table: owner may update products; clerk may update_stock; analyst may only select the view.

On PostgreSQL you would write roughly:

CREATE ROLE shop_clerk;
GRANT SELECT, UPDATE ON products TO shop_clerk;
GRANT shop_clerk TO clerk_login;

Engine GRANT stops the statement. Our UPDATE still runs, but the WHERE includes the grant check, so a non-clerk changes zero rows. Always read changes().

SQL BROWSER RUNNER

Let the clerk decrement stock

Session 2 is clerk. UPDATE stock if role_grants allow update or update_stock.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

INSERT INTO session_staff VALUES (2);

UPDATE products
SET stock = stock - 1
WHERE sku = 'NB-1'
  AND EXISTS (
    SELECT 1
    FROM session_staff AS s
    JOIN staff AS st ON st.staff_id = s.staff_id
    JOIN role_grants AS g
      ON g.role = st.role
     AND g.object = 'products'
     AND g.action IN ('update', 'update_stock')
  );

SELECT changes() AS rows_changed;

SELECT sku, stock
FROM products
WHERE sku = 'NB-1';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is rows_changed, and what is stock for NB-1 after the update?

SQL BROWSER RUNNER

Stop the analyst from raising listed_cents

Session 3 is analyst. Same UPDATE pattern, action = update only.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

INSERT INTO session_staff VALUES (3);

UPDATE products
SET listed_cents = listed_cents + 50
WHERE sku = 'NB-1'
  AND EXISTS (
    SELECT 1
    FROM session_staff AS s
    JOIN staff AS st ON st.staff_id = s.staff_id
    JOIN role_grants AS g
      ON g.role = st.role
     AND g.object = 'products'
     AND g.action = 'update'
  );

SELECT changes() AS rows_changed;

SELECT sku, listed_cents
FROM products
WHERE sku = 'NB-1';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is rows_changed 0 and listed_cents still 1200, even though UPDATE did not error?

Filter rows to the current person

Ada must not download Grace's orders. That is row-level privilege. PostgreSQL RLS policies add USING (customer_id = current_setting('app.customer_id')::int) (or current_user mapping). Here we join session_customer. The second SELECT without the filter is the leak: two orders instead of Ada's one.

If the API looks up orders by id in the URL and skips this filter, changing /orders/2 becomes Grace's receipt. Privilege is not only GRANT. It is every WHERE that names “me.”

SQL BROWSER RUNNER

Ada's session versus an unfiltered order list

session_customer 10, then COUNT of all orders.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

INSERT INTO session_customer VALUES (10);

SELECT o.order_id, c.email, o.sku, o.cents
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN session_customer AS sc ON sc.customer_id = o.customer_id;

SELECT COUNT(*) AS leaked_if_unfiltered
FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many rows should Ada see, what is leaked_if_unfiltered, and which JOIN enforced her boundary?

Audit the key ring

You cannot protect what you cannot list. Join staff to role_grants. Count grants per role. On PostgreSQL: information_schema.role_table_grants or \\dp in psql. After someone leaves, REVOKE and confirm the audit query is empty for that login.

Default privileges on future tables matter. A new products_audit table should not be world-readable because nobody ran GRANT on it yet—or the opposite: it should not be forgotten and left to the public role. PostgreSQL ALTER DEFAULT PRIVILEGES is that policy.

SQL BROWSER RUNNER

List every staff grant and count by role

Join staff to role_grants, then GROUP BY role.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

SELECT st.email, st.role, g.object, g.action
FROM staff AS st
JOIN role_grants AS g ON g.role = st.role
ORDER BY st.role, g.object, g.action;

SELECT role, COUNT(*) AS grant_count
FROM role_grants
GROUP BY role
ORDER BY role;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many grant rows does analyst have compared with owner, and which object is the analyst limited to?

Owner is for restore, not for the website

Last lesson's DROP and rebuild are owner work. The public app connecting as owner can DELETE PEN-2 because a bug (or next lesson's injection) said so. This runner does that DELETE on purpose so you see the blast radius. Production: humans use owner (or a migrate role) from a bastion; shop_app is a service login with clerk-like grants.

SQL BROWSER RUNNER

See that owner can delete a product

Session 1, detect delete grant, DELETE PEN-2, list remaining SKUs.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

INSERT INTO session_staff VALUES (1);

SELECT st.role,
  MAX(CASE WHEN g.action = 'delete' THEN 1 ELSE 0 END) AS can_delete_products
FROM session_staff AS s
JOIN staff AS st ON st.staff_id = s.staff_id
LEFT JOIN role_grants AS g
  ON g.role = st.role
 AND g.object = 'products'
GROUP BY st.role;

-- The website must not connect as owner. A leaked owner session can:
DELETE FROM orders WHERE sku = 'PEN-2';
DELETE FROM products WHERE sku = 'PEN-2';

SELECT sku FROM products ORDER BY sku;
SELECT sku FROM orders ORDER BY sku;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is can_delete_products, which sku remains, and which login should the web app use instead?

Service accounts are roles that never sleep

A human owner logs in rarely. Metabase and the checkout API stay connected. Give them kind = 'service' and a non-owner role. The query “service AND owner” must be empty. Rotate those passwords (or IAM tokens) without sharing them in chat. One app, one login; do not reuse the analyst warehouse password on the website.

SQL BROWSER RUNNER

Inventory human versus service logins

connections table, then find service owners (should be none).

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

CREATE TABLE connections (
  login TEXT PRIMARY KEY,
  kind TEXT NOT NULL CHECK (kind IN ('human', 'service')),
  role TEXT NOT NULL
);

INSERT INTO connections VALUES
  ('owner@shop.test', 'human', 'owner'),
  ('shop_app', 'service', 'clerk'),
  ('metabase', 'service', 'analyst');

SELECT login, kind, role
FROM connections
ORDER BY kind, login;

SELECT login
FROM connections
WHERE kind = 'service' AND role = 'owner';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which logins are services, and how many rows should the service-owner query return?

PostgreSQL and MySQL, in one card

  • LOGIN vs NOLOGIN roles. A group role holds grants; personal and service roles inherit it.
  • GRANT / REVOKE on DATABASE, SCHEMA, TABLE, SEQUENCE, and column lists.
  • PUBLIC. Revoke sloppy default PUBLIC grants if your engine gave them.
  • Row-level security (PostgreSQL) for Ada/Grace filters the engine always applies.
  • SUPERUSER / rds_superuser. Not for apps. Not for analysts.

SQLite files are often “whoever can read the file owns the database.” Least privilege then lives in the OS user, the app process, and the session filters you just wrote. Do not copy a production SQLite file to a laptop and call it fine.

What you should be able to do

  • Define least privilege with a cashier-and-safe sentence.
  • Name a role as a grant bundle, not a person.
  • Hide columns with a view or column GRANT.
  • Treat changes() = 0 as a denied write in this catalog.
  • Filter rows to the session and keep owner off the website.

Independent lab: a reporter who cannot touch cost

Add a reporter (new staff row and grants, or reuse analyst). Connect as them. Read catalog_store. Attempt UPDATE products SET cost_cents = 1 with a grant-aware WHERE. Expect zero rows. Paste their grant list as the audit trail. If you widen CHECK to a fourth role, add role_grants before the session INSERT.

SQL BROWSER RUNNER

Connect a reporter and prove cost cannot move

Start as analyst. Add a reporter if you want a fourth role.

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'clerk', 'analyst'))
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  cost_cents INTEGER NOT NULL,
  listed_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL
);

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  cents INTEGER NOT NULL
);

CREATE TABLE session_staff (
  staff_id INTEGER NOT NULL REFERENCES staff(staff_id)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE role_grants (
  role TEXT NOT NULL,
  object TEXT NOT NULL,
  action TEXT NOT NULL,
  PRIMARY KEY (role, object, action)
);

INSERT INTO staff VALUES
  (1, 'owner@shop.test', 'owner'),
  (2, 'clerk@shop.test', 'clerk'),
  (3, 'analyst@shop.test', 'analyst');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 400, 1200, 12),
  ('PEN-2', 'Gel pen', 80, 400, 40);

INSERT INTO customers VALUES
  (10, 'ada@example.com'),
  (11, 'grace@example.com');

INSERT INTO orders VALUES
  (1, 10, 'NB-1', 1200),
  (2, 11, 'PEN-2', 400);

INSERT INTO role_grants VALUES
  ('owner', 'products', 'select'),
  ('owner', 'products', 'update'),
  ('owner', 'products', 'delete'),
  ('clerk', 'products', 'select'),
  ('clerk', 'products', 'update_stock'),
  ('analyst', 'catalog_store', 'select');

CREATE VIEW catalog_store AS
SELECT sku, name, listed_cents, stock
FROM products;

-- 1. INSERT a reporter staff row (pick a new staff_id) with role analyst
--    or extend CHECK and role_grants for a 'reporter' role.
-- 2. Set session_staff to that person.
-- 3. SELECT from catalog_store (should work).
-- 4. UPDATE products.cost_cents with a grant-aware WHERE (should change 0 rows).
-- 5. List role_grants for that role as your audit.

INSERT INTO session_staff VALUES (3);

SELECT sku, listed_cents
FROM catalog_store;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What did catalog_store return, what was changes() on cost, and which role_grants rows did you record?

Common mistakes to avoid

  • Putting the owner password in the web app's environment “just for now.”
  • Granting SELECT on the base table when a view would hide cost.
  • Checking role only in the UI, never in SQL or GRANT.
  • Treating a successful UPDATE with zero matching rows as a business success.
  • Forgetting the customer_id filter on “get order by id.”
  • Leaving PUBLIC or default grants unreviewed after a migration.
  • Copying the production SQLite file to shared Slack.

Lesson review

  • I can explain least privilege as the smallest key ring that still works.
  • I can treat a role as a reusable grant bundle.
  • I can hide sensitive columns with a view (or column GRANT on a server).
  • I can deny a write with a session check and read changes().
  • I can filter orders to the current customer.
  • I can keep owner/migrate logins off the public app and audit who still has them.
KNOWLEDGE CHECK

Check your privilege reasoning

Answer all ten questions, then reopen the runner whose session role or changes() = 0 still feels unclear.

01What does least privilege mean?
02Why does this SQLite runner model a session table instead of CREATE ROLE?
03What is a role, in production SQL?
04Why hide cost_cents behind a view for clerks?
05A clerk UPDATE ... WHERE role IN ('clerk','owner') reports changes() = 0. What happened?
06What is a service account?
07Row-level access (Ada sees only Ada's orders) is which idea?
08Why audit who has owner?
09GRANT SELECT ON ALL TABLES IN SCHEMA shop TO shop_readonly is closest to which catalog object?
10What should you do after adding a reporter role?
PREVIOUS LESSONBackups, restores, and migrations
NEXT LESSONPreventing SQL injection
ON THIS PAGELeast privilegeSessionsHidden columnsRoles and grantsRow filtersAuditOwner blast radiusService accountsIndependent labCommon mistakesKnowledge check
Course contents