SnowPro Associate: Platform Question 139
Single answerObject access by roleA company uses role-based access control in Snowflake. The ANALYST role needs to query the table SALES_DB.PUBLIC.ORDERS using the warehouse ANALYST_WH. A user is assigned the ANALYST role but reports they can successfully use the warehouse and database, yet receive an error when running SELECT * FROM SALES_DB.PUBLIC.ORDERS. The security administrator confirms the ANALYST role already has USAGE on the warehouse, USAGE on the database, and USAGE on the schema. Which additional privilege must be granted to the ANALYST role to allow the query to succeed?
- A
Grant SELECT on table SALES_DB.PUBLIC.ORDERS to role ANALYST
- B
Grant OPERATE on warehouse ANALYST_WH to role ANALYST
- C
Grant OWNERSHIP on schema SALES_DB.PUBLIC to role ANALYST
- D
Grant CREATE TABLE on schema SALES_DB.PUBLIC to role ANALYST
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, object access is controlled through roles and explicit privileges. To successfully run a query against a table, a role typically needs: USAGE on the warehouse used for compute, USAGE on the database, USAGE on the schema, and SELECT on the table itself. In this scenario, the role already has all required container and compute access, so the remaining missing privilege is SELECT on SALES_DB.PUBLIC.ORDERS. This aligns with Snowflake's role-based access control model and least-privilege best practices: grant only the privileges needed for the task rather than broader privileges such as OWNERSHIP. See Snowflake documentation on access control privileges and table privileges for details.
- A. Correct.
Correct. To query data from an existing table, the role must have the SELECT privilege on that table (or on all/future tables in the schema, depending on how access is managed). Since the role already has USAGE on the warehouse, database, and schema, the missing object-level privilege for reading the table is SELECT.
- B. Incorrect.
Incorrect. OPERATE on a warehouse allows actions such as suspending or resuming the warehouse, but it is not required simply to run queries. For query execution, USAGE on the warehouse is sufficient. This option reflects a common confusion between using a warehouse and administering it.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would be excessive for a role that only needs to query data. Snowflake best practice is to grant the minimum required privilege. A role does not need ownership of the schema to select from a table within it.
- D. Incorrect.
Incorrect. CREATE TABLE on the schema allows the role to create new tables in that schema, but it does not provide permission to read data from an existing table. This distractor targets the misconception that schema-level create privileges imply read access to existing objects.