SnowPro Associate: Platform Question 128
Single answerExplain role hierarchyA Snowflake administrator is redesigning access for the analytics team. They create a role hierarchy where ANALYST is granted to SENIOR_ANALYST, and SENIOR_ANALYST is granted to ANALYTICS_MANAGER. The SELECT privilege on the SALES_DB.REPORTING.MONTHLY_REVENUE table is granted only to the ANALYST role. A user has only the ANALYTICS_MANAGER role assigned and activates that role in their session. Which statement best describes what access the user has and why?
- A
The user can query SALES_DB.REPORTING.MONTHLY_REVENUE because privileges granted to a lower role are inherited by higher roles in the hierarchy.
- B
The user cannot query SALES_DB.REPORTING.MONTHLY_REVENUE because table privileges must be granted directly to the active role.
- C
The user can query SALES_DB.REPORTING.MONTHLY_REVENUE only if ANALYST is also set as a secondary role in the session.
- D
The user cannot query SALES_DB.REPORTING.MONTHLY_REVENUE because inherited privileges apply only to warehouse usage, not to database objects.
Show answer and explanation
Correct answer: A
Explanation
Snowflake uses a hierarchical role-based access control model. If role A is granted to role B, then role B inherits the privileges of role A. This enables administrators to build layered access models, such as assigning base data access to an ANALYST role and then granting that role to more senior roles. In this scenario, ANALYTICS_MANAGER inherits privileges from SENIOR_ANALYST, which in turn inherits privileges from ANALYST. Therefore, a user operating with ANALYTICS_MANAGER as the active role can query the table even though SELECT was granted only to ANALYST. This aligns with Snowflake documentation on access control and role hierarchy, which explains that higher-level roles inherit privileges from roles granted to them. A best practice is to grant object privileges to functional roles and then grant those roles to higher-level roles rather than duplicating direct grants.
- A. Correct.
Correct. In Snowflake's role hierarchy, when one role is granted to another, the receiving role inherits the privileges of the granted role. Since ANALYST was granted to SENIOR_ANALYST and SENIOR_ANALYST was granted to ANALYTICS_MANAGER, the ANALYTICS_MANAGER role inherits the SELECT privilege on the table. If the user activates ANALYTICS_MANAGER, they can use inherited privileges from lower roles in that hierarchy.
- B. Incorrect.
Incorrect. This reflects a common misconception that object privileges must be granted directly to the currently active role. In Snowflake, roles inherit privileges from roles granted to them, so direct grants are not required when a valid role hierarchy exists.
- C. Incorrect.
Incorrect. Secondary roles are not required in this scenario because the active primary role, ANALYTICS_MANAGER, already inherits privileges from SENIOR_ANALYST and ANALYST through the role hierarchy. Secondary roles are relevant when a user needs privileges from additional granted roles that are not already available through the active primary role.
- D. Incorrect.
Incorrect. Inherited privileges in a Snowflake role hierarchy apply broadly to privileges held by granted roles, including privileges on securable objects such as databases, schemas, tables, and warehouses, subject to the specific grants made. They are not limited to warehouse usage.