ADA-C01 Question 166
Single answerProtect sensitive data with security policiesA healthcare company stores member data in a Snowflake table named PATIENTS, including columns for SSN, EMAIL, and DIAGNOSIS_CODE. Analysts in the ANALYST role need to query the table for reporting, but only users with the COMPLIANCE role should see full SSN values. The company also wants analysts to see partially masked email addresses, while DIAGNOSIS_CODE should remain fully visible to all authorized users. The solution must be enforced centrally at query time without creating duplicate tables or views for each audience. Which approach should the Snowflake administrator implement?
- A
Create a masking policy for SSN that checks CURRENT_ROLE() and reveals full values only to COMPLIANCE, create a second masking policy for EMAIL that returns a partially masked value for non-privileged roles, and apply both policies directly to the relevant columns.
- B
Create a row access policy on PATIENTS so that ANALYST cannot access rows containing SSN values, and use object tags on EMAIL to automatically hide it from non-COMPLIANCE roles.
- C
Create a network policy so only COMPLIANCE users can connect from approved IP addresses to query SSN, and grant ANALYST SELECT only on a secure view that excludes the EMAIL column.
- D
Use dynamic data masking by revoking SELECT on SSN from ANALYST while granting SELECT on EMAIL, then create a session policy to obfuscate email values for non-COMPLIANCE users.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use Snowflake masking policies, which provide dynamic data masking at query time and can be applied directly to columns containing sensitive data. In this scenario, SSN should use a policy that returns the original value only when the active role is COMPLIANCE and a masked value otherwise. EMAIL should use a separate masking policy that returns a partially masked email address for non-privileged roles while optionally revealing the full value to COMPLIANCE. This approach is centralized, scalable, and does not require duplicate tables or audience-specific views.
This question tests an administrator's understanding of choosing the correct security policy type for the problem. Row access policies are for filtering rows, not masking columns. Network policies and session policies address access and session controls, not data redaction. Snowflake documentation and best practices distinguish these policy types clearly: masking policies protect sensitive column data dynamically, and tag-based masking can help scale policy assignment, but the actual enforcement still comes from masking policies. For applied administration scenarios, dynamic data masking is the most appropriate control when different roles need different representations of the same column values.
- A. Correct.
Correct. Snowflake masking policies are designed for centralized, query-time protection of sensitive column values. A masking policy can use context functions such as CURRENT_ROLE() to conditionally reveal or mask values based on the active role. Applying one masking policy to SSN and another to EMAIL directly on the columns meets the requirement to protect data without duplicating tables or maintaining separate views for each audience. DIAGNOSIS_CODE can remain unprotected if all authorized users should see it in full.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible, not how individual column values are masked. They are appropriate for filtering records, such as restricting a region's rows to certain users, but not for showing full SSN to one role and masked SSN to another on the same rows. Also, object tags by themselves do not automatically hide data from roles; tags can be used with tag-based masking, but tagging alone does not enforce masking.
- C. Incorrect.
Incorrect. Network policies restrict login access based on network location and are not a mechanism for role-based masking of column values. While secure views can be used to expose curated projections, this option does not provide the required centrally enforced masking of both SSN and partially masked EMAIL at the column level within the same base table access pattern. It also introduces separate objects rather than using native security policies for query-time protection.
- D. Incorrect.
Incorrect. Revoking SELECT on a column would block access rather than dynamically masking the value. Standard table privileges in Snowflake do not provide a native partial-value masking behavior for one role and full visibility for another. Session policies govern session behavior such as authentication-related controls and do not obfuscate column values.