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

  1. User queries main.sales.customers
  2. Unity Catalog applies the row filter to each row
  3. Does the filter return TRUE for this row? Yes: Keep the row. No: Hide the row.
  4. Apply column masks to the kept rows
  5. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic