12.3 Fine-Grained Security: Row Filters & Column Masks

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:

---

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

1. When a user executes SELECT * FROM sales_records;, Unity Catalog intercepts the query.
2. Unity Catalog executes the attached row filter UDF against each row's column attributes and the user's session properties.
3. 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

1. The column mask function receives the original raw data value of the column as an input parameter.
2. The function checks session conditions (e.g., is_account_group_member()).
3. 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.