COF-C03 Question 181
Single answerColumn-level securityA healthcare company stores patient data in a Snowflake table named PATIENTS. The SSN column must be fully visible only to users with the ANALYST_PII role, while all other users who can query the table should see masked values. The security team wants a solution that is enforced automatically whenever the column is queried, without requiring changes to existing SQL statements. Which approach should the data engineer implement?
- A
Create a masking policy on the SSN column that uses CURRENT_ROLE() in the policy body to return the real value for ANALYST_PII 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 to filter rows unless the querying role is ANALYST_PII, and attach the policy to the SSN column.
- C
Grant SELECT on the SSN column only to ANALYST_PII and deny SELECT on the PATIENTS table to all other roles so they cannot see the sensitive values.
- D
Create a secure view over PATIENTS that uses a CASE expression to mask SSN for unauthorized users, and replace all references to the base table with the secure view.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy, which is Snowflake's native mechanism for column-level security through dynamic data masking. Masking policies are attached directly to columns and are evaluated automatically at query time, allowing different users or roles to see different representations of the same value. This is the most direct and maintainable way to protect sensitive fields such as SSN while preserving access to the rest of the row. Row access policies are for row-level filtering, not column transformation. Secure views can help in some designs, but they are less suitable here because they require query redirection to the view. Snowflake documentation for Dynamic Data Masking describes applying masking policies to table or view columns and using context functions such as CURRENT_ROLE() to drive conditional masking behavior.
- A. Correct.
Correct. Dynamic data masking in Snowflake is designed for column-level security. A masking policy can be attached directly to the SSN column so that Snowflake automatically evaluates the policy whenever the column is queried. Using a context function such as CURRENT_ROLE() is a standard pattern to expose the original value only to an authorized role and return a masked representation otherwise. This meets the requirement for automatic enforcement without changing existing queries.
- B. Incorrect.
Incorrect. Row access policies control which rows are visible based on a policy expression; they do not mask or transform individual column values. Also, row access policies are applied at the table or view level, not attached to a single column to hide part of a row. This reflects a common confusion between row-level security and column-level security.
- C. Incorrect.
Incorrect. Snowflake does support privileges on specific columns for some operations, but this approach does not satisfy the requirement that other users should still be able to query the table and see a masked version of SSN. Revoking access to the sensitive column prevents access rather than masking the value. The requirement is selective visibility of the same column, which is what masking policies provide.
- D. Incorrect.
Incorrect. A secure view can be used to implement masking logic, and it helps protect underlying query logic and data exposure. However, it requires consumers to query the view instead of the base table, which means changing object references or existing SQL. The scenario explicitly asks for enforcement without requiring changes to existing SQL statements, so attaching a masking policy directly to the column is the better solution.