I85PREP
⌘ K
SnowPro Core study materials
SnowPro Core study resources

A Masking Policy That Silently Broke a Join

SnowPro Core · Put your knowledge to work in context.

All case studies

A Masking Policy That Silently Broke a Join

A healthcare payer's CLAIMS table has a masking policy on MEMBER_ID: the policy returns the real value only when CURRENT_ROLE() is PAYER_ADMIN and returns '***MASKED***' otherwise. ANALYST has SELECT on the table. A marketing team publishes ENROLLMENT, which maps MEMBER_ID to campaign codes, and a report joins the two tables on MEMBER_ID. Since the policy was applied, that report returns zero rows for ANALYST but works for PAYER_ADMIN. ANALYST also inherits from CLINICAL_READ, a role that is supposed to see unmasked member IDs. No one has changed the report SQL.

1. Explain why the join returns zero rows, and why granting CLINICAL_READ to ANALYST does not fix it.

Read the worked answer

Two independent mechanisms are at play.

  1. Masking is applied at query time, inside the predicate. When ANALYST runs the query, CLAIMS.MEMBER_ID evaluates to the literal '***MASKED***' for every row, so the join compares a constant against real enrollment IDs and matches nothing. It is not a permission error - the query succeeds and returns an empty result, which is why it went unnoticed. Data access policies rewrite the value, not the row set.
  2. CURRENT_ROLE() returns only the session's active primary role, not the role hierarchy. Even after GRANT ROLE CLINICAL_READ TO ROLE ANALYST, a session running as ANALYST still evaluates CURRENT_ROLE() as ANALYST and stays masked. Only USE ROLE CLINICAL_READ in the session (or rewriting the policy with IS_ROLE_IN_SESSION('CLINICAL_READ')) changes the outcome.

Diagnostic habit worth building: when a query returns fewer rows than expected rather than erroring, check for masking and row access policies on the joined columns before debugging the join logic.

2. How do you make the report work for ANALYST without exposing member IDs, and what governance step is required?

Read the worked answer

First decide what ANALYST actually needs: campaign codes, not member IDs.

  • Preferred: move the masking boundary. Create a secure view owned by a privileged role that performs the join on the unmasked MEMBER_ID and projects only CAMPAIGN_CODE, CLAIM_AMOUNT, and other non-PII columns. Do not mask the join key; mask the output. ANALYST gets SELECT on the view and loses direct access to CLAIMS.MEMBER_ID.
  • If the key must be exposed: join on a deterministic surrogate (a keyed hash of MEMBER_ID computed identically on both sides) so the join works while the raw identifier stays masked.
  • If role inheritance is the real intent: change the policy condition to IS_ROLE_IN_SESSION('CLINICAL_READ'), which accounts for the hierarchy. This is a meaningful broadening of access - every role inheriting CLINICAL_READ now unmasks, including roles nobody thought about.

Governance step: policy changes are global and retroactive. Before altering, enumerate consumers with POLICY_REFERENCES, test under each affected role, and record the change; a masking policy edit is a data-access change request, not a SQL refactor.