COF-C03 Question 141
Single answerSecurable object hierarchyA Snowflake administrator creates a custom role named ANALYST_R and grants it USAGE on database SALES and USAGE on schema SALES.PUBLIC. The administrator does not grant any privileges directly on the tables in SALES.PUBLIC. An analyst is assigned ANALYST_R and runs the following query: SELECT * FROM SALES.PUBLIC.ORDERS; The query fails with an access control error. Which additional privilege must be granted to ANALYST_R so the analyst can query the ORDERS table while following the securable object hierarchy?
- A
Grant USAGE on warehouse COMPUTE_WH to ANALYST_R
- B
Grant SELECT on table SALES.PUBLIC.ORDERS to ANALYST_R
- C
Grant OWNERSHIP on schema SALES.PUBLIC to ANALYST_R
- D
Grant CREATE TABLE on schema SALES.PUBLIC to ANALYST_R
Show answer and explanation
Correct answer: B
Explanation
In Snowflake, securable objects are organized hierarchically. To access a table using a fully qualified name such as SALES.PUBLIC.ORDERS, a role typically needs USAGE on the database, USAGE on the schema, and the relevant privilege on the table itself. For querying data, that table-level privilege is SELECT. This is a common access-control pattern tested on SnowPro Core: container privileges such as USAGE let a role traverse the hierarchy and resolve object names, but they do not grant permissions on child objects automatically. Snowflake documentation on access control and object privileges distinguishes between privileges on containers like databases and schemas and privileges on objects like tables and views. Following least privilege, the correct fix is to grant SELECT on the specific table rather than broader privileges such as OWNERSHIP.
- A. Incorrect.
Incorrect. A running warehouse is required to execute queries, so USAGE on a warehouse may also be needed in practice. However, the scenario specifically states the query fails because the role has no privilege on the table itself. In Snowflake's securable object hierarchy, USAGE on the parent database and schema allows resolution of object names, but it does not permit reading data from a table. The missing object-level privilege for querying the table is SELECT.
- B. Correct.
Correct. To query a table in Snowflake, the role needs SELECT on that table (or on all/future tables in the schema or database, depending on how access is managed), in addition to USAGE on the parent database and schema. This reflects Snowflake's hierarchy: access to child objects requires appropriate privileges on the object itself as well as USAGE on containers used to resolve the object path.
- C. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and is not required just to query data. Granting OWNERSHIP would be excessive and violates least-privilege best practices. A common misconception is that broader privileges on the schema automatically allow reading all contained objects, but table access still depends on table-level privileges such as SELECT.
- D. Incorrect.
Incorrect. CREATE TABLE on a schema allows creating new tables in that schema, not reading data from existing tables. This option is plausible because schema-level privileges affect objects within the schema, but CREATE TABLE does not confer any read access to existing tables like ORDERS.