ARA-C01 Question 58
Single answerRow-level securityA global retailer stores all sales transactions in a single Snowflake table named SALES_FACT. Each row contains a REGION_CODE column. Analysts in different regional roles must only see rows for their own region, while users with the CENTRAL_AUDIT role must see all rows across all regions. The company also wants the solution to continue working when queries are issued through BI tools, shared worksheets, or views built on top of SALES_FACT. Which approach should the architect implement to meet these requirements with the least ongoing maintenance?
- A
Create a row access policy on SALES_FACT that checks CURRENT_ROLE() against a role-to-region mapping table, and returns TRUE for CENTRAL_AUDIT.
- B
Create secure views for each region that filter REGION_CODE and grant each regional role access only to its own view, while granting CENTRAL_AUDIT access to the base table.
- C
Use a masking policy on REGION_CODE so unauthorized users see NULL for regions they should not access, while CENTRAL_AUDIT sees all values.
- D
Grant regional roles access to SALES_FACT and rely on BI tool filters to restrict REGION_CODE based on the logged-in user's role.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a row access policy on the table. In Snowflake, row access policies are designed specifically for row-level security and are evaluated at query time to determine which rows a user can see. This approach is more scalable and maintainable than creating many region-specific views, especially when role assignments evolve. It also satisfies the requirement that controls work consistently through BI tools and views, because the policy is applied to the protected object itself. A common best practice is to externalize entitlement logic in a mapping table and reference context functions such as CURRENT_ROLE() or related functions in the policy body. CENTRAL_AUDIT can be handled as an exception in the policy logic to allow unrestricted access. Snowflake documentation distinguishes row access policies from masking policies: masking policies protect column values, while row access policies filter returned rows. For architect-level design, native policy-based governance is generally preferred over application-side filtering because it provides centralized, auditable enforcement.
- A. Correct.
Correct. A row access policy is Snowflake's native mechanism for row-level security. Attaching the policy directly to SALES_FACT ensures filtering is enforced regardless of whether users query the table directly, through views, or from BI tools. Using a mapping table allows centralized administration as role-to-region assignments change, and explicitly allowing CENTRAL_AUDIT to return TRUE supports full access without duplicating logic.
- B. Incorrect.
Incorrect. Secure views can implement row filtering, but creating and maintaining a separate view per region increases operational overhead and does not scale well as regions or role assignments change. It also fragments the access model. While secure views can help protect underlying logic, Snowflake row access policies are the more direct and maintainable solution for consistent row-level enforcement on a shared base table.
- C. Incorrect.
Incorrect. Masking policies protect the visibility of specific column values, not which rows are returned. If a user can still query the row, masking REGION_CODE does not prevent exposure of the rest of the transaction data. This is a common misconception: dynamic data masking is for column-level protection, whereas row access policies are for row-level filtering.
- D. Incorrect.
Incorrect. BI tool filters are not a security boundary and can be bypassed by querying the table through another client, worksheet, or downstream object. The requirement explicitly calls for enforcement across access paths, so security must be implemented within Snowflake rather than delegated to the reporting layer.