SnowPro Advanced: Security Engineer Question 126
Single answerDesign and apply row-access policies with SQL expressions and Snowflake functionsA healthcare company stores patient billing data in a table named BILLING.PUBLIC.CLAIMS with columns CLAIM_ID, REGION, AMOUNT, and DIAGNOSIS_CODE. Analysts in different regional roles must only see rows for their assigned REGION, while users with the SECURITYADMIN role must be able to see all rows for audit purposes. The company already maintains a mapping table SEC.ADMIN.ROLE_REGION_MAP with columns ROLE_NAME and REGION_CODE, where each analyst role is mapped to exactly one region. The security engineer wants a row access policy that can be attached to CLAIMS and evaluated at query time using Snowflake SQL functions. Which approach should the engineer implement?
- A
Create a row access policy on REGION that returns TRUE when CURRENT_ROLE() = 'SECURITYADMIN' OR EXISTS (SELECT 1 FROM SEC.ADMIN.ROLE_REGION_MAP m WHERE m.ROLE_NAME = CURRENT_ROLE() AND m.REGION_CODE = REGION).
- B
Create a masking policy on REGION that replaces unauthorized REGION values with NULL, and rely on BI tools to filter out rows where REGION is NULL.
- C
Create a row access policy on REGION that returns TRUE when CURRENT_AVAILABLE_ROLES() contains 'SECURITYADMIN' OR EXISTS (SELECT 1 FROM SEC.ADMIN.ROLE_REGION_MAP m WHERE m.ROLE_NAME = CURRENT_ROLE() AND m.REGION_CODE = REGION).
- D
Create a secure view over CLAIMS that filters with INVOKER_ROLE() and grant analysts access only to the view, because row access policies cannot reference mapping tables.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to implement a row access policy that evaluates the row's REGION value against an authorization mapping table using a SQL EXISTS expression, while allowing a designated audit role such as SECURITYADMIN to see all rows. This is a common Snowflake design for row-level security because it centralizes access logic and applies it consistently wherever the protected table is queried.
Key best-practice points:
- Use a row access policy, not a masking policy, when the requirement is to hide entire rows rather than redact column values.
- Use Snowflake context functions such as CURRENT_ROLE() to evaluate the active role at query time.
- A mapping table is a practical way to avoid hardcoding role-to-region logic inside the policy body.
- Granting broad access based on available roles rather than the active role can unintentionally overexpose data.
This aligns with Snowflake documentation and recommended patterns for row access policies, which support SQL expressions, conditional logic, and lookups against mapping tables to enforce dynamic row-level security.
- A. Correct.
Correct. A row access policy is the appropriate Snowflake feature for filtering which rows are visible at query time. Using CURRENT_ROLE() allows the policy to evaluate the active role in the session, and the EXISTS subquery against a mapping table is a common design pattern for centrally managing region-to-role authorization. Including a condition for SECURITYADMIN allows that role to bypass regional filtering for audit access. This approach matches how row access policies are designed to use SQL expressions and Snowflake context functions.
- B. Incorrect.
Incorrect. A masking policy protects column values, not row visibility. If the goal is to prevent unauthorized users from seeing entire rows, masking REGION to NULL does not stop access to other columns such as AMOUNT or DIAGNOSIS_CODE. It also incorrectly shifts enforcement to downstream tools rather than enforcing access control in Snowflake.
- C. Incorrect.
Incorrect. CURRENT_AVAILABLE_ROLES() is not the right function to use in a row access policy for this purpose. Row access policies should typically evaluate the active role context, commonly via CURRENT_ROLE() or related supported context functions. Even if a user has SECURITYADMIN among available roles, that should not automatically grant access unless it is the currently active role used for the query.
- D. Incorrect.
Incorrect. Secure views can implement row filtering, but the statement that row access policies cannot reference mapping tables is false. Row access policies are specifically designed for centralized row-level security and can use SQL logic, including subqueries to mapping tables, subject to proper privileges and design. Also, INVOKER_ROLE() is not the standard function used here for row access policy evaluation.