SnowPro Advanced: Security Engineer Question 132
Single answerTroubleshoot row access policy enforcementA security engineer applies a row access policy to the SALES.ORDERS table to restrict rows by REGION. The policy compares the row's REGION value to entries in a mapping table that stores which regions each role can access. Users with the ANALYST role report that they now see zero rows, even though the mapping table contains valid entries for ANALYST and the policy was attached successfully. The policy owner role does not have privileges on the mapping table, but the ANALYST role does. Which action is most likely to resolve the issue?
- A
Grant SELECT on the mapping table to the role that owns the row access policy, then retest the policy
- B
Grant APPLY ROW ACCESS POLICY on the SALES.ORDERS table to the ANALYST role so the policy can evaluate under the querying user's privileges
- C
Recreate the row access policy as a masking policy because masking policies can reference lookup tables using the querying user's role
- D
Transfer ownership of the SALES.ORDERS table to the ANALYST role so the policy and table execute in the same security context
Show answer and explanation
Correct answer: A
Explanation
This scenario tests troubleshooting of row access policy enforcement when the policy body references a mapping table. In Snowflake, row access policies can be attached successfully yet still return unexpected results if the policy body cannot access required objects. A critical best practice is to ensure the role that owns the row access policy has the needed privileges on any referenced lookup tables or objects used in the policy expression. Granting privileges only to the querying role does not solve this, because policy evaluation for those object references uses the policy owner's rights. Candidates should distinguish between privileges used to apply a policy, privileges used to query the protected object, and privileges required by the policy body itself. This aligns with Snowflake documentation on row access policies, policy context, and troubleshooting policy behavior when conditional logic depends on mapping tables.
- A. Correct.
Correct. When a row access policy body references other objects such as a mapping table, Snowflake evaluates those object references using the policy owner's privileges, not the querying user's privileges. If the policy owner cannot read the mapping table, the policy logic will not be able to evaluate as intended, often resulting in all rows being filtered out. Granting the necessary privileges, such as SELECT on the mapping table, to the policy owner role is the appropriate fix.
- B. Incorrect.
Incorrect. APPLY ROW ACCESS POLICY is the privilege needed to set or unset a row access policy on a table or view. It does not change the execution context of the policy during query time. A common misconception is that granting more privileges to the querying role helps policy evaluation, but lookup table access in the policy is not based on the querying user's privileges.
- C. Incorrect.
Incorrect. Row access policies and masking policies serve different purposes. A masking policy obfuscates column values, while a row access policy determines which rows are visible. Replacing the policy type would not address the underlying issue of privilege context for the lookup table reference and would also change the security behavior inappropriately.
- D. Incorrect.
Incorrect. Ownership of the protected table does not determine the privilege context used when the row access policy accesses referenced objects inside the policy body. The key issue is the privileges held by the policy owner role on the mapping table. Transferring table ownership to ANALYST is unnecessary and would create avoidable security risk.