ADA-C01 Question 174
Single answerConfigure a row access policy on an objectA healthcare company stores patient claims in PROD.CORE.CLAIMS. Analysts in the CLAIMS_ANALYST role should only see rows where REGION matches a region listed for their current role in a mapping table SEC_ADMIN.REGION_ROLE_MAP(ROLE_NAME, REGION). Administrators want to enforce this rule directly on the table so that existing BI tools continue to query the same object without changes. Which approach should the Snowflake administrator use?
- A
Create a row access policy that takes REGION as an argument, checks CURRENT_ROLE() against SEC_ADMIN.REGION_ROLE_MAP, and then attach the policy to the CLAIMS table on the REGION column.
- B
Create a masking policy on the REGION column and return NULL for unauthorized users; Snowflake will automatically suppress rows where REGION is NULL.
- C
Create a secure view over CLAIMS with a WHERE clause on CURRENT_ROLE(), then attach a row access policy to the secure view so the base table remains unrestricted.
- D
Grant SELECT on SEC_ADMIN.REGION_ROLE_MAP to CLAIMS_ANALYST and rely on the BI tool to add a WHERE clause filtering REGION based on the mapping table.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a row access policy on the target table. In Snowflake, row access policies are designed to evaluate a Boolean expression for each row and determine whether that row is visible to the querying context. They can reference context functions such as CURRENT_ROLE() and compare against entitlements stored in a mapping table. This provides centralized, transparent row-level security without requiring query changes in downstream tools. By contrast, masking policies only alter displayed values in columns and do not filter rows. A secure view can also enforce row filtering, but it changes the queried object unless consumers are redirected to the view, which the scenario rules out. Best practice is to enforce row-level access as close to the data object as possible when the same table must remain the access point. Refer to Snowflake documentation on row access policies, policy signatures, and applying policies with ALTER TABLE ... ADD ROW ACCESS POLICY ... ON (...).
- A. Correct.
Correct. A row access policy is the appropriate Snowflake feature for row-level security. The policy can be defined with a signature such as (REGION STRING) RETURNS BOOLEAN and include logic like EXISTS (SELECT 1 FROM SEC_ADMIN.REGION_ROLE_MAP m WHERE m.ROLE_NAME = CURRENT_ROLE() AND m.REGION = REGION). The policy is then applied to the table using ALTER TABLE ... ADD ROW ACCESS POLICY ... ON (REGION). This enforces filtering transparently on the protected object, which matches the requirement to keep BI queries unchanged.
- B. Incorrect.
Incorrect. Masking policies protect the values returned in specific columns, not which rows are visible. Returning NULL from a masking policy does not cause Snowflake to remove the row from the result set. This is a common confusion between dynamic data masking and row-level security.
- C. Incorrect.
Incorrect. A secure view can implement row filtering, but the scenario explicitly requires enforcing the rule directly on the existing object so current BI tools can continue querying the same table without switching to a new view. Also, attaching a row access policy to a view is not the intended solution here when the goal is to secure the base table itself.
- D. Incorrect.
Incorrect. This relies on client-side behavior rather than centralized enforcement in Snowflake. Granting access to the mapping table and expecting the BI tool to apply the correct filter is not a secure or reliable row-level security design. Users could query the table directly without the expected filter.