5.7 Row-Level and Column-Level Security

Quick Answer: Unity Catalog restricts data at the row and column level, evaluated at query time. Row filters drop rows a user may not see; column masks redact or transform sensitive column values. Both are SQL UDFs bound to a table. Dynamic views are the alternative — a view that uses current_user() and is_account_group_member() to filter or mask conditionally.

Row Filters and Column Masks

Sometimes SELECT on a table is too coarse — a sales rep should see only their region's rows, and most analysts should see emails masked. Row filters and column masks add fine-grained control on top of table grants:

• A row filter is a SQL UDF that returns a boolean per row; rows returning false are hidden. It is attached to the table and applied automatically to every query.
• A column mask is a SQL UDF applied to a specific column; it transforms the value (e.g., returns *** or a hash) based on who is querying.

Both are evaluated at query time against the current user's identity, so one physical table serves many audiences.

Applying Filters and Masks

You define a UDF, then attach it. A column mask example:

CREATE FUNCTION mask_email(email STRING)
RETURN CASE WHEN is_account_group_member('pii_admins')
            THEN email ELSE '***@***' END;

ALTER TABLE customers
  ALTER COLUMN email SET MASK mask_email;

A row filter example:

CREATE FUNCTION region_filter(region STRING)
RETURN is_account_group_member('all_regions') OR region = current_user_region();

ALTER TABLE sales SET ROW FILTER region_filter ON (region);

Key identity functions used inside these UDFs and in dynamic views:

Dynamic Views and ABAC Policies

A dynamic view is the alternative pattern: instead of altering the base table, you create a view that reshapes data and gate it with the same functions. Grant users access to the view, not the underlying table.

CREATE VIEW customers_secure AS
SELECT id,
  CASE WHEN is_account_group_member('pii_admins')
       THEN email ELSE 'REDACTED' END AS email
FROM customers
WHERE is_account_group_member('all_regions') OR region = current_region();

Dynamic views are flexible (they can also join and transform), but you must remember to revoke direct access to the base table or users could bypass the view.

For enterprise scale, ABAC policies built on governed tags apply masks and filters centrally — defined once at a catalog/schema and enforced automatically on any tagged column or table. Because higher-level admins set them, table owners cannot override or remove them, and the policy logic evaluates more efficiently than per-table UDFs. Use dynamic views or table-level UDFs for targeted cases; use ABAC policies when the same rule must blanket many objects.

Choosing Between the Three Approaches

Unity Catalog offers three ways to do fine-grained, query-time access control, and the exam expects you to pick the right one:

The big advantage of row filters and column masks over dynamic views is that they protect the base table directly, so users cannot bypass them by querying the raw table — there is no separate view to grant carefully. Dynamic views, by contrast, only protect data if you remember to revoke direct access to the underlying table.

Combining Filters and Masks

Row filters and column masks compose: a single table can carry one row filter plus multiple column masks (one per column), and they evaluate together at query time against the caller's identity. Because the UDFs reference current_user() and is_account_group_member(), the same physical table returns different results to different users — a regional manager sees their region's rows with full emails, while a general analyst sees a filtered, masked view. This single-copy approach avoids maintaining multiple derived tables and keeps the security logic in one auditable place.

Tying Fine-Grained Security to the Exam

The key exam takeaway is that Unity Catalog enforces row- and column-level security at query time against the caller's identity, so one governed table can safely serve many audiences. Row filters decide which rows appear; column masks decide how individual values are revealed or redacted; both are SQL UDFs that lean on current_user() and is_account_group_member(). Dynamic views accomplish similar goals by wrapping base tables, but they shift the burden onto you to revoke direct base-table access — forget that step and the protection is bypassable.

For breadth, ABAC policies driven by governed tags let an administrator apply the same filter or mask across many tables centrally, beyond the reach of individual table owners. When a question describes restricting which rows a user sees, think row filter or dynamic-view WHERE; when it describes hiding or transforming a column's values, think column mask or CASE expression; and when it describes a rule that must blanket many objects, think ABAC policy with governed tags.