Unity Catalog Row Filters, Column Masks and ABAC Policies (Databricks Data Engineer Associate)

A row filter is a SQL user-defined function that returns BOOLEAN, bound to a Unity Catalog table so that every query on the table only returns the rows for which the function returns true. Rows where it returns false (or NULL) are left out at query time. The table keeps its name, nothing is copied, and every path that reads the table, whether a notebook, a dashboard or a view built on top of it, gets the filtered result.

You write the function once, then attach it with ALTER TABLE ... SET ROW FILTER, naming the table columns whose values are passed to the function's parameters in order. A new table can take the filter at creation with WITH ROW FILTER.

CREATE FUNCTION gov.rules.de_shipments(country STRING)
RETURN IF(is_account_group_member('logistics_leads'), true,
          country = 'DE');

ALTER TABLE ops.logistics.shipments
  SET ROW FILTER gov.rules.de_shipments ON (country);

-- remove it again
ALTER TABLE ops.logistics.shipments DROP ROW FILTER;

Read the function exactly as written to predict what a user sees. Here a member of logistics_leads gets true for every row and sees the whole table; anyone else gets true only where country = 'DE', so if the table holds 70 German and 30 French shipments they see the 70 German ones. Trace the two branches separately: who is exempt, and what the condition keeps for everyone else. A filter does not need a column at all: a function with zero parameters bound with ON () returns the same answer for every row, which shows a user either the whole table or nothing.

Filters act on reads. They never validate or reject incoming data the way a constraint does; they only decide which stored rows each reader gets back.

A column mask is a SQL user-defined function whose first parameter receives the masked column's value and which returns either that value or a replacement of the same type (or one castable to it). Every row stays visible; only the value in that column changes per reader. You attach it to one column with ALTER COLUMN ... SET MASK.

CREATE FUNCTION gov.rules.iban_mask(iban STRING)
RETURN CASE
  WHEN is_account_group_member('payments_ops') THEN iban
  ELSE concat('XXXX', right(iban, 4))
END;

ALTER TABLE fin.ap.suppliers
  ALTER COLUMN iban SET MASK gov.rules.iban_mask;

ALTER TABLE fin.ap.suppliers ALTER COLUMN iban DROP MASK;

The return type follows the column: a mask on a numeric column returns the number or a substitute such as NULL, never a boolean (deciding yes or no is a filter's job).

A mask can depend on more than the masked value. Extra arguments go in USING COLUMNS: other columns of the same row, or constant literals. The masked column is always the first argument, implicitly; you never list it again.

-- phone is revealed only to the team that owns the row's branch
ALTER TABLE ops.service.tickets
  ALTER COLUMN phone SET MASK gov.rules.mask_phone
  USING COLUMNS (branch);

-- one function, two columns, two levels passed as literals
ALTER TABLE legal.docs.contracts
  ALTER COLUMN clauses SET MASK gov.rules.by_tier
  USING COLUMNS ('tier1');
ALTER TABLE legal.docs.contracts
  ALTER COLUMN abstract SET MASK gov.rules.by_tier
  USING COLUMNS ('tier3');

Unity Catalog has no column-level SELECT grant, so "hide this one column from some readers" is a mask, not a narrower grant.

Inside filters, masks and dynamic views, use is_account_group_member('group') to test membership of an account-level group, which is where Unity Catalog groups live (including groups synced from your identity provider). is_member('group') checks workspace-local groups only, so a function that calls it with an account group's name returns false for everyone, and the filter or mask behaves as if no one were a member.

To test the individual reader, use session_user() (or current_user()), which returns the querying user's identity, such as an email. Comparing it to a hard-coded list is fragile; comparing it to a table of entitlements is the scalable pattern described below. Keep the two kinds of check apart: session_user() returns one user's identity, so compare it with user identities, while group membership is always tested with is_account_group_member(). Tag functions such as has_tag_value() describe data objects in ABAC policies and say nothing about the reader.

When access depends on hundreds or thousands of values (stores, cost centres, customers) and assignments change often, one group per value is unmanageable. Instead, keep an entitlement (mapping) table and have the filter look the reader up in it with session_user():

CREATE FUNCTION gov.rules.branch_filter(bid INT)
RETURN is_account_group_member('risk_office')
    OR EXISTS (SELECT 1 FROM gov.rules.branch_officers o
               WHERE o.branch_id = bid
                 AND o.officer = session_user());

ALTER TABLE bank.lending.loans
  SET ROW FILTER gov.rules.branch_filter ON (branch_id);

Updating the mapping table changes access immediately, with no function or binding change. If the rule is all-or-nothing per user (a list of approved readers who may see every row), the function needs no column: give it zero parameters, check session_user() against the list, and bind it with ON ().

Keep the mapping table itself free of filters and masks, because a filter function cannot read a table that has its own active filter or mask. Protect it with table grants. Limiting which rows a reader gets is always a filter's job; a mask on the key column would change its values but still return every row.

A table carries one row filter, and each column carries one mask. Rules do not stack: running a second SET ROW FILTER replaces the binding rather than adding a second filter. When a table needs several row rules, put them all in one function and combine them with AND/OR, for example the existing region condition AND NOT is_test, then attach that function.

How you change a rule depends on who else uses the function:

• Change it everywhere: run CREATE OR REPLACE FUNCTION under the same name. Every table bound to that function picks up the new logic, and the bindings stay exactly as they are. This is how you add a new exempt group to a filter shared by five tables.
• Change it for one table only: write a new function containing all the cases that table needs, and set it on that table (or column) in place of the shared one. Replacing the shared function would also change every other table that uses it.

Order matters when you retire a function: unbind first, drop second. If you DROP FUNCTION while a table still references it, the table is left inaccessible and every query fails, for admins too. The fix is to change the binding, with SET ROW FILTER (or SET MASK) using the replacement function, or DROP ROW FILTER / DROP MASK. The broken reference belongs to the table's security binding, not its data, so operations that roll back or recreate the data leave it in place; only changing or dropping the binding clears it.

Attaching a table-level filter or mask is a change to the table's security, so it takes control of the table plus the right to call the function:

Two consequences are worth remembering. MODIFY lets you change data, not security, so it does not let a non-owner attach a filter; MANAGE does. And when the function lives in another team's schema, EXECUTE alone is not enough: you also need USE SCHEMA on that schema (and USE CATALOG on its catalog) to reach it. Ownership of the function is not part of the requirement: EXECUTE on it is.

Readers need nothing on the function because filters and masks run with the definer's rights: the function body executes with its owner's privileges. The exception is identity functions such as session_user() and is_account_group_member(), which always evaluate the person actually running the query; that is what makes per-user rules work.

Filters and masks attach to tables, not to views. ALTER TABLE ... SET ROW FILTER on a view fails. Put the filter on the base table instead: any view, including a gold-layer view, that reads a filtered table sees the filtered result, so one binding covers every path.

Tables with table-level filters or masks also have operational limits:

• No clones: deep and shallow clones of a table with row-level security or column masks are not supported, even with every privilege on both catalogs. A protected copy for developers needs another route, such as a masked extract.
• No time travel on tables with table-level filters or masks.
• No nested protection: a filter or mask function cannot read a table that has its own active filter or mask. If a lookup (mapping) table gets a row filter, every query on the table whose filter reads it starts failing. Secure lookup tables with ordinary grants instead.
• No Delta Sharing of tables protected by table-level filters or masks (ABAC-protected tables can be shared under the conditions described below).

Row filters and column masks are enforced by Unity Catalog-aware compute, so the compute type and runtime decide whether a job can touch a protected table at all.

The general rule: on dedicated compute, whether a job can read or write a protected table depends on the runtime version, not on the job's privileges or on which DML statement it uses. When a dedicated-mode job fails right after a filter or mask is added, look at the runtime (or move the job to serverless) before touching grants. ABAC policies have their own, stricter compute requirement, covered below.

A dynamic view is an ordinary view whose SQL checks the reader, typically with is_account_group_member() in a CASE expression (to redact columns) or a WHERE clause (to drop rows). Users get SELECT on the view and nothing on the base tables. It predates row filters and masks and still has a job of its own.

-- revenue per client, contact email redacted for most readers
CREATE VIEW partner.views.client_revenue AS
SELECT c.client_id,
       CASE WHEN is_account_group_member('account_managers')
            THEN c.contact_email ELSE 'REDACTED' END AS contact_email,
       sum(i.amount) AS revenue
FROM billing.core.invoices i
JOIN billing.core.clients c USING (client_id)
GROUP BY c.client_id, c.contact_email;

Granting SELECT on the base tables alongside a mask would let a partner read the un-joined data, which a curated view avoids. Conversely, if many notebooks and dashboards already query the table directly, a view protects nothing until every one of them is rewritten, while a mask on the table protects them all at once.

Attribute-based access control (ABAC) in Unity Catalog lets a governance team define row filters and column masks once, as policies attached to a catalog, schema or table, that apply wherever matching governed tags appear. A policy still calls an ordinary SQL UDF for its logic; what changes is how the function is attached. Instead of binding it table by table, the policy finds the tables and columns by their tags, including tables created later, and table owners cannot override or remove it.

Governed tags are the attributes ABAC reads. They are defined at the account level with a controlled set of allowed values (for example sensitivity = public, internal, confidential, restricted), and who may apply them is controlled: assigning one needs the ASSIGN permission on the governed tag plus APPLY TAG on the object. Free-form tags, table properties and column comments are not policy attributes. Two inheritance rules decide what a policy matches:

• Tables and schemas inherit tags from their parents. A tag on a catalog flows to every schema and table in it, so tagging a catalog classification = confidential makes every table in it satisfy a WHEN has_tag_value('classification', 'confidential') condition. Tag high in the hierarchy only when every child should match.
• Columns do not inherit table tags. MATCH COLUMNS checks tags on the columns themselves, so a column mask policy matching pii = phone needs that tag on the phone column itself, not on its table.

A policy is created with CREATE POLICY. Each clause answers one question: where it lives, what it applies, to whom, and to which tables and columns.

CREATE POLICY redact_phone
ON CATALOG prod                        -- scope: CATALOG, SCHEMA or TABLE
COLUMN MASK gov.rules.phone_mask       -- or ROW FILTER fn
TO `account users`                     -- who gets masked or filtered
EXCEPT `support_managers`              -- who sees unmodified data
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'phone') AS ph
ON COLUMN ph;                          -- the column the mask replaces

CREATE POLICY confidential_rows
ON SCHEMA prod.finance
ROW FILTER gov.rules.country_ok
TO `account users` EXCEPT `auditors`
FOR TABLES
WHEN has_tag_value('classification', 'confidential') -- which tables
MATCH COLUMNS has_tag('country_code') AS cc  -- which columns to pass
USING COLUMNS (cc);                          -- arguments for the filter

• WHEN selects tables by their (possibly inherited) tags; omit it and every table in scope qualifies.
• MATCH COLUMNS condition AS alias selects columns by their own tags and names them. has_tag('key') matches any value of a tag; has_tag_value('key', 'value') matches one value.
• A column mask policy names the masked column with ON COLUMN alias (extra mask arguments can follow in USING COLUMNS). A row filter policy passes columns to its function with USING COLUMNS (alias).
• TO lists who is filtered or masked; EXCEPT lists who is exempt. "Everyone except one group" is expressed as TO `account users` EXCEPT `that_group`, inside the policy, not with a grant or revoke.

To create, edit or drop a policy on a catalog, schema or table you need MANAGE on that securable (or ownership) plus EXECUTE on the UDF it calls. MODIFY is not enough, and MANAGE on each table does not let you attach a policy to the schema. Policies at the metastore level need a metastore admin.

Because a table owner cannot override an ABAC policy, ALTER TABLE ... ALTER COLUMN ... DROP MASK removes only a table-level mask and leaves a policy's mask in place. To exempt someone, a principal with MANAGE on the policy's securable adds them to EXCEPT or edits the policy. A second mask on the same column, whatever its source, does not override the first; it is a conflict (below).

Exempt the identities that copy data. Principals in EXCEPT see unmodified data, which matters in two places:

• Pipelines. Materialized views and streaming tables are refreshed as the pipeline's owner or run-as identity, and policies on the source tables are evaluated for that identity. If it is masked, the masked values are what gets stored, and no reader of the materialized view can see real values afterwards, however exempt they are. Add the run-as identity to EXCEPT on the source tables' policies, then control the materialized view's own access with grants (or policies on it).
• Delta Sharing. A table with ABAC policies can be added to a share only when the share's owner is in the policies' EXCEPT clause; internal users stay masked.

Conflicts. For a given user, only one distinct row filter may resolve per table and one distinct mask per column. If two policies (say one on the catalog and one on a schema) apply different functions, or the same function with different arguments, the query fails with an error. There is no precedence: a narrower scope does not win, and masks never stack or cancel out. Policies that resolve to the same function with the same arguments do not conflict, and the value is masked once. To fix a real conflict, make only one policy match the user, for example by adding that group to the other policy's EXCEPT.

Older runtimes. Standard and dedicated compute on runtimes earlier than 16.4 cannot read ABAC-secured tables at all. Until a job can be upgraded, the documented workaround is to scope the policy to specific groups rather than all account users and put the older-runtime principal in EXCEPT, which only works if that principal may see the raw data. Where it may not, a table-level filter or mask (readable from dedicated 15.4 LTS+) is the alternative.