Row Filters & Column Masks

Unity Catalog provides two fine-grained access controls that go beyond the table-level SELECT grant: row filters hide entire rows from a caller, and column masks redact or transform values in specific columns. Both are implemented as SQL UDFs attached to a table — the policy engine rewrites every SELECT against that table to apply the filter and mask before results are returned. They replace the older dynamic view pattern with a model that does not require everyone to read from a wrapper view.

1. Query Rewrite Flow

When a caller issues SELECT, Unity Catalog injects the filter and mask UDFs into the physical plan. The user sees no syntactic difference, but the rows and columns they see depend on their group membership.

┌──────────────────────────────────────────────────────────────────────────────────┐
│                                    USER QUERY                                    │
│              SELECT * FROM finance.tx.payments WHERE region = 'NA';              │
└──────────────────────────────────────────────────────────────────────────────────┘
                                         │
                                         ▼
┌──────────────────────────────────────────────────────────────────────────────────┐
│                           UNITY CATALOG POLICY ENGINE                            │
│    1. Resolve table   2. Look up ROW FILTER UDF   3. Look up COLUMN MASK UDFs    │
└──────────────────────────────────────────────────────────────────────────────────┘
                                         │  rewrite query
                                         ▼
┌──────────────────────────────────────────────────────────────────────────────────┐
│                             REWRITTEN PHYSICAL PLAN                              │
│             SELECT mask_ssn(ssn), mask_email(email), amount, region              │
│              FROM payments WHERE row_filter_region(region) = TRUE;               │
└──────────────────────────────────────────────────────────────────────────────────┘
                                         │
                                         ▼
┌──────────────────────────────────────────────────────────────────────────────────┐
│                     RESULT — Filtered Rows + Masked Columns                      │
└──────────────────────────────────────────────────────────────────────────────────┘

2. SQL UDF Prerequisites

• The table must live in a Unity-Catalog-managed catalog (no Hive metastore).
• The cluster must be on a Unity Catalog-enabled compute: a shared cluster on DBR 12.2 LTS+ or a SQL warehouse.
• The UDF must be a SQL UDF (not Python or Scala) and live in the same metastore.
• The UDF owner needs USE CATALOG / USE SCHEMA on its location; callers need EXECUTE on the UDF.

3. Row Filters

A row filter is a SQL UDF that returns BOOLEAN. It is attached to a table with ALTER TABLE ... SET ROW FILTER .... Rows where the UDF returns FALSE are silently dropped.

-- 1. Define the filter function
CREATE FUNCTION finance.security.region_filter(region STRING)
RETURN
  is_account_group_member('global-analysts')           -- global team sees all
  OR (is_account_group_member('na-analysts') AND region = 'NA')
  OR (is_account_group_member('eu-analysts') AND region = 'EU');

-- 2. Attach to the table
ALTER TABLE finance.tx.payments
SET ROW FILTER finance.security.region_filter ON (region);

-- 3. Verify
DESCRIBE EXTENDED finance.tx.payments;

To remove the filter:

ALTER TABLE finance.tx.payments DROP ROW FILTER;

4. Column Masks

A column mask is a SQL UDF that takes the column value (and optionally other columns from the same row) and returns a transformed value of the same type. Attach with ALTER TABLE ... ALTER COLUMN ... SET MASK ....

-- 1. SSN mask: only the privacy team sees raw values
CREATE FUNCTION finance.security.ssn_mask(ssn STRING)
RETURN CASE
  WHEN is_account_group_member('privacy-team') THEN ssn
  ELSE 'XXX-XX-' || RIGHT(ssn, 4)
END;

-- 2. Email mask: domain only
CREATE FUNCTION finance.security.email_mask(email STRING)
RETURN CASE
  WHEN is_account_group_member('privacy-team') THEN email
  ELSE '***@' || SPLIT(email, '@')[1]
END;

-- 3. Attach masks
ALTER TABLE finance.tx.payments
  ALTER COLUMN ssn   SET MASK finance.security.ssn_mask;

ALTER TABLE finance.tx.payments
  ALTER COLUMN email SET MASK finance.security.email_mask;

To drop a mask:

ALTER TABLE finance.tx.payments ALTER COLUMN ssn DROP MASK;

5. Dynamic Policies (current_user / group membership)

Two built-in functions drive almost every dynamic policy:

• current_user() — returns the caller's email/principal. Useful for "rows owned by the caller" patterns.
• is_account_group_member('group-name') — returns BOOLEAN based on SCIM group membership at the account level. Group changes flow through within minutes.

-- "Rows the caller created" pattern
CREATE FUNCTION finance.security.owner_filter(owner_email STRING)
RETURN owner_email = current_user()
  OR is_account_group_member('finance-managers');

6. Performance Considerations

• Predicate pushdown still works. The optimizer can push the filter UDF into the Parquet scan as long as the UDF references only the column it's keyed on.
• Avoid expensive UDFs. The mask runs once per row in the result set; a Python UDF here is a non-starter — keep it pure SQL.
• Group membership is cached. is_account_group_member() is evaluated once per query, not per row.
• Photon-compatible. SQL UDFs with simple expressions are inlined and run vectorized on Photon.
• Materialized views recompute under the policy of the materialization owner — be careful that masking does not leak into a downstream MV.

7. Comparison vs. Dynamic Views

For new pipelines on UC, prefer row filters and column masks. Reserve dynamic views for cases where you need to re-shape the schema (e.g. drop columns entirely, or add computed columns) for a specific audience.

↑ Back to Top