5.1 Dynamic Row Filters & Column Masking Implementation

5.1 Dynamic Row Filters & Column Masking Implementation

DP-750 Exam Focus: Master fine-grained access control (FGAC) in Unity Catalog using native row filters and column masks. Understand how to write scalar SQL User-Defined Functions (UDFs) with is_account_group_member() and session_user(), attach them to base tables via ALTER TABLE ... SET ROW FILTER and ALTER COLUMN ... SET MASK, evaluate multi-column conditional masking (USING COLUMNS), and analyze query rewrite mechanics in the Catalyst optimizer.

---

1. Evolution of Fine-Grained Access Control (FGAC)

In legacy data architectures (and early Hive Metastore implementations on Databricks), securing subsets of rows or sensitive columns required creating intermediate Dynamic Views. Data engineers wrote views containing CASE statements or WHERE clauses that evaluated user context, revoked base table permissions, and granted users access strictly to the view.

While dynamic views provided basic security, they introduced significant architectural drawbacks:

• Namespace Sprawl & Management Overhead: Every security tier or user persona required a distinct view (e.g., customers_us_view, customers_eu_view, customers_redacted_view).
• Broken Write Pathways: Users could not issue INSERT, UPDATE, DELETE, or MERGE operations through views containing complex conditional logic.
• Fragmented Metadata & Lineage: Downstream BI tools connected to disparate view names rather than the canonical base table, obscuring data lineage and complicating performance optimization.

Unity Catalog Row Filters and Column Masks solve these challenges by binding security logic directly to the base table metadata. The table maintains a single, unified three-level namespace identifier (catalog.schema.table), and Unity Catalog transparently enforces filtering and masking at query runtime across all compute modalities.

Comparative Analysis: Legacy Dynamic Views vs. Unity Catalog FGAC

---

2. Built-In Contextual Functions

Row filters and column masks rely on Databricks built-in contextual functions to determine the identity, group membership, and runtime environment of the executing session.

+-----------------------------------------------------------------------------------------+
|                         UNITY CATALOG CONTEXTUAL FUNCTIONS                              |
+-----------------------------------------------------------------------------------------+
|                                                                                         |
|  1. is_account_group_member('group_name')                                               |
|     - Evaluates whether the active querying user belongs to the specified Databricks    |
|       account-level group (e.g., 'finance_analysts', 'compliance_officers').             |
|     - RECOMMENDED: Operates natively with SCIM-synchronized Entra ID groups.            |
|                                                                                         |
|  2. session_user() / current_user()                                                     |
|     - Returns the authenticated user's email address or service principal Application   |
|       ID (e.g., 'jane.doe@enterprise.com', 'a1b2c3d4-e5f6-...').                         |
|     - session_user() returns the primary interactive user even inside nested contexts.   |
|                                                                                         |
|  3. is_member('group_name')                                                             |
|     - Legacy workspace-level group check. In Unity Catalog, always prefer                |
|       is_account_group_member() for multi-workspace consistency.                        |
+-----------------------------------------------------------------------------------------+

Exam Tip: For DP-750 questions concerning Unity Catalog security, always choose is_account_group_member() over the legacy is_member(). Unity Catalog identities are managed centrally at the account level and synchronized via Microsoft Entra ID (Azure AD) SCIM; therefore, account group evaluation guarantees uniform authorization across all regional workspaces.

---

3. Dynamic Row Filters: Architectural Principles & DDL Syntax

A Row Filter is a SQL User-Defined Function (UDF) that returns a BOOLEAN result. When attached to a table, Unity Catalog evaluates the UDF for every candidate row during query execution. If the function evaluates to TRUE, the row is returned to the user; if it evaluates to FALSE or NULL, the row is silently filtered out.

                                ROW FILTER EXECUTION FLOW
                                
  Query: SELECT * FROM prod.sales.orders;
                        |
                        v
           +--------------------------+
           |  Scan Base Delta Table   |
           +--------------------------+
                        |
                        v
           +--------------------------+
           | Evaluate Filter SQL UDF  | <--- is_account_group_member('eu_sales')
           +--------------------------+
             /                      \
   (Evaluates TRUE)           (Evaluates FALSE)
          /                            \
         v                              v
  +------------------+         +------------------+
  | Row Included in  |         | Row Filtered Out | 
  | Query Result Set |         | (Never Returned) |
  +------------------+         +------------------+

Step-by-Step Row Filter Implementation

Step 1: Create the Row Filter SQL UDF

The UDF accepts one or more table column arguments and implements the conditional authorization logic:

-- Create a reusable row filter function in a shared governance schema
CREATE OR REPLACE FUNCTION prod.governance.region_row_filter(region_code STRING)
RETURNS BOOLEAN
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
COMMENT 'Filters rows based on user regional group membership or admin status'
RETURN
  -- System administrators and auditors see all records
  is_account_group_member('enterprise_auditors')
  OR is_account_group_member('data_platform_admins')
  -- Regional managers see records matching their specific operating region
  OR (is_account_group_member('na_sales_reps') AND region_code = 'NA')
  OR (is_account_group_member('eu_sales_reps') AND region_code = 'EU')
  OR (is_account_group_member('apac_sales_reps') AND region_code = 'APAC');

Step 2: Apply the Row Filter to a Base Table

You can attach the filter during initial table creation or alter an existing table:

-- Method A: Applying to an existing Delta table
ALTER TABLE prod.sales.customer_orders
SET ROW FILTER prod.governance.region_row_filter ON (order_region);

-- Method B: Applying during table creation (CTAS or DDL)
CREATE TABLE prod.sales.regional_metrics (
  metric_id BIGINT GENERATED ALWAYS AS IDENTITY,
  region_code STRING,
  metric_date DATE,
  revenue_amount DECIMAL(18,2)
)
WITH ROW FILTER prod.governance.region_row_filter ON (region_code);

Step 3: Multi-Column Row Filters

Row filter functions can accept multiple column arguments to evaluate composite business rules:

-- UDF accepting both business unit and classification status
CREATE OR REPLACE FUNCTION prod.governance.multi_attr_filter(dept_name STRING, is_confidential BOOLEAN)
RETURNS BOOLEAN
RETURN
  is_account_group_member('executive_board')
  OR (is_account_group_member('hr_department') AND dept_name = 'HR')
  OR (is_account_group_member('general_staff') AND is_confidential = FALSE);

-- Attach multi-column filter
ALTER TABLE prod.hr.employee_records
SET ROW FILTER prod.governance.multi_attr_filter ON (department, confidential_flag);

Step 4: Removing or Updating Row Filters

To drop an existing filter from a table:

ALTER TABLE prod.sales.customer_orders DROP ROW FILTER;

---

4. Column Masking: Architectural Principles & DDL Syntax

A Column Mask is a scalar SQL UDF that accepts the value of a target column (and optionally additional columns) and returns a masked or transformed value of the exact same data type.

                               COLUMN MASK EXECUTION FLOW
                               
  Query: SELECT ssn, full_name, email FROM prod.customers.pii_data;
                        |
                        v
           +--------------------------+
           |  Scan Base Delta Table   |
           +--------------------------+
                        |
                        v
           +--------------------------+
           | Evaluate Mask SQL UDF    | <--- is_account_group_member('hr_admins')
           +--------------------------+
             /                      \
     (Is Member: TRUE)       (Is Member: FALSE)
            /                        \
           v                          v
  +------------------+       +----------------------+
  | Return Original: |       | Return Masked Value: |
  | '123-45-6789'    |       | '***-**-6789'        |
  +------------------+       +----------------------+

Step-by-Step Column Mask Implementation

Step 1: Create the Column Mask SQL UDF

The return data type must match the target column data type exactly:

-- Create a masking function for Social Security Numbers (SSN)
CREATE OR REPLACE FUNCTION prod.governance.ssn_mask(ssn_val STRING)
RETURNS STRING
LANGUAGE SQL
DETERMINISTIC
COMMENT 'Redacts SSN for non-compliance users, showing only last 4 digits'
RETURN
  CASE 
    WHEN is_account_group_member('compliance_hr_specialists') THEN ssn_val
    WHEN ssn_val IS NULL THEN NULL
    ELSE concat('***-**-', substring(regexp_replace(ssn_val, '[^0-9]', ''), 6, 4))
  END;

-- Create a masking function for financial amounts (DECIMAL)
CREATE OR REPLACE FUNCTION prod.governance.salary_mask(salary_amount DECIMAL(18,2))
RETURNS DECIMAL(18,2)
RETURN
  CASE 
    WHEN is_account_group_member('payroll_officers') THEN salary_amount
    ELSE CAST(NULL AS DECIMAL(18,2))
  END;

Step 2: Apply the Column Mask to Table Columns

Attach the mask using ALTER TABLE ... ALTER COLUMN:

-- Apply SSN mask to the ssn column
ALTER TABLE prod.customers.pii_data 
ALTER COLUMN ssn SET MASK prod.governance.ssn_mask;

-- Apply salary mask to the compensation column
ALTER TABLE prod.hr.compensation_details
ALTER COLUMN base_salary SET MASK prod.governance.salary_mask;

Step 3: Conditional Masking with Additional Columns (USING COLUMNS)

Unity Catalog allows column masks to evaluate secondary context columns from the same table row to dynamically decide whether to mask the primary column. This is achieved using the USING COLUMNS clause:

-- Mask UDF that checks a country_code column in addition to the phone_number
CREATE OR REPLACE FUNCTION prod.governance.conditional_phone_mask(
  phone STRING,
  country STRING
)
RETURNS STRING
RETURN
  CASE 
    -- Full access if compliance admin
    WHEN is_account_group_member('global_privacy_admins') THEN phone
    -- Country-specific access rules
    WHEN is_account_group_member('eu_support') AND country = 'FR' THEN phone
    WHEN is_account_group_member('eu_support') AND country = 'DE' THEN phone
    WHEN is_account_group_member('us_support') AND country = 'US' THEN phone
    -- Otherwise redact everything except the last 3 digits
    ELSE concat('XXX-XXX-', substring(phone, length(phone) - 2, 3))
  END;

-- Apply the mask using the secondary column 'residence_country'
ALTER TABLE prod.customers.contact_directory
ALTER COLUMN phone_number 
SET MASK prod.governance.conditional_phone_mask USING COLUMNS (residence_country);

Step 4: Removing Column Masks

To remove a column mask from a column:

ALTER TABLE prod.customers.contact_directory
ALTER COLUMN phone_number DROP MASK;

---

5. Execution Engine, Catalyst Optimizer Rewriting, & Security Guarantees

Understanding the runtime mechanics of how Unity Catalog enforces row filters and column masks is essential for performance tuning and troubleshooting on the DP-750 exam.

+-----------------------------------------------------------------------------------------+
|                       SPARK CATALYST QUERY REWRITE LIFECYCLE                            |
+-----------------------------------------------------------------------------------------+
|                                                                                         |
|  1. User Submits Query:                                                                 |
|     SELECT customer_id, ssn, total_spend FROM sales.orders WHERE total_spend > 5000;    |
|                                                                                         |
|  2. Metastore Security Resolution:                                                      |
|     - Validates user has SELECT privilege on sales.orders.                              |
|     - Discovers active Row Filter: region_row_filter(order_region)                     |
|     - Discovers active Column Mask: ssn_mask(ssn)                                       |
|                                                                                         |
|  3. Logical Plan Transformation (Rewritten Query):                                      |
|     SELECT                                                                              |
|       customer_id,                                                                      |
|       ssn_mask(ssn) AS ssn,                                                             |
|       total_spend                                                                       |
|     FROM sales.orders                                                                   |
|     WHERE region_row_filter(order_region) = TRUE                                        |
|       AND (total_spend > 5000);                                                         |
|                                                                                         |
|  4. Physical Optimization & Predicate Pushdown:                                         |
|     - Push deterministic filter expressions into Delta scan file pruning.               |
|     - Vectorized evaluation of mask UDF in Photon / Spark executor CPU registers.       |
+-----------------------------------------------------------------------------------------+

Security & Side-Channel Attack Guarantees

A common vulnerability in database security systems is the error-based side-channel attack, where a malicious user attempts to deduce hidden data by injecting expressions that trigger division-by-zero or casting errors on filtered rows (e.g., SELECT * FROM table WHERE 1 / (CASE WHEN secret_col = 'admin_password' THEN 0 ELSE 1 END) = 1).

Unity Catalog provides rigorous mathematical guarantees against side-channel leaks:

1. Predicate Reordering Barrier: The Catalyst optimizer enforces that row filter predicates are evaluated prior to or in strict conjunction with user-supplied WHERE clauses. The engine guarantees that user expressions never evaluate against unauthorized rows.
2. Deterministic Evaluation: Functions used in row filters and column masks must be deterministic and side-effect free. They cannot execute arbitrary external Python code or invoke un-audited network sockets.
3. Subquery Restrictions: Row filter and column mask UDFs cannot execute subqueries against other mutable tables (to prevent locking loops and race conditions).

Write-Path Semantics (DML Operations)

How do row filters and column masks behave during write operations (INSERT, UPDATE, DELETE, MERGE)?

• Row Filter Enforcement on Writes:
  • When a user issues an UPDATE or DELETE on a table with a row filter, the statement only affects rows that the user is permitted to see. If a user tries to run DELETE FROM orders;, they will delete only the records satisfying their row filter; unauthorized rows remain completely untouched.
  • When inserting rows (INSERT INTO), if the inserted row would evaluate to FALSE under the user's row filter, the write is permitted, but subsequent SELECT queries by that same user will not display the newly inserted row.
• Column Mask Enforcement on Writes:
  • Users with write permissions (MODIFY) must supply the actual, unmasked data when executing INSERT or UPDATE statements. Masking is strictly applied during read projection (SELECT), preventing users from accidentally overwriting raw data with masked string constants.

---

6. Administrative Privileges, Ownership, & Governance Lifecycle

Implementing fine-grained access control requires specific Unity Catalog privileges across the catalog, schema, function, and table securable hierarchy.

Privilege Matrix for Creating and Applying Policies

-- Administrative Script: Delegating Governance Setup
-- 1. Grant governance team permission to manage functions in governance schema
GRANT USAGE, CREATE FUNCTION ON SCHEMA prod.governance TO `data_security_officers`;

-- 2. Allow data engineers to execute the mask function
GRANT EXECUTE ON FUNCTION prod.governance.ssn_mask TO `data_engineers`;
GRANT EXECUTE ON FUNCTION prod.governance.region_row_filter TO `data_engineers`;

-- 3. Data engineers apply the filter to the sales table
GRANT MODIFY ON TABLE prod.sales.customer_orders TO `data_engineers`;

-- 4. Analysts only receive SELECT on the table
GRANT SELECT ON TABLE prod.sales.customer_orders TO `business_analysts`;