Databricks DE · Topic 8.3

Data-Level Security

Control which rows and column values each user sees — row filters, column masks, and dynamic views, all keyed off user/group identity. This deep dive walks each with the verified SQL.

row filtercolumn maskdynamic viewis_account_group_member

The one-liner

Row filters hide rows (UDF RETURNS BOOLEAN), column masks redact values (transform UDF), and dynamic views wrap a table to filter/mask — all varying by current_user() / is_account_group_member(). Object GRANT can't do this.

📄 Analogy: a redacted document — everyone gets "the same file," but a row filter removes whole paragraphs you're not cleared for, and a column mask blacks out specific words (emails) based on your clearance.

Same table, different view per role — click a role

query: SELECT id, region, email FROM customers

Auditor (admin)

Support (no PII)

Manager — West

Three mechanisms — click each

Row filter — UDF RETURNS BOOLEAN attached to the table; rows returning FALSE are hidden.

CREATE FUNCTION region_filter(region STRING) RETURNS BOOLEAN
  RETURN is_account_group_member('admins')
      OR is_account_group_member('region_' || region);
ALTER TABLE prod.sales.customers SET ROW FILTER region_filter ON (region);

Column mask — UDF that transforms a column value per user; USING COLUMNS can reference other columns.

CREATE FUNCTION mask_email(e STRING) RETURNS STRING
  RETURN CASE WHEN is_account_group_member('pii_readers') THEN e ELSE '***@***' END;
ALTER TABLE prod.sales.customers ALTER COLUMN email SET MASK mask_email;

Dynamic view — a view that filters/masks; grant the view instead of the table. Best for curated/shared slices.

CREATE VIEW prod.sales.cust_safe AS
SELECT id, region,
  CASE WHEN is_account_group_member('pii_readers') THEN email ELSE '***' END AS email
FROM prod.sales.customers
WHERE is_account_group_member('admins') OR is_account_group_member('region_' || region);

How it composes with GRANT (8.2)

Grants decide whether you can touch the table; filters/masks decide what you see once you can. A user still needs USE CATALOG + USE SCHEMA + SELECT — and then the row filter/mask shapes the result.

Row filter / mask vs dynamic view

	Row filter / column mask	Dynamic view
Attaches to	the base table (`ALTER TABLE`)	a separate view object
Users query	the table directly	the view (grant on view)
Best for	universal table-level enforcement	curated/shared data slices

Why it matters

Real data has PII and tenant boundaries — you can't just GRANT the whole table. This is how one table safely serves many audiences.
"Mask the email column / hide other regions' rows" → column mask + row filter.

🔏 Real-world use case: one customers table — support sees all rows but email masked; regional managers see only their region's rows; auditors see everything.

Uses, edge cases & limitations

Uses: PII masking, multi-tenant/region row isolation, "same table, different view per role."
Edge cases: keep UDFs simple — they run per row/query and the optimizer "always makes the secure choice" (perf cost); limit distinct masks; drive rules from is_account_group_member() or a mapping table (no invented helper functions).
Limitation (verified): a table with row filters/column masks does not support time travel, CLONE (deep/shallow), or Delta Lake API access. At scale, prefer ABAC by tag (8.4).

Common gotchas

❌ Using object GRANT to hide rows/columns — it can't; use filters/masks/dynamic views.
❌ Omitting RETURNS BOOLEAN (row filter) / RETURNS <type> (mask) in the signature.
❌ Inventing a per-user helper — use is_account_group_member() / current_user() or a mapping table.
❌ Forgetting filters/masks disable time travel & CLONE on that table.
❌ Hand-managing masks on hundreds of tables when ABAC by tag would scale.