SnowPro Associate: Platform Question 136
Single answerPrivilegesA Snowflake administrator needs to let a data analyst query tables in the FINANCE_DB database, but the analyst must not be able to create objects or modify any existing data. The analyst should also be able to use an existing virtual warehouse named ANALYST_WH. Which set of grants will meet these requirements with the least privilege?
- A
Grant USAGE on warehouse ANALYST_WH, USAGE on database FINANCE_DB, USAGE on schema(s), and SELECT on the required tables to the analyst role.
- B
Grant OPERATE on warehouse ANALYST_WH, OWNERSHIP on database FINANCE_DB, and SELECT on the required tables to the analyst role.
- C
Grant USAGE on warehouse ANALYST_WH, MODIFY on the required tables, and CREATE TABLE on the schema(s) to the analyst role.
- D
Grant MONITOR on warehouse ANALYST_WH, USAGE on database FINANCE_DB, and REFERENCES on the required tables to the analyst role.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, querying data requires access at multiple levels: a role must be able to use a warehouse (USAGE), traverse the database and schema hierarchy (USAGE on database and schema), and read the underlying objects (SELECT on tables or views). Granting only object-level SELECT is not sufficient if the role lacks USAGE on the parent database and schema. Conversely, powerful privileges such as OWNERSHIP, CREATE TABLE, or MODIFY violate least-privilege requirements when the user only needs read-only access. Snowflake best practices recommend granting the minimum set of privileges needed for a role's tasks. See Snowflake documentation on access control, privileges, and object hierarchy requirements for querying data.
- A. Correct.
Correct. To run queries, the role needs USAGE on the warehouse, USAGE on the database, and USAGE on the relevant schema(s). To read data without changing it, the role needs SELECT on the target tables or views. This follows least-privilege design because it allows querying but does not permit creating objects or modifying data.
- B. Incorrect.
Incorrect. OPERATE on a warehouse is more privilege than required for simply running queries; it allows actions such as suspending and resuming the warehouse. OWNERSHIP on the database is the highest level of privilege and would allow broad control, which violates the requirement to prevent object creation and modification.
- C. Incorrect.
Incorrect. MODIFY on tables allows changes to table properties and can support administrative changes, and CREATE TABLE on schemas would let the analyst create new objects. Both exceed the stated requirement of read-only access.
- D. Incorrect.
Incorrect. MONITOR on a warehouse allows viewing warehouse usage and status, but it does not allow using the warehouse to execute queries. REFERENCES is not the privilege needed for querying table data; SELECT is required to read rows from tables.