Row-Level and Column-Level Security

Quick Answer: Column masks transform sensitive column values (e.g., masking SSNs) based on user identity. Row filters restrict which rows users can see. Both are defined at the table level in Unity Catalog and enforced automatically for all queries.

Column Masks

Column masks apply transformation functions to column values based on the querying user:

-- Create a masking function
CREATE FUNCTION mask_ssn(ssn STRING)
RETURN CASE
    WHEN is_account_group_member('pii-admins') THEN ssn
    ELSE concat('***-**-', substring(ssn, 8, 4))
END;

-- Apply the mask to a column
ALTER TABLE customers ALTER COLUMN ssn SET MASK mask_ssn;

How Column Masks Work

• PII admins see: 123-45-6789
• Other users see: ***-**-6789
• The mask is applied automatically on every query
• Users cannot bypass the mask — it is enforced at the engine level

Common Masking Patterns

Row Filters

Row filters restrict which rows a user can see:

-- Create a filter function
CREATE FUNCTION region_filter(region STRING)
RETURN (
    is_account_group_member('global-team')
    OR region = CASE
        WHEN is_account_group_member('us-team') THEN 'US'
        WHEN is_account_group_member('eu-team') THEN 'EU'
        ELSE 'NONE'
    END
);

-- Apply the row filter
ALTER TABLE orders SET ROW FILTER region_filter ON (region);

How Row Filters Work

• Global team sees: all rows
• US team sees: only rows where region = 'US'
• EU team sees: only rows where region = 'EU'
• Filters are applied transparently — users do not see the filter logic

Dynamic Views (Alternative Approach)

-- Create a dynamic view with embedded security
CREATE VIEW secure_customers AS
SELECT
    customer_id,
    name,
    CASE
        WHEN is_account_group_member('pii-admins') THEN email
        ELSE '***@***.***'
    END AS email,
    CASE
        WHEN is_account_group_member('pii-admins') THEN phone
        ELSE '***-***-****'
    END AS phone
FROM customers
WHERE (
    is_account_group_member('all-data')
    OR region = current_user_region()
);

Column Masks vs. Dynamic Views

On the Exam: Know that column masks and row filters are applied at the table level and cannot be bypassed. Understand the functions is_account_group_member() and current_user() for identity-based security.