SnowPro Advanced: Security Engineer Question 127
Single answerDesign and apply row-access policies with SQL expressions and Snowflake functionsA healthcare analytics company stores claims data in PROD.CLAIMS.CLAIM_FACT. The table includes REGION_CODE, MEMBER_ID, and CLAIM_AMOUNT. Analysts in different regional roles must only see rows for their own region, while users with the SECURITYADMIN role must be able to see all rows for troubleshooting. The company also wants the policy logic to remain centralized so it can be reused across multiple tables. A security engineer creates a mapping table SEC_DB.POLICY.MANAGER_ROLE_REGION_MAP with columns ROLE_NAME and REGION_CODE, and plans to use a row access policy on REGION_CODE. Which solution best meets the requirement?
- A
Create a row access policy that returns TRUE when CURRENT_ROLE() = 'SECURITYADMIN' or when EXISTS (SELECT 1 FROM SEC_DB.POLICY.MANAGER_ROLE_REGION_MAP m WHERE m.ROLE_NAME = CURRENT_ROLE() AND m.REGION_CODE = REGION_CODE), then apply the policy to the table on the REGION_CODE column.
- B
Create a masking policy on REGION_CODE that replaces unauthorized values with NULL based on CURRENT_ROLE(), then apply it to CLAIM_AMOUNT and MEMBER_ID so nonmatching rows are hidden from analysts.
- C
Create a secure view over CLAIM_FACT that filters rows using CURRENT_USER() and grant access only to the view, because row access policies cannot reference mapping tables in SQL expressions.
- D
Create a row access policy that checks IS_ROLE_IN_SESSION('SECURITYADMIN') or IS_ROLE_IN_SESSION(REGION_CODE), then apply the policy to MEMBER_ID because the policy can infer the row's region from the table.
- E
Create a row access policy that returns TRUE only when INVOKER_ROLE() matches REGION_CODE directly, then attach the policy to the table without specifying a column because row access policies automatically evaluate against all table columns.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because it uses the native Snowflake feature designed for row-level filtering: a row access policy. This approach centralizes the authorization logic in one policy object and supports reuse across multiple tables by referencing a mapping table that connects business entitlements to session context. In Snowflake, row access policies are defined with a signature that accepts one or more column values from the protected table, and the policy body returns a BOOLEAN expression to determine whether each row is visible. Snowflake also supports context functions such as CURRENT_ROLE() and role checks in policy logic. Using a mapping table is a common best practice when entitlements are too complex to hard-code in the policy. Masking policies are not a substitute because they redact column values rather than filter out rows. Secure views can implement similar filtering but do not meet the requirement as directly as a reusable row access policy attached to multiple tables. Relevant Snowflake documentation includes topics on row access policies, using SQL expressions and context functions in policies, and policy-based access control patterns with mapping tables.
- A. Correct.
Correct. A row access policy is the appropriate control for filtering visible rows. Using a centralized mapping table allows the logic to be reused across multiple protected tables. The policy body can use SQL expressions and context functions such as CURRENT_ROLE(), and it can evaluate an EXISTS subquery against a mapping table to determine whether the active role is authorized for the row's REGION_CODE. Including an override for SECURITYADMIN satisfies the troubleshooting requirement. Applying the policy to the REGION_CODE column is also correct because row access policies are bound to one or more columns whose values are passed into the policy expression for each row.
- B. Incorrect.
Incorrect. Masking policies protect the values returned in specific columns; they do not remove rows from the result set. Applying a masking policy to CLAIM_AMOUNT and MEMBER_ID would still allow unauthorized users to see that rows exist and could expose other unmasked metadata. This option reflects a common misconception that dynamic data masking and row-level filtering are interchangeable. For this scenario, the requirement is to restrict row visibility, which is the purpose of a row access policy.
- C. Incorrect.
Incorrect. A secure view can implement row filters, and in some designs it may be used, but the statement that row access policies cannot reference mapping tables is false. Row access policies support SQL expressions that can incorporate subqueries and mapping-table lookups, making them well suited for centralized reusable enforcement. This option also shifts enforcement to a view rather than using the requested policy-based approach for reuse across tables.
- D. Incorrect.
Incorrect. IS_ROLE_IN_SESSION() checks whether a specified role is active in the current session role hierarchy, but passing REGION_CODE into IS_ROLE_IN_SESSION(REGION_CODE) assumes region values are role names, which is not the stated design. Also, applying the row access policy to MEMBER_ID would not make the row's REGION_CODE automatically available unless the policy signature and binding were designed around the needed column values. The requirement explicitly says the policy is intended to operate on REGION_CODE.
- E. Incorrect.
Incorrect. INVOKER_ROLE() is not the correct function for this requirement, and the option incorrectly assumes the role name should directly equal the REGION_CODE value, which does not use the provided mapping table and is not realistic in most enterprise role designs. In addition, row access policies are associated with specified columns; they do not automatically evaluate against all table columns without explicit binding.