ARA-C01 Question 46
Single answerData SecurityA healthcare company stores PHI in a Snowflake table named PATIENT_EVENTS. Analysts in the ANALYST role must be able to query non-sensitive columns for all rows, but only members of the PRIVACY_OFFICER role should see the PATIENT_SSN and DIAGNOSIS columns in clear text. In addition, analysts should only see rows for their own business unit, which is stored in the BUSINESS_UNIT column and mapped to the current user in a reference table. The company wants a solution that centralizes enforcement in Snowflake and minimizes application changes. Which approach best meets these requirements?
- A
Create a secure view on PATIENT_EVENTS that filters rows by BUSINESS_UNIT, and grant analysts access only to the view. Keep sensitive columns in the base table and revoke direct access to those columns.
- B
Apply a row access policy to filter rows by BUSINESS_UNIT and a masking policy on PATIENT_SSN and DIAGNOSIS based on the current role, then attach both policies to PATIENT_EVENTS.
- C
Use Dynamic Data Masking for PATIENT_SSN and DIAGNOSIS, and enforce row filtering by requiring each BI tool to append a WHERE clause using the user's business unit.
- D
Encrypt PATIENT_SSN and DIAGNOSIS with client-side encryption before loading the table, and create a materialized view for each business unit for analyst access.
Show answer and explanation
Correct answer: B
Explanation
The best solution is to use Snowflake's policy-based security controls directly on the table: a row access policy for row-level authorization and masking policies for column-level protection. This aligns with Snowflake best practices for centralized governance, especially for sensitive data such as PHI. Row access policies let architects enforce per-row visibility based on session context and lookup tables, while masking policies allow conditional redaction of sensitive columns based on roles or other attributes. This design is more maintainable and secure than embedding logic only in views or relying on consuming applications to enforce filters. Relevant Snowflake documentation includes topics on Dynamic Data Masking, Row Access Policies, and policy-based access control patterns for sensitive data protection.
- A. Incorrect.
This is not the best answer because secure views can help centralize logic and protect underlying definitions, but Snowflake does not support revoking access at the individual column level within a table in the way this option implies. More importantly, this approach pushes both row and column protection into a custom view design, which becomes harder to scale and govern consistently across many tables. A candidate might choose this because secure views are commonly used for data protection, but row access policies and masking policies are the purpose-built Snowflake features for centralized, table-level enforcement.
- B. Correct.
This is correct. A row access policy can enforce row-level filtering on BUSINESS_UNIT using context functions such as CURRENT_USER or CURRENT_ROLE and a lookup table that maps users to allowed business units. A masking policy can be attached to PATIENT_SSN and DIAGNOSIS so that only authorized roles such as PRIVACY_OFFICER see clear text, while other roles receive masked values. This approach centralizes security in Snowflake, applies consistently regardless of the consuming tool, and minimizes application changes.
- C. Incorrect.
This is partially correct but not the best solution. Dynamic data masking is appropriate for protecting sensitive columns, but relying on each BI tool or application to add the proper WHERE clause is not centralized enforcement and is error-prone. Users could access the table through another client and bypass the intended filter if row restrictions are not enforced in Snowflake itself. The misconception is that application-layer filtering is sufficient for governance-sensitive data such as PHI.
- D. Incorrect.
This is not the best answer. Client-side encryption may protect data before it reaches Snowflake, but it does not solve the requirement for PRIVACY_OFFICER users to see values in clear text while analysts see masked values unless key management and decryption logic are introduced into applications, which increases complexity and application changes. Creating a separate materialized view for each business unit is operationally cumbersome, does not scale well, and is not the intended Snowflake mechanism for row-level security.