Home / Databricks on Azure

Databricks on AzureHow-toIntermediate

Unity Catalog row filters and column masks: restricting rows and hiding sensitive columns in Azure Databricks

Add row-level security and column masking to Unity Catalog tables with SQL UDFs and group membership, check which tables are protected, and avoid the common performance and compute traps.

Deepak Bhardwaj5 min read

Applies toAzure Databricks, Unity Catalog, Databricks SQL warehouses and supported runtimes (check current compute requirements)

What you can do

Unity Catalog lets you attach two kinds of SQL functions to a table:

  • A row filter: a function that returns TRUE for rows the current user may see.
  • A column mask: a function that returns either the real value or a masked one.

They are applied every time the table is queried, from any notebook, job, SQL warehouse or BI tool, so you don't need separate views for each audience.

Typical requirements:

Requirement Feature
EU staff see only EU rows Row filter on region
Only HR sees national ID numbers Column mask on national_id
Analysts see the last 4 digits of card numbers Column mask returning a partial value

Step 1: create a schema for security functions

Keep policy functions in one place, owned by a platform group:

CREATE SCHEMA IF NOT EXISTS governance.security;
ALTER SCHEMA governance.security OWNER TO `platform-admins`;

Step 2: row filter

CREATE OR REPLACE FUNCTION governance.security.region_filter(region STRING)
RETURN
  is_account_group_member('data-admins')
  OR (is_account_group_member('sales-eu') AND region = 'EU')
  OR (is_account_group_member('sales-us') AND region = 'US');

ALTER TABLE sales.core.orders
  SET ROW FILTER governance.security.region_filter ON (region);

The function's parameters map, in order, to the columns listed in ON (...).

A mapping table scales better than hard-coded groups when there are many regions:

-- governance.security.region_access(group_name STRING, region STRING)
CREATE OR REPLACE FUNCTION governance.security.region_filter_v2(region STRING)
RETURN
  is_account_group_member('data-admins')
  OR EXISTS (
    SELECT 1 FROM governance.security.region_access a
    WHERE a.region = region_filter_v2.region
      AND is_account_group_member(a.group_name)
  );

Step 3: column mask

CREATE OR REPLACE FUNCTION governance.security.mask_national_id(national_id STRING)
RETURN CASE
  WHEN is_account_group_member('hr') THEN national_id
  ELSE '***-**-****'
END;

ALTER TABLE hr.core.employees
  ALTER COLUMN national_id SET MASK governance.security.mask_national_id;

Partial mask:

CREATE OR REPLACE FUNCTION governance.security.mask_card(card STRING)
RETURN CASE
  WHEN is_account_group_member('payments-ops') THEN card
  ELSE concat('**** **** **** ', right(card, 4))
END;

The mask function must return the same data type as the column.

Tip

Use is_account_group_member() (account-level groups), not is_member() (workspace-local groups). Unity Catalog permissions are account-level, and workspace-local groups give inconsistent results across workspaces.

Step 4: grant and test

Users need EXECUTE on the functions only if they call them directly; querying the table is enough for the filter and mask to apply. Grant table access as usual:

GRANT SELECT ON TABLE sales.core.orders TO `sales-eu`;

Test as a member of each group (or ask someone who is), and compare counts:

SELECT region, count(*) FROM sales.core.orders GROUP BY region;
SELECT national_id FROM hr.core.employees LIMIT 5;

See what is protected

DESCRIBE TABLE EXTENDED sales.core.orders;   -- shows the row filter and masks

The information_schema in each catalog has views for row filters and column masks (row_filters, column_masks in recent releases; check your workspace):

SELECT * FROM sales.information_schema.row_filters;
SELECT * FROM hr.information_schema.column_masks;

Remove protection:

ALTER TABLE sales.core.orders DROP ROW FILTER;
ALTER TABLE hr.core.employees ALTER COLUMN national_id DROP MASK;

Caution

Dropping a filter or mask exposes the data immediately to everyone with SELECT. Treat it as a security change: approval, change record, and re-apply in the same deployment if it was temporary.

Things that catch people out

Problem Cause What to do
Query fails on older clusters Compute doesn't support filtered/masked tables Use a SQL warehouse or a supported runtime and access mode; check the current requirements in the Databricks docs
Queries became slow Filter function does a heavy lookup per row, or blocks predicate pushdown Keep functions simple; use a small mapping table; avoid Python UDFs in policies
Admin sees masked data Admins are not exempt; the function decides Add an admin group explicitly in the function
Some features refuse the table Certain operations (for example some sharing, cloning or streaming scenarios) have limits with filters and masks Check the limitations page for your runtime before rolling out
Different results in two workspaces is_member() used with workspace-local groups Switch to is_account_group_member()

Manage at scale

Applying functions table by table becomes hard to audit with hundreds of tables. Options:

  • Keep the ALTER TABLE ... SET ROW FILTER / SET MASK statements in a versioned SQL file deployed with your table DDL (Asset Bundles or a migration tool).
  • Use governed tags plus attribute-based policies where available in your workspace, so a tag like pii drives masking automatically. Check whether ABAC is enabled for your account before planning around it.

Prevent it next time

  • Decide policies per data classification, not per table, and write them once as functions.
  • Name functions clearly (mask_*, *_filter) and keep them in one governance schema.
  • Add tests to your pipeline that query protected tables as a low-privilege service principal and assert that rows and values are hidden.
  • Review group membership regularly; the policy is only as good as the groups it checks.

Related fixes