COF-C03 Question 176
Single answerData maskingA healthcare company stores patient data in a Snowflake table named PATIENTS. The SSN column must be fully visible only to users with the PHI_ADMIN role. Analysts with the ANALYST role should be able to query the table, but they must see a masked value in the SSN column. The company wants to enforce this rule centrally so that applications and BI tools do not need to implement their own masking logic. Which solution best meets this requirement?
- A
Create a masking policy on the SSN column that uses CURRENT_ROLE() to return the real SSN for PHI_ADMIN and a masked value for all other roles, then apply the policy to PATIENTS.SSN.
- B
Create a secure view on PATIENTS that excludes the SSN column, and grant ANALYST access only to the view while granting PHI_ADMIN access to the base table.
- C
Encrypt the SSN values in the table by using a user-defined function, and allow only PHI_ADMIN to call a separate decryption function.
- D
Create a row access policy on PATIENTS so that ANALYST can see all rows but receives masked SSN values, while PHI_ADMIN sees unmasked values.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a masking policy applied to the SSN column. In Snowflake, dynamic data masking provides centralized, column-level protection that is enforced at query time and is independent of the client application. This is ideal when different roles need different views of the same sensitive field. A common implementation uses a CASE expression in the masking policy with context functions such as CURRENT_ROLE() to determine whether to show the original value or a masked representation. Secure views can help with data sharing and controlled exposure, but they are not the primary feature for dynamic column masking and would require separate objects or logic. Row access policies solve a different problem: row-level filtering. Snowflake documentation and best practices distinguish these governance controls clearly: masking policies for sensitive column values and row access policies for row visibility.
- A. Correct.
Correct. Dynamic data masking in Snowflake is designed for exactly this use case: centrally enforcing column-level masking logic regardless of the querying application. A masking policy can evaluate context such as CURRENT_ROLE() and return either the original value or a masked substitute. Applying the policy directly to PATIENTS.SSN ensures analysts can still query the table while seeing masked values, and PHI_ADMIN can see the full SSN.
- B. Incorrect.
Incorrect. A secure view can restrict exposure, but this option excludes the SSN column entirely rather than masking it. The requirement states that analysts should still be able to query the table and see a masked value in the SSN column. Also, using views for each access pattern is less centralized and less flexible than a masking policy for column-level protection.
- C. Incorrect.
Incorrect. This approach shifts enforcement into custom encryption/decryption logic rather than using Snowflake's native centralized masking controls. It also adds operational complexity and does not align as well with the requirement that BI tools and applications should not implement their own logic. Snowflake data masking is the intended feature for dynamic display of masked versus unmasked values.
- D. Incorrect.
Incorrect. Row access policies control which rows a user can see, not how individual column values are masked. They are used for row-level security, such as filtering records by region or department. They do not provide column-level masking behavior for SSN values.