SnowPro Advanced: Security Engineer Question 111
Single answerDesign and apply Dynamic Data Masking policiesA healthcare company stores patient records in a Snowflake table named PATIENTS. The SSN column must be fully visible only to users with the PHI_FULL_ACCESS role. Members of the BILLING role should see only the last 4 digits, and all other roles should see a fixed masked value. The security team wants this enforced centrally so applications do not need to implement masking logic. Which approach best meets the requirement?
- A
Create a dynamic data masking policy on the SSN column that uses CURRENT_ROLE() in a CASE expression to return the full SSN for PHI_FULL_ACCESS, a partially masked value for BILLING, and a fully masked value for all other roles.
- B
Create a row access policy on the PATIENTS table to hide rows unless the user has PHI_FULL_ACCESS or BILLING, and grant SELECT on the SSN column to all other users.
- C
Create a secure view over PATIENTS with CASE logic to mask SSN, and require all applications to query the view instead of the base table.
- D
Encrypt the SSN column with a customer-managed key and grant the key only to PHI_FULL_ACCESS; BILLING users will automatically see only the last 4 digits.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a dynamic data masking policy on the SSN column. In Snowflake, masking policies provide centralized, query-time, column-level protection and can return different values based on context such as role, database role, or other attributes evaluated in SQL expressions. This is the appropriate control when different user groups need different views of the same sensitive field.
A typical design would define a masking policy with logic similar to: full value for PHI_FULL_ACCESS, a transformed value such as CONCAT('XXX-XX-', RIGHT(ssn, 4)) for BILLING, and a static masked token for everyone else. The policy is then applied directly to the SSN column using ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY or during table creation.
This is preferable to secure views when the requirement is centralized enforcement because masking policies stay attached to the data object and reduce the chance of bypass through direct table access. It is also distinct from row access policies, which govern row visibility rather than masking column values. Snowflake documentation on Dynamic Data Masking and masking policies describes this pattern as a best practice for protecting sensitive fields such as PII and PHI while preserving business usability for authorized roles.
- A. Correct.
Correct. Dynamic data masking policies are designed for centralized, column-level protection based on the querying context. A masking policy can be defined for the SSN data type and use conditional logic such as CASE with CURRENT_ROLE() to return different representations depending on role membership or active role context. This directly satisfies the requirement to centrally enforce full, partial, and fully masked outputs without changing application logic.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible, not how a column value is transformed for different users. Even if row access policies restrict access to records, they do not implement partial masking such as showing only the last 4 digits of SSN. This option confuses row-level filtering with column-level data protection.
- C. Incorrect.
Incorrect. A secure view can implement masking logic, but it does not best meet the requirement because the team wants centralized enforcement independent of application behavior. Requiring all applications to use a view introduces operational risk: users or tools with access to the base table could bypass the view unless access is tightly redesigned. Dynamic masking policies attach directly to the column and are the native Snowflake feature for this use case.
- D. Incorrect.
Incorrect. Encryption protects data at rest and in transit, but it does not provide role-based partial reveal semantics such as exposing only the last 4 digits to one role and the full value to another at query time. Snowflake's masking policies, not encryption key grants, control dynamic presentation of column values to different roles.