Where session 3 sits in the ladder
Session 1 wrote the access design and session 2 split the shared analyst role into five functional roles with column grants that match it. That split took leaks from 70 to 22 on the bench, and every one of the 22 that remain is a row problem or a pseudonym problem, not a column problem. Tonight takes the row problem: the same policy set, plus one row access policy keyed on region, over saltbox.analytics.customers.
★ What you walk out with tonight. A working row access policy on a real table, driven by a mapping table you can edit without touching the policy. The reason to write it with IS_ROLE_IN_SESSION rather than CURRENT_ROLE, demonstrated by breaking it on purpose. And the five documented limitations, so the first time you hear "the materialized view stopped refreshing" is in this room and not in an incident channel.
LiveWhy grants alone leak across regions3 min▶
A grant is a statement about an object. SELECT on a table means every row in that table, because a grant has no vocabulary for rows. So the moment two populations with different audiences share one table, which is every customers table that ever existed, a column grant is the wrong instrument for half the requirement.
The usual workarounds are a view per region, which multiplies every downstream object by the number of regions, or a filter the analyst is trusted to add, which is not a control. A row access policy attaches the filter to the table itself, so every query path, every tool and every forgotten WHERE clause gets the same answer.
Saltbox's APAC contractor was given SELECT on customers for a plan-mix count, scoped in the ticket to APAC only. The scoping lived in the ticket. The grant was on the table, so the contractor's first query returned EU and UK customers too. Nothing was exported, but the bench counts it as what it was: four leaks for one role, and nobody had made a mistake as far as the grants were concerned.
Self-studyThe vocabulary for tonight3 min▶
- Row access policy - a schema-level object holding a boolean expression. Attached to a table or view, it is evaluated per row at query time and only rows where it returns TRUE are returned.
- Mapping table - a small table the policy consults, typically role name against the value it may see. Editing the mapping changes behaviour without redeploying the policy.
- Primary and secondary roles - a session has one primary role and may activate secondary roles alongside it. Privileges aggregate across them, and a policy that only looks at one of them will disagree with the grants.
- CURRENT_ROLE() - returns the single active primary role of the session.
- IS_ROLE_IN_SESSION(role) - returns whether the named role is among the session's active primary or secondary roles, and therefore handles role hierarchies better.
- Evaluation order - where a table carries both a row access policy and masking policies, the row access policy is evaluated first. Session 4 builds on that.
A row access policy is a boolean the warehouse asks on your behalf
10 min liveStrip away the syntax and a row access policy is one question, asked once per row: given this row's value in one column, and given who is asking, should this row come back? The policy object holds the question. The mapping table holds the answer key. The role function decides what "who is asking" means, and that last part is where most policies quietly go wrong.
LiveThe policy object: schema-level, one argument, returns a boolean4 min▶
A row access policy is a schema-level object, created with CREATE ROW ACCESS POLICY. Its signature takes one column with its data type and returns a boolean, and the arrow introduces the condition: (column data_type) RETURNS BOOLEAN -> condition. When the policy is attached to a table, the argument is bound to a named column of that table, and the condition runs once per row with that row's value substituted in.
Two things follow from the shape. First, the policy is reusable: because it is a schema object rather than a clause inside a view, one policy can be attached to many tables that share the keying column, and dropping it from one table does not affect another. Second, the policy has no idea what a "region" is. It receives a value and returns a boolean. Every piece of business meaning lives in the condition, which is why the condition should be as small as possible and should push the meaning into a table you can read.
Keep the condition to one lookup. A policy body that encodes six regions and three exceptions as a nested CASE is a policy nobody will dare edit. A body that asks one mapping table one question is a policy whose behaviour you can review by reading the table.
LiveThe mapping table, and where it has to live3 min▶
The mapping table pattern is a two-column table of role name against permitted value, and a policy condition that asks whether a row exists for the current session's role and this row's value. It turns access changes into DML: onboarding a new region for the finance role is one INSERT, not a policy redeploy, and the history of who could see what becomes the history of a table you can time-travel.
| role_name | region |
|---|---|
| fr_analyst_eu | EU |
| fr_support_agent | EU, UK (two rows) |
| fr_data_scientist | EU, UK, APAC (three rows) |
| fr_finance | EU, UK, APAC (three rows) |
| fr_contractor_apac | APAC |
The documented placement rule: the mapping table should live in the same database as the protected table, and it is required to when the policy calls IS_DATABASE_ROLE_IN_SESSION. Putting it in a shared utilities database looks tidy and makes the policy depend on a cross-database reference that a database clone or a permissions change can break silently.
Two consequences that get missed. Whoever can INSERT into the mapping table can widen access to every table the policy protects, so the mapping table's own grants are part of the control, not housekeeping. And a mapping table with no row for a role means that role sees nothing, which is the right default and also the thing that will generate the first support ticket.
LiveCURRENT_ROLE() versus IS_ROLE_IN_SESSION()3 min▶
CURRENT_ROLE() returns the single active primary role of the session. IS_ROLE_IN_SESSION('fr_analyst_eu') returns whether that role is among the session's active primary or secondary roles, and therefore handles role hierarchies better. That one sentence is the difference between a policy that works for a user with exactly one role and a policy that works for the two-layer model built in session 2.
Consider a user whose primary role is a reporting access role and who has fr_analyst_eu active as a secondary role, or who reaches fr_analyst_eu through the hierarchy. The grants say this user may read the customers table, because privileges aggregate across active roles. A policy written with CURRENT_ROLE() looks up the primary role in the mapping table, finds no row, and returns FALSE for every row. The user gets an empty result and an error-free query, which is the worst kind of failure because nothing tells anybody it happened.
Saltbox's first region policy was written with CURRENT_ROLE(). It passed every test, because every test was run by the engineer who wrote it, with one role active. The first analyst to switch on secondary roles saw zero customers, assumed the table was empty, and filed a data quality ticket. Three days went into the pipeline before somebody asked which role the policy was checking.
Self-studyWhy the row policy is a different object from a grant, and why that matters3 min▶
- Grants are evaluated first. A role with no SELECT on the table never reaches the row policy. The policy narrows what a grant allowed; it cannot widen it, which is the correct dependency.
- The policy travels with the table. Every tool, every notebook and every downstream view built on the table inherits the filter, which is what a hand-written WHERE clause could never promise.
- Ownership of the policy is separate from ownership of the table. The team that owns the data does not have to be the team that owns the access rule, and for personal data it usually should not be.
- The policy has no memory. Every query re-evaluates it. Change the mapping table and the very next query gets the new answer, with no cache to invalidate and no session to restart.
The limitations, before you ship one
7 min liveRow access policies are the right tool for reads, and they are documented as exactly that. Five limitations follow, and each one has a team somewhere that discovered it in production. The sixth fact is not a limitation but an ordering rule, and it is the one that shapes everything in session 4.
LiveThe five, and what each one means on a Tuesday4 min▶
- It cannot prevent row insertion or deletion. The policy governs which rows a query returns. A role with INSERT or DELETE on the table can add or remove rows it would never be shown. Write control is a grants problem and stays one.
- It conflicts with materialized views. A materialized view precomputes rows for everybody, which is the opposite of a per-session filter. A table that feeds an MV and a table that carries a row access policy should not be the same table; pick which one you need before building either.
- External tables cannot serve as mapping tables. The role-to-region map has to be a real table, in the same database as the protected table. A mapping "table" that is really a file in object storage will not do.
- Subqueries in the policy body may error. The mapping-table lookup is the documented pattern. Anything more elaborate inside the condition, such as a second correlated subquery or a join to a third table, is where policies start failing in ways that read like syntax errors and are not.
- Aggregate performance can degrade. COUNT, SUM and their relatives used to skip straight to metadata or a pruned scan. With a row policy attached they have to evaluate the filter per row first. A dashboard that ran in a second can run in ten, and the only thing that changed is a policy nobody told the dashboard team about.
Announce the policy to the people who own the aggregates. The dashboard slowdown is the limitation most likely to be blamed on the warehouse rather than on the policy, and it is the one you can head off with a message the day you deploy.
LiveEvaluation order: the row policy runs first2 min▶
Where a table carries both a row access policy and masking policies, the row access policy is evaluated first. Rows the policy rejects never reach the masking stage; rows it keeps then have their sensitive columns transformed. One more rule rides along with it: a single column cannot carry both a row access policy and a masking policy at the same time.
The design consequence is the shape of session 4. Filter rows by region first, then mask what remains. The column the row policy keys on, region here, therefore stays in clear, and it has to: it is the column doing the filtering. That is not a leak, because region is non-sensitive by design and every job needs it in clear. It would be a problem if the row policy were keyed on something sensitive, which is one more argument for filtering on a coarse, non-sensitive attribute.
LiveWhat the row policy does to the bench: 22 leaks to 33 min▶
The policy bench in session 8 evaluates 150 access requests against the written design. With the two masking policies and the five-way role split in place, it reports 22 leaks. Switch on the row access policy on region and it reports 3, with over-blocks still at 0 and standing privileges still at 1.
| Policies on | Leaks | Over-blocks | Standing privileges |
|---|---|---|---|
| + split the shared role five ways | 22 | 0 | 1 |
| + row access policy on region | 3 | 0 | 1 |
The 19 leaks it removes are every cross-region read the grants allowed: the EU analyst's four clear columns in UK and APAC, the support agent's seven clear columns in APAC, and the APAC contractor's two clear columns in EU and UK. None of those is a column problem, which is why the role split in session 2 could not touch them. The 3 that remain are all one cell: the data scientist holding a real customer_id where the design asked for a pseudonym, and that is session 5's job.
A region policy on saltbox.analytics.customers
★ 20 min · everyone buildsBring the five functional roles from session 2: fr_analyst_eu, fr_support_agent, fr_data_scientist, fr_finance and fr_contractor_apac, each already holding SELECT on saltbox.analytics.customers. Run the steps in order, and do not skip step six, because the point of step six is to watch the wrong function fail quietly.
Create the mapping table, in the same database as the protected table. Two columns, ten rows, one row per role-and-region pair the design allows.
Role names are stored upper-case because that is how an unquoted identifier is stored, and a case mismatch here produces an empty result with no error. Then restrict who can write to this table: it is now part of the control.
Create the row access policy with IS_ROLE_IN_SESSION against the mapping table. One argument, one boolean, one lookup.
The mapping-table lookup is the documented pattern for the condition. The caution about subqueries applies to anything beyond it, so resist adding a second lookup or a join inside the body.
Attach it to the table, binding the argument to the region column.
Check the attach clause against the current reference for your platform version before running it on anything shared; the policy body shape above is the documented one, and the attach statement is the part that varies most between platforms.
Test as fr_analyst_eu and count rows by region. You should see one region.
One row back, EU. If you see three, the policy is not attached or the role has a path around it. If you see zero, check the case of the role name in the mapping table before anything else.
Test as fr_finance and confirm all three regions. Same query, different role.
Three rows: APAC, EU, UK. Nothing about the policy changed between step four and step five. Only the mapping table's answer did, which is the whole argument for the pattern.
Now break it on purpose: swap IS_ROLE_IN_SESSION for CURRENT_ROLE and test with a secondary role active.
With fr_finance as the primary role this still returns three regions, because the primary role has rows in the map. Now switch the primary role to one that is not in the mapping table and keep fr_finance active as a secondary role. The grants still allow the read, because privileges aggregate across active roles. The policy returns FALSE for every row, because it is asking about the primary role only. The result is zero rows and no error. Put IS_ROLE_IN_SESSION back before you leave.
Test with the roles people actually run with, not the role you wrote the policy under. A policy author almost always has one role active. The analysts who hit the policy have primary roles, secondary roles and hierarchy paths the author never activated. The step-six failure has no error message, so the only way to find it is to go looking with a session shaped like theirs.
Before session 4
30 min- Attach the region policy to one real table with a region or tenant column. Test it under at least two roles, one of them with a secondary role active.
- Find every materialized view built on that table, or that you were planning to build. Decide in writing which of the two you are keeping.
- Time one aggregate dashboard query before and after attaching the policy. Send the two numbers to whoever owns the dashboard, before they send them to you.
- List who can INSERT into your mapping table. Every one of them can widen access to the protected table, so the list should be as short as the list of people who can edit the policy.
- Search your existing policies for CURRENT_ROLE(). Each one is a policy that returns empty results to anybody running with a secondary role, and nobody has told you yet.
Three questions
A user runs with a reporting role as primary and fr_analyst_eu active as a secondary role. The grants allow the read. The row policy is written with CURRENT_ROLE(). What comes back?
CURRENT_ROLE() reads the single active primary role. IS_ROLE_IN_SESSION() checks primary and secondary roles and therefore handles hierarchies. The failure is silent, which is why it survives testing.
A table carries both a row access policy on region and a masking policy on email. In what order are they evaluated?
Row access policies are evaluated before masking policies. The masking stage only ever sees rows the row policy kept, and one column cannot carry both policy types at once.
Which of these does a row access policy NOT do?
The policy governs which rows a query returns. Write control remains a grants problem. The other two are exactly what makes the policy better than a WHERE clause somebody was trusted to add.