COF-C03 Question 177
Single answerData maskingA healthcare company stores patient data in a Snowflake table named PATIENTS. The SSN column must be fully visible to users with the PHI_ADMIN role, but analysts with the ANALYST role should only see a masked value. The company wants this protection enforced centrally so queries against the table do not need to be rewritten. Which approach should be used?
- A
Create a masking policy that checks CURRENT_ROLE() and attach it to the SSN column.
- B
Create a row access policy on the PATIENTS table to hide the SSN values for ANALYST users.
- C
Create a secure view that excludes the SSN column and require analysts to query the view instead of the table.
- D
Grant SELECT on the SSN column only to PHI_ADMIN and deny SELECT on the table to ANALYST.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a masking policy on the SSN column. Snowflake dynamic data masking allows administrators to define a policy using SQL expressions and apply it directly to one or more columns. The policy can evaluate session context such as CURRENT_ROLE() and return the original value for authorized roles like PHI_ADMIN while returning a redacted or tokenized value for other roles such as ANALYST. This is the Snowflake-native way to enforce column-level protection centrally and consistently.
Row access policies are for filtering rows, not masking columns. Secure views can help with data protection, especially when exposing curated datasets, but they are not the most direct answer when the requirement is centralized masking on the base column. Best practice in Snowflake governance is to use masking policies for column-level obfuscation and row access policies for row-level visibility. See Snowflake documentation on Dynamic Data Masking and Access Control for the distinction between masking policies and row access policies.
- A. Correct.
Correct. Dynamic data masking in Snowflake is implemented with a masking policy that can evaluate context such as CURRENT_ROLE() and return either the original value or a masked value. Attaching the policy directly to the SSN column enforces centralized protection regardless of how the table is queried, which matches the requirement.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible, not how individual column values are transformed or obscured. This is a common confusion because both are governance features, but row access policies do not mask a column value within otherwise visible rows.
- C. Incorrect.
Incorrect. A secure view can be used to restrict exposure, but it does not meet the stated goal of enforcing masking centrally on the table itself without changing how consumers query data. It also depends on users querying the view rather than the base table unless additional access controls are put in place.
- D. Incorrect.
Incorrect. Snowflake supports object privileges such as SELECT on tables and views, but this option does not implement dynamic masking of a specific column value for different roles. It also conflicts with the requirement that analysts should still be able to query the table and see a masked SSN rather than lose access entirely.