Row filters and column masks in Unity Catalog

Table-level row filters and column masks attach security logic directly to a table, so every query on that table is filtered without creating a new object. They are managed per table with ALTER TABLE.

A row filter is a SQL user-defined function (UDF) that returns a boolean; rows for which it returns FALSE are excluded. A column mask is a SQL UDF that receives the column value and returns either it or a masked version; the return type must match or be castable to the column type. A table has at most one row filter, and each column has at most one mask.

CREATE FUNCTION main.sec.us_filter(region STRING)
RETURN IF(is_account_group_member('admin'), true, region = 'US');
ALTER TABLE main.sales.orders SET ROW FILTER main.sec.us_filter ON (region);

CREATE FUNCTION main.sec.ssn_mask(ssn STRING)
RETURN CASE WHEN is_account_group_member('hr') THEN ssn
            ELSE '***-**-****' END;
ALTER TABLE main.hr.people ALTER COLUMN ssn SET MASK main.sec.ssn_mask;

Syntax details:

• Apply at creation with CREATE TABLE ... WITH ROW FILTER f ON (col) or a column definition such as ssn STRING MASK f.
• A filter that uses no columns is applied with ON ().
• USING COLUMNS (other_col, 'literal') passes extra arguments to a mask; the first function parameter is always the masked column.
• Remove with ALTER TABLE t DROP ROW FILTER and ALTER TABLE t ALTER COLUMN c DROP MASK. Drop the filter or mask before dropping the function, or the table becomes inaccessible until you drop the orphaned reference.
• Python or Scala logic must be wrapped in a SQL UDF; applying a Python UDF directly fails with [ROUTINE_NOT_FOUND].
• Functions run with definer’s rights, except identity checks such as session_user() and is_account_group_member(), which run as the invoker. A mapping table queried inside the function (an access-control list) is a common pattern.

Permissions: to assign a function you need EXECUTE on it plus USE SCHEMA and USE CATALOG; on an existing table you must be the owner or hold both MANAGE and SELECT; adding a masked column in the same statement also needs MODIFY.

Compute: a SQL warehouse, standard access mode on Runtime 12.2 LTS or above, or dedicated access mode on Runtime 15.4 LTS or above (with serverless enabled; writes on dedicated compute need 16.3 or above and must use supported patterns such as MERGE INTO). Runtimes below 12.2 fail securely and return no data.

Limitations to know:

• You can’t apply a row filter or mask to a view.
• Iceberg REST catalog and Unity REST API access, Delta Lake APIs and path-based access don’t work on protected tables.
• Time travel isn’t supported with table-level filters or masks, and deep and shallow clones aren’t supported.
• OpenSharing providers can’t share tables with table-level filters or masks.
• REPLACE TABLE keeps an existing row filter, and keeps masks on columns with the same names.
• Parameter types must match column types. With ANSI mode off, a value that can’t be cast becomes NULL silently, which can make a filter return every row; Databricks recommends spark.sql.ansi.enabled = true.

Common trap: Running DROP FUNCTION on a filter that is still attached - the table becomes inaccessible; run ALTER TABLE ... DROP ROW FILTER (or DROP MASK) first.

Get the whole book

This note is one section of Ultra Transcenders DP-750: Implementing Data Engineering Solutions Using Azure Databricks, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-750 terms in the glossary · All DP-750 study notes