ADA-C01 Question 168
Single answerImplement column-level security using data masking policiesA healthcare company stores patient data in a table named PATIENTS, including a column SSN of type STRING. The security team requires that users with the role COMPLIANCE_ANALYST see full SSN values, while all other roles should see only masked values such as XXX-XX-1234. The table is queried by BI tools and ad hoc SQL users, so the solution must be enforced centrally at the column level without changing application code. Which approach should the Snowflake administrator implement?
- A
Create a masking policy on the SSN column that checks CURRENT_ROLE() and returns the full value for COMPLIANCE_ANALYST, otherwise a partially masked STRING; then apply the policy to PATIENTS.SSN.
- B
Create a row access policy on PATIENTS that filters rows based on CURRENT_ROLE(), because row access policies can also rewrite sensitive column values for unauthorized users.
- C
Create a secure view over PATIENTS that uses CASE with CURRENT_ROLE() to mask SSN, and drop direct access to the base table; this is the only way Snowflake supports column-level masking.
- D
Encrypt the SSN column with client-side encryption and grant the COMPLIANCE_ANALYST role access to the warehouse, because warehouse access controls who can decrypt protected column values.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a masking policy attached to the SSN column. In Snowflake, masking policies provide native column-level security by evaluating expressions at query time and returning either the original or masked value based on role, user, or other context. This is the recommended approach when an organization needs centralized enforcement across BI tools, ad hoc queries, and multiple consumers without modifying application logic. A typical implementation uses CREATE MASKING POLICY with a STRING input and STRING return type, then a CASE expression using CURRENT_ROLE() or IS_ROLE_IN_SESSION() to determine whether to expose the full SSN. The policy is then bound to the column with ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY. Row access policies are for row filtering, not value masking. Secure views can help in some designs but are not Snowflake's native column-level masking feature and usually increase administrative complexity. This aligns with Snowflake documentation and best practices for Dynamic Data Masking and policy-based governance.
- A. Correct.
Correct. Dynamic data masking in Snowflake is implemented with masking policies attached to specific columns. A masking policy can evaluate context functions such as CURRENT_ROLE() and return either the original value or a masked version, as long as the return type matches the column data type. Applying the policy directly to PATIENTS.SSN enforces masking centrally for all queries against that column, which fits the requirement to avoid application changes.
- B. Incorrect.
Incorrect. Row access policies control which rows a user can see, not how column values are transformed. They are designed for row-level security predicates and cannot be used to selectively rewrite a column like SSN into a masked format for unauthorized roles.
- C. Incorrect.
Incorrect. A secure view can be used to implement custom masking logic, and it may work in some environments, but it is not the only way Snowflake supports column-level masking. Snowflake provides native masking policies specifically for centralized column-level protection. Using a secure view instead of a masking policy adds operational overhead and does not meet the question's best-fit requirement as directly as a masking policy does.
- D. Incorrect.
Incorrect. Encryption protects data at rest or in transit, but it does not implement role-based query-time masking of a column in result sets. Warehouse privileges are unrelated to decrypting specific column values in query output. This option confuses storage/security controls with Snowflake's dynamic data masking capability.