SnowPro Associate: Platform Question 138
Single answerObject access by roleA Snowflake administrator creates a custom role named ANALYST_R and grants it the following privileges: USAGE on database FINANCE_DB, USAGE on schema FINANCE_DB.REPORTING, and SELECT on table FINANCE_DB.REPORTING.MONTHLY_REVENUE. The role is then granted to user JLEE. When JLEE logs in and runs a query against FINANCE_DB.REPORTING.MONTHLY_REVENUE, the query fails with an access error. Which action is required so JLEE can successfully query the table?
- A
Grant the MONITOR privilege on FINANCE_DB.REPORTING.MONTHLY_REVENUE to ANALYST_R
- B
Grant USAGE on a warehouse to ANALYST_R or another active role available to JLEE
- C
Grant OWNERSHIP on schema FINANCE_DB.REPORTING to JLEE
- D
Grant CREATE TABLE on schema FINANCE_DB.REPORTING to ANALYST_R
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, querying a table requires both object access privileges and compute access. Specifically, the active role needs USAGE on the database, USAGE on the schema, and SELECT on the table or view. Separately, the user must have access to a virtual warehouse, typically via the USAGE privilege on that warehouse, in order to run the query. A common troubleshooting mistake is to focus only on table permissions and overlook warehouse access. Snowflake documentation on access control and virtual warehouses emphasizes that roles control object access, while warehouse privileges control the ability to use compute resources. Following least-privilege best practices, the correct fix is to grant warehouse USAGE rather than broader privileges such as OWNERSHIP.
- A. Incorrect.
Incorrect. MONITOR does not allow querying table data. For querying a table, the role needs the appropriate object access privileges such as SELECT on the table, along with the ability to use the containing database and schema. In addition, the session must have access to a virtual warehouse to execute the query. MONITOR is related to viewing metadata or monitoring certain objects, not reading table rows.
- B. Correct.
Correct. To run a query in Snowflake, the active role must have the required object privileges on the database, schema, and table, and the user must also have access to a virtual warehouse to execute the SQL statement. In this scenario, the role already has USAGE on the database and schema and SELECT on the table, so the missing requirement is warehouse access, typically USAGE on a warehouse granted to the active role.
- C. Incorrect.
Incorrect. JLEE does not need OWNERSHIP on the schema to query a table. OWNERSHIP is the highest privilege and is unnecessary for read-only access. This option reflects a common misconception that object access problems are solved by giving broad administrative privileges rather than the minimum required privileges.
- D. Incorrect.
Incorrect. CREATE TABLE on the schema allows creation of new tables in the schema, but it does not provide the ability to execute queries against an existing table. The issue in this scenario is not object creation; it is the ability to run the query, which requires warehouse access in addition to the existing object privileges.