ADA-C01 Question 175
Single answerConfigure a row access policy on an objectA healthcare company stores patient billing records in PROD.FINANCE.BILLING_TRANSACTIONS. Analysts in the FINANCE_ANALYST role should see all rows, but regional managers should see only rows where REGION matches the region mapped to their current role in a lookup table SECURITY.REGION_ROLE_MAP(ROLE_NAME, REGION). The company wants the filtering enforced in Snowflake so it applies consistently across queries without changing analyst SQL. Which approach should the administrator use to configure this requirement?
- A
Create a row access policy that takes the REGION column as input, uses CURRENT_ROLE() to compare against SECURITY.REGION_ROLE_MAP, and then attach the policy to the BILLING_TRANSACTIONS table on the REGION column.
- B
Create a masking policy on the REGION column that returns NULL when CURRENT_ROLE() is not mapped to the row's region, and attach it to the BILLING_TRANSACTIONS table.
- C
Create a secure view over BILLING_TRANSACTIONS with a WHERE clause on REGION and grant access only to the view; this is the only way to implement row-level filtering in Snowflake.
- D
Create a tag-based masking policy on the BILLING_TRANSACTIONS table and assign tags to regional managers so only matching rows are returned.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a row access policy attached to the table. In Snowflake, row access policies are intended for row-level security and evaluate to TRUE or FALSE for each row. They can reference the protected table's columns and context functions such as CURRENT_ROLE() or IS_ROLE_IN_SESSION(), and they can also use lookup tables to manage entitlements. This approach centralizes enforcement on the object so BI tools, worksheets, and ad hoc queries all receive consistent filtered results without rewriting SQL. By contrast, masking policies and tag-based masking policies protect column values rather than suppressing rows. Secure views can also implement row filtering, but they are not the only mechanism and are less direct when the requirement is specifically to configure row-level access on the object itself. This aligns with Snowflake documentation on row access policies, which describes applying a policy to a table or view and using policy expressions to determine row visibility based on role or other session context.
- A. Correct.
Correct. A row access policy is the Snowflake feature designed for row-level security. The policy can accept one or more columns from the protected table, evaluate the current execution context such as CURRENT_ROLE(), and return a BOOLEAN expression that determines whether each row is visible. Attaching the policy to the table on the REGION column enforces filtering centrally without requiring users to modify their SQL. Using a mapping table is a common real-world pattern for maintainable policy logic.
- B. Incorrect.
Incorrect. A masking policy controls how column values are presented, not whether rows are returned. Even if REGION were masked to NULL, the row itself would still be visible unless separate row-level controls were implemented. This option reflects a common misconception that masking policies can replace row access policies.
- C. Incorrect.
Incorrect. A secure view can implement row filtering, but it is not the only way in Snowflake and does not meet the requirement as directly as a row access policy. The question specifically asks for a configuration that enforces filtering consistently on the object without changing user SQL against the table. A row access policy is purpose-built for this and applies directly to the table.
- D. Incorrect.
Incorrect. Tag-based masking policies apply masking logic to tagged columns, not row filtering. Tags also are not used to assign users or roles access to specific rows in the way described here. This distractor mixes two separate governance features: tagging and dynamic data masking.