ARA-C01 Question 39
Single answerSecondary rolesA Snowflake architect is designing access for a data engineering user who must run a deployment script that creates a table in schema PROD_DB.CURATED and then immediately load data from an internal stage owned by a different functional role. The user has been granted two roles: DEPLOYER_ROLE, which has USAGE on PROD_DB and CREATE TABLE on schema CURATED, and LOADER_ROLE, which has USAGE on the stage and INSERT on the target table after it is created. The company wants to avoid switching roles multiple times during automation and wants the script to succeed with the least operational friction. Which action should the architect recommend?
- A
Grant both DEPLOYER_ROLE and LOADER_ROLE to the user, set DEPLOYER_ROLE as the primary role, and execute the script after running USE SECONDARY ROLES ALL.
- B
Grant DEPLOYER_ROLE to LOADER_ROLE, set LOADER_ROLE as the primary role, and rely on inherited privileges without using secondary roles.
- C
Set both roles as secondary roles for the user and execute the script without a primary role because secondary roles can own newly created objects.
- D
Keep DEPLOYER_ROLE as the primary role and expect privileges from LOADER_ROLE to apply automatically because all granted roles are evaluated by default in each session.
Show answer and explanation
Correct answer: A
Explanation
This question tests practical use of secondary roles in a deployment workflow. In Snowflake, a user can be granted multiple roles, but each session has a single primary role. Secondary roles are optional and must be activated, commonly with USE SECONDARY ROLES ALL. When active, privileges from the primary and secondary roles are considered for authorization checks. However, object ownership for newly created objects is assigned to the primary role, not to a secondary role. That distinction is critical in automation scenarios that both create objects and access resources controlled by different roles. Best practice is to keep the role that should own newly created objects as the primary role and activate secondary roles only when their additional privileges are needed. This behavior is documented in Snowflake guidance on role-based access control, active roles in a session, and secondary roles.
- A. Correct.
Correct. In Snowflake, a session has one primary role, and secondary roles can be activated with USE SECONDARY ROLES ALL (or a specific setting). When secondary roles are active, privilege checks can consider privileges from the primary and active secondary roles. However, object creation is still performed using the primary role, which is important here because the table must be created by a role with CREATE TABLE on the schema. Keeping DEPLOYER_ROLE as primary allows the CREATE TABLE statement to succeed, and activating secondary roles allows the same session to also use privileges from LOADER_ROLE for the stage and subsequent load operations. This is the most operationally efficient approach for the scenario.
- B. Incorrect.
Incorrect. Granting DEPLOYER_ROLE to LOADER_ROLE could allow privilege inheritance if LOADER_ROLE is made the active role, but this changes the role hierarchy and may not reflect separation-of-duties requirements. More importantly, the scenario specifically calls for using secondary roles to avoid switching roles during automation while preserving distinct functional roles. Although role hierarchy can solve some access problems, it is not the best recommendation here because it alters governance design rather than leveraging session-based secondary roles as intended.
- C. Incorrect.
Incorrect. Snowflake sessions always operate with a primary role; secondary roles supplement privilege evaluation but do not replace the primary role. Also, only the primary role owns newly created objects. Therefore, a script cannot run with only secondary roles, and secondary roles cannot become the owner of objects created in the session.
- D. Incorrect.
Incorrect. Privileges from roles granted to a user are not automatically active in every session unless secondary roles are explicitly enabled. A common misconception is that all granted roles are evaluated by default. In practice, the session uses the primary role, and secondary roles must be activated before their privileges are considered.