ARA-C01 Question 40
Single answerSecondary rolesA Snowflake architect is reviewing access behavior for a data engineering team. Each engineer has the primary role DEVELOPER and is also granted the secondary roles DATA_READER and STAGE_OPERATOR. The team reports inconsistent results: a user can successfully query tables in a shared analytics schema without running USE ROLE DATA_READER, but the same user cannot create an external stage in an integration schema unless they explicitly switch roles to STAGE_OPERATOR first. The architect wants to explain this behavior and recommend the correct approach. Which statement best explains what is happening?
- A
Secondary roles are automatically active for object access only when the session has ENABLE_SECONDARY_ROLES set to ALL, but creating objects still requires the active primary role to hold the required CREATE privilege on the target schema.
- B
Secondary roles are only evaluated for SELECT statements; DDL statements such as CREATE STAGE never consider privileges from secondary roles.
- C
If a user has multiple granted roles, Snowflake merges all privileges from primary and secondary roles for every operation, so the failure indicates missing OWNERSHIP on the database.
- D
Secondary roles can be used only for privileges inherited through role hierarchy; directly granted secondary roles do not apply unless the user runs USE ROLE for each one.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests a subtle but important Snowflake design point about secondary roles. When secondary roles are active in a session, Snowflake can use privileges from those roles for access checks, which is why a user may be able to query objects without switching their primary role. However, CREATE operations are different because the created object is owned by the active primary role, not a secondary role. As a result, the role set as primary with USE ROLE must itself have the necessary CREATE privilege on the target schema for the operation to succeed. In practice, architects should distinguish between read/access patterns, where secondary roles can simplify user experience, and object creation patterns, where users often need to switch to the role intended to own and manage the new object. This aligns with Snowflake documentation on role-based access control, secondary roles, and object ownership behavior during DDL.
- A. Correct.
Correct. In Snowflake, secondary roles can be activated in a session, commonly via ENABLE_SECONDARY_ROLES = ALL, and their privileges are considered for many authorization checks such as querying objects. However, when creating objects, Snowflake uses the active primary role as the owner of the new object. Therefore, the primary role must have the necessary CREATE privilege on the container object (for example, the schema). This explains why reading works with active secondary roles but creating the stage requires switching the primary role to STAGE_OPERATOR if that role holds the required CREATE STAGE privilege.
- B. Incorrect.
Incorrect. This is a common misconception. Secondary roles are not limited to SELECT statements. They can be considered for authorization broadly when active in the session. The key nuance is not that DDL ignores secondary roles entirely, but that object creation has ownership semantics tied to the primary role, which is why CREATE operations often require the primary role to be the one with the necessary privileges.
- C. Incorrect.
Incorrect. Snowflake does not simply merge privileges from all roles for every operation without nuance. Even when secondary roles are active, object creation is special because the new object is owned by the primary role in use. The error is not evidence that OWNERSHIP on the database is required; creating an external stage typically requires the appropriate CREATE STAGE privilege on the schema (and USAGE on parent containers, plus integration-related privileges as applicable), held by the active primary role for creation.
- D. Incorrect.
Incorrect. Directly granted secondary roles do apply when secondary roles are enabled in the session. A user does not need to run USE ROLE for each role just to make directly granted secondary roles available for eligible authorization checks. The issue in the scenario is the distinction between using privileges for access versus using the primary role as the owner during object creation.