COF-C03 Question 180
Single answerColumn-level securityA financial services company stores customer data in a Snowflake table named CUSTOMERS, including columns CUSTOMER_ID, NAME, EMAIL, and SSN. Analysts in the ANALYST role need full access to all rows, but they must see SSN only as masked values unless they are using the COMPLIANCE role. The company wants this protection enforced directly in Snowflake so that even ad hoc queries against the base table do not expose raw SSN values to unauthorized roles. Which solution best meets this requirement?
- A
Create a masking policy on the SSN column that uses CURRENT_ROLE() to return the real SSN only for the COMPLIANCE role and a masked value for other roles, then apply the policy to CUSTOMERS.SSN.
- B
Create a row access policy on the CUSTOMERS table that filters out rows when the querying role is not COMPLIANCE, then grant SELECT on the table to ANALYST.
- C
Create a secure view over CUSTOMERS that replaces SSN with a masked expression, and require analysts to query the view instead of the base table.
- D
Encrypt the SSN column with client-side encryption before loading it into Snowflake, and grant the decryption key only to the COMPLIANCE role.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy, which is Snowflake's native feature for column-level security. Masking policies can be applied directly to sensitive columns such as SSN and can use context functions like CURRENT_ROLE() to determine what a user sees at query time. This is more appropriate than a row access policy, which filters rows rather than masking column values. A secure view can be useful for abstraction, but it does not inherently protect the base table if users can still query it directly. Snowflake documentation distinguishes dynamic data masking for column-level protection from row access policies for row-level filtering, making masking policies the correct control for this scenario.
- 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 and can evaluate context such as CURRENT_ROLE() to determine whether to reveal the original value or a masked substitute. Because the policy is bound to the column itself, it protects ad hoc access to the base table as required.
- B. Incorrect.
Incorrect. A row access policy controls which rows are visible, not how individual column values are displayed. In this scenario, analysts should still see all rows, just not the unmasked SSN values. Using a row access policy would solve a different problem and does not implement column-level masking.
- C. Incorrect.
Incorrect. A secure view can hide or transform columns, and it is a plausible approach, but it does not satisfy the requirement that protection be enforced even for direct queries against the base table. If users retain access to the underlying table, they could bypass the view. Column-level masking policies are the Snowflake-native control intended for this use case.
- D. Incorrect.
Incorrect. Encrypting data before loading may protect data outside Snowflake, but it does not provide Snowflake-native role-based column display behavior for SQL queries. It also makes common analytics workflows more difficult unless decryption occurs outside or through custom logic. This does not match the requirement for built-in column-level security enforced in Snowflake query results.