Row-level security when the filter column is not on your table

[Ashwin_DSA]

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

1. The problem
2. How row filters and ABAC work
3. Why filtering the mapping table does not work
4. How to scope the fact table
5. One control table for many tables
6. When users also need to read the mapping table
7. Checking it works
8. Things to keep in mind

The problem

Here is the model. There are three tables.

• The claims fact table, claim_fact. One row per claim, keyed by policy_id. It does not carry an agent id.
• The mapping table, policy_agent_map. One row per policy an agent services, holding policy_id and agent_id.
• The directory, agent_directory. Maps the signed-in user, user_email, to their agent_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.

The claims table reaches its entitlement two joins away, through the mapping table to the directory, based on the signed-in user.

The objects in the schema. The claims fact and the mapping table, the directory and the control table, and the two filter functions.

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.

A filtered mapping table cannot be read inside the claims table's filter. The two protections cannot stack this way.

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.

The bind fails because the filter reads a table that already has its own filter. The call sequence in the error names both tables.

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);

The claims table in Catalog Explorer. The row filter is bound to scope_by_policy on the policy_id column.

The filter function as it sits in the catalog. Note that the security type is DEFINER, which is why the agent needs no access to the lookup.

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_agent function 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.

The mapping table with its own row filter, 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.

Claims grouped by policy for the signed-in agent. Three policies returned, 11 of the 40 claims in the table.

The same agent reading the mapping table sees only their own three policies, not all twelve.

The mapping table read by the same agent. Three of its twelve rows.

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.

[ivanvyd]

Thanks for sharing this @Ashwin_DSA . I'd be interested in your thoughts on scaling this pattern when the entitlement table has millions of mappings, but only a few per user. Could filtering by the current user keep the lookup small enough to broadcast, or would you suggest a different table layout in that case?

[Ashwin_DSA]

Hi @ivanvyd,

Thanks for the question. The short version is that filtering by current_user() usually keeps the broadcast small, but the thing to factor in the design is the lookup in the entitlement table, not the broadcast itself. current_user() resolves to a single constant for the query, so the subquery runs once, not per row. It returns only that user's handful of keys, and that small result is broadcast to filter the fact table. So even with millions of mappings, the broadcast side stays tiny as long as each user maps to only a few keys.

The expensive part is reading the millions-row entitlement table to find those few rows on every query. That is where I think the layout matters. Cluster the entitlement table on user_email, liquid clustering is the easy choice, so Delta can skip to the user's files instead of scanning the whole table. Keep it compacted with OPTIMIZE, avoid a pile of small files, and make sure you are not wrapping user_email in a function or cast that defeats data skipping. With that in place, the per-query lookup prunes to a few files, and the pattern should hold at scale.

If you hit a case where per-user sets are legitimately large, that is the point to rethink the layout, for example, scoping by a group or an attribute rather than an explicit per-row mapping. But for a few mappings per user, a well-clustered control table is the right call and should scale fine.

[data_pulse]

@Ashwin_DSA 

Good to see this post here as we landed on the same pattern a few months back after testing a few approaches and looping in the Databricks team to sense-check whether we were missing anything.

Here's what we observed:

• Started with a direct LEFT SEMI JOIN on the control table, got SortMergeJoin with two full shuffle exchanges across 200 partitions on both sides. Contributing factor: missing table statistics, the optimizer had no idea the per-user result was small. 

FROM wfm_site s
LEFT SEMI JOIN wfm_user_site_mapping m
    ON  s.site_id    = m.site_id
    AND m.user_email = lower(current_user())
-- Physical plan: SortMergeJoin
-- Exchange hashpartitioning(site_id, 200)  ← shuffle
​

• Switched to a SQL UDF with EXISTS: the plan moved to BroadcastHashJoin LeftSemi. Since SQL UDFs are inlined at planning time, Photon treats it as a native join with no ColumnarToRow transition, unlike a Python UDF which would have shown BatchEvalPython and fallen back on every row.

CREATE OR REPLACE FUNCTION fx_row_filter_wfm_site(site_id LONG)
RETURNS BOOLEAN
RETURN EXISTS (
    SELECT /*+ BROADCAST(m) */ 1
    FROM   wfm_user_site_mapping m
    WHERE  m.user_email = lower(current_user())
      AND  m.site_id    = fx_row_filter_wfm_site.site_id
)

• The broadcast is EXECUTOR_BROADCAST driven by AQE, not hint-driven. The user_email = current_user() predicate pushes all the way to the parquet scan, so the per-user rows are already small before the exchange happens.  

Confirmed in physical plan: 
Scan parquet wfm_user_site_mapping 
PushedFilters: [EqualTo(user_email, <current_user>), IsNotNull(site_id)]

• Ran ANALYZE TABLE .. COMPUTE STATISTICS FOR ALL COLUMNS after each mapping table rebuild, this made the broadcast strategy consistent across both the UDF and direct JOIN approaches. 

ANALYZE TABLE wfm_user_site_mapping
COMPUTE STATISTICS FOR ALL COLUMNS

@ivanvyd 

On the scaling front:

The per-user predicate pushdown is what keeps the broadcast small regardless of overall table size. We confirmed it in the physical plan, PushedFilters: [EqualTo(user_email, <current_user>)] appears at the scan node, not after. We also split into separate lean tables per dimension (one for site, one for contract) to avoid cross-join bloat from contract-to-site relationships inflating the broadcast payload.

One thing worth flagging: a unified two-param nullable function (site_id IS NULL OR ..) was noticeably slower than a single-param function per dimension. The IS NULL OR pattern blocks predicate pushdown, Spark could't push a partial OR condition as a simple equality filter to the parquet scan, so it falls back to filtering in memory after the scan. Plain equality predicates pushed cleanly.

-- IS NULL OR blocks predicate pushdown
CREATE FUNCTION fx_row_filter_wfm(site_id LONG, contract_id LONG)
RETURNS BOOLEAN
RETURN EXISTS (
    SELECT 1 FROM wfm_user_permission_mapping m
    WHERE  m.user_email = lower(current_user())
      AND (site_id     IS NULL OR m.site_id     = fx_row_filter_wfm.site_id)
      AND (contract_id IS NULL OR m.contract_id = fx_row_filter_wfm.contract_id)
    -- Spark couldn't push a partial OR condition to parquet scan
);

-- Plain equality → full predicate pushdown to scan
CREATE FUNCTION fx_row_filter_wfm_site(site_id LONG)
RETURNS BOOLEAN
RETURN EXISTS (
    SELECT 1 FROM wfm_user_site_mapping m
    WHERE  m.user_email = lower(current_user())
      AND  m.site_id    = fx_row_filter_wfm_site.site_id
    -- PushedFilters: [EqualTo(user_email,...), IsNotNull(site_id)]
);

On scale: we also tested this on much larger tables (~2B and ~18B rows) with the same row filter enforced. The overhead scales with how much data the user is permitted to see, not the total table size. Users with narrow access actually outperformed broader-access users on larger tables, fewer permitted keys means fewer matching files, so file pruning kicks in more aggressively at the scan.  A narrow-access restricted user on an 18B row table came in faster than a broad-access user on a 2B row table across repeated runs.