COF-C03 Question 161
Single answerFunctional rolesA Snowflake administrator is redesigning access for the finance team. The team needs analysts to query only finance reporting tables, data engineers to load data into finance schemas, and a small group of finance managers to both query the data and manage grants on finance objects. The company wants to follow Snowflake best practices using functional roles so that access is easier to audit and maintain as users change jobs. Which approach BEST meets these requirements?
- A
Create a separate custom role for each user, grant object privileges directly to those user-specific roles, and assign the roles to the individual users.
- B
Create functional roles such as FINANCE_ANALYST, FINANCE_ENGINEER, and FINANCE_MANAGER; grant the required object privileges to these roles; and assign the roles to users based on job responsibilities.
- C
Grant SELECT, INSERT, and OWNERSHIP privileges on all finance objects directly to the PUBLIC role so all finance staff can work without switching roles.
- D
Use only system-defined roles such as SYSADMIN and SECURITYADMIN for finance users, and let managers inherit all access by activating higher-level administrative roles when needed.
Show answer and explanation
Correct answer: B
Explanation
Snowflake best practice is to use role-based access control (RBAC) with custom functional roles that align to business functions, then assign those roles to users. This improves manageability, auditing, and least-privilege enforcement. In this scenario, roles such as FINANCE_ANALYST, FINANCE_ENGINEER, and FINANCE_MANAGER allow privileges to be grouped by responsibility rather than by person. This is easier to maintain when users join, leave, or change jobs. Snowflake documentation on access control and RBAC emphasizes assigning privileges to roles, not directly to users, and using custom roles to reflect organizational functions rather than relying on broad system roles or the PUBLIC role.
- A. Incorrect.
This is not the best approach for functional roles. Creating a role per user leads to role sprawl and makes auditing and maintenance harder, especially when employees change responsibilities. Snowflake best practice is to model access around job functions, not individuals, and then grant those roles to users.
- B. Correct.
This is correct. Functional roles are designed around business functions or job responsibilities, such as analyst, engineer, or manager. Granting object privileges to these roles and then assigning roles to users simplifies administration, supports least privilege, and makes access reviews easier. Managers can be granted both usage/query capabilities and grant-management privileges through an appropriate functional role design.
- C. Incorrect.
This is incorrect because the PUBLIC role is inherited by every role in the account, so granting finance access to PUBLIC would broadly expose access beyond the intended users. It also violates least-privilege principles and is not an appropriate way to isolate finance-specific responsibilities.
- D. Incorrect.
This is incorrect because system-defined administrative roles like SYSADMIN and SECURITYADMIN are intended for administration, not day-to-day business access for end users. Using these roles for finance users grants excessive privileges and weakens separation of duties. Functional roles should be created for business use cases instead.