ARA-C01 Question 57
Single answerRow-level securityA global retailer stores all regional sales in a single table, SALES_FACT, with columns including REGION_ID, ORDER_ID, and AMOUNT. Analysts in different regional roles must see only rows for their assigned regions, while a small finance team role must see all rows across all regions. The company wants a solution that is centrally managed in Snowflake and enforced regardless of which BI tool or SQL client is used. Which approach best meets these requirements?
- A
Create a row access policy on SALES_FACT that evaluates CURRENT_ROLE() or IS_ROLE_IN_SESSION() against a region-to-role mapping table, and include logic to allow the finance role to see all rows.
- B
Create separate secure views per region and grant each regional role access only to its view, while granting the finance role access to the base table.
- C
Use masking policies on REGION_ID so unauthorized users see NULL for the region value, which prevents them from seeing rows outside their region.
- D
Grant regional roles access to SALES_FACT and rely on BI tool filters to restrict visible rows by REGION_ID, while granting the finance role unrestricted dashboard access.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a row access policy on the shared SALES_FACT table. Row access policies are designed specifically for row-level security in Snowflake and are evaluated in Snowflake at query time, making them independent of the consuming application. A common enterprise pattern is to map roles to allowed business entities, such as regions, using a reference table and policy logic based on CURRENT_ROLE() or IS_ROLE_IN_SESSION(). This supports centralized administration and simplifies onboarding new regions or roles. A privileged finance role can be explicitly allowed to see all rows in the policy logic. By contrast, masking policies do not filter rows, and BI-tool-only filtering is not a secure architecture. Secure views can be part of a solution, but for this requirement they are generally less maintainable than a single row access policy applied directly to the table. This aligns with Snowflake best practices for governance and fine-grained access control using row access policies.
- A. Correct.
Correct. A row access policy is Snowflake's native mechanism for row-level security. Attaching the policy to SALES_FACT ensures filtering is enforced centrally in Snowflake for all queries, regardless of client or BI tool. Using CURRENT_ROLE() or IS_ROLE_IN_SESSION() with a mapping table is a practical design for managing region-to-role relationships at scale. Adding explicit logic for the finance role to bypass regional filtering satisfies the requirement for full-table visibility for a limited set of users.
- B. Incorrect.
Incorrect. Secure views can implement row filtering, but creating and managing separate views per region is operationally heavier and less centralized than a single row access policy on the base table. It also scales poorly as the number of regions changes. While this can work technically, it does not best meet the stated requirement for centrally managed enforcement on the shared table itself.
- C. Incorrect.
Incorrect. Masking policies protect column values, not row visibility. Masking REGION_ID may hide the value in that column, but it does not prevent users from querying or aggregating rows they should not access. This is a common misconception: dynamic data masking and row access policies solve different security problems.
- D. Incorrect.
Incorrect. BI tool filters are not a secure enforcement mechanism for row-level security because they depend on client-side configuration and can be bypassed by direct SQL access or misconfigured reports. The requirement explicitly calls for enforcement regardless of BI tool or SQL client, which requires Snowflake-side controls such as row access policies.