ARA-C01 Question 59
Single answerRow access policiesA global retailer stores all regional sales transactions in a single table, SALES_TXN, with columns including REGION_CODE, STORE_ID, AMOUNT, and TXN_DATE. Analysts in different regions should only see rows for their own region, while a small central audit team must see all rows across all regions. The company wants to minimize duplicate data and avoid embedding region filters in every BI query. The architect decides to implement a row access policy and use a mapping table that relates Snowflake roles to allowed REGION_CODE values. Which design best meets these requirements?
- A
Create a row access policy on SALES_TXN that checks CURRENT_ROLE() against a role-to-region mapping table, and returns TRUE for all rows when the role is an approved audit role; otherwise return TRUE only when REGION_CODE matches an allowed mapping.
- B
Create a masking policy on REGION_CODE so unauthorized users see NULL for disallowed regions, while keeping all rows visible to every role.
- C
Grant SELECT on separate secure views per region and require BI tools to query the correct regional view; do not use a row access policy because policies cannot reference mapping tables.
- D
Cluster SALES_TXN by REGION_CODE and rely on micro-partition pruning so each regional analyst only reads rows for that region.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a row access policy directly on SALES_TXN and drive entitlements from a role-to-region mapping table, with explicit logic to allow approved audit roles to see all rows. This is a standard Snowflake design for centralized, fine-grained row-level security. Row access policies evaluate a Boolean expression for each row and can use contextual functions such as CURRENT_ROLE() as well as lookups to mapping tables to determine whether a row should be returned. This approach satisfies the requirements to keep a single copy of the data, avoid pushing filters into every BI query, and support exceptions such as an audit role with broader visibility. By contrast, masking policies protect column values rather than suppress rows, and clustering is purely a performance optimization. Snowflake documentation and best practices for row-level security describe applying row access policies to tables or views and commonly using mapping tables to manage entitlements at scale.
- 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. A common and scalable pattern is to centralize entitlements in a mapping table and have the policy body evaluate whether the querying role is allowed to see the row's REGION_CODE. The same policy can include logic to permit designated audit roles to see all rows. This approach keeps one physical table, avoids duplicating data, and removes the need to hard-code filters in every downstream query.
- B. Incorrect.
Incorrect. A masking policy protects column values, not row visibility. If all rows remain visible and only REGION_CODE is masked, users could still infer sensitive information from other columns such as AMOUNT or TXN_DATE and from row counts. The requirement is to restrict entire rows by region, which is exactly what a row access policy is for.
- C. Incorrect.
Incorrect. Separate secure views per region can work functionally, but this design does not best meet the stated requirement to avoid embedding or managing regional filtering logic across multiple BI assets. Also, the claim that row access policies cannot reference mapping tables is false; Snowflake supports row access policies that use mapping table lookups to determine access. Using many regional views increases administrative overhead compared with a centralized policy on the base table.
- D. Incorrect.
Incorrect. Clustering affects storage organization and can improve query performance through pruning, but it does not enforce security. Micro-partition pruning is an optimization mechanism, not an access-control feature. Without a row access policy or equivalent security layer, analysts with SELECT privilege on the table could still access rows from all regions.