Row Filters, Column Masks and ABAC Policies

What it is

Privileges decide whether a user can read a table at all. Row filters and column masks decide what the user sees inside it: which rows come back, and whether a sensitive column shows its real value.

Both are SQL user defined functions attached to a table. ABAC policies (attribute based access control) apply the same kind of function centrally, to every table and column that carries a governed tag, instead of table by table.

How it works

Row filters

A row filter is a function that returns a boolean for each row. Rows where it returns false are hidden from that user.

CREATE FUNCTION main.sec.us_only(region STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('admins') OR region = 'US';

ALTER TABLE main.sales.orders SET ROW FILTER main.sec.us_only ON (region);

The ON (region) clause maps table columns to the function's parameters. Admins see every row; everyone else sees only US rows. Remove the filter with ALTER TABLE main.sales.orders DROP ROW FILTER.

Column masks

A column mask is a function that takes the column value and returns what the user should see. The return type matches the column.

CREATE FUNCTION main.sec.mask_ssn(ssn STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('hr') THEN ssn
            ELSE concat('***-**-', right(ssn, 4)) END;

ALTER TABLE main.hr.employees ALTER COLUMN ssn SET MASK main.sec.mask_ssn;

HR members see the full value; others see only the last four digits. Remove it with ALTER COLUMN ssn DROP MASK. A filter or mask can also be set when the table is created.

Group based logic

is_account_group_member('group') checks the current user's membership in an account level group. It is the usual building block, because access then follows group membership, not names hard coded in the function.

Before row filters and column masks, the same effect was built with dynamic views: a view with CASE or WHERE logic on group membership, and users granted the view but not the table. Filters and masks protect the table itself, so every query path gets the rule.

ABAC policies

Per table filters and masks must be attached to each table. With hundreds of tables, that is easy to miss. ABAC moves the rule to a higher level and selects targets by governed tags.

A policy is created on a catalog, schema or table and applies to all tables beneath it. Its conditions use has_tag('name') and has_tag_value('name', 'value'), with WHEN to choose tables and MATCH COLUMNS to choose columns.

CREATE POLICY mask_ssn ON SCHEMA prod.customers
COLUMN MASK main.sec.ssn_last4
TO `us_analysts` EXCEPT `admins`
FOR TABLES
MATCH COLUMNS has_tag_value('pii', 'ssn') AS ssn
ON COLUMN ssn;

TO lists the principals the policy applies to, and EXCEPT lists principals who see unmodified data. A row filter policy has the same shape with ROW FILTER and USING COLUMNS to pass matched columns to the function.

A newly tagged column in a new table is protected as soon as the tag is applied, with no extra ALTER TABLE.

How the exam asks about this

• "Sales reps see only rows for their own region; managers see all." A row filter using is_account_group_member.
• "Only the fraud team sees full card numbers; others see the last four digits." A column mask on the column.
• "Every column tagged as PII across the catalog must be masked, including future tables." An ABAC policy on the catalog matching the governed tag.