SnowPro Associate: Platform Question 102
Single answer● SchemasA data engineering team stores raw ingestion tables in the schema PROD_DB.RAW. A contractor role named ETL_CONTRACTOR must load data into existing tables in RAW for the next 2 months, but the security team wants to prevent that role from creating new objects or accessing any other schemas in the database. Which privilege assignment best meets this requirement?
- A
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.RAW, and INSERT on all existing tables in PROD_DB.RAW to the ETL_CONTRACTOR role.
- B
Grant OWNERSHIP on schema PROD_DB.RAW to the ETL_CONTRACTOR role so it can load data into tables without access to other schemas.
- C
Grant USAGE on schema PROD_DB.RAW and CREATE TABLE on schema PROD_DB.RAW to the ETL_CONTRACTOR role.
- D
Grant SELECT, INSERT, UPDATE, and DELETE on database PROD_DB to the ETL_CONTRACTOR role.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, schema access is controlled hierarchically. A role must have USAGE on the database and USAGE on the schema before it can access objects within that schema. Privileges to act on tables, such as INSERT, are granted on the table objects themselves. To prevent object creation, do not grant CREATE privileges on the schema. This aligns with Snowflake's least-privilege best practice for role design. Relevant Snowflake documentation covers access control privileges for databases, schemas, and tables, including the need for USAGE on parent containers and object-specific privileges for DML operations.
- A. Correct.
Correct. To work with objects in a schema, a role needs USAGE on the parent database and the schema. To load data into existing tables, the role also needs the appropriate table privilege, such as INSERT, on those tables. This combination limits access to the RAW schema and does not allow creation of new objects because no CREATE privilege on the schema is granted.
- B. Incorrect.
Incorrect. OWNERSHIP is the highest privilege on an object and would allow the role to fully control the schema, including granting privileges and managing objects. This violates least-privilege principles and exceeds the requirement to only load data into existing tables.
- C. Incorrect.
Incorrect. CREATE TABLE on a schema explicitly allows creation of new tables in that schema, which the security team wants to prevent. Also, USAGE on the database is still required to access objects in the schema.
- D. Incorrect.
Incorrect. Database-level object privileges like SELECT, INSERT, UPDATE, and DELETE are not granted in this broad way to satisfy access to tables in a specific schema. The role would also need USAGE on the database and schema, and this option would be far too broad because it targets the entire database rather than only the RAW schema.