COF-C03 Question 136
Single answer2.1 Explain Snowflake security model and principlesA company is onboarding a new analytics team to Snowflake. The security architect wants to follow least-privilege principles and ensure that analysts can query curated reporting tables in the ANALYTICS database, but cannot modify objects, grant access to others, or access raw ingestion data stored in separate schemas. The architect also wants future analysts to receive the same permissions with minimal administrative effort. Which approach should the architect implement?
- A
Grant SELECT on the curated tables directly to each analyst user, and grant OWNERSHIP on the reporting schema to the team lead so that permissions can be managed centrally.
- B
Create a custom role for analysts, grant USAGE on the database and reporting schema plus SELECT on the curated tables to that role, grant the role to analyst users, and use future grants where appropriate for new curated objects.
- C
Grant the built-in SYSADMIN role to all analysts because it already has the privileges needed to query reporting data without accessing account-level security settings.
- D
Create a resource monitor for the analytics warehouse and assign it to analysts so that warehouse controls enforce access only to curated reporting tables.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Snowflake's role-based access control model with a custom role tailored to the analytics team's job function. Snowflake best practice is to grant privileges to roles, then assign roles to users, rather than granting privileges directly to users. For read-only access to curated data, the role should receive only the minimum required privileges, such as USAGE on the relevant database and schema and SELECT on the needed tables or views. Future grants can help automate access for newly created objects in the curated schema, supporting consistent onboarding with less manual effort. This design also supports separation of duties by avoiding high-level roles such as SYSADMIN and by not granting access to raw ingestion schemas. These principles align with Snowflake documentation on access control, system and custom roles, least privilege, and future grants.
- A. Incorrect.
Incorrect. Granting privileges directly to users is not aligned with Snowflake best practice, which favors role-based access control (RBAC) for scalable and manageable security. In addition, granting OWNERSHIP on the schema to a team lead is excessive because OWNERSHIP is the highest privilege on an object and allows full control, including transferring ownership and managing grants. That violates least-privilege for a team that only needs read access.
- B. Correct.
Correct. Snowflake security is based primarily on RBAC. The appropriate design is to create a custom analyst role, grant only the required privileges to that role, and then grant the role to users. For querying curated data, analysts typically need USAGE on the database and schema and SELECT on the relevant tables or views. Future grants can reduce administration when new objects are created in the curated schema. This approach prevents modification of objects and avoids granting access to raw data schemas unless explicitly configured.
- C. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for managing objects across the account. Granting it to analysts would provide far broader privileges than necessary and conflicts with least-privilege design. Even though SYSADMIN is not the top security role, it is still inappropriate for end users who only need read access to a limited set of data objects.
- D. Incorrect.
Incorrect. Resource monitors control credit usage for warehouses; they do not control data access to databases, schemas, tables, or views. Someone might choose this option because it involves governance controls, but resource monitors are about consumption management, not authorization within Snowflake's security model.