SnowPro Advanced: Security Engineer Question 109
Single answerImplement column-level security:A healthcare company stores patient records in a Snowflake table named PATIENTS with columns PATIENT_ID, FULL_NAME, DIAGNOSIS, and SSN. Analysts in the ANALYST role must be able to query the table, but they should see SSN values only for rows they are authorized to access through existing row access policies. For all authorized rows, only users with the PII_ACCESS role should see the full SSN; all other users should see a masked value. The security engineer wants a solution that is centralized, reusable across tables, and enforced directly in Snowflake. Which approach best meets these requirements?
- A
Create a masking policy on the SSN column that checks the current role and returns the full SSN only when the active role is PII_ACCESS; otherwise return a masked value. Attach the policy to the SSN column and continue using the existing row access policy for row filtering.
- B
Grant SELECT on the SSN column only to the PII_ACCESS role and deny column access to ANALYST, while relying on the existing row access policy to expose masked SSN values for authorized rows.
- C
Create a secure view over PATIENTS that uses CASE logic to mask SSN for non-PII_ACCESS users, and remove the row access policy because secure views already enforce row-level filtering.
- D
Encrypt the SSN column with client-side encryption so only users with the PII_ACCESS role can decrypt it after querying; this replaces the need for Snowflake column-level security.
- E
Create a tag on the SSN column and assign the PII_ACCESS role to the tag so Snowflake automatically masks SSN for all other roles without a masking policy.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy on the SSN column and keep the existing row access policy in place. In Snowflake, row access policies and masking policies address different layers of data protection and are commonly used together: row access policies determine which rows a user can see, while masking policies determine how sensitive column values are displayed. For column-level security requirements like showing full SSN only to a privileged role and masked values to others, dynamic data masking is the intended feature.
This approach is also centralized and reusable. A masking policy can be attached directly to columns and reused across tables with similar sensitivity requirements. Snowflake documentation on dynamic data masking and row access policies describes these features as complementary controls. If the organization wants to scale further, tag-based masking can help apply masking policies based on tags, but tags do not replace masking policies themselves.
Best practice is to enforce sensitive-data controls natively in Snowflake rather than relying solely on downstream BI tools or ad hoc secure views, unless there is a specific architectural reason to do so.
- A. Correct.
Correct. Dynamic data masking is Snowflake's native column-level security mechanism. A masking policy can evaluate context such as the current role and return either the original SSN or a masked value. Applying the masking policy directly to the SSN column centralizes enforcement in Snowflake and makes the policy reusable across multiple columns and tables. This also works alongside an existing row access policy: row access policies control which rows are visible, while masking policies control what is shown in protected columns for the rows that remain visible.
- B. Incorrect.
Incorrect. Snowflake does not implement column-level masking by simply granting or withholding SELECT on a single column in the way described here to produce masked results. If a user lacks access to a column, they cannot query it; they do not automatically receive masked output. The requirement is that analysts can query the table and see masked SSN values, which is exactly the use case for a masking policy rather than privilege-based denial.
- C. Incorrect.
Incorrect. A secure view can implement masking logic, but it does not replace row access policies, nor does it provide the most centralized and reusable solution requested. Row access policies are designed specifically for row-level filtering and can coexist with masking policies. Replacing direct table access with custom secure views can increase maintenance overhead and reduce reuse compared with attaching a masking policy to sensitive columns.
- D. Incorrect.
Incorrect. Encryption protects data at rest and in transit, but it is not the Snowflake-native mechanism for context-aware column-level presentation of cleartext versus masked values based on role. Client-side encryption would also shift complexity to application key management and would not satisfy the requirement for centralized, reusable enforcement directly in Snowflake query results.
- E. Incorrect.
Incorrect. Tags by themselves do not automatically enforce masking. In Snowflake, tags can be associated with masking policies through tag-based masking, but a masking policy still must be defined and associated appropriately. Simply assigning a role to a tag does not create automatic column-level security behavior.