ADA-C01 Question 169
Single answerImplement column-level security using data masking policiesA financial services company stores customer Social Security numbers in PROD.CUSTOMER_DATA.SSN. The security team needs analysts with the ANALYST role to see only masked values, while users with the PII_ADMIN role must see the full value. The company also wants the control to remain effective even if analysts query through different views built on the table. Which solution should the Snowflake administrator implement?
- A
Create a masking policy that checks CURRENT_ROLE() for PII_ADMIN and returns the original SSN only for that role, then apply the masking policy directly to the SSN column on the base table.
- B
Create a secure view over CUSTOMER_DATA that uses a CASE expression to hide SSN for ANALYST, and grant analysts access only to the secure view.
- C
Encrypt the SSN column with client-side encryption and allow PII_ADMIN to decrypt values through a UDF, while ANALYST queries the encrypted column directly.
- D
Create a row access policy on CUSTOMER_DATA that filters rows unless CURRENT_ROLE() = 'PII_ADMIN', and attach it to the SSN column.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a masking policy on the SSN column of the base table. In Snowflake, masking policies are the native mechanism for column-level security and are designed to dynamically transform sensitive data at query time based on context such as the active role. Applying the policy to the base table column provides consistent protection when that column is referenced through views, which is a key requirement in this scenario. Secure views can complement security designs, but they are not a substitute for masking policies when you need centralized, reusable column-level protection. Row access policies solve a different problem by filtering rows, not masking values. This aligns with Snowflake best practices for dynamic data masking and column-level security described in Snowflake documentation on masking policies and policy-based governance.
- A. Correct.
Correct. Dynamic data masking in Snowflake is implemented with masking policies attached to specific columns. Applying the masking policy to the base table column ensures the masking logic follows that column even when queried through views, which is exactly what the scenario requires. Using CURRENT_ROLE() or similar context functions in the policy body is a standard way to expose cleartext only to authorized roles such as PII_ADMIN while masking it for ANALYST.
- B. Incorrect.
Incorrect. A secure view can help protect sensitive logic and limit exposure, but this approach does not meet the requirement as well as a masking policy on the base column. It relies on all users accessing the data only through that specific view. If other views or direct table access exist, the CASE logic in one secure view does not automatically protect the underlying column everywhere. The scenario explicitly requires the control to remain effective across different views.
- C. Incorrect.
Incorrect. Snowflake supports encryption for data at rest and in transit, but client-side encryption plus a UDF is not the appropriate native solution for role-based column-level security in this scenario. It adds unnecessary complexity and does not align with Snowflake's built-in dynamic data masking feature, which is designed specifically for this requirement.
- D. Incorrect.
Incorrect. Row access policies control which rows are visible, not how individual column values are transformed or masked. Attaching a row access policy would restrict record visibility rather than masking SSN values. This reflects a common misconception between row-level security and column-level security.