ADA-C01 Question 45
Single answerDetermine use cases for primary and secondary rolesA Snowflake administrator is onboarding a data engineer who needs to run a deployment script that creates tables in the ANALYTICS database, manages tasks in the PIPELINE schema, and reads data from a shared utility database. The engineer has been granted three functional roles: ANALYTICS_DDL, PIPELINE_OPERATOR, and UTIL_READONLY. The security team wants to avoid repeatedly switching roles during the deployment, but also wants object ownership and auditability to remain clear. Which approach best meets these requirements?
- A
Set ANALYTICS_DDL as the primary role and activate PIPELINE_OPERATOR and UTIL_READONLY as secondary roles before running the script.
- B
Grant all privileges from the three roles into ACCOUNTADMIN and instruct the engineer to use ACCOUNTADMIN for the deployment.
- C
Set all three roles as primary roles for the session so Snowflake can evaluate ownership from each role independently.
- D
Use only secondary roles for the session because objects created while secondary roles are active inherit ownership from all active roles.
Show answer and explanation
Correct answer: A
Explanation
This question tests practical use of primary versus secondary roles. In Snowflake, a session operates with one primary role, and optionally one or more secondary roles can be activated to allow privilege aggregation across granted roles. This is a common use case when a user needs permissions from multiple functional roles in a single workflow, such as deployment, troubleshooting, or data operations, without constantly switching roles. However, for DDL and object ownership, the primary role remains important because newly created objects are owned by the primary role in effect for the session. That makes the best practice to choose the role that should own created objects as the primary role, then enable secondary roles for any supplemental privileges needed. This aligns with Snowflake documentation on role activation, secondary roles, and access control best practices, especially the principle of least privilege and maintaining clear ownership boundaries.
- A. Correct.
Correct. In Snowflake, a user has one active primary role and can optionally activate granted secondary roles for privilege evaluation. Secondary roles are useful when a user needs combined privileges from multiple granted roles in a single session without repeatedly issuing USE ROLE. However, object creation and ownership are associated with the primary role, which preserves clear ownership and auditability. Setting ANALYTICS_DDL as the primary role is appropriate because the script creates tables in ANALYTICS, while the secondary roles provide additional permissions needed to manage tasks and read from the utility database.
- B. Incorrect.
Incorrect. Although ACCOUNTADMIN might have broad privileges, using it for routine deployment violates least-privilege best practices and reduces audit clarity. The scenario specifically asks for a solution that avoids unnecessary privilege elevation while preserving clear ownership. Granting everything to ACCOUNTADMIN is a common but poor administrative pattern.
- C. Incorrect.
Incorrect. Snowflake sessions do not support multiple primary roles. A session has exactly one primary role, and optional secondary roles can also be active for authorization checks. This option reflects a misunderstanding of Snowflake's role model.
- D. Incorrect.
Incorrect. Secondary roles can contribute privileges when enabled, but object ownership is not assigned from all active roles. For SQL actions such as creating objects, the primary role is what determines ownership context. Therefore, using only secondary roles would not satisfy the ownership requirement described in the scenario.