A common question when setting up row-level security in Unity Catalog is what to do when the column that decides who can see a row is not on the table you want to protect. The value lives in a related table, one join away. This comes up often in insurance, where a claims table is keyed by policy, but which agent services which policy is held in a separate mapping table.
This post walks through that situation. It shows why the first idea does not work, the pattern that does, and a way to set it up so one small control table drives the same rule across many tables. Every table and column name here is a plain example you can rename to your own.
Key takeaways
- A row filter is a SQL function, so it can hold a subquery. That lets you scope a table by a value that lives in a related table.
- You cannot filter a table and also read that same table inside another table's filter. Unity Catalog stops it with an error.
- Keep the lookup in a small control table that is never filtered, and point every table's filter at it.
- The filter reads the control table under the owner's rights, so users need no access to it. Onboarding a user is a one row change.
What is in this post
The problem
Here is the model. There are three tables.
- The claims fact table,
claim_fact. One row per claim, keyed bypolicy_id. It does not carry an agent id. - The mapping table,
policy_agent_map. One row per policy an agent services, holdingpolicy_idandagent_id. - The directory,
agent_directory. Maps the signed-in user,user_email, to theiragent_id.
The goal is that an agent sees only the claims for the policies they service, and can also read the mapping table and see only their own policies. The value that decides all of this, the agent id, is two joins away from the claims table.
How row filters and ABAC work
A row filter in Unity Catalog is a SQL function bound to a column. It runs for every row and returns true or false. If it returns true, the row is shown. A simple filter on a table that already has the agent id looks like this.
CREATE OR REPLACE FUNCTION scope_by_agent(agent_id STRING) RETURN is_account_group_member('claims_supervisors') OR agent_id = current_user(); ALTER TABLE agent_book SET ROW FILTER scope_by_agent ON (agent_id);
Attribute-based access control, ABAC, does the same job through a governed tag on a column plus a policy. You tag the column once, write one policy, and it covers every table that carries that tag, including tables added later. That is the better fit when the same rule applies across many tables. The row filter idea below works the same way whether you bind it to one table by hand or apply it through a tag and policy.
Why filtering the mapping table does not work
The first idea most people reach for is to put a row filter on the mapping table, then have the claims filter read that mapping table to find the agent's policies. This does not work.
Unity Catalog does not allow a table that already has an active row filter or column mask to be read inside another table's filter. The query fails with the error UNSUPPORTED_NESTED_ROW_OR_COLUMN_ACCESS_POLICY. So the mapping table cannot be both filtered for users and used as the lookup for the claims filter simultaneously.
Here is the error in the workspace. The mapping table already has a row filter, and the claims table's filter tries to read it.
How to scope the fact table
A row filter is a SQL function, so it can hold a subquery. Bind the filter to the join key on the claims table, and resolve the agent inside the function by joining the mapping table to the directory on the current user. As long as the mapping table has no filter of its own, there is no nesting and it works for every agent.
-- The filter takes the claims table's join key, the policy id. CREATE OR REPLACE FUNCTION scope_claims_by_policy(policy_id STRING) RETURN is_account_group_member('claims_supervisors') OR policy_id IN ( SELECT m.policy_id FROM policy_agent_map m JOIN agent_directory d ON m.agent_id = d.agent_id WHERE d.user_email = current_user() ); ALTER TABLE claim_fact SET ROW FILTER scope_claims_by_policy ON (policy_id);
scope_by_policy on the policy_id column.
There is a useful detail in how this runs. Row filters run with the object owner's rights, apart from the identity checks current_user() and is_account_group_member(), which run as the querying user. So an agent needs no access to the mapping table or the directory. The function reads them under the owner's rights, and only the identity check runs as the agent. The agent needs access only to the claims table.
One control table for many tables
In a real estate of tables, the same question comes up again and again on different tables. Rather than scatter lookups across many functions, put the mappings in one place. Create a small control table that holds the user and every key you scope on, and never apply a filter to it. Then point the filter for each table at that one control table.
-- One control table. Keep it locked down and never filter it. CREATE TABLE agent_entitlements ( user_email STRING, agent_id STRING, policy_id STRING ); REVOKE ALL PRIVILEGES ON TABLE agent_entitlements FROM `account users`; -- Filter for any table keyed by policy id. CREATE OR REPLACE FUNCTION scope_by_policy(policy_id STRING) RETURN is_account_group_member('claims_supervisors') OR policy_id IN ( SELECT policy_id FROM agent_entitlements WHERE user_email = current_user() ); -- Filter for any table keyed by agent id, the mapping table included. CREATE OR REPLACE FUNCTION scope_by_agent(agent_id STRING) RETURN is_account_group_member('claims_supervisors') OR agent_id IN ( SELECT agent_id FROM agent_entitlements WHERE user_email = current_user() );
Because the control table is a separate object that is never filtered, you can now apply a filter to any table safely, including the mapping table. Tag each key column with a governed tag and create one policy per key type in Catalog Explorer, and the rule covers every table that carries that key. You can also bind a function to a single table by hand with SET ROW FILTER. Onboarding an agent or moving a book of policies becomes a change to rows in the control table, not a change to code.
When users also need to read the mapping table
If agents only ever read the claims table, you are done. If they also need to read the mapping table directly, there are two clean choices.
- A view. Leave the mapping table unfiltered, and create a view over it that filters to the current user. Give agents access to the view, not the base table. This is the smaller change.
- The control table. With the control table in place, apply a filter to the mapping table directly with the
scope_by_agentfunction above. The control table is separate and never filtered, so there is no nesting and no view to maintain.
-- The view option, if you would rather not filter the mapping table. CREATE OR REPLACE VIEW policy_agent_map_user AS SELECT * FROM policy_agent_map WHERE is_account_group_member('claims_supervisors') OR agent_id IN ( SELECT agent_id FROM agent_entitlements WHERE user_email = current_user() );
Taking the control table option, the mapping table then carries its own row filter, and it resolves through the same control table as the claims table.
scope_by_agent on the agent_id column, driven by the same control table.Checking it works
These results are from a live run. The claims table holds 40 claims across 12 policies and 3 agents. Signed in as an agent who services 3 policies, a plain query with no where clause returns only their rows.
The same agent reading the mapping table sees only their own three policies, not all twelve.
| Query, run with no where clause | All rows in the table | Seen as one agent |
|---|---|---|
| claims | 40 | 11 |
| distinct policies | 12 | 3 |
| mapping rows | 12 | 3 |
Test the all-access path too. A member of the claims_supervisors group, or a service account that runs the pipelines, gets every row. To check that path without waiting on group membership to take effect, add one row to the control table for your own user and rerun. The counts move at once. Remove the row and they return to the scoped numbers.
Things to keep in mind
- The tables a filter reads must not have their own active filter or mask. Keep the control table and the directory unfiltered.
- Keep the control table small so the join runs as a broadcast hash join. A few simple conditions read faster than many.
- Match the function parameter type to the key column type, and keep ANSI mode on, so a bad cast raises an error rather than returning null and letting rows through.
- A table can have one row filter in effect. A policy keyed on policy id and a policy keyed on agent id are separate and do not clash on the same table.
- Use the key that matches the table's grain. Use the policy id for a policy-level table, or a household or account key for those grains, and tag whichever key you join on.
The shape of this pattern is the same whenever the value that decides access is not on the table you want to protect. Put the mapping in a control table, keep it unfiltered, and read it from a row filter bound to the join key. It scopes one table or a whole estate with the same small set of pieces.
Further reading in the Databricks documentation: Filter sensitive table data using row filters and column masks, Attribute-based access control in Unity Catalog, and Create a dynamic view.
I would like to hear from others, too. Have you had cases where the value that decides access sits in an awkward place, a different table, a hierarchy, or several hops away, and how did you handle it in your own projects? Share what worked for you in the comments.
