SnowPro Advanced: Security Engineer Question 113
Single answerCreate masking policies with SQL expressions and Snowflake functionsA healthcare analytics team stores patient contact data in a Snowflake table named PATIENTS, including a VARCHAR column EMAIL. Security requirements state that users with the CUSTOM_PII_VIEWER role should see the full email address, while all other roles should see only the domain and a masked local part, for example j***@example.com. The solution must be implemented as a dynamic data masking policy using SQL expressions and Snowflake functions so that access is evaluated at query time based on the active role. Which masking policy definition best meets the requirement?
- A
CREATE MASKING POLICY email_mask AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN CURRENT_ROLE() = 'CUSTOM_PII_VIEWER' THEN val ELSE REGEXP_REPLACE(val, '^[^@]', '***') END;
- B
CREATE MASKING POLICY email_mask AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN IS_ROLE_IN_SESSION('CUSTOM_PII_VIEWER') THEN val ELSE REGEXP_REPLACE(val, '^(.)([^@])(@.)$', '\1***\3') END;
- C
CREATE MASKING POLICY email_mask AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN CURRENT_AVAILABLE_ROLES() = 'CUSTOM_PII_VIEWER' THEN val ELSE SPLIT_PART(val, '@', 1) || '@***' END;
- D
CREATE MASKING POLICY email_mask AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN INVOKER_ROLE() = 'CUSTOM_PII_VIEWER' THEN val ELSE HASH(val) END;
Show answer and explanation
Correct answer: B
Explanation
The best answer is the policy that uses IS_ROLE_IN_SESSION together with a SQL expression that formats the masked email correctly. In Snowflake, masking policies are evaluated dynamically at query time, and built-in context functions can be used to determine whether a given role should see cleartext data. For role-based masking, IS_ROLE_IN_SESSION is a strong choice when the authorized role may be active as either a primary or secondary role. By contrast, CURRENT_ROLE only reflects the current primary role and can lead to unexpectedly masked results when users rely on secondary roles.
The SQL expression must also match the required masking output. A regular expression such as '^(.)([^@])(@.)$' captures the first character of the local part, the rest of the local part, and the domain separately; replacing with '\1***\3' preserves the first character and domain while masking the remainder of the local part. This is a practical pattern for implementing partial email masking in Snowflake.
This aligns with Snowflake best practices for dynamic data masking: define a masking policy with conditional logic using context functions such as CURRENT_ROLE or IS_ROLE_IN_SESSION as appropriate, then attach the policy to the sensitive column. Candidates should recognize both the policy-evaluation context and the correctness of the SQL masking expression.
- A. Incorrect.
Incorrect. CURRENT_ROLE() checks only the current primary role, so it can fail to allow access when CUSTOM_PII_VIEWER is an activated secondary role in the session. In addition, the regular expression '^[^@]' replaces only the first character before the @, producing output like '***ohn@example.com' rather than the required format of keeping the first character and masking the rest of the local part.
- B. Correct.
Correct. IS_ROLE_IN_SESSION('CUSTOM_PII_VIEWER') is appropriate in a masking policy when access should depend on whether a role is active in the current session, including secondary roles. The REGEXP_REPLACE pattern '^(.)([^@])(@.)$' preserves the first character, masks the remaining local part with '', and preserves the domain, producing a result such as 'j@example.com'. This satisfies both the role-based access requirement and the output format requirement.
- C. Incorrect.
Incorrect. CURRENT_AVAILABLE_ROLES() does not return a single string value that can be directly compared with = 'CUSTOM_PII_VIEWER'; it returns the roles available to the user, not the roles currently active for policy evaluation in this way. The masking expression is also wrong for the stated requirement because it reveals the full local part and masks the domain instead of preserving the domain and masking most of the local part.
- D. Incorrect.
Incorrect. INVOKER_ROLE() is not the right choice for this role-checking requirement in a masking policy, and HASH(val) irreversibly transforms the entire email address rather than preserving the domain and first character of the local part. This option does not meet the business requirement for partially masked display.