SnowPro Associate: Platform Question 122
Single answerRole-Based Access Control (RBAC)A Snowflake administrator needs to allow a group of analysts to query tables in the FINANCE_DB database without giving them the ability to modify data or manage object privileges. The analysts should also be able to use an existing virtual warehouse named ANALYST_WH. The administrator wants to follow Snowflake RBAC best practices by assigning privileges through roles rather than directly to users. Which approach best meets these requirements?
- A
Grant the OWNERSHIP privilege on FINANCE_DB and its schemas to a custom ANALYST role, then grant the role to the analyst users.
- B
Create a custom ANALYST role, grant USAGE on ANALYST_WH, grant USAGE on FINANCE_DB and the required schemas, grant SELECT on the required tables, and then grant the role to the analyst users.
- C
Grant the SYSADMIN role to the analyst users so they can inherit access to the warehouse and database objects they need.
- D
Grant SELECT on FINANCE_DB directly to the analyst users, and grant OPERATE on ANALYST_WH so they can run queries.
Show answer and explanation
Correct answer: B
Explanation
In Snowflake RBAC, privileges are granted to roles, and roles are granted to users. For read-only query access, the appropriate design is to create a custom role for the analyst function and grant only the necessary privileges: USAGE on the virtual warehouse, USAGE on the database, USAGE on the relevant schemas, and SELECT on the required tables or views. This aligns with Snowflake best practices around least privilege and role-based administration. High-level roles such as SYSADMIN should not be used for end-user data access unless that level of authority is actually required. Likewise, OWNERSHIP is too powerful for consumers of data, and database-level USAGE alone does not permit selecting from tables without schema and object-level privileges. Snowflake documentation on access control and privileges emphasizes role hierarchy, least privilege, and granting object privileges at the appropriate scope.
- A. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and effectively gives full control, including the ability to manage grants and transfer ownership. This violates the requirement to provide read-only access and not allow the analysts to manage object privileges. A candidate might choose this option by confusing object ownership with access delegation, but Snowflake best practice is to grant only the minimum required privileges.
- B. Correct.
Correct. This follows the principle of least privilege and Snowflake RBAC best practices. To query tables, the role needs USAGE on the warehouse, database, and schemas, plus SELECT on the target tables or views. Granting the custom role to users is the recommended way to manage access, because privileges are assigned to roles and roles are assigned to users.
- C. Incorrect.
Incorrect. SYSADMIN is a powerful system-defined role intended for managing warehouses and many account objects. Granting it to analysts would provide excessive privileges far beyond read-only data access, violating least-privilege principles. This is a common misconception when administrators try to solve access problems quickly by using high-level system roles instead of custom functional roles.
- D. Incorrect.
Incorrect. SELECT is not granted at the database level for querying all contained objects; object privileges such as SELECT are granted on tables or views. In addition, OPERATE on a warehouse allows actions such as suspend and resume, but it does not provide the basic ability to use the warehouse for query execution in the way required here; USAGE is needed. This option combines two common RBAC misunderstandings.