ARA-C01 Question 60
Single answerRow access policiesA global retailer stores all sales transactions in a single Snowflake table, SALES_FACT, with columns including REGION_CODE, CUSTOMER_ID, and AMOUNT. Analysts in different regional roles must only see rows for their assigned region, while members of the FINANCE_ADMIN role must see all rows across all regions. The architect wants a solution that is centrally managed, reusable across tables, and based on the active role at query time. Which approach best meets these requirements?
- A
Create a row access policy that uses CURRENT_ROLE() and a mapping table of role-to-region values, returns TRUE for FINANCE_ADMIN, and attach the policy to the REGION_CODE column on SALES_FACT.
- B
Create a masking policy on REGION_CODE that replaces unauthorized region values with NULL, and grant FINANCE_ADMIN the UNMASK privilege so they can see all rows.
- C
Create secure views per region on top of SALES_FACT and grant each analyst role access only to its corresponding secure view, while FINANCE_ADMIN gets direct access to the base table.
- D
Create a network policy that allows users from each region's office IP range to query only rows where REGION_CODE matches their location, and exempt FINANCE_ADMIN from the policy.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a row access policy. In Snowflake, row access policies are intended for row-level security and can evaluate context at query time, including the active role through functions such as CURRENT_ROLE(). A common best-practice design is to maintain a mapping table that relates roles to authorized business attributes such as region, business unit, or country, and have the policy reference that mapping. This creates centralized governance and allows the same policy logic or policy pattern to be reused across multiple protected tables.
Masking policies are a common distractor because they are also policy objects, but they protect column values rather than suppressing unauthorized rows. Secure views can sometimes implement row filtering, but for enterprise-scale governance they are typically less maintainable than dedicated row access policies when the requirement is reusable, centralized row-level security. Network policies are unrelated to SQL row filtering.
This aligns with Snowflake documentation and best practices for dynamic data protection: use row access policies for row-level security, use masking policies for column-level obfuscation, and use role/context-aware policy expressions for centrally governed access control.
- A. Correct.
Correct. Row access policies are designed to filter which rows are visible at query time. They can reference context functions such as CURRENT_ROLE() and can implement logic using a mapping table to relate roles to allowed region values. Including an exception for FINANCE_ADMIN satisfies the requirement for full visibility. Attaching the row access policy to the REGION_CODE column enforces row-level filtering on the table in a centrally managed way and supports reuse of the same policy pattern across multiple tables.
- B. Incorrect.
Incorrect. Masking policies protect sensitive column values, not row visibility. Even if REGION_CODE were masked, unauthorized users could still see the row itself and potentially infer sensitive information from other columns such as AMOUNT or CUSTOMER_ID. UNMASK is also related to masking behavior, not row-level authorization.
- C. Incorrect.
Incorrect. Secure views can be used to restrict access, but this approach does not best meet the stated requirement for a centrally managed, reusable policy based on the active role at query time. Maintaining separate secure views per region becomes harder to scale across many regions and tables. It is a workable design in some cases, but Snowflake row access policies are the purpose-built feature for this requirement.
- D. Incorrect.
Incorrect. Network policies control authentication access based on network locations and related access restrictions; they do not enforce row-level filtering in SQL query results. Tying row visibility to office IP range is also operationally brittle and does not satisfy the requirement to evaluate access by active role at query time.