COF-C03 Question 169
Single answerSecondary rolesA data engineer logs in to Snowflake with the role ANALYST. The user has also been granted the roles REPORTING_READ and STAGE_WRITER. REPORTING_READ has SELECT on a shared reporting schema, and STAGE_WRITER has USAGE on an integration and WRITE privileges needed for loading files to an internal stage. The engineer wants to run a session where they can query the reporting tables and load files to the stage without repeatedly switching roles. Which action should they take to make privileges from the additional granted roles available in the current session while keeping ANALYST as the primary role?
- A
Execute USE SECONDARY ROLES ALL;
- B
Execute USE ROLE REPORTING_READ; then Snowflake will automatically combine privileges from all granted roles
- C
Grant REPORTING_READ and STAGE_WRITER to the ANALYST role so inherited privileges are enabled only for this session
- D
Execute SET SECONDARY ROLES = ('REPORTING_READ','STAGE_WRITER');
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, a user has one primary role active at a time, but can also enable granted roles as secondary roles for the session. To keep ANALYST as the primary role and make privileges from other granted roles available, the user should execute USE SECONDARY ROLES ALL. This is useful in real-world workflows where a user needs combined access from multiple granted roles without repeatedly issuing USE ROLE. By contrast, USE ROLE switches the primary role, and changing role grants alters long-term RBAC design rather than solving a session-level need. Snowflake documentation on role-based access control and secondary roles describes that secondary roles can be enabled for a session and that privileges from enabled secondary roles are considered along with the primary role's privileges.
- A. Correct.
Correct. USE SECONDARY ROLES ALL activates all roles granted to the user as secondary roles for the current session, while the current primary role remains ANALYST. In this state, Snowflake can evaluate privileges from the primary role together with enabled secondary roles for operations in the session. This is the intended way to make privileges from additional granted roles available without switching the primary role repeatedly.
- B. Incorrect.
Incorrect. USE ROLE REPORTING_READ changes the primary role to REPORTING_READ; it does not keep ANALYST as the primary role. Also, Snowflake does not automatically combine privileges from all granted roles just because one role is activated as the primary role. Secondary roles must be explicitly enabled if the user wants privileges from multiple granted roles considered in the session.
- C. Incorrect.
Incorrect. Granting REPORTING_READ and STAGE_WRITER to ANALYST would change the role hierarchy and broaden privileges for anyone using ANALYST, not just for this user's session. It is also not a session-only mechanism. This option reflects a common misconception that role grants can be used as a temporary session setting; in reality, grants are persistent administrative changes.
- D. Incorrect.
Incorrect. SET SECONDARY ROLES is not the SQL command used to enable secondary roles in a session. Snowflake uses the USE SECONDARY ROLES command, such as USE SECONDARY ROLES ALL or USE SECONDARY ROLES NONE. This distractor is plausible because many session behaviors use SET, but secondary role activation does not.