Row-level security

Before you start

• Level: 2 (intermediate).
• Time: about 2 hours.
• Builds on: Anti-joins, self-joins and UNION (EXISTS and NOT EXISTS), Joins without losing rows (fan-out from a duplicate key) and Relationships, role-playing dates and many-to-many (filters flowing through a model).
• You need: Python 3.9 or newer. Nothing to install. No Power BI or Tableau: the rule is rebuilt in sqlite3, so you can test it before any tool enforces it.

The problem

Tiffinbell, the fictional food delivery app from phase 00, is giving its city managers their own sales dashboard on Monday. Each manager must see only their own city's orders; Bilal, the head of operations, sees everything. The access list is a small table that HR and operations keep by hand: who sees which city. Bilal asks you to write the rule and, before launch, prove three things: nobody sees a city they should not, nobody sees an order twice, and no order is visible only to head office by accident.

In plain words

Think of a block of flats with one post room. Every resident has a key, and each key opens only that resident's letter box. The building manager has a master key. A visitor with no key opens nothing, and a resident who moved out hands their key back. The letters are all in one room; the keys decide who sees which.

• Row-level security (RLS) filters the rows of a table by who is looking. Everyone opens the same dashboard, and each person sees only their rows.
• A mapping table (also called a security or entitlement table) lists who may see what: here one row per (user, city). It is the list of keys.
• Dynamic RLS reads the mapping table at query time, using the viewer's login. Adding a manager means adding a row, not editing the dashboard.
• Deny by default means a person with no matching row sees nothing. The opposite, showing everything unless a rule says no, leaks the whole table to anyone the list forgot.
• A wildcard row, here city *, grants every city, including orders with no city yet. It is the master key.
• EXISTS (subquery) is true when the subquery returns at least one row. It checks for a key without multiplying anything.

What you will build from scratch

• visible_sales: the rows one login may see, with deny by default, a wildcard and inactive rows ignored.
• visible_revenue_by_city: the dashboard visual on top of the same rule.
• access_audit: what every person in the mapping table can see, for Bilal to sign off.
• uncovered_sales: the orders that no active city manager covers.

The data in data/ is synthetic. make_data.py builds it with a fixed random seed: sales.csv (476 orders in September and October 2026 across Mumbai, Nagpur, Nashik and Pune, 20 of them with no city yet) and user_access.csv (8 hand-typed rows). Emails use the reserved example.com domain.

The idea

The rule for one row of sales is: show it if there is at least one row in the mapping table where the email is the viewer's login, the row is active, and the city is the sale's city or *.

flowchart LR
    U["Viewer's login"] --> N["lower, trim"]
    N --> M{"Active mapping rows for this login?"}
    M -->|"none"| E["Sees nothing"]
    M -->|"city = '*'"| A["Sees every row, including no city"]
    M -->|"city = Mumbai, Nashik"| F["Sees rows where sales.city is one of them"]

In SQL that is a WHERE EXISTS:

SELECT ... FROM sales s
WHERE EXISTS (
  SELECT 1 FROM user_access a
  WHERE lower(trim(a.user_email)) = lower(trim(:user))
    AND a.active = 1
    AND (a.city = '*' OR a.city = s.city)
)

Four details carry the weight:

1. EXISTS, not JOIN. A JOIN to the mapping table returns one sales row per matching key. If a pair is typed twice, or a manager has both * and a city, each of their orders appears twice. EXISTS only asks whether a key exists.
2. Deny by default comes free. With no matching row, EXISTS is false for every sale, so the result is empty. Nothing has to be written for the unknown user, and nothing can be forgotten.
3. Clean both sides. A login arrives as esha@tiffinbell.example.com; the list has Esha@Tiffinbell.example.com with a space at each end. lower(trim(...)) on both sides makes them match. Treat the emails in an access list as case-insensitive; a plain = on text in SQLite is not.
4. NULL never matches. An order with no city has s.city NULL, and a.city = NULL is never true. So only the wildcard sees it, which is the safe default: unassigned orders go to head office until they are assigned.

The :user in the query is a named parameter: sqlite3 fills it in from con.execute(sql, {"user": login}), so a login can never change the SQL itself.

Power BI and Tableau both have built-in ways to enforce a rule like this; read the tool's own documentation for the details, and keep the audit in this lesson either way. One tool detail connects to lesson 08: Microsoft's USERELATIONSHIP page notes that the function cannot be used when row-level security is defined for the table in which the measure is included (USERELATIONSHIP function, Microsoft Learn, read 31 October 2026). Test a role-playing date measure as a secured user, not only as the model's author.

Walk through

Each query runs on the tables in data/. The block under each query is what sqlite3 returns. In the exercise, the login is the parameter :user; here it is written in the query so you can run each block as it is.

Step 1. Read the mapping table. The brackets show the spaces.

SELECT '[' || user_email || ']' AS user_email_as_typed, city, active
FROM user_access
ORDER BY lower(trim(user_email)), city

user_email_as_typed              city    active
[asha@tiffinbell.example.com]    Nagpur  0
[bilal@tiffinbell.example.com]   *       1
[dev@tiffinbell.example.com]     Pune    1
[ Esha@Tiffinbell.example.com ]  Pune    1
[farhan@tiffinbell.example.com]  Mumbai  1
[farhan@tiffinbell.example.com]  Nashik  1
[farhan@tiffinbell.example.com]  Nashik  1
[gauri@tiffinbell.example.com]   Mumbai  1

Bilal has the wildcard. Farhan covers Mumbai and Nashik, and his Nashik row was typed twice. Esha, an analyst for Pune, was typed with capitals and spaces. Asha managed Nagpur and has left.

Step 2. What Farhan sees.

SELECT COALESCE(s.city, 'Unassigned') AS city, COUNT(*) AS orders, ROUND(SUM(s.amount), 2) AS revenue
FROM sales s
WHERE EXISTS (
  SELECT 1 FROM user_access a
  WHERE lower(trim(a.user_email)) = lower(trim('farhan@tiffinbell.example.com'))
    AND a.active = 1
    AND (a.city = '*' OR a.city = s.city)
)
GROUP BY 1
ORDER BY 1

city    orders  revenue
Mumbai  153     77899.35
Nashik  94      47229.63

Two cities, each order once, even though Nashik is in the list twice.

Step 3. Deny by default. A login that is not in the list, Asha who has left, and Esha typed exactly as she logs in:

SELECT login,
  (SELECT COUNT(*) FROM sales s WHERE EXISTS (
     SELECT 1 FROM user_access a
     WHERE lower(trim(a.user_email)) = lower(trim(login))
       AND a.active = 1 AND (a.city = '*' OR a.city = s.city))) AS orders_visible
FROM (SELECT 'stranger@example.com' AS login
      UNION ALL SELECT 'asha@tiffinbell.example.com'
      UNION ALL SELECT 'esha@tiffinbell.example.com')

login                        orders_visible
stranger@example.com         0
asha@tiffinbell.example.com  0
esha@tiffinbell.example.com  137

The stranger and Asha see nothing, without a line of code written for them. Esha sees Pune's 137 orders because both sides were cleaned.

Step 4. The audit: one row per person, what they can see. This is what Bilal signs off before launch.

WITH people AS (SELECT DISTINCT lower(trim(user_email)) AS user_email FROM user_access)
SELECT p.user_email,
  COUNT(s.order_id) AS orders_visible,
  ROUND(COALESCE(SUM(s.amount), 0), 2) AS revenue_visible
FROM people p
LEFT JOIN sales s
  ON EXISTS (
    SELECT 1 FROM user_access a
    WHERE lower(trim(a.user_email)) = p.user_email
      AND a.active = 1
      AND (a.city = '*' OR a.city = s.city)
  )
GROUP BY p.user_email
ORDER BY p.user_email

user_email                     orders_visible  revenue_visible
asha@tiffinbell.example.com    0               0.0
bilal@tiffinbell.example.com   476             245078.38
dev@tiffinbell.example.com     137             74488.47
esha@tiffinbell.example.com    137             74488.47
farhan@tiffinbell.example.com  247             125128.98
gauri@tiffinbell.example.com   153             77899.35

Bilal sees all 476 orders, which is the whole table. Nobody else sees more than their cities. LEFT JOIN ... ON EXISTS keeps Asha with 0 instead of dropping her row, so the audit lists everyone in the mapping table.

Step 5. Coverage: which orders does no active city-level row cover?

SELECT COALESCE(s.city, 'Unassigned') AS city, COUNT(*) AS orders, ROUND(SUM(s.amount), 2) AS revenue
FROM sales s
WHERE NOT EXISTS (
  SELECT 1 FROM user_access a WHERE a.active = 1 AND a.city = s.city
)
GROUP BY 1
ORDER BY 1

city        orders  revenue
Nagpur      72      35796.86
Unassigned  20      9664.07

Nagpur's 72 orders have no manager since Asha left: only Bilal sees them. The 20 unassigned orders are expected to sit with head office. The Nagpur gap is the one to raise before Monday.

Common mistakes

1. Joining to the mapping table (the trap). A JOIN returns one row per matching key:

SELECT COUNT(*) AS rows_shown, COUNT(DISTINCT s.order_id) AS distinct_orders, ROUND(SUM(s.amount), 2) AS revenue
FROM sales s
JOIN user_access a ON a.city = s.city
WHERE lower(trim(a.user_email)) = 'farhan@tiffinbell.example.com' AND a.active = 1

rows_shown  distinct_orders  revenue
341         247              172358.61

Farhan's dashboard would show 172,358.61 instead of 125,128.98, because every Nashik order appears twice. Nobody notices until the city totals stop matching finance. Use EXISTS (or IN), which never multiplies rows.

2. Comparing emails exactly. a.user_email = 'esha@tiffinbell.example.com' finds no row for Esha, so she sees nothing and files a ticket. That one fails safe, but the same habit in an allow-all rule ("hide the rows of anyone in this exclusion list") fails open. Clean both sides.

3. Allow by default. A rule written as "if the user has a city, filter to it, otherwise show everything" shows all 476 orders to every stranger and to Asha after she left. Write the rule so that no row in the mapping means no rows in the result, and test it with a login that is not in the list.

4. Forgetting the inactive flag and the rows nobody owns. Ignoring active would leave Asha with Nagpur after she left. Checking only that each manager sees the right rows misses the opposite failure: Nagpur's 72 orders have no active manager at all. Run both the audit and the coverage check.

5. Testing only as yourself. The person who builds the dashboard usually has full access, so every visual looks right to them. Test as each role, with real logins, including one that should see nothing.

Your turn

Open exercise.sql and write visible_sales, visible_revenue_by_city, access_audit and uncovered_sales. The first two use the parameter :user.

python3 test_lesson.py

Run it from this folder. The test runs your per-user queries once for every email in the mapping table as typed, again in capitals, and for logins that must see nothing (a stranger, an empty string, NULL, and the text *). It compares each result with a plain Python version of the rule, and fails with a clear message if a login sees rows it should not, or sees an order twice. It runs on the committed data, on a second dataset from make_data.build() with another seed, on a hand-made table, and on empty tables.

Stretch: Tiffinbell adds regions, each a group of cities. Add a city_region table and let a mapping row grant a whole region. What changes in the EXISTS?

Check yourself

1. What is row-level security, and why use a mapping table instead of one role per city?
2. Why is EXISTS safer than a JOIN to the mapping table?
3. What should a user with no row in the mapping table see, and how do you test it?
4. How would you prove to an auditor that no order is visible to the wrong people?

What to tell the stakeholder

"The rule is ready for Monday: each manager sees only their cities, and a login that is not on the list sees nothing. The audit shows Farhan sees Mumbai and Nashik (247 orders), Gauri Mumbai, Dev and Esha Pune, and only you see all 476 orders. Two things to fix in the access list: Nagpur has had no active manager since Asha left, so its 72 orders (35,796.86) are visible only to you, and Farhan's Nashik row is typed twice, which is harmless with this rule but should be cleaned up."

In real job postings

In the Real Analyst Jobs data drop of 25 October 2026, Power BI was asked for in 350 of the 900 India analyst postings that list tools (38.9%), more than Tableau's 309 (data/job-market/2026-10). See the Power BI skills page.

Next

12 Tableau table calculations vs LOD