SnowPro Advanced: Security Engineer Question 124
Single answerImplement row-access policies:A healthcare company stores patient claims in PROD.CLAIMS with columns CLAIM_ID, PATIENT_ID, REGION, and AMOUNT. Analysts in different regional roles must only see rows for their assigned REGION, while users with the SECURITYADMIN role must be able to see all rows for audit investigations. The company also wants the solution to continue working if analysts query through a secure view built on top of PROD.CLAIMS. Which approach best meets these requirements?
- A
Create a row access policy on PROD.CLAIMS that uses CURRENT_ROLE() to compare the active role against a mapping table of role-to-region values, and returns TRUE for SECURITYADMIN.
- B
Create a masking policy on the REGION column so unauthorized users see NULL for REGION, which prevents them from accessing rows outside their region.
- C
Grant SELECT on PROD.CLAIMS only to regional roles and rely on the BI tool to add a WHERE REGION = '
' filter in all dashboards. - D
Create separate secure views for each region and grant each role access only to its regional view; avoid row access policies because they do not apply through views.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a row access policy on the base table and use context functions such as CURRENT_ROLE() together with a mapping table to determine whether a row should be visible. This is the Snowflake-native way to implement row-level security. A row access policy returns a BOOLEAN expression that decides whether each row is visible to the querying context. This approach is more secure and maintainable than pushing filters into BI tools or creating many region-specific views. It also satisfies the requirement that access control continues to work through secure views, because the policy is enforced on the underlying protected object. Snowflake documentation and best practices distinguish row access policies from masking policies: masking protects column values, while row access policies control row visibility.
- A. Correct.
Correct. A row access policy is designed to filter which rows are visible at query time based on context such as the current role. Using a mapping table of role-to-region assignments is a common and scalable design pattern. Including logic that allows SECURITYADMIN to return TRUE for all rows satisfies the audit requirement. Because row access policies are evaluated on the protected table, they continue to enforce filtering even when users query through a secure view built on top of that table.
- B. Incorrect.
Incorrect. Masking policies obfuscate column values; they do not remove rows from the result set. If REGION is masked to NULL, users could still see other columns such as CLAIM_ID, PATIENT_ID, and AMOUNT for rows they should not access. This is a common confusion between dynamic data masking and row-level security.
- C. Incorrect.
Incorrect. Relying on a BI tool or application layer to inject filters is not a robust security control. Users might query the table directly outside the BI tool, and Snowflake would not enforce row-level restrictions natively. Best practice is to enforce row filtering in Snowflake with a row access policy rather than depending on client-side query behavior.
- D. Incorrect.
Incorrect. Separate secure views can work operationally in some cases, but the statement that row access policies do not apply through views is false. If the base table has a row access policy, queries through views over that table still honor the policy. Maintaining one view per region is also less scalable and more error-prone than a centralized row access policy.