Row filters bypassed by a definer-rights view and a lingering direct grant
A bank runs a Unity Catalog metastore with dev and prod catalogs. PII sits in prod.risk.customer, which has email, phone, national_id, and region. The analysts_emea group must see only region = 'EMEA' rows with email masked. Today the access path is prod.risk.v_customer_emea, a view owned by a service principal that holds SELECT on the base table; the view applies WHERE region = 'EMEA' and mask_email(email) in its select list. Analysts also still hold SELECT on prod.risk.customer because an older dashboard queries it directly - that dashboard shows US and APAC customers with unmasked emails. A contractor in analysts_emea was additionally granted MODIFY on the schema to fix a lookup table. Seven new tables are expected in the schema next quarter.
1. Why did the view-based control fail, and how do row filters and column masks differ from a view with a WHERE clause?
Read the worked answer
Two independent failures. First, the view is not the only path: SELECT on the base table lets any analyst or any dashboard service principal query prod.risk.customer directly, and the view's WHERE clause and masking expression simply do not exist in that query. Second, a Unity Catalog view evaluates with the owner's privileges, so the service principal's SELECT is what authorizes the read - the view grants no security boundary of its own, only a curated projection.
Row filters and column masks are attached to the table, not to a query, so they are applied whenever the table is read: through the view, through a dashboard, through a notebook, through a downstream table's definition. That is the property the bank actually needs.
The fix is ALTER TABLE prod.risk.customer SET ROW FILTER <fn> ON (region) where the function returns a boolean using is_account_group_member('analysts_emea') and the region predicate, plus ALTER TABLE ... ALTER COLUMN email SET MASK <fn>. Then REVOKE SELECT ON prod.risk.customer FROM analysts_emea or narrow it, keep the view as a convenience only, and grant SELECT on the view. Verify with a second account in the group, not the owner.
2. The contractor has `MODIFY` on the schema. Why is that a governance incident, and what grant model do you put in place so new tables are safe by default?
Read the worked answer
MODIFY on a schema is broad: it allows creating, altering, and dropping objects in that schema, and in Unity Catalog a user who owns or can alter a table can also remove its row filter or column mask, or simply create a copy of the data without them. A contractor needing to fix one lookup table should get USE SCHEMA on the schema plus MODIFY and SELECT on that single table - never schema-wide.
For the seven upcoming tables, safety comes from the absence of a grant, not from a promise. Unity Catalog grants nothing implicitly, so do not grant SELECT at schema level; grant USE SCHEMA only, and grant on specific tables and views. Apply the row filter and mask as part of the table creation script, and enforce that in review so a table cannot be merged without its policy. If the workspace supports tag-based policies, attach a governed tag such as pii at creation and let the tag drive the filter and mask centrally.
Finally, audit continuously: query system.access.audit for SELECT on the PII table and information_schema.table_privileges for unexpected grants, and alert on both. Ownership of the schema belongs to a group, never an individual, so the contractor leaving does not strand it.
3. Analysts with a legitimate need still report unmasked emails through one path. How do you find it, and what order do you apply the remediation?
Read the worked answer
Any unmasked read means a path where the mask is not in force. Enumerate first: information_schema.table_privileges shows direct grants on the base table, but the leak may also be through a second view or a table that was created as SELECT * from the base table before the mask existed. If a downstream table was built from the raw data, the mask does not retroactively apply to it - it must be masked at its own definition or rebuilt from the governed source.
Order of remediation: apply the row filter and mask to the base table first, so every existing query becomes safe immediately without changing analyst code. Then revoke the stale direct grant and any grants on derived copies, and route access through the governed view. Third, scan for copies of the masked columns in other tables and either mask them or rebuild them from the filtered source. Fourth, tell the dashboard owner what changed, because a dashboard showing 40% fewer rows will otherwise be reported as a data bug.
Run a verification as a member of analysts_emea: count rows and select email to confirm both controls are active, then check the audit log for the remediation timestamp.