ARA-C01 Question 51
Single answerColumn-level securityA healthcare company stores patient data in a Snowflake table named PATIENTS with columns PATIENT_ID, NAME, DOB, EMAIL, DIAGNOSIS, and INSURANCE_NUMBER. Business analysts should be able to query the table, but only users with the PHI_ACCESS role should see unmasked values for EMAIL and INSURANCE_NUMBER. The company wants to minimize application changes and enforce the policy centrally across all queries. Which solution best meets these requirements?
- A
Create a dynamic data masking policy for EMAIL and INSURANCE_NUMBER that checks CURRENT_ROLE() for PHI_ACCESS, and apply the policy directly to those columns in the PATIENTS table.
- B
Create a row access policy on PATIENTS that filters rows unless the current role is PHI_ACCESS, and apply it to the EMAIL and INSURANCE_NUMBER columns.
- C
Create a secure view over PATIENTS that excludes EMAIL and INSURANCE_NUMBER for non-PHI_ACCESS users, and require all analysts to use the view instead of the base table.
- D
Grant SELECT on EMAIL and INSURANCE_NUMBER only to PHI_ACCESS and grant SELECT on the remaining columns to analyst roles.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use dynamic data masking policies on the sensitive columns. In Snowflake, masking policies are the primary feature for column-level security and allow architects to define logic that returns masked or original values based on context such as CURRENT_ROLE(), CURRENT_AVAILABLE_ROLES(), or entitlement tables via policy conditions. This approach is centrally enforced whenever the protected columns are queried, which aligns with the requirement to minimize application changes. Row access policies are for row-level filtering, not column masking. Secure views can help with data abstraction and protection, but they are not the most direct solution for consistent column-level enforcement on an existing table. Snowflake documentation on Dynamic Data Masking and Access Control describes masking policies as the recommended mechanism for protecting sensitive column data such as PII or PHI.
- A. Correct.
Correct. Dynamic data masking is Snowflake's native mechanism for column-level security when the goal is to return masked or unmasked values based on role or other context. Applying masking policies directly to EMAIL and INSURANCE_NUMBER centralizes enforcement at the column level and does not require query or application changes beyond existing access to the table. Using CURRENT_ROLE() or a role-checking function in the policy body is a standard pattern for role-based masking.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible, not how individual column values are masked. Applying a row access policy would potentially hide entire rows rather than selectively masking sensitive column values. This is a common confusion between row-level security and column-level security in Snowflake.
- C. Incorrect.
Incorrect. A secure view can be used to present different projections of data, but this approach does not best meet the requirement to minimize application changes and centrally enforce protection across all access paths to the table. It also depends on users consistently querying the view instead of the base table, unless additional access restrictions are implemented. Masking policies are the more direct and scalable control for column-level security.
- D. Incorrect.
Incorrect. Snowflake grants SELECT at the object level, not on individual columns in the way described here for standard privilege management. Column-level security for sensitive values is implemented using masking policies rather than per-column SELECT grants. This option reflects a common assumption from other database platforms.