ADA-C01 Question 25
Single answerAnalyze the implications of role inheritance when granting or revoking privilegesA Snowflake administrator is troubleshooting why members of the ANALYST role can still query the FINANCE_DB.REPORTING.MONTHLY_SUMMARY view after a cleanup of inherited access. The current role hierarchy and grants are as follows:
- ANALYST is granted to users in the BI team.
- REPORTING_READ is granted to ANALYST.
- DATA_SCIENCE_READ is also granted to ANALYST.
- REPORTING_READ has USAGE on database FINANCE_DB and schema REPORTING, and SELECT on view MONTHLY_SUMMARY.
- DATA_SCIENCE_READ has no privileges on FINANCE_DB or REPORTING.
The administrator executes the following command: REVOKE SELECT ON VIEW FINANCE_DB.REPORTING.MONTHLY_SUMMARY FROM ROLE ANALYST;
Afterward, BI users with the ANALYST role active can still query the view.
Which action will remove the access while minimizing impact to other privileges currently available through role inheritance?
- A
Revoke the REPORTING_READ role from the ANALYST role.
- B
Revoke SELECT on the view from the REPORTING_READ role.
- C
Revoke USAGE on schema REPORTING from the ANALYST role.
- D
Drop and recreate the ANALYST role so inherited privileges are recalculated.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake RBAC, privileges can be granted directly to a role or inherited through other roles granted to that role. Revoking a privilege from a parent role does not remove the same privilege if it is still available through another granted role. In this scenario, ANALYST can still query the view because the privilege comes from REPORTING_READ, which is granted to ANALYST. Therefore, revoking SELECT directly from ANALYST has no effect on the inherited access path.
To remove access with minimal impact, the administrator should revoke only the specific SELECT privilege from REPORTING_READ rather than revoke the entire REPORTING_READ role from ANALYST. This aligns with Snowflake access-control best practices: identify the actual grant path before revoking privileges, and use the least disruptive change necessary. Snowflake documentation on access control and role hierarchies explains that roles inherit privileges from roles granted to them, and effective privileges are the union of direct and inherited grants.
- A. Incorrect.
Incorrect. Revoking the REPORTING_READ role from ANALYST would remove access to the view, but it would also remove all other privileges inherited from REPORTING_READ. The question asks for the action that removes the specific access while minimizing impact to other inherited privileges. Removing an entire child role is broader than necessary.
- B. Correct.
Correct. The SELECT privilege is granted to REPORTING_READ, not directly to ANALYST. Because ANALYST inherits privileges through REPORTING_READ, revoking SELECT from ANALYST has no effect on the inherited privilege. To remove the access with the least collateral impact, the administrator must revoke the SELECT privilege from the role that actually holds it: REPORTING_READ.
- C. Incorrect.
Incorrect. ANALYST does not hold the relevant schema privilege directly, and revoking USAGE on the schema from ANALYST would not remove the inherited privilege coming from REPORTING_READ. Also, even if direct schema access existed, this would be a broader change than needed because it would affect access to all objects in the schema rather than just the single view.
- D. Incorrect.
Incorrect. Snowflake does not require dropping and recreating roles to recalculate inherited privileges. Role inheritance and privilege evaluation are dynamic. This option reflects a common misconception from other platforms where permission caches or group membership propagation may require refresh-like actions.