COF-C03 Question 137
Single answer2.1 Explain Snowflake security model and principlesA Snowflake administrator needs to let a group of data analysts query tables in the FINANCE_DB database without allowing them to modify data, manage warehouses, or grant access to other users. The company also wants access to be easy to maintain as analysts join or leave the team. Which approach should the administrator use?
- A
Grant SELECT on the required tables directly to each analyst user account, and grant USAGE on the warehouse to each user.
- B
Create a custom role for the analysts, grant it USAGE on the database, schema, and warehouse, and grant SELECT on the required tables or views to that role. Then grant the role to the analyst users.
- C
Grant the SYSADMIN role to the analysts so they can inherit the privileges needed to query the FINANCE_DB objects without needing additional grants.
- D
Create a resource monitor for the analysts and assign it to the warehouse so they can query data but not modify it or grant privileges.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use a custom role and grant privileges to that role rather than to individual users. Snowflake's security model centers on roles, with privileges granted to roles and roles granted to users. This supports least privilege, simplifies administration, and aligns with recommended RBAC design. To query objects, users typically need USAGE on the warehouse, database, and schema, along with SELECT on the target tables or views. They do not need administrative roles such as SYSADMIN, and cost-governance features like resource monitors are separate from access control. This reflects Snowflake best practices documented for access control, role hierarchy, and privilege management.
- A. Incorrect.
This approach would work technically, but it does not follow Snowflake security best practice for scalable access control. Snowflake uses role-based access control (RBAC), and privileges should generally be granted to roles rather than directly to users. Direct user grants become difficult to audit and maintain as team membership changes.
- B. Correct.
This is correct. Snowflake security is based primarily on RBAC and the principle of least privilege. A custom role can be created for the analyst job function and granted only the minimum privileges required: USAGE on the warehouse, database, and schema, plus SELECT on the necessary tables or views. Granting the role to users makes onboarding and offboarding simpler and avoids unnecessary administrative privileges.
- C. Incorrect.
This is incorrect because SYSADMIN is a powerful system-defined role intended for managing objects and operational administration. It provides far more access than is required for read-only analytics and violates the principle of least privilege. Analysts would gain excessive capabilities beyond querying specific finance data.
- D. Incorrect.
This is incorrect because a resource monitor controls credit usage for warehouses; it does not control object-level data access or privilege delegation. It cannot be used to enforce read-only access to database objects or prevent users from granting privileges.