Unity Catalog Column-Level Security: Row Filters and Column Masks in Practice

Shannon Lowder

Shannon Lowder

— 2 min read

Unity Catalog's permission model handles table-level and schema-level access cleanly. What it also gives you — that Hive metastore doesn't — is column-level security: the ability to restrict specific columns within a table, and row-level security through dynamic row filters. If you're dealing with PII, financial data, or any scenario where different users should see different subsets of the same table, these are the tools.

Column Masking

A column mask is a SQL function that Unity Catalog applies automatically when a user queries a masked column. Users who are exempt from the mask see the full value; everyone else sees the masked value. The function decides which is which.

-- Create a masking function
-- The function receives the column value and returns the masked version
CREATE FUNCTION prod_analytics.security.mask_email(email STRING)
RETURNS STRING
RETURN CASE
WHEN is_account_group_member('pii-access-group') THEN email
ELSE CONCAT(LEFT(email, 2), '***@***.***')
END;

-- Apply the mask to the column
ALTER TABLE prod_analytics.silver.customer_orders
ALTER COLUMN customer_email
SET MASK prod_analytics.security.mask_email;

-- Verify
-- User NOT in pii-access-group sees: sh***@***.***
-- User IN pii-access-group sees: [email protected]
SELECT customer_email FROM prod_analytics.silver.customer_orders LIMIT 5;

Row Filters

A row filter is a SQL function that Unity Catalog applies as an additional WHERE clause on every query. Users subject to the filter only see rows for which the function returns true.

-- Create a row filter function
-- Returns TRUE for rows the current user is allowed to see
CREATE FUNCTION prod_analytics.security.filter_by_region(region_code STRING)
RETURNS BOOLEAN
RETURN CASE
WHEN is_account_group_member('all-regions-access') THEN TRUE
ELSE region_code = (
SELECT user_region
FROM prod_analytics.security.user_region_map
WHERE username = current_user()
)
END;

-- Apply the row filter to the table
ALTER TABLE prod_analytics.gold.regional_sales
SET ROW FILTER prod_analytics.security.filter_by_region ON (region_code);

Multiple Masks on the Same Table

-- Different columns can have different masks
ALTER TABLE prod_analytics.silver.customer_orders
ALTER COLUMN customer_email
SET MASK prod_analytics.security.mask_email;

ALTER TABLE prod_analytics.silver.customer_orders
ALTER COLUMN customer_phone
SET MASK prod_analytics.security.mask_phone;

ALTER TABLE prod_analytics.silver.customer_orders
ALTER COLUMN customer_ssn
SET MASK prod_analytics.security.mask_ssn_full; -- Always fully masked except for compliance group

Testing Your Security Policies

Unity Catalog provides a way to test what a specific user would see without impersonating them:

-- Check what grants a user has
SHOW GRANTS TO `[email protected]`;

-- Audit column masks on a table
SELECT column_name, mask_name
FROM prod_analytics.information_schema.column_masks
WHERE table_name = 'customer_orders';

-- Audit row filters
SELECT filter_name, filter_definition
FROM prod_analytics.information_schema.table_constraints
WHERE table_name = 'regional_sales';

The Performance Consideration

Column masks and row filters are evaluated at query time by Unity Catalog. For simple masks (a CASE statement), the overhead is negligible. For row filters that involve a subquery to a lookup table (like the region map above), the subquery executes on every query against the filtered table. Cache that lookup table if it's queried frequently. As always, I'm here to help.

Read more