---
title: 'Row-Level Security'
source: 'https://academia.sh/en/courses/database-administration/row-level-security'
course: 'Relational Database Administration'
language: en
updated: '2026-08-23T07:00:40+00:00'
license: 'CC BY-SA 4.0'
---

# Row-Level Security

Showing a different row set on the same table depending on the account: the policy expression, the session variable, counting the rows a policy leaks, auditing the write side, and the policy's interaction with the access path.

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.

```bash
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
```

```text
┌─────────────────────────┬──────┐
│         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.

```bash
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
```

```text
┌──────┬──────────┬──────────────────┬────────┐
│ 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:

```bash
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
```

```text
┌───────────────┬──────┐
│    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:

```bash
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
```

```text
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.