Row filters, column masks and ABAC

When schema-level access is too coarse

The reader group sees the whole table. Often that is wrong. A regional manager should see their region's rows. A support analyst should see the customer, but only the last four digits of the phone number. Copying the table per audience solves it badly: more tables, more drift, more places for personal data to leak.

Unity Catalog controls this inside the table, at query time. In this lesson we cover row filters and column masks, when to choose the newer attribute-based policies, and what limits to check before you commit. We do not cover dynamic views in depth.

Row filters

The documentation describes a row filter as a SQL user-defined function bound to a table. The function takes values of one or more columns and returns a Boolean. Rows for which it returns true are visible to the querying user. You bind the function to the table with ALTER TABLE ... SET ROW FILTER ... ON (...).

The documentation's own pattern looks like this:

CREATE FUNCTION salary_filter(dept STRING) RETURNS BOOLEAN
RETURN dept IS NULL;
 
ALTER TABLE employees SET ROW FILTER salary_filter ON (department);

That filter is a bare illustration of the mechanism. In practice the function checks group membership. The next listing shows the intent in our naming convention: members of the finance group see every row, and everyone else sees only the rows of the region their group maps to.

-- Schematic row filter. Check function names and parameter rules in the current reference.
CREATE FUNCTION prd_finance.security.region_filter(region STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('grp-uc-prd-finance-all-regions')
    OR (region = 'NL' AND is_account_group_member('grp-uc-prd-finance-region-nl'))
    OR (region = 'BE' AND is_account_group_member('grp-uc-prd-finance-region-be'));
 
ALTER TABLE prd_finance.gold.invoices
  SET ROW FILTER prd_finance.security.region_filter ON (region);

The documentation mentions is_account_group_member() as an example of a function for gating dynamic views. Because rights follow Entra ID groups, a person who joins the region group sees the rows without anyone touching the table.

Column masks

A column mask is a SQL function bound to a single column. It receives the column value and returns the value the user may see. The documentation states that the return type must match the column type or be castable to it. You bind it with ALTER TABLE ... ALTER COLUMN ... SET MASK.

-- Schematic column mask: show the last four characters to everyone except the privileged group.
CREATE FUNCTION prd_finance.security.mask_phone(phone STRING)
RETURNS STRING
RETURN CASE
  WHEN is_account_group_member('grp-uc-prd-finance-pii-reader') THEN phone
  ELSE CONCAT('******', RIGHT(phone, 4))
END;
 
ALTER TABLE prd_finance.gold.customers
  ALTER COLUMN phone SET MASK prd_finance.security.mask_phone;

Keep mask and filter functions in a dedicated security schema with tight grants, since whoever can change the function changes the policy. Put the function definitions in the repository and deploy them through the pipeline.

Type safety matters

The documentation warns that parameter types should match column types. With ANSI mode disabled, values that cannot be cast silently become NULL, which can give wrong results in a policy. It recommends enabling ANSI mode:

SET spark.sql.ansi.enabled = true;

A policy that silently returns NULL for a bad cast may hide rows or leak a value, depending on how you wrote it. Test policies with edge values: nulls, empty strings and odd formats.

Attribute-based access control

For many tables, binding a function per table does not scale. The documentation says Databricks recommends attribute-based access control (ABAC) policies for consistent rules across many tables, and table-level controls for table-specific logic.

ABAC grants or restricts access by evaluating attributes on securable objects. The attributes are governed tags. A policy uses tag conditions to identify the data it protects, so one policy can cover every column tagged as personal data across a catalog or schema. Evaluation is dynamic: a new table with the right tag is protected the moment it exists.

The documentation lists these policy types: row filter and column mask policies, GRANT policies that dynamically grant privileges, and DENY policies. Policies attach at catalog, schema or table level, and at metastore level in Beta. The documentation labels specific features as Beta: metastore-level policies, view support, DENY policies, and time travel with ABAC. It states no overall status for the feature, so check the release notes for your region.

This is where lesson 1 pays off. If every column that holds personal data carries a tag such as pii, one policy masks all of them. Our post on ABAC on views and time travel argues that policy tests should be a release gate, and it applies as soon as you rely on policies for privacy.

Choosing between the options

A filter hides rows from a table people can reach. If an audience should never touch the table, do not rely on a filter, use access control and a separate schema.

Limits to check before you commit

The documentation lists limits for table-level filters and masks. The ones that decide designs:

• They are not supported on runtimes below 12.2 LTS, where queries return no data.
• You cannot apply them to views.
• Delta Lake APIs are not supported.
• Tables with table-level filters or masks cannot be shared through OpenSharing (lesson 6).
• Time travel is not supported with table-level controls. ABAC time travel is in Beta.
• Deep and shallow clones are not supported.
• Dedicated access mode has runtime rules: not supported on 15.3 or below, read-only from 15.4 through 16.2, writes from 16.3.
• Column masks cannot apply to columns referenced by generated columns.

Check the current page before you design, since limits change. If you plan to share a table, the sharing limit alone may make a physical masked copy in a gold schema the better design.

Test the policy

A policy is code, so test it like code. For each policy, write test cases for a member of each group and for a non-member, and check row counts and masked values. Run the tests in the pipeline with test identities in test and acc. Never test with production personal data in dev or test. Use synthetic or masked data.

Common mistakes

• A filter where a schema boundary was needed.
• One mask function per table, copied. Use ABAC and tags.
• No tests. A policy that returns everything looks fine until an audit.
• Ignoring the sharing and time-travel limits.
• A function anyone can edit. Lock down the security schema.

Summary

Use row filters and column masks for access inside a table, and ABAC policies on governed tags when the same rule applies to many tables. Keep functions in a locked schema, enable ANSI mode, test with real identities and check the documented limits first.

Next lesson: lineage, audit logs and system tables, the evidence that governance works.