SnowPro Associate: Platform Question 137
Select 1Object access by roleA Snowflake administrator creates a custom role named ANALYST_ROLE for a reporting team. The team must be able to query the table SALES_DB.PUBLIC.ORDERS. The administrator executes the following grants:
- GRANT USAGE ON WAREHOUSE REPORTING_WH TO ROLE ANALYST_ROLE;
- GRANT SELECT ON TABLE SALES_DB.PUBLIC.ORDERS TO ROLE ANALYST_ROLE;
- GRANT ROLE ANALYST_ROLE TO USER JLEE;
When JLEE logs in and switches to ANALYST_ROLE, the query SELECT * FROM SALES_DB.PUBLIC.ORDERS; fails with an access control error. Which additional grants are required so JLEE can query the table successfully?
- A
GRANT USAGE ON DATABASE SALES_DB TO ROLE ANALYST_ROLE; and GRANT USAGE ON SCHEMA SALES_DB.PUBLIC TO ROLE ANALYST_ROLE;
- B
GRANT OWNERSHIP ON DATABASE SALES_DB TO ROLE ANALYST_ROLE; and GRANT OWNERSHIP ON SCHEMA SALES_DB.PUBLIC TO ROLE ANALYST_ROLE;
- C
GRANT SELECT ON ALL TABLES IN SCHEMA SALES_DB.PUBLIC TO ROLE ANALYST_ROLE;
- D
GRANT USAGE ON INTEGRATION SALES_DB TO ROLE ANALYST_ROLE;
- E
GRANT CREATE TABLE ON SCHEMA SALES_DB.PUBLIC TO ROLE ANALYST_ROLE;
Show answer and explanation
Correct answer: A
Explanation
In Snowflake's access control model, privileges are granted to roles, and users inherit access by using those roles. To query a table successfully, the active role must have: (1) USAGE on the virtual warehouse to execute the query, (2) USAGE on the database, (3) USAGE on the schema, and (4) SELECT on the table itself. Even if a role has SELECT on a table, the query will fail if the role cannot access the containing database and schema. This question tests a common real-world issue: object privileges alone are not enough without the required container-level USAGE privileges. This aligns with Snowflake documentation and best practices around role-based access control and least-privilege design.
- A. Correct.
Correct. To query an object in Snowflake, a role needs the required privilege on the object itself and the ability to traverse the hierarchy that contains it. For a table, that means the role must have USAGE on the database and USAGE on the schema, in addition to SELECT on the table. The warehouse USAGE grant is also required to run the query. Since ANALYST_ROLE already has warehouse USAGE and table SELECT, the missing grants are USAGE on SALES_DB and USAGE on SALES_DB.PUBLIC.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and is not required for querying data. Granting OWNERSHIP would be excessive and violates least-privilege best practices. A common misconception is that broad administrative privileges are needed when access errors occur, but for simple querying, USAGE on container objects plus SELECT on the table is sufficient.
- C. Incorrect.
Incorrect. ANALYST_ROLE already has SELECT on the specific table SALES_DB.PUBLIC.ORDERS. Granting SELECT on all tables in the schema is broader than necessary and still would not solve the problem if the role lacks USAGE on the database and schema. This option reflects the misconception that object-level access alone is enough without container-level USAGE privileges.
- D. Incorrect.
Incorrect. Integrations are separate Snowflake objects used for external connectivity features such as storage integrations or security integrations. They are unrelated to querying a standard table in a database schema. SALES_DB is a database, not an integration object, so this grant is invalid in this scenario.
- E. Incorrect.
Incorrect. CREATE TABLE on the schema allows the role to create new tables in the schema, but it does not provide the ability to access existing tables unless the role also has the proper USAGE and SELECT privileges. This is a plausible distractor because schema privileges are involved, but CREATE TABLE does not address the access path required for querying.