COF-C03 Question 140
Single answerSecurable object hierarchyA Snowflake administrator is creating a role for a new analytics team. Team members must be able to query tables in the ANALYTICS database, but they must not be able to create objects or change database settings. The administrator wants to grant the minimum privileges needed at the correct levels of the securable object hierarchy so the role can access all existing tables in the PUBLIC schema. Which grant strategy should the administrator use?
- A
Grant USAGE on the ANALYTICS database, USAGE on the PUBLIC schema, and SELECT on all tables in the PUBLIC schema to the role.
- B
Grant SELECT on the ANALYTICS database and SELECT on the PUBLIC schema to the role.
- C
Grant OWNERSHIP on the PUBLIC schema and SELECT on all tables in the ANALYTICS database to the role.
- D
Grant USAGE on the warehouse only, because warehouse access is sufficient to query tables in Snowflake.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, securable objects are organized hierarchically, and privileges must often be granted at multiple levels to make child objects usable. For querying a table, the role typically needs USAGE on the warehouse used for compute, USAGE on the parent database, USAGE on the parent schema, and SELECT on the table or view. The key hierarchy concept tested here is that privileges on child objects do not bypass the need for access to parent containers. Following Snowflake best practices, administrators should grant the minimum required privileges rather than broad privileges such as OWNERSHIP. This aligns with Snowflake documentation on access control, securable objects, and privilege requirements for databases, schemas, and tables.
- A. Correct.
Correct. To query table data, a role needs the appropriate privileges at each relevant level of the object hierarchy: USAGE on the database, USAGE on the schema, and SELECT on the tables. Without USAGE on the parent database and schema, the role cannot resolve or access the child objects even if SELECT exists on the tables. This also follows least-privilege principles because it does not grant create or administrative capabilities.
- B. Incorrect.
Incorrect. SELECT is not a valid privilege on a database or schema for the purpose of querying tables. In Snowflake, access to a table requires USAGE on the containing database and schema, plus SELECT on the table itself. This option reflects a common misunderstanding that read access can be granted at higher levels using SELECT.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest-level privilege on an object and would allow the role to control the schema, which violates the requirement to avoid object creation or configuration changes. Also, granting SELECT on all tables in the database is broader than required because the scenario only mentions the PUBLIC schema.
- D. Incorrect.
Incorrect. Warehouse access is necessary to run queries, but it is not sufficient by itself. Snowflake separates compute access from data access. A role also needs privileges on the database, schema, and table objects in the securable object hierarchy to query data.