COF-C03 Question 162
Single answerAccount rolesA Snowflake administrator is onboarding a new analytics team. The team needs to query tables in the FINANCE_DB database and use the ANALYST_WH warehouse, but they must not be able to grant those privileges to other roles or users. The administrator wants to follow least-privilege and make future access reviews easy. Which approach should the administrator take?
- A
Grant USAGE on FINANCE_DB, its schemas, SELECT on the required tables, and USAGE on ANALYST_WH directly to each user.
- B
Create an account role for the analytics team, grant the required database and warehouse privileges to that role, and grant the role to the users without using WITH GRANT OPTION.
- C
Grant the SYSADMIN role to the analytics team users because SYSADMIN already inherits object privileges needed for analytics workloads.
- D
Create a database role in FINANCE_DB, grant it to the users, and rely on the database role to provide warehouse access as well.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to create an account role, grant the necessary object privileges to that role, and then grant the role to the users. In Snowflake RBAC, roles are the recommended unit for assigning privileges because they simplify administration and support least-privilege design. Account roles can hold privileges on warehouses and can also receive database roles, making them the right choice for end-to-end access patterns. Direct grants to users are harder to manage at scale, while SYSADMIN is overly permissive for analytics users. Database roles are limited to privileges within their database and cannot carry privileges on account-level objects such as warehouses. This aligns with Snowflake documentation on access control, role hierarchy, and the distinction between account roles and database roles.
- A. Incorrect.
Incorrect. Directly granting privileges to individual users works technically, but it does not align well with least-privilege administration or scalable access management. Best practice is to grant privileges to roles and then grant roles to users. Using user-level grants makes future auditing, onboarding, and offboarding harder.
- B. Correct.
Correct. An account role is the appropriate mechanism to bundle access across securable objects such as databases, schemas, tables, and warehouses, then assign that access to users. Not using WITH GRANT OPTION ensures the analytics team can use the privileges but cannot delegate them to other roles. This approach is consistent with Snowflake best practices for role-based access control (RBAC).
- C. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for managing objects, not for routine end-user analytics access. Granting SYSADMIN violates least-privilege because it provides significantly broader permissions than required, increasing security and governance risk.
- D. Incorrect.
Incorrect. Database roles are scoped to a single database and are useful for managing privileges on database objects within that database. However, warehouse privileges are not database-scoped and cannot be granted through a database role. To provide warehouse access, an account role is still required.