SnowPro Associate: Platform Question 127
Single answerExplain role hierarchyA Snowflake administrator is redesigning access for an analytics team. The team lead wants the ANALYST role to be able to query curated tables in the PROD_DB database, and the SENIOR_ANALYST role to have all ANALYST privileges plus the ability to create views in the same schema. To simplify administration, the company wants privileges granted once and inherited through the role structure rather than assigned repeatedly to users. Which approach best meets this requirement?
- A
Grant the SENIOR_ANALYST role to the ANALYST role, then grant object privileges directly to both roles as needed.
- B
Grant the ANALYST role to the SENIOR_ANALYST role, grant SELECT privileges on the curated tables to ANALYST, and grant CREATE VIEW on the schema to SENIOR_ANALYST.
- C
Grant both ANALYST and SENIOR_ANALYST directly to each user, then assign object privileges only to users who need them.
- D
Grant SELECT and CREATE VIEW privileges to ANALYST, then grant ANALYST to SENIOR_ANALYST so SENIOR_ANALYST can inherit all privileges.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, access control is based on role-based access control (RBAC), and role hierarchy allows one role to inherit privileges from another. To make a senior role include all privileges of a junior role, grant the junior role to the senior role. In this scenario, granting ANALYST to SENIOR_ANALYST means SENIOR_ANALYST inherits ANALYST privileges. Then privileges can be assigned at the appropriate level: common privileges such as SELECT go to ANALYST, while elevated privileges such as CREATE VIEW go only to SENIOR_ANALYST. This design supports least privilege, reduces duplicate grants, and aligns with Snowflake documentation and best practices for building role hierarchies and managing access through roles rather than direct user grants.
- A. Incorrect.
Incorrect. This reverses the intended hierarchy. If SENIOR_ANALYST is granted to ANALYST, then ANALYST would inherit SENIOR_ANALYST privileges, potentially giving junior analysts elevated access such as CREATE VIEW. In Snowflake role hierarchy, the role granted to another role is inherited by the receiving role.
- B. Correct.
Correct. This follows Snowflake role hierarchy best practice. By granting ANALYST to SENIOR_ANALYST, the higher-level role inherits the lower-level role's privileges. Then SELECT can be granted once to ANALYST, while CREATE VIEW is granted only to SENIOR_ANALYST. This minimizes administrative overhead and preserves least privilege.
- C. Incorrect.
Incorrect. Granting both roles directly to users does not use role hierarchy to simplify management. It also shifts privilege management toward user-level administration, which is less scalable and contrary to recommended RBAC design in Snowflake.
- D. Incorrect.
Incorrect. Granting both SELECT and CREATE VIEW to ANALYST would allow all analysts to create views, which violates the requirement that only SENIOR_ANALYST should have that additional capability. Although granting ANALYST to SENIOR_ANALYST is correct, the privilege assignment is too broad.