SnowPro Associate: Platform Question 124
Single answerRole-Based Access Control (RBAC)A Snowflake administrator is onboarding a new analytics team. Team members need to query tables in the SALES database, create their own temporary and permanent tables in a shared ANALYTICS schema, and run queries using an existing virtual warehouse named BI_WH. The administrator wants to follow least-privilege RBAC practices and avoid granting broad administrative roles. Which set of grants should be made to a custom role assigned to the analytics team?
- A
Grant USAGE on database SALES, USAGE on schema SALES.ANALYTICS, SELECT on all required tables, USAGE on warehouse BI_WH, and CREATE TABLE on schema SALES.ANALYTICS.
- B
Grant OWNERSHIP on schema SALES.ANALYTICS, SELECT on all required tables, and OPERATE on warehouse BI_WH.
- C
Grant the SYSADMIN role to the team, because SYSADMIN automatically provides access to databases, schemas, and warehouses needed for analytics work.
- D
Grant USAGE on warehouse BI_WH and CREATE TABLE on database SALES only, because schema-level privileges are inherited automatically from the database.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake RBAC, privileges are granted to roles, and roles are then assigned to users. For analysts to query existing tables, Snowflake requires the role to have USAGE on the database, USAGE on the schema, and SELECT on the tables or views. To execute queries with a virtual warehouse, the role needs USAGE on that warehouse. To create permanent tables in an existing schema, the role needs CREATE TABLE on the schema. This is the least-privilege approach recommended in Snowflake documentation: use custom roles with only the object privileges required for the job function, rather than assigning broad system roles such as SYSADMIN. A common misconception is that database-level privileges are sufficient for schema object creation or that OPERATE on a warehouse allows query execution; in practice, schema-level privileges and warehouse USAGE are both required.
- A. Correct.
Correct. To query objects, the role needs USAGE on the database and schema containing those objects, plus SELECT on the tables or views. To use the existing warehouse for query execution, the role needs USAGE on the warehouse. To create permanent tables in the shared schema, the role needs CREATE TABLE on that schema. This aligns with least-privilege RBAC design by granting only the required object privileges instead of broad administrative roles.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and is far broader than necessary for analysts who only need to query data and create tables. It would allow the team to manage the schema itself, including grant management and object control. Also, OPERATE on a warehouse is used for actions such as suspend/resume; it does not replace USAGE, which is required to run queries.
- C. Incorrect.
Incorrect. Granting SYSADMIN violates least-privilege principles for this scenario. SYSADMIN is a powerful system-defined role intended for creating and managing objects, not for routine analyst access. The question explicitly asks to avoid broad administrative roles, and a custom role with specific object privileges is the recommended approach.
- D. Incorrect.
Incorrect. CREATE TABLE must be granted at the schema level for creating tables in that schema. Granting it on the database does not allow table creation within a schema. In addition, schema access is not automatically inherited simply because a role has database access; the role still needs USAGE on the target schema.