SnowPro Advanced: Security Engineer Question 128
Single answerUnderstand policy precedence and interactionsA Snowflake security engineer is troubleshooting why a contractor can still query sensitive columns in PROD_DB.HR.EMPLOYEE after several security controls were introduced. The environment has the following configuration:
- A masking policy named MP_SSN is attached directly to EMPLOYEE.SSN and returns NULL unless CURRENT_ROLE() is in ('HR_FULL', 'SECURITYADMIN').
- A row access policy named RAP_EMP_REGION is attached to EMPLOYEE and filters rows based on the user's region.
- A tag named SENSITIVE is associated with a masking policy named MP_TAG_REDACT, and the SENSITIVE tag is set on EMPLOYEE.SSN.
- The contractor is using a role hierarchy where ANALYST_CONTRACTOR is granted to HR_FULL.
The engineer observes that when the contractor uses the HR_FULL role, SSN values are visible for rows allowed by the row access policy. Which explanation best describes Snowflake policy precedence and interaction in this scenario?
- A
The tag-based masking policy overrides the directly assigned masking policy, so MP_TAG_REDACT is exposing the column.
- B
The directly assigned masking policy on EMPLOYEE.SSN takes precedence over the tag-based masking policy, and the row access policy only limits which rows are returned, not whether the visible SSN values are masked.
- C
The row access policy is evaluated before the masking policy, so once a row is allowed by RAP_EMP_REGION, masking policies are skipped for that row.
- D
Because ANALYST_CONTRACTOR is granted to HR_FULL, CURRENT_ROLE() evaluates all roles in the hierarchy, so any user granted ANALYST_CONTRACTOR automatically satisfies the HR_FULL check in the masking policy.
Show answer and explanation
Correct answer: B
Explanation
This scenario tests two important policy interaction rules in Snowflake. First, when both a direct masking policy and a tag-based masking policy could apply to the same column, the directly assigned masking policy takes precedence over the tag-based masking policy. Second, row access policies and masking policies are complementary controls: row access policies determine which rows are visible, while masking policies determine how protected column values are presented in those visible rows. Therefore, it is expected that the contractor can see SSN values for allowed rows when using the HR_FULL role, because the direct masking policy explicitly permits visibility for CURRENT_ROLE() in ('HR_FULL', 'SECURITYADMIN'). A key best practice is to design masking policy conditions carefully and avoid relying on role hierarchy assumptions when using context functions such as CURRENT_ROLE(). This aligns with Snowflake documentation on masking policy assignment precedence, tag-based masking behavior, and the independent but combined use of row access and masking policies.
- A. Incorrect.
Incorrect. In Snowflake, a masking policy directly assigned to a column has higher precedence than a masking policy applied indirectly through a tag. A common misconception is that tag-based governance is more global and therefore overrides direct assignment, but Snowflake uses the direct column-level assignment first when both exist.
- B. Correct.
Correct. Snowflake evaluates the directly attached masking policy on EMPLOYEE.SSN before any tag-based masking policy for that same column because direct masking policy assignment has higher precedence. The row access policy and masking policy work together rather than replacing each other: the row access policy determines which rows the user can see, and the masking policy determines what value is shown in the protected column for those visible rows.
- C. Incorrect.
Incorrect. Although row access and masking policies both affect query results, row access does not suppress masking evaluation. If a row is returned, masking still applies to protected columns in that row. This option reflects the misunderstanding that row-level filtering and column-level protection are mutually exclusive stages where one disables the other.
- D. Incorrect.
Incorrect. CURRENT_ROLE() returns the currently active primary role, not every role in the user's role hierarchy. If the user activates HR_FULL as the current role, the masking policy condition can evaluate to true and expose SSN. Merely having another granted role in the hierarchy does not cause CURRENT_ROLE() to match all inherited roles.