Row‑Level Filters & Column Masks in Unity Catalog

Mohammad Gufran Jahangir August 17, 2025 0

Table of Contents

What you’ll learn

What row filters and column masks are, and when to use them (vs. dynamic views)
Minimal, copy‑paste examples with account group checks
How to apply, test, modify, and remove filters/masks safely
Mapping‑table pattern for fine‑grained per‑user or per‑region access
Performance tips and common gotchas

Prereqs: Unity Catalog enabled; DBR 12.2 LTS+ for reads (see notes for dedicated compute), and a SQL warehouse or UC‑enabled cluster.

Concepts in 30 seconds

Row filter → a boolean policy (UDF) attached to a table that hides disallowed rows at query time.
Column mask → a policy (UDF) attached to a column that returns a redacted value per row based on who’s querying.
Both are policies stored in UC and enforced automatically—your table name and queries don’t change.

When to use vs. Dynamic Views

Use row filters/masks when you want the logic on the table itself (no new object name).
Use a dynamic view when you need a read‑only layer that combines multiple inputs or you want to publish a filtered alias.

Fast start: group‑based row filter (SQL)

Create a policy function (returns BOOLEAN). Example: admins see all; others only see US rows.

CREATE OR REPLACE FUNCTION governance.us_filter(region STRING)
RETURNS BOOLEAN
RETURN IF(is_account_group_member('admin'), true, region = 'US');

Apply it to a table (one row filter per table):

ALTER TABLE sales SET ROW FILTER governance.us_filter ON (region);
-- or at creation
-- CREATE TABLE sales(region STRING, id INT)
--   WITH ROW FILTER governance.us_filter ON (region);

Test as different users/groups (you’ll see a subset of rows without changing queries):

SELECT * FROM sales;

Remove / change the filter

ALTER TABLE sales DROP ROW FILTER;                -- remove
CREATE OR REPLACE FUNCTION governance.us_filter... -- update logic

Tip: Always drop the filter from the table before dropping the function to avoid an orphaned policy.

Fast start: column mask for PII (SQL)

Mask SSNs unless the user is in the HR group.

CREATE OR REPLACE FUNCTION governance.mask_ssn(ssn STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('HumanResourceDept')
            THEN ssn ELSE '***-**-****' END;

ALTER TABLE hr.curated.employees
  ALTER COLUMN ssn SET MASK governance.mask_ssn;  -- apply mask
-- To remove later: ALTER TABLE ... ALTER COLUMN ssn DROP MASK;

Mapping‑table pattern (fine‑grained access without many groups)

Use a small permission table to map who can see what (user or group → region/account). Reference it in the filter function.

-- 1) A mapping table of allowed regions per principal
CREATE TABLE governance.allowed_regions(
  principal STRING,  -- group or user name
  region    STRING
);
-- Example data
INSERT INTO governance.allowed_regions VALUES
  ('analyst_us','US'),
  ('analyst_eu','EU'),
  ('admin','*');

-- 2) Row‑filter function that consults the mapping
CREATE OR REPLACE FUNCTION governance.region_allowed(region STRING)
RETURNS BOOLEAN
RETURN (
  is_account_group_member('admin')
  OR EXISTS (
       SELECT 1 FROM governance.allowed_regions ar
       WHERE (is_account_group_member(ar.principal) OR current_user() = ar.principal)
         AND (ar.region = region OR ar.region = '*')
  )
);

-- 3) Apply to tables that carry a region column
ALTER TABLE finance.sales SET ROW FILTER governance.region_allowed ON (region);

Scale by editing the mapping rows—no code changes needed. For very large orgs, consider ABAC policies with governed tags as a more centralized approach.

Permissions you need

To assign a filter/mask: EXECUTE on the function, plus USE SCHEMA and USE CATALOG. To alter an existing table, you must be owner or have MANAGE on that table.
To read filtered/masked tables: use a SQL warehouse or a UC‑enabled cluster (see runtime notes below).

Performance tips

Keep policy functions simple and deterministic; avoid heavy subqueries.
Minimize function arguments; each extra column argument can limit optimization.
One table can have one row filter—don’t chain many conditions if you can avoid it.

Runtime & limitations (heads‑up)

Supported on DBR 12.2 LTS+ (reads). Dedicated access mode requires 15.4 LTS+ for reads; 16.3+ for writes (via supported patterns like MERGE).
Not supported on views; time travel; some deep/shallow clones; or certain complex MERGE cases.

Troubleshooting

“Function not found / can’t apply” → Check USE CATALOG/SCHEMA, spelling, and EXECUTE privilege on the function.
Table becomes inaccessible after dropping function → Re‑attach with ALTER TABLE <tbl> DROP ROW FILTER (or ... DROP MASK) to remove the orphaned reference.
No effect in notebooks → Confirm your compute meets the UC/runtime requirements and that you’re querying the same 3‑part‑name table that has the policy.

Handy cheatsheet

Add row filter: ALTER TABLE <tbl> SET ROW FILTER <fn> ON (<col_or_literal>[,...]);
Remove row filter: ALTER TABLE <tbl> DROP ROW FILTER;
Add column mask: ALTER TABLE <tbl> ALTER COLUMN <col> SET MASK <fn> [USING COLUMNS (...)];
Remove mask: ALTER TABLE <tbl> ALTER COLUMN <col> DROP MASK;
Group check: is_account_group_member('<group>')

Wrap‑up

Row filters and column masks let you lock sensitive data down at the table itself without changing query patterns. Start with a simple group‑based policy, move to a mapping‑table if you need per‑user rules, and consider ABAC for centralized, scalable governance across catalogs.

Tags: Databricks

Category:

Databricks