COF-C03 Question 167
Single answerCustom rolesA Snowflake administrator is designing access control for a finance analytics team. The team needs to query tables in the FINANCE_DB database and create temporary reporting tables in the FINANCE_SANDBOX schema, but they must not be able to grant their privileges to other users. The administrator wants to follow least-privilege and use custom roles instead of granting privileges directly to users. Which approach best meets these requirements?
- A
Create a custom role FINANCE_ANALYST, grant USAGE on the warehouse, database, and schemas needed, grant SELECT on the required finance tables, grant CREATE TABLE on the FINANCE_SANDBOX schema, and grant the role to the finance users without WITH GRANT OPTION.
- B
Grant SELECT on FINANCE_DB tables and CREATE TABLE on FINANCE_SANDBOX directly to each finance user, because object privileges are evaluated the same whether assigned to users or roles.
- C
Create a custom role FINANCE_ANALYST, grant OWNERSHIP on FINANCE_DB and FINANCE_SANDBOX to the role so the team can query and create reporting tables, and then grant the role to the finance users.
- D
Create a custom role FINANCE_ANALYST, grant SELECT on the finance tables and CREATE TABLE on FINANCE_SANDBOX, and grant the role to finance users with WITH GRANT OPTION so they can activate the role in their sessions.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a custom role and grant only the minimum privileges required for the job function. In Snowflake RBAC, best practice is to grant object privileges to roles, then grant roles to users. For querying data, the active role must have USAGE on the warehouse, database, and schema, along with SELECT on the target tables or views. For creating tables in a specific schema, the role needs CREATE TABLE on that schema. To prevent privilege delegation, the administrator should not grant the role to users WITH GRANT OPTION. This design aligns with Snowflake's role-based access control model and least-privilege guidance for custom roles.
- A. Correct.
Correct. This approach uses a custom role to group required privileges and follows least-privilege. To query objects, users need the relevant USAGE privileges on the warehouse, database, and schema, plus SELECT on the tables or views. To create tables in a schema, the role needs CREATE TABLE on that schema. Granting the role to users without WITH GRANT OPTION prevents those users from regranting the role to others.
- B. Incorrect.
Incorrect. Although privileges can be granted directly to users in some cases, Snowflake best practice is to grant privileges to roles and then assign roles to users. Direct user grants are harder to manage and audit, and this option ignores the stated requirement to use custom roles. It also weakens scalable access control design.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest level of privilege on an object and is far broader than necessary for querying tables and creating reporting tables in a sandbox schema. Granting OWNERSHIP on an entire database and schema violates least-privilege and could allow unintended administrative actions such as transferring ownership or modifying object definitions.
- D. Incorrect.
Incorrect. The role itself is the correct abstraction, but granting a role to users WITH GRANT OPTION would allow those users to grant that role to other roles or users, which directly conflicts with the requirement that they must not be able to grant their privileges onward. WITH GRANT OPTION is not required for a user to activate a granted role in a session.