Row-Level Security

The previous lesson built privileges at the object level: an account could either read the loan table or it could not. The library’s real requirement is not met by this binary. The Shore branch clerk needs to read loan records, but only the rows of that branch. Seeing the Central branch’s rows is not a requirement — it is a leak.

This is a constraint that an object-level privilege cannot express. Rather than opening or closing access to the table wholesale, the solution attaches a policy to the table: the condition a row must satisfy to be visible. The engine adds this condition automatically to every query directed at that table. This is called row-level security.

Policy Expression and Session Variable

A policy is a predicate — a condition using the row’s columns and a piece of information belonging to the session. Session information is a value fixed when the connection opens and readable from within a query: which account connected, which branch that account belongs to. This is called a session variable.

The policy in the library example is this: branch_id must equal the session’s branch number. The engine adds this condition even if the query writes nothing into its own WHERE clause; if the query does write its own condition, the two apply together.

The example below models the policy through a view. Real row-level security attaches the policy to the base table and does not require a view; what is modeled here is how the policy expression narrows the row set, and that narrowing is the same either way.

rm -f library.db
sqlite3 -box -header library.db <<'SQL'
CREATE TABLE loan(
  id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
  branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT);
CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL);

INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES
 (11,101,1,'2024-03-01','2024-03-14'),(12,102,1,'2024-03-03',NULL),
 (13,103,1,'2024-03-05',NULL),(14,104,2,'2024-03-02','2024-03-16'),
 (15,105,2,'2024-03-06',NULL),(16,106,2,'2024-03-08',NULL),
 (17,107,2,'2024-03-09','2024-03-20'),(18,108,3,'2024-03-04','2024-03-18'),
 (19,109,3,'2024-03-11',NULL);
INSERT INTO session VALUES ('branch_id','2');

CREATE VIEW visible_loan AS
  SELECT * FROM loan
  WHERE branch_id = (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id');

SELECT 'base table' AS source, COUNT(*) AS rows FROM loan
UNION ALL
SELECT 'after policy (branch 2)', COUNT(*) FROM visible_loan;

UPDATE session SET value='1' WHERE key='branch_id';
SELECT 'after policy (branch 1)' AS source, COUNT(*) AS rows FROM visible_loan;
SQL
rm -f library.db

┌─────────────────────────┬──────┐
│         source          │ rows │
├─────────────────────────┼──────┤
│ base table              │ 9    │
│ after policy (branch 2) │ 4    │
└─────────────────────────┴──────┘
┌─────────────────────────┬──────┐
│         source          │ rows │
├─────────────────────────┼──────┤
│ after policy (branch 1) │ 3    │
└─────────────────────────┴──────┘

The query text did not change; only the session value changed, and the result set dropped from 4 rows to 3. This is what the policy means: the same query returns a different row set depending on who runs it. Forgetting to write WHERE branch_id = ? in application code no longer produces a leak, because the engine adds the condition, not the application.

Counting the Rows a Policy Leaks

A policy is written once and stands for years. A condition correct on the day it was written can broaden through a “convenience” added later. A common broadening is this: a decision is made that returned records should “no longer count as hidden,” and an OR is added to the policy.

The effect of a change like this is not predicted, it is counted. The difference between the intended row set and the set the policy in force actually returns is the rows the policy leaks.

rm -f policy.db
sqlite3 -box -header policy.db <<'SQL'
CREATE TABLE loan(
  id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
  branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT);
INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES
 (11,101,1,'2024-03-01','2024-03-14'),(12,102,1,'2024-03-03',NULL),
 (13,103,1,'2024-03-05',NULL),(14,104,2,'2024-03-02','2024-03-16'),
 (15,105,2,'2024-03-06',NULL),(16,106,2,'2024-03-08',NULL),
 (17,107,2,'2024-03-09','2024-03-20'),(18,108,3,'2024-03-04','2024-03-18'),
 (19,109,3,'2024-03-11',NULL);

WITH
 intended AS (SELECT id FROM loan WHERE branch_id = 2),
 broadened AS (SELECT id FROM loan WHERE branch_id = 2 OR returned IS NOT NULL)
SELECT
 (SELECT COUNT(*) FROM loan)      AS base,
 (SELECT COUNT(*) FROM intended)  AS intended,
 (SELECT COUNT(*) FROM broadened) AS broadened_policy,
 (SELECT COUNT(*) FROM broadened WHERE id NOT IN (SELECT id FROM intended)) AS leaked;

SELECT id, member_id, branch_id, returned FROM loan
WHERE (branch_id = 2 OR returned IS NOT NULL) AND branch_id <> 2 ORDER BY id;
SQL
rm -f policy.db

┌──────┬──────────┬──────────────────┬────────┐
│ base │ intended │ broadened_policy │ leaked │
├──────┼──────────┼──────────────────┼────────┤
│ 9    │ 4        │ 6                │ 2      │
└──────┴──────────┴──────────────────┴────────┘
┌────┬───────────┬───────────┬────────────┐
│ id │ member_id │ branch_id │  returned  │
├────┼───────────┼───────────┼────────────┤
│ 1  │ 101       │ 1         │ 2024-03-14 │
│ 8  │ 108       │ 3         │ 2024-03-18 │
└────┴───────────┴───────────┴────────────┘

The number of leaked rows is two, and which ones is listed: returned records from the Central and Hill branches, along with member numbers, were opened up to the Shore clerk. In a nine-row table this difference is visible to the eye; in a ten-million-row table it is visible only through this query.

This query is an audit tool, and it is run on every change to the policy. Its shape can be turned into a template: put the intended condition into one common table expression, the policy in force into another, and count the difference. Any result greater than zero requires looking at the text of the policy.

Auditing the Write Side

A policy narrows reading. It does not automatically narrow writing. This distinction is the most commonly skipped side of row-level security: the Shore clerk can write a loan record belonging to the Central branch, and then be unable to see the row afterward.

The example below produces this situation. An insert made through the view reaches the base table, but the inserted row is missing from the view because it falls outside the policy:

rm -f write.db
sqlite3 -box -header write.db <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
                   branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT);
CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL);
INSERT INTO session VALUES ('branch_id','2');
INSERT INTO loan(book_id,member_id,branch_id,pickup,returned) VALUES
 (15,105,2,'2024-03-06',NULL),(16,106,2,'2024-03-08',NULL);

CREATE VIEW visible_loan AS
  SELECT * FROM loan
  WHERE branch_id = (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id');

CREATE TRIGGER visible_loan_insert INSTEAD OF INSERT ON visible_loan
BEGIN
  INSERT INTO loan(book_id,member_id,branch_id,pickup,returned)
  VALUES (NEW.book_id, NEW.member_id, NEW.branch_id, NEW.pickup, NEW.returned);
END;

INSERT INTO visible_loan(book_id,member_id,branch_id,pickup,returned)
VALUES (20,110,3,'2024-03-12',NULL);

SELECT 'from the view' AS source, COUNT(*) AS rows FROM visible_loan
UNION ALL SELECT 'base table', COUNT(*) FROM loan;
SQL
rm -f write.db

┌───────────────┬──────┐
│    source     │ rows │
├───────────────┼──────┤
│ from the view │ 2    │
│ base table    │ 3    │
└───────────────┴──────┘

The base table has three rows, the view has two. The third row was written and disappeared. From a data integrity standpoint, this is worse than leaking through reads: records that exist but nobody can see accumulate, counts stop adding up, and the cause is hard to find.

The fix is to enforce the same condition on the write path as well. The trigger below rejects a row that does not conform to the policy before it is written:

rm -f write2.db
sqlite3 write2.db <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT NOT NULL, member_id INT NOT NULL,
                   branch_id INT NOT NULL, pickup TEXT NOT NULL, returned TEXT);
CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL);
INSERT INTO session VALUES ('branch_id','2');

CREATE VIEW visible_loan AS
  SELECT * FROM loan
  WHERE branch_id = (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id');

CREATE TRIGGER visible_loan_insert INSTEAD OF INSERT ON visible_loan
BEGIN
  SELECT CASE WHEN NEW.branch_id <>
      (SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id')
    THEN RAISE(ABORT, 'row outside the policy cannot be written') END;
  INSERT INTO loan(book_id,member_id,branch_id,pickup,returned)
  VALUES (NEW.book_id, NEW.member_id, NEW.branch_id, NEW.pickup, NEW.returned);
END;

INSERT INTO visible_loan(book_id,member_id,branch_id,pickup,returned)
VALUES (20,110,3,'2024-03-12',NULL);
SQL
echo "exit code: $?"
rm -f write2.db

Runtime error near line 19: row outside the policy cannot be written (19)
exit code: 1

Trigger syntax and the shape of the error message vary by engine; in engines that support row-level security natively, the same check is defined as a separate side of the policy. The rule that holds regardless is this: a policy’s read side and write side are written separately, and their agreement is tested separately as well.

Policy and Access Path

The policy predicate is a condition added to the query’s own condition; the planner sees both together. This has two consequences.

The first is favorable: if a suitable index exists on the policy column, the policy does not slow the query down — on the contrary, it can speed it up by increasing selectivity. The index and partitioning decisions made in the previous topic pay off here; a table partitioned by branch number aligns naturally with a branch policy.

The second requires attention: the evaluation order between the policy predicate and the query’s own predicate is not always defined. The existence of a row outside the policy can be sensed indirectly, through a function running on that row erroring out or slowing down. Engines that guard against this kind of inference guarantee that the policy predicate is evaluated first. The responsibility on the operations side is to not allow user-defined functions as predicates on tables that carry a policy.

Paths the Policy Does Not Cover

Row-level security checks only access that passes through the table it is applied to. The audit checklist includes the following questions.

The table owner and broadly privileged accounts. Engines generally exempt the table owner and the manager account from the policy. This is why the application account not being the table owner matters.

Copies without a policy. Copying the same data into a report table, a materialized view, or an export file leaves the policy behind. The copy itself needs a policy too; if it does not, the copy must be produced in a way that narrows it to what the policy covers.

Inference through aggregate functions. A policy hides rows; it may not hide counts. If there is a path where an account can take a sum or a count over rows it cannot see, information leaks even without row content. Verifying that aggregate queries run after the policy is part of the audit.

Backups. Every protection built in this lesson belongs to the running system. A backup file is a copy without a policy, and it is protected separately — that is the subject of the next lesson.

Summary

• Row-level security, through a policy predicate attached to a table, makes the same query return a different row set depending on who runs it.
• The policy uses a session variable; the engine adds the condition, not application code.
• The effect of a policy change is not predicted, it is counted: the difference between the intended set and the set in force gives the leaked rows.
• A read policy does not narrow writing; without a separate check on the write side, invisible rows accumulate.
• The policy predicate enters the query plan; an index and partitioning choice suited to the policy column preserves performance.
• The table owner, copies without a policy, inference through aggregates, and backup files are paths the policy does not cover.

Next Step

Access control keeps data from reaching the wrong person; it does not keep data from ceasing to exist. A disk failure, a mistakenly written DELETE, or a faulty schema change can render even the most carefully built privilege chart’s database unusable. The next lesson moves to backups: what logical, physical, and incremental backups are, what they cost to take, and how to prove that a restored backup is equal to the source.