SnowPro Associate: Platform Question 101
Single answer● SchemasA data engineering team created a schema named SALES in the ANALYTICS database and loaded several tables into it. A BI role, BI_ANALYST, already has USAGE on the ANALYTICS database, but analysts still receive an error when they try to query ANALYTICS.SALES.ORDERS. The security administrator wants to grant only the minimum additional access required so the role can query existing tables in the SALES schema, without granting broader privileges than necessary. Which grant should the administrator apply?
- A
GRANT USAGE ON SCHEMA ANALYTICS.SALES TO ROLE BI_ANALYST;
- B
GRANT SELECT ON DATABASE ANALYTICS TO ROLE BI_ANALYST;
- C
GRANT SELECT ON ALL SCHEMAS IN DATABASE ANALYTICS TO ROLE BI_ANALYST;
- D
GRANT OWNERSHIP ON SCHEMA ANALYTICS.SALES TO ROLE BI_ANALYST;
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, schemas are namespaces within databases, and object access depends on privileges at multiple levels. To query a table using a fully qualified name, a role needs USAGE on the database, USAGE on the schema, and the relevant object privilege such as SELECT on the table or view. In this scenario, the role already has USAGE on the database, so the minimum additional schema-related privilege is USAGE on the SALES schema. This aligns with Snowflake access control best practices of least privilege. Refer to Snowflake documentation on access control privileges for databases, schemas, and tables, especially the distinction between container privileges like USAGE and object privileges like SELECT.
- A. Correct.
Correct. To access objects inside a schema, a role must have USAGE on both the parent database and the schema. Since BI_ANALYST already has USAGE on the ANALYTICS database, granting USAGE on the SALES schema is the minimum additional privilege needed at the schema level to allow object resolution. Assuming the role already has the required object privilege such as SELECT on the table, this is the missing schema-related permission that enables querying existing tables in that schema.
- B. Incorrect.
Incorrect. Snowflake does not support granting SELECT on a database as a way to read tables within it. SELECT is granted on objects such as tables and views, not at the database level for all contained objects. This option reflects a common misconception that database privileges cascade to table read access.
- C. Incorrect.
Incorrect. This is not a valid Snowflake privilege model. SELECT is not granted on schemas to read all contained tables. In Snowflake, schema privileges such as USAGE allow object name resolution, while object-level privileges like SELECT must be granted on tables or views. A candidate might choose this if they confuse schema/container privileges with object privileges.
- D. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and transfers control of the schema. It is far broader than required and violates the requirement to grant minimum necessary access. This would allow the BI role to manage the schema rather than simply access objects within it.