Row filters & column masks
Databricks · Unity Catalog & Governance
Theory
Row filters and column masks give fine-grained access inside one table. Both are SQL user-defined functions stored in Unity Catalog. A row filter function returns TRUE or FALSE for each row and is attached with ALTER TABLE t SET ROW FILTER fn ON (column); rows where it returns FALSE are hidden from the user. A column mask function receives a column value and returns either the real value or a masked one, attached with ALTER TABLE t ALTER COLUMN c SET MASK fn.
Inside the function you usually call is_account_group_member('group') or current_user() to decide per user. The rules apply everywhere the table is queried, including SQL warehouses, notebooks and BI tools. For many tables at once, Databricks recommends attribute-based access control (ABAC) policies driven by governed tags. For example, members of the jaipur_sales group see only Jaipur customers, and non-admins see names masked as A***.
Why we use it
Without row and column security you would create one view per audience and keep them all in sync. Filters and masks keep one table while each user sees only the rows and values they are allowed to see.
When to use it
Use row filters for regional or tenant data and column masks for PII such as names, phone numbers or PAN. Use ABAC policies for consistent rules across many tables, and dynamic views when you need logic that functions cannot express.
How it works
- User queries main.sales.customers
- Unity Catalog applies the row filter to each row
- Does the filter return TRUE for this row? Yes: Keep the row. No: Hide the row.
- Apply column masks to the kept rows
- User sees only allowed rows and masked values
Syntax
CREATE FUNCTION main.sales.fn(col TYPE) RETURNS BOOLEAN RETURN <condition>
ALTER TABLE t SET ROW FILTER main.sales.fn ON (col)
ALTER TABLE t DROP ROW FILTER
CREATE FUNCTION main.sales.mask(col STRING) RETURNS STRING RETURN CASE ... END
ALTER TABLE t ALTER COLUMN c SET MASK main.sales.mask
is_account_group_member('group') | current_user()Example
-- Row filter: admins see all rows, Jaipur team sees only Jaipur
CREATE OR REPLACE FUNCTION main.sales.city_filter(city STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('admins')
OR (is_account_group_member('jaipur_sales') AND city = 'Jaipur');
ALTER TABLE main.sales.customers
SET ROW FILTER main.sales.city_filter ON (city);
-- Column mask: only admins see full names
CREATE OR REPLACE FUNCTION main.sales.name_mask(name STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('admins') THEN name
ELSE concat(substr(name, 1, 1), '***') END;
ALTER TABLE main.sales.customers
ALTER COLUMN name SET MASK main.sales.name_mask;Key points
- Row filter function returns BOOLEAN; FALSE rows are hidden
- Column mask function returns the same type as the column
- is_account_group_member() makes rules depend on the querying user
- Rules are enforced on every query path: SQL warehouses, notebooks, BI tools
- ABAC policies with governed tags are recommended for rules across many tables