SnowPro Associate: Platform Question 140
Single answerObject access by roleA company uses role-based access control in Snowflake. The SALES_DB database contains a schema named ANALYTICS with a table named MONTHLY_REVENUE. Analysts are assigned the custom role ANALYST_R. After a recent security review, the team wants ANALYST_R to query only MONTHLY_REVENUE without granting unnecessary access to other objects. A security administrator grants SELECT on SALES_DB.ANALYTICS.MONTHLY_REVENUE to ANALYST_R, but users with that role still receive an access error when running SELECT statements against the table. Which additional privilege grant is required for ANALYST_R to successfully query the table?
- A
Grant USAGE on the warehouse used for the query to ANALYST_R
- B
Grant USAGE on the SALES_DB database and USAGE on the ANALYTICS schema to ANALYST_R
- C
Grant OWNERSHIP on the MONTHLY_REVENUE table to ANALYST_R
- D
Grant SELECT on all tables in the ANALYTICS schema to ANALYST_R
Show answer and explanation
Correct answer: B
Explanation
Snowflake uses role-based access control with object privileges applied at different levels. For a role to query a table, it must be able to traverse the object hierarchy: USAGE on the database, USAGE on the schema, and SELECT on the table. This is a common exam scenario because administrators often grant table-level privileges but forget the required USAGE privileges on parent containers. Best practice is to follow least privilege by granting only the minimum necessary privileges, rather than broad grants like OWNERSHIP or SELECT on all tables. See Snowflake documentation on access control privileges and the requirement for USAGE on databases and schemas in addition to object-specific privileges.
- A. Incorrect.
This is not the required additional object-access grant for reaching the table itself. A role does need USAGE on a warehouse to execute queries, but the scenario specifically says SELECT on the table was granted and access errors still occur when querying that object. To access an object in Snowflake, the role must also have USAGE on the parent database and schema. Warehouse access is separate from object navigation and does not replace database/schema USAGE.
- B. Correct.
Correct. In Snowflake, object access is hierarchical for databases and schemas. To query a table, the active role must have USAGE on the containing database, USAGE on the containing schema, and the appropriate privilege on the table itself, such as SELECT. Granting SELECT only on the table is insufficient if the role cannot access the database and schema that contain it.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would be excessive for a read-only analyst role. It also transfers control of the object and is not required just to query data. This option reflects a common misconception that broader privileges solve access issues; in Snowflake, least privilege should be used and USAGE on parent objects is what is missing.
- D. Incorrect.
Incorrect. Granting SELECT on all tables in the schema broadens access beyond the stated requirement and still does not address the specific issue if USAGE on the database and schema is missing. The problem is not lack of SELECT on additional tables; it is missing access to the parent objects needed to resolve and use the table.