ExamFinch.com My Study

Study guide

12.3 Fine-Grained Security: Row Filters & Column Masks

Practice this exam

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

12.3 Fine-Grained Security: Row Filters & Column Masks

Key Takeaways Explain This Topic AI

Row filters dynamically restrict which rows a user can view based on session context functions like is_account_group_member() or column attribute values.

Column masks dynamically transform or redact specific column values (e.g., masking PII/SSN to show only the last 4 digits) depending on the querying user's group membership.

Row filters and column masks are implemented using SQL User-Defined Functions (UDFs) attached directly to Delta Lake tables in Unity Catalog.

Fine-grained security policies apply transparently across all queries, notebooks, Databricks SQL Warehouses, and connected BI tools without altering downstream SQL code.

Applying a row filter or column mask requires ALTER TABLE privileges or table ownership, along with EXECUTE privilege on the governing UDF.

Last updated: July 2026

Traditional coarse-grained access control in databases relies on granting or denying access to entire tables or views ( GRANT SELECT ON TABLE ). However, modern enterprise data governance frequently requires fine-grained access control (FGAC) —restricting specific rows based on user attributes or obfuscating sensitive columns containing Personally Identifiable Information (PII) like Social Security Numbers, credit card details, or salary data. Databricks Unity Catalog provides native Row Filters and Column Masks that enforce dynamic, fine-grained security policies directly on Delta Lake tables without requiring duplicate tables or complex view hierarchies.

Architectural Advantages: Native Security vs. Legacy Views

Before Unity Catalog fine-grained security, organizations created custom dynamic SQL views ( CREATE VIEW ... WHERE region = current_user_region() ) to enforce row and column security. While functional, legacy view-based approaches suffered from severe operational drawbacks:

Metric / Dimension Legacy View-Based Security Unity Catalog Row Filters & Column Masks

Lineage & Discovery Obscured; downstream users query views, losing direct lineage to raw Delta tables. Preserved; lineage tracks directly back to the underlying table while enforcing security.

Governance Overhead High; requires maintaining hundreds of custom view definitions across schemas. Low; single policy UDF attached directly to the Delta table governs all queries.

Storage & Performance Can cause query optimizer bypasses and complex view wrapping penalties. Pushed down directly into the Photon execution engine for optimized execution.

Access Channel Consistency Only protects queries routed through specific views; raw table access remains exposed. Universal protection across Databricks SQL, AI/BI Dashboards, PySpark, and JDBC/ODBC connectors.

Row Filters: Dynamic Row-Level Security (RLS)

A Row Filter is a SQL scalar User-Defined Function (UDF) attached to a Unity Catalog table that evaluates a boolean condition ( TRUE or FALSE ) for each row based on the querying user's session context.

How Row Filters Function

When a user executes SELECT * FROM sales_records; , Unity Catalog intercepts the query.

Unity Catalog executes the attached row filter UDF against each row's column attributes and the user's session properties.

If the UDF returns TRUE , the row is included in the query result. If it returns FALSE , the row is silently omitted.

Key Built-In Session Functions

is_account_group_member('group_name') : Evaluates whether the querying user belongs to the specified Unity Catalog account group.

current_user() : Returns the email address of the active user executing the query.

SQL Implementation Example: Region-Based Row Filtering

Suppose an enterprise table finance.sales.transactions contains global transaction data. Regional managers should only see transactions for their region, while members of group_executives must see all transactions. -- Step 1: Create the SQL UDF defining the row filtering logic

  • CREATE FUNCTION finance.sales.region_filter(region STRING)
  • RETURN IF(
  • is_account_group_member('group_executives'),
  • TRUE,
  • is_account_group_member(CONCAT('group_mgr_', LOWER(region)))
  • );
  • - Step 2: Attach the row filter UDF to the Delta Lake table
  • ALTER TABLE finance.sales.transactions
  • SET ROW FILTER finance.sales.region_filter ON (region);
  • Column Masks: Dynamic Column-Level Security & Redaction

A Column Mask is a SQL UDF attached to a specific table column that dynamically transforms, obfuscates, or redacts column values based on the querying user's authorization level.

How Column Masks Function

The column mask function receives the original raw data value of the column as an input parameter.

The function checks session conditions (e.g., is_account_group_member() ).

Privileged users receive the raw, unmasked data value. Unprivileged users receive a transformed output (e.g., masked string or NULL ).

SQL Implementation Example: PII Social Security Number Masking

Suppose hr.employee_db.personnel contains an ssn column. Members of group_hr_compliance require full 9-digit SSNs, whereas all other analysts should only view the last 4 digits (e.g., XXX-XX-1234 ). -- Step 1: Create the Column Mask UDF

  • CREATE FUNCTION hr.employee_db.ssn_mask(ssn STRING)
  • RETURN CASE
  • WHEN is_account_group_member('group_hr_compliance') THEN ssn
  • ELSE CONCAT('XXX-XX-', RIGHT(ssn, 4))
  • END;
  • - Step 2: Apply the column mask to the SSN column
  • ALTER TABLE hr.employee_db.personnel
  • ALTER COLUMN ssn SET MASK hr.employee_db.ssn_mask;
  • Required Privileges for Fine-Grained Security

To successfully create, apply, or drop row filters and column masks, a administrative user must possess specific privileges across both the target table and the governing UDF:

Table Level : Must be the Owner of the table or hold ALTER TABLE privileges on the target table.

UDF Level : Must hold EXECUTE privilege on the filter/mask UDF, as well as USE CATALOG and USE SCHEMA on the UDF's parent catalog and schema.

Data Access : End users querying the table only require standard SELECT privileges on the table, plus USE CATALOG and USE SCHEMA . They do not require direct EXECUTE privileges on the underlying mask UDF to read masked data.

  • Administrative Commands: Managing Security Policies -- View existing policies on a table
  • DESCRIBE TABLE EXTENDED finance.sales.transactions;
  • - Remove a row filter from a table
  • ALTER TABLE finance.sales.transactions DROP ROW FILTER;
  • - Remove a column mask from a table column
  • ALTER TABLE hr.employee_db.personnel ALTER COLUMN ssn DROP MASK;
  • Real-World Exam Scenarios & Common Pitfalls
  • Scenario 1: Transparent Query Execution

Question : An analyst runs SELECT ssn, salary FROM hr.employee_db.personnel; and receives masked SSN values XXX-XX-8812 . Why did the query succeed without throwing a permissions error?

Explanation : Column masks operate transparently. The user holds valid SELECT access, so Unity Catalog applies the mask function during query evaluation rather than denying read access.

Scenario 2: UDF Permission Errors During Attachment

Question : A data engineer attempts to run ALTER TABLE sales SET ROW FILTER region_filter ON (region); and receives Permission Denied: Missing EXECUTE privilege .

Explanation : Attaching a filter UDF requires EXECUTE on the function region_filter . The engineer must be granted GRANT EXECUTE ON FUNCTION region_filter TO principal; .

Summary Checklist for Exam Readiness

Row filters control which rows a user can see (Row-Level Security).

Column masks control how column values are presented (Column-Level Security/Redaction).

Security policies are defined using SQL UDFs and attached via ALTER TABLE .

is_account_group_member() and current_user() are core functions used in security UDF logic.

Table owners or users with ALTER TABLE + UDF EXECUTE can manage policy attachments.