SnowPro Advanced: Security Engineer Question 112
Single answerCreate masking policies with SQL expressions and Snowflake functionsA healthcare analytics team stores patient contact data in a Snowflake table named PATIENTS. The EMAIL column must remain fully visible to users with the ANALYST_PHI role, while all other roles should see a partially masked value that preserves the email domain for troubleshooting, such as j***@example.com. Security administrators want the masking logic implemented directly in Snowflake using a masking policy, without creating separate views for each role. Which SQL definition best meets this requirement?
- A
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() = 'ANALYST_PHI' THEN val ELSE REGEXP_REPLACE(val, '^(.).(@.)$', '\1***\2') END;
- B
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING -> CASE WHEN IS_ROLE_IN_SESSION('ANALYST_PHI') THEN val ELSE REGEXP_REPLACE(val, '^(.).(@.)$', '\1***\2') END;
- C
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_AVAILABLE_ROLES() = 'ANALYST_PHI' THEN val ELSE CONCAT('***', SPLIT_PART(val, '@', 2)) END;
- D
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING -> CASE WHEN INVOKER_ROLE() = 'ANALYST_PHI' THEN val ELSE SHA2(val) END;
Show answer and explanation
Correct answer: B
Explanation
The best answer is the masking policy that uses IS_ROLE_IN_SESSION to determine whether the authorized role is active for the query context. In Snowflake, masking policies are defined with SQL expressions and can incorporate context functions such as CURRENT_ROLE and IS_ROLE_IN_SESSION. For security-sensitive implementations, IS_ROLE_IN_SESSION is generally preferred when authorization should consider the active role set rather than only the single primary role. The masking logic should also match the business requirement precisely: preserve enough of the original value for operational use while still protecting sensitive content. Here, REGEXP_REPLACE is a practical built-in function for preserving the first character and domain of an email address. This aligns with Snowflake best practices for dynamic data masking: centralize policy logic in masking policies, use context-aware functions for role evaluation, and avoid brittle approaches like duplicating logic across views.
- A. Incorrect.
Incorrect. CURRENT_ROLE() returns the primary role for the current session, not whether a role is active anywhere in the session role hierarchy. In real deployments, users often operate with role inheritance or secondary roles enabled, so checking only CURRENT_ROLE() can incorrectly mask data for authorized users. The masking expression itself is valid SQL, but the role-checking approach is too narrow for the stated requirement.
- B. Correct.
Correct. IS_ROLE_IN_SESSION('ANALYST_PHI') is the appropriate Snowflake function when a masking policy should allow access if the specified role is active in the session, including through role hierarchy. The REGEXP_REPLACE expression preserves the first character and domain while masking the rest of the local part, producing results like j***@example.com. This matches the requirement to implement dynamic masking directly in Snowflake with SQL expressions and built-in functions.
- C. Incorrect.
Incorrect. CURRENT_AVAILABLE_ROLES() does not return a scalar string suitable for direct equality comparison to a role name in this way. Also, CONCAT('***', SPLIT_PART(val, '@', 2)) would produce an invalid email-like output such as ***example.com, missing the @ symbol and the first visible character requirement. This option combines both a role-evaluation error and an incorrect masking pattern.
- D. Incorrect.
Incorrect. INVOKER_ROLE() is not the correct function to use for this masking-policy requirement, and hashing the entire value with SHA2(val) does not preserve the email domain for troubleshooting. Hashing is useful for irreversible obfuscation in some scenarios, but it does not satisfy the stated business need for a partially readable masked email.