PII, regional row access, and a partner share that leaked
ORDERS (800M rows) holds EMAIL, PHONE, and REGION. PII columns carry a PII tag and a tag-based masking policy; REGION is protected by a row access policy that maps CURRENT_ROLE() to a region via a mapping table. All 300 analysts across four regions use the shared ANALYST role. A quarterly loading job creates new tables with CREATE TABLE AS SELECT inside a stored procedure. A partner share, created by a pilot team, points at a secure view over ORDERS; the partner has reported seeing unmasked emails. An internal audit found APAC analysts reading EU rows. Nobody can currently say which objects the partner can actually see.
1. Explain both leaks. Which is a policy design error and which is a lifecycle error?
Read the worked answer
The regional leak is a policy design error. The row access policy keys on CURRENT_ROLE(), but every analyst in every region holds the same ANALYST role. The mapping table can only return one region per role, so either everyone sees one region or the mapping returns something permissive. The policy was written for a role-per-region model that was never implemented. It must key on an attribute that actually varies - the user, or a session attribute maintained per user - and the mapping table's coverage must include every user, with a default-deny branch for unmapped users.
The partner leak is a lifecycle error, not a masking-policy error. Tag-based masking only protects columns that carry the tag. Any path that produces a column without the tag - including objects built by CREATE TABLE AS SELECT in the loading procedure, and any view whose output columns are not tagged - is unmasked by construction. The secure view is not automatically safe just because its base table is.
Before fixing anything, enumerate what the share actually exposes: SHOW GRANTS TO SHARE, and the objects the share contains, not the objects someone intended it to contain.
2. Design the control set so that a new column or a new table cannot silently bypass protection.
Read the worked answer
Prevention has to sit at the point of creation, because detection after the fact is what failed here.
- Enforce classification at DDL time. Restrict CREATE TABLE / CREATE VIEW in the serving schemas to a governed role, and run a post-deployment check that every column in a PII-bearing schema carries the PII tag. Columns without a tag become a pipeline failure, not a quarterly surprise.
- Prefer tag-based masking as the default, but treat untagged columns as unprotected and monitor TAG_REFERENCES coverage per schema.
- Default-deny row access. The mapping table gets a deny branch, and the policy is tested against a user with no mapping row.
- Share only through a curated view layer, owned by a role that the ETL process does not hold. Nothing is shared directly from a base table.
- Verify with a reader account on every share change - the cheapest way to see the consumer's view of the data before the partner does.
- Audit with ACCESS_HISTORY to answer 'who read which columns' without guessing.
3. The partner says they need emails for their own matching. What options do you have, and what do you recommend?
Read the worked answer
Reframe the request: they need to match, not to read.
- Share a masked or tokenised value. If the partner matches against a list they can hash on their side, an HMAC of the email with a shared key gives them joinability without disclosure. Whether Snowflake's masking functions are the right tool depends on whether the transformation is stable across the rows they need to match - verify the behaviour in a reader account before promising it.
- Share a derived table, not the column. Provide a view exposing only matched identifiers or aggregated results, so the raw PII never crosses the boundary.
- Written exception as a last resort. If a contractual basis exists for sharing plaintext, document it, scope it to specific rows and a time window, and keep the exception outside the default view so removing it is a one-line change.
Recommendation: the tokenised or derived path, because it satisfies the business need without creating a second class of data that the governance model cannot see. State the residual risk explicitly - tokenisation is only as strong as key management, and that is now a control you own.