SnowPro Advanced: Security Engineer Question 123
Single answerUse projection policiesA healthcare analytics team stores patient data in a Snowflake table named PATIENTS. The SSN column must remain available for internal compliance workflows, but analysts should not be able to include SSN in ad hoc query results unless they use an approved secure view. The security engineer wants to prevent direct projection of SSN from the base table while minimizing changes to existing role-based access patterns. Which approach best meets this requirement?
- A
Create a projection policy that blocks the SSN column from being selected directly, and apply it to PATIENTS.SSN while allowing access through an approved secure view.
- B
Create a masking policy on SSN that returns NULL for analyst roles, because masking policies prevent the column from being projected in SELECT statements.
- C
Create a row access policy on PATIENTS so analysts cannot retrieve rows containing SSN values unless they query through a secure view.
- D
Revoke SELECT on the PATIENTS table from analysts and grant SELECT only on a secure view, because projection policies cannot control direct selection of individual columns.
Show answer and explanation
Correct answer: A
Explanation
Projection policies are intended for controlling whether protected columns can be projected directly in query output. In this scenario, the goal is not merely to obscure SSN values, but to stop analysts from including SSN in ad hoc SELECT lists against the base table. A masking policy would only change the returned value, and a row access policy would only restrict rows. Those are different controls. Revoking base-table access and routing everyone to a secure view is a possible architectural workaround, but it is broader than necessary and conflicts with the requirement to minimize role changes. Best practice is to use the most targeted governance control for the risk being addressed: projection policies for projection restrictions, masking policies for value obfuscation, and row access policies for row filtering. This aligns with Snowflake's data governance model, where different policy types address distinct security objectives.
- A. Correct.
Correct. Projection policies are designed to control whether a column can be explicitly projected in query results. Applying a projection policy to PATIENTS.SSN lets the organization prevent users from selecting SSN directly from the base table while still supporting controlled access patterns, such as exposing the data through an approved secure view or other authorized path defined by policy logic. This directly addresses the requirement to restrict ad hoc projection without broadly redesigning role grants.
- B. Incorrect.
Incorrect. Masking policies transform the displayed value based on context, but they do not prevent the column from being selected. An analyst could still include SSN in the SELECT list and receive the masked result. That does not satisfy the stated requirement to prevent direct projection of the column itself.
- C. Incorrect.
Incorrect. Row access policies filter which rows are visible based on the executing context. They do not control whether a specific column can appear in the projection list. Using a row access policy for this problem confuses row-level visibility with column-level projection control.
- D. Incorrect.
Incorrect. Restricting access to the base table and forcing all usage through a secure view can work operationally, but it does not best meet the requirement to minimize changes to existing role-based access patterns. Also, the statement that projection policies cannot control direct selection of individual columns is false; that is precisely their purpose.