SnowPro Advanced: Security Engineer Question 108
Single answerImplement column-level security:A healthcare company stores patient data in a Snowflake table named PATIENTS with columns PATIENT_ID, NAME, EMAIL, SSN, DIAGNOSIS, and LAST_VISIT_DATE. Analysts in the ANALYST role must be able to query the table, but only users with the PRIVACY_ADMIN role should see unmasked SSN values. The company wants the solution to be enforced directly in Snowflake, require minimal application changes, and continue to work regardless of which BI tool is used. Which approach should the security engineer implement?
- A
Create a masking policy on the SSN column that returns the real value only when CURRENT_ROLE() = 'PRIVACY_ADMIN', and a masked value for all other roles; then apply the policy to PATIENTS.SSN.
- B
Create a row access policy on the PATIENTS table that filters out rows when the querying role is not PRIVACY_ADMIN, and attach it to the SSN column.
- C
Grant SELECT on PATIENTS to ANALYST and revoke access to the SSN column by denying column privileges directly to the role.
- D
Create a secure view that omits the SSN column for ANALYST users, and require all BI tools and users to query only the view instead of the base table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a masking policy on the SSN column. In Snowflake, masking policies are the primary feature for column-level security and are well suited for protecting PII such as Social Security numbers. They are enforced centrally by Snowflake at query time and can use role-aware logic such as CURRENT_ROLE() or more advanced entitlement-table lookups to determine whether to reveal or mask a value. This makes them preferable when the goal is consistent protection across different consumers, including worksheets, JDBC/ODBC clients, and BI platforms, without relying on application-specific logic. By contrast, row access policies address row visibility rather than column masking, and secure views are a possible workaround but usually require stricter routing of all access through the view and more administrative control. Snowflake documentation on Dynamic Data Masking and masking policies describes this as the standard approach for implementing column-level security.
- A. Correct.
Correct. Dynamic data masking is Snowflake's native column-level security mechanism for protecting sensitive data such as SSNs. A masking policy can be applied directly to a column and can evaluate context such as CURRENT_ROLE() to determine whether to expose the original value or a masked representation. This satisfies the requirement for enforcement in Snowflake itself and works consistently across SQL clients and BI tools because the policy is evaluated by Snowflake at query time.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible, not how individual column values are displayed. They are designed for row-level security and are attached to tables or views, not used to mask a single column value like SSN. This option reflects a common confusion between row-level and column-level security features.
- C. Incorrect.
Incorrect. Snowflake does not support a generic DENY model for revoking access to a specific column after table-level SELECT has been granted in the way described here. Snowflake column-level protection for sensitive data is typically implemented with masking policies, rather than deny-style column permissions. A user with SELECT on the table can otherwise read the column unless additional protections such as masking are applied.
- D. Incorrect.
Incorrect. A secure view can be used to expose a subset of columns and is sometimes part of a security design, but it does not best meet the stated requirement of minimal application changes and universal enforcement regardless of tool usage unless access to the base table is tightly restricted and all consumers are redirected. That creates additional operational overhead and is less direct than applying a masking policy to the sensitive column itself.