What you’ll learn

• What row filters and column masks are, and when to use them (vs. dynamic views)
• Minimal, copy‑paste examples with account group checks
• How to apply, test, modify, and remove filters/masks safely
• Mapping‑table pattern for fine‑grained per‑user or per‑region access
• Performance tips and common gotchas

Prereqs: Unity Catalog enabled; DBR 12.2 LTS+ for reads (see notes for dedicated compute), and a SQL warehouse or UC‑enabled cluster.

Concepts in 30 seconds

• Row filter → a boolean policy (UDF) attached to a table that hides disallowed rows at query time.
• Column mask → a policy (UDF) attached to a column that returns a redacted value per row based on who’s querying.
• Both are policies stored in UC and enforced automatically—your table name and queries don’t change.

When to use vs. Dynamic Views

• Use row filters/masks when you want the logic on the table itself (no new object name).
• Use a dynamic view when you need a read‑only layer that combines multiple inputs or you want to publish a filtered alias.

Fast start: group‑based row filter (SQL)

1. Create a policy function (returns BOOLEAN). Example: admins see all; others only see US rows.

CREATE OR REPLACE FUNCTION governance.us_filter(region STRING)
RETURNS BOOLEAN
RETURN IF(is_account_group_member('admin'), true, region = 'US');

2. Apply it to a table (one row filter per table):

ALTER TABLE sales SET ROW FILTER governance.us_filter ON (region);
-- or at creation
-- CREATE TABLE sales(region STRING, id INT)
--   WITH ROW FILTER governance.us_filter ON (region);

3. Test as different users/groups (you’ll see a subset of rows without changing queries):

SELECT * FROM sales;

Remove / change the filter

ALTER TABLE sales DROP ROW FILTER;                -- remove
CREATE OR REPLACE FUNCTION governance.us_filter... -- update logic

Tip: Always drop the filter from the table before dropping the function to avoid an orphaned policy.

Fast start: column mask for PII (SQL)

Mask SSNs unless the user is in the HR group.

CREATE OR REPLACE FUNCTION governance.mask_ssn(ssn STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('HumanResourceDept')
            THEN ssn ELSE '***-**-****' END;

ALTER TABLE hr.curated.employees
  ALTER COLUMN ssn SET MASK governance.mask_ssn;  -- apply mask
-- To remove later: ALTER TABLE ... ALTER COLUMN ssn DROP MASK;

Mapping‑table pattern (fine‑grained access without many groups)

Use a small permission table to map who can see what (user or group → region/account). Reference it in the filter function.

-- 1) A mapping table of allowed regions per principal
CREATE TABLE governance.allowed_regions(
  principal STRING,  -- group or user name
  region    STRING
);
-- Example data
INSERT INTO governance.allowed_regions VALUES
  ('analyst_us','US'),
  ('analyst_eu','EU'),
  ('admin','*');

-- 2) Row‑filter function that consults the mapping
CREATE OR REPLACE FUNCTION governance.region_allowed(region STRING)
RETURNS BOOLEAN
RETURN (
  is_account_group_member('admin')
  OR EXISTS (
       SELECT 1 FROM governance.allowed_regions ar
       WHERE (is_account_group_member(ar.principal) OR current_user() = ar.principal)
         AND (ar.region = region OR ar.region = '*')
  )
);

-- 3) Apply to tables that carry a region column
ALTER TABLE finance.sales SET ROW FILTER governance.region_allowed ON (region);

Scale by editing the mapping rows—no code changes needed. For very large orgs, consider ABAC policies with governed tags as a more centralized approach.

Permissions you need

• To assign a filter/mask: EXECUTE on the function, plus USE SCHEMA and USE CATALOG. To alter an existing table, you must be owner or have MANAGE on that table.
• To read filtered/masked tables: use a SQL warehouse or a UC‑enabled cluster (see runtime notes below).

Performance tips

• Keep policy functions simple and deterministic; avoid heavy subqueries.
• Minimize function arguments; each extra column argument can limit optimization.
• One table can have one row filter—don’t chain many conditions if you can avoid it.

Runtime & limitations (heads‑up)

• Supported on DBR 12.2 LTS+ (reads). Dedicated access mode requires 15.4 LTS+ for reads; 16.3+ for writes (via supported patterns like MERGE).
• Not supported on views; time travel; some deep/shallow clones; or certain complex MERGE cases.

Troubleshooting

• “Function not found / can’t apply” → Check USE CATALOG/SCHEMA, spelling, and EXECUTE privilege on the function.
• Table becomes inaccessible after dropping function → Re‑attach with ALTER TABLE <tbl> DROP ROW FILTER (or ... DROP MASK) to remove the orphaned reference.
• No effect in notebooks → Confirm your compute meets the UC/runtime requirements and that you’re querying the same 3‑part‑name table that has the policy.

Handy cheatsheet

• Add row filter: ALTER TABLE <tbl> SET ROW FILTER <fn> ON (<col_or_literal>[,...]);
• Remove row filter: ALTER TABLE <tbl> DROP ROW FILTER;
• Add column mask: ALTER TABLE <tbl> ALTER COLUMN <col> SET MASK <fn> [USING COLUMNS (...)];
• Remove mask: ALTER TABLE <tbl> ALTER COLUMN <col> DROP MASK;
• Group check: is_account_group_member('<group>')

Wrap‑up

Row filters and column masks let you lock sensitive data down at the table itself without changing query patterns. Start with a simple group‑based policy, move to a mapping‑table if you need per‑user rules, and consider ABAC for centralized, scalable governance across catalogs.

Mohammad Gufran Jahangir