SnowPro Advanced: Security Engineer Question 238
Single answerAudit user roles and permissionsA security engineer must review whether analysts in the role ANALYST_RO have accumulated unintended access through nested role grants. The engineer needs to identify all database object privileges that users effectively receive through ANALYST_RO, including privileges inherited from lower-level roles granted to it. Which approach should the engineer use?
- A
Run SHOW GRANTS TO ROLE ANALYST_RO and rely on the output to include all inherited object privileges from child roles.
- B
Query ACCOUNT_USAGE.GRANTS_TO_ROLES for rows where GRANTEE_NAME = 'ANALYST_RO'; this fully expands inherited privileges from nested roles automatically.
- C
Use the ACCOUNT_USAGE.GRANTS_TO_ROLES and ACCOUNT_USAGE.GRANTS_OF tables together with role hierarchy information from ACCOUNT_USAGE.GRANTS_TO_ROLES or SHOW GRANTS OF ROLE to recursively trace role-to-role grants and derive effective privileges.
- D
Query ACCOUNT_USAGE.GRANTS_TO_USERS for users assigned to ANALYST_RO; direct user grants are sufficient to determine all effective object privileges for the role.
Show answer and explanation
Correct answer: C
Explanation
In Snowflake, auditing effective access for a role is more complex than listing only direct grants. Roles can be granted to other roles, creating a hierarchy in which privileges are inherited. To determine what users effectively receive through ANALYST_RO, the security engineer must evaluate both direct object grants to ANALYST_RO and grants to any roles granted to ANALYST_RO, recursively. Views such as SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES help identify grants to roles, but they do not by themselves flatten the full hierarchy into effective privileges for a target role. SHOW GRANTS OF ROLE is also useful for identifying role relationships. Best practice is to build or use a recursive audit process that traces nested role grants and then aggregates inherited object privileges. This aligns with Snowflake's RBAC model and the documented distinction between direct grants and inherited privileges.
- A. Incorrect.
Incorrect. SHOW GRANTS TO ROLE ANALYST_RO returns privileges granted directly to the role. It does not by itself provide a fully expanded view of privileges inherited through roles granted to ANALYST_RO. A common mistake is assuming SHOW output automatically resolves nested role inheritance for effective access analysis.
- B. Incorrect.
Incorrect. ACCOUNT_USAGE.GRANTS_TO_ROLES records grants to roles, but filtering only on GRANTEE_NAME = 'ANALYST_RO' returns direct grants to that role, not a fully expanded inheritance chain. To audit effective privileges, the engineer must also account for role-to-role relationships recursively.
- C. Correct.
Correct. Effective privilege auditing for a role with nested grants requires combining object privilege metadata with role hierarchy data. In Snowflake, GRANTS_TO_ROLES can be used to identify privileges granted to roles and role grants, while SHOW GRANTS OF ROLE or equivalent hierarchy analysis helps trace inherited access. Recursively walking the role graph is the practical way to determine all effective privileges received through ANALYST_RO.
- D. Incorrect.
Incorrect. ACCOUNT_USAGE.GRANTS_TO_USERS shows privileges or roles granted directly to users, but it does not answer the core question of what effective object privileges are inherited through the role ANALYST_RO itself. User-level analysis can be useful later, but it is not sufficient for auditing the role's effective permissions.