SnowPro Advanced: Security Engineer Question 125
Single answerImplement row-access policies:A healthcare company stores patient claims in PROD.CLAIMS.CLAIM_FACT. Analysts from different regional teams use the same BI dashboards, but each analyst must see only rows for the region assigned to their active role. The security engineer wants a solution that is centrally managed, continues to work when queries are issued through BI tools, and does not require rewriting every dashboard query. Which approach best meets these requirements?
- A
Create a row access policy on CLAIM_FACT that evaluates CURRENT_ROLE() against a role-to-region mapping table and returns TRUE only for authorized rows.
- B
Create a secure view over CLAIM_FACT and require BI developers to add a WHERE clause filtering REGION based on a session variable set by each analyst.
- C
Enable object tagging on CLAIM_FACT and assign a REGION tag to each row so Snowflake can automatically suppress rows that do not match the user's role.
- D
Grant SELECT on CLAIM_FACT only to regional roles and rely on warehouse-level isolation so each role can access only its own region's rows.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a row access policy directly on the protected table (or on a view, if that is the design) so Snowflake enforces row-level filtering transparently for all queries, including those generated by BI tools. This is the intended control for row-level security in Snowflake and supports centralized management. A common implementation pattern is for the policy body to compare a row attribute such as REGION with authorization data from a mapping table, using context functions like CURRENT_ROLE() or other session/role-evaluation functions supported by Snowflake. In contrast, secure views plus session variables depend on application behavior and query design, tags do not enforce row filtering, and object privileges alone cannot restrict access to subsets of rows. This aligns with Snowflake documentation and best practices for implementing row access policies and centralized fine-grained access control.
- A. Correct.
Correct. A row access policy is designed for row-level security and can be attached directly to a table or view. Using CURRENT_ROLE() (or another context function such as IS_ROLE_IN_SESSION where appropriate) with a mapping table allows centralized enforcement based on the active role. Because the policy is evaluated by Snowflake at query time, BI tools do not need custom query logic, which matches the requirement for centralized, query-independent enforcement.
- B. Incorrect.
Incorrect. A secure view can help encapsulate logic, but this option depends on BI developers or users setting and using a session variable correctly in every context. That is operationally fragile and does not satisfy the requirement to avoid rewriting dashboard queries. It also shifts enforcement from a centrally attached row access policy to query/view design patterns that are easier to bypass or misconfigure.
- C. Incorrect.
Incorrect. Tags in Snowflake are for classification, governance, and metadata-driven automation use cases, not for automatic row-level filtering of query results. Assigning a tag does not cause Snowflake to suppress table rows based on role. This distractor reflects a common confusion between governance metadata and enforcement controls.
- D. Incorrect.
Incorrect. Granting SELECT controls object-level access, not row-level access. If a role has SELECT on the table, it can query all rows unless additional controls such as a row access policy are applied. Warehouse isolation affects compute resource usage and workload separation, not data filtering within a table.