COF-C03 Question 163
Single answerAccount rolesA Snowflake administrator is setting up access for a new analytics team. Team members need to query tables in the ANALYTICS database and create their own temporary and permanent tables in a shared schema for experimentation. They should not be able to grant their privileges to other roles, and access should be managed using the principle of least privilege. Which role design best meets these requirements?
- A
Grant the SYSADMIN role to the analytics team because it already has broad object management privileges and simplifies administration.
- B
Create a custom account role, grant USAGE on the warehouse, USAGE on the database and schema, and SELECT plus CREATE TABLE on the target schema, then grant that custom role to the team members.
- C
Create a database role in the ANALYTICS database, grant it all required privileges including warehouse USAGE, and grant that database role directly to the users.
- D
Grant OWNERSHIP on the shared schema to the analytics team so they can create tables and manage access without needing additional privileges.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to create a custom account role with only the necessary privileges. In Snowflake, account roles are the primary mechanism for granting privileges that span the account, including warehouse access and privileges on databases, schemas, and objects. For this scenario, the team needs warehouse USAGE to run queries, USAGE on the database and schema to access objects in the namespace, SELECT on existing tables, and CREATE TABLE on the shared schema to create permanent tables. Temporary tables can also be created in a schema when the role has the necessary schema-level create privilege. To prevent privilege delegation, the administrator should avoid granting privileges with the grant option. Snowflake best practices recommend using custom roles to implement least privilege instead of assigning broad system roles such as SYSADMIN. Also, database roles are scoped to a single database and cannot replace account roles for privileges such as warehouse USAGE.
- A. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined account role intended for broad administrative control over objects. Granting it to an analytics team violates least privilege and would provide far more access than needed. This is a common mistake when administrators prioritize convenience over secure role design.
- B. Correct.
Correct. A custom account role is the appropriate choice because the team needs privileges across securable objects including a warehouse and objects in a database/schema. Granting USAGE on the warehouse, database, and schema allows access to the execution and namespace context, while SELECT and CREATE TABLE on the schema provide the required data access and object creation capability. Not granting the WITH GRANT OPTION ensures team members cannot delegate these privileges to others.
- C. Incorrect.
Incorrect. A database role can hold privileges on objects within its database, but it cannot be used to hold privileges on warehouses. In addition, database roles are granted to account roles, not directly to users for general cross-account-role access management. This option mixes database-role scope with account-role responsibilities.
- D. Incorrect.
Incorrect. OWNERSHIP is the highest level of control over an object and exceeds the stated requirement. While it would allow table creation and privilege management, it would also let the team transfer ownership and manage grants in ways that are inconsistent with least privilege. CREATE TABLE on the schema is sufficient for the requirement to create tables.