COF-C03 Question 168
Single answerSecondary rolesA data engineer logs in to Snowflake with the primary role ANALYST. The user also has been granted two other roles: ETL_ROLE, which has USAGE on warehouse ETL_WH and SELECT on several source tables, and REPORTING_ROLE, which has SELECT on reporting views. The engineer needs to run a query that joins a source table and a reporting view without changing the primary role from ANALYST. Which action should the engineer take to ensure the session can use privileges from the additional granted roles?
- A
Run USE ROLE ETL_ROLE so Snowflake automatically combines privileges from ANALYST, ETL_ROLE, and REPORTING_ROLE for the session.
- B
Run USE SECONDARY ROLES ALL so the session can use privileges from granted secondary roles in addition to the primary role.
- C
Grant ETL_ROLE to REPORTING_ROLE, because secondary roles cannot be used for SQL statements.
- D
Run SET ROLE HIERARCHY = TRUE so all directly granted roles become active for the session.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, a session has one primary role, set with USE ROLE, and can optionally use additional granted roles as secondary roles. By default, secondary roles may not be active unless enabled. The command USE SECONDARY ROLES ALL activates all roles granted to the user as secondary roles for the current session, allowing privileges from those roles to be considered alongside the primary role for SQL operations. This is useful when a user needs access across objects granted through multiple roles without switching the primary role. A key exam point is that setting a primary role does not automatically merge all other directly granted roles; candidates must understand the distinction between primary role, role hierarchy, and activated secondary roles. This behavior is documented in Snowflake's access control and secondary roles documentation, including the USE SECONDARY ROLES command and role-based access control best practices.
- A. Incorrect.
Incorrect. USE ROLE changes the active primary role for the session to ETL_ROLE. Snowflake does not automatically combine arbitrary peer roles just because one role is set as primary. Privileges available through the primary role's hierarchy are active, but directly granted sibling roles such as REPORTING_ROLE are not automatically added unless secondary roles are enabled.
- B. Correct.
Correct. USE SECONDARY ROLES ALL activates all roles granted to the user as secondary roles for the session, in addition to the current primary role. This allows the engineer to keep ANALYST as the primary role while using privileges from ETL_ROLE and REPORTING_ROLE during query execution, assuming the roles were granted to the user and object privileges are otherwise sufficient.
- C. Incorrect.
Incorrect. Secondary roles can be used for many SQL operations, including querying objects, when they are activated in the session. Granting ETL_ROLE to REPORTING_ROLE would change role hierarchy design and may not be appropriate or necessary. The requirement is specifically to avoid changing the primary role, which USE SECONDARY ROLES ALL addresses directly.
- D. Incorrect.
Incorrect. SET ROLE HIERARCHY = TRUE is not a valid Snowflake command for activating directly granted roles in a session. This distractor reflects a common misconception that role inheritance can be toggled with a session parameter. In Snowflake, secondary roles are managed with USE SECONDARY ROLES.