SnowPro Advanced: Security Engineer Question 122
Single answerUse projection policiesA healthcare company stores a PATIENTS table in Snowflake with columns including PATIENT_ID, FULL_NAME, DOB, SSN, DIAGNOSIS, and TREATMENT_PLAN. Analysts should be able to query the table, but the security team must prevent most roles from projecting highly sensitive columns such as SSN in ad hoc SELECT lists. The team wants a control that specifically governs whether a column can appear in the result set, while still allowing standard object privileges and future policy-based exceptions. Which approach best meets this requirement?
- A
Create and apply a projection policy to the sensitive columns, and use the policy logic to allow or deny those columns from being selected into query results based on context such as role.
- B
Create a masking policy on SSN and attach it to the table so unauthorized users cannot include the SSN column in the SELECT clause at all.
- C
Create a row access policy on the PATIENTS table so unauthorized users cannot project SSN, while authorized users can still select it.
- D
Revoke SELECT privilege on the PATIENTS table from analyst roles and expose only a secure view, because projection policies cannot be applied at the column level.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a projection policy on the sensitive columns. In Snowflake, projection policies are a column-level governance feature used to control whether a column can be projected in query results. This makes them the most appropriate mechanism when the security requirement is about preventing sensitive fields from appearing in ad hoc SELECT output. By contrast, masking policies govern how values are returned, not whether the column can be selected, and row access policies govern which rows are visible, not column projection. Secure views can be part of a broader design, but they do not replace the purpose-built control provided by projection policies. This aligns with Snowflake's policy-based security model, where each policy type addresses a distinct control plane: row filtering, value masking, join constraints, aggregation constraints, and projection restrictions.
- A. Correct.
Correct. Projection policies are designed to control whether a column may be projected into a query result. This aligns with the requirement to restrict ad hoc exposure of sensitive columns such as SSN while still leaving room for context-aware logic and exceptions. They apply at the column level and are intended for governing projection behavior rather than transforming values.
- B. Incorrect.
Incorrect. A masking policy changes or obfuscates the value returned for a column, but it does not prevent the column from being referenced in the SELECT list. Someone might choose this because masking is a common protection for PII, but the scenario explicitly requires controlling projection of the column itself, not merely redacting its contents.
- C. Incorrect.
Incorrect. A row access policy filters which rows a user can see based on policy logic. It does not control whether a specific column may appear in the output. This is a common misconception because both are governance features, but row access and projection address different dimensions of access control.
- D. Incorrect.
Incorrect. Using secure views can be a valid design pattern in some environments, but the statement that projection policies cannot be applied at the column level is false. Projection policies are specifically intended for column-level projection control. A secure view may help with abstraction, but it does not best match the requirement as directly as a projection policy.