ADA-C01 Question 4
Single answerDomain 1.0: Snowflake Security, Role-Based Access Control (RBAC), and User Administration (31%)A Snowflake administrator is asked to let a data engineering team create and manage pipelines in schema PROD_DB.ETL while preventing them from changing ownership of existing objects or granting access to other roles. The team must be able to create tables, views, stages, file formats, tasks, and streams in the schema, and they must be able to operate their own tasks after creation. The security team also wants to follow least-privilege principles and avoid using ACCOUNTADMIN or SECURITYADMIN for day-to-day operations. Which setup best meets these requirements?
- A
Grant the team role OWNERSHIP on schema PROD_DB.ETL and USAGE on database PROD_DB so they can create and manage all objects in the schema.
- B
Grant the team role USAGE on database PROD_DB and schema PROD_DB.ETL, CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE FILE FORMAT, CREATE TASK, and CREATE STREAM on schema PROD_DB.ETL, plus OPERATE on tasks after task creation as needed.
- C
Grant the team role ALL PRIVILEGES on database PROD_DB and schema PROD_DB.ETL so they can create and run any ETL object without requiring additional grants.
- D
Grant the team role CREATE TASK and CREATE STREAM on database PROD_DB, then rely on future grants so the role automatically receives ownership of objects it creates in schema PROD_DB.ETL.
Show answer and explanation
Correct answer: B
Explanation
The best answer is Option 2 because it applies Snowflake RBAC correctly and follows least-privilege design. In Snowflake, creating objects inside a schema requires USAGE on the containing database and schema, along with the appropriate CREATE privileges on the schema. For example, CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE FILE FORMAT, CREATE TASK, and CREATE STREAM are granted at the schema level. When a role creates an object, that role becomes the owner of that object, which lets the team manage the objects they create without needing OWNERSHIP on the schema itself. For task administration, OPERATE is the relevant privilege for running, suspending, or resuming tasks. This design prevents unnecessary administrative power, avoids granting schema OWNERSHIP or broad ALL PRIVILEGES, and is consistent with Snowflake best practices for access control and separation of duties as described in Snowflake documentation on access control privileges, schema object creation privileges, and task privileges.
- A. Incorrect.
Incorrect. OWNERSHIP on the schema is overly permissive and directly conflicts with the requirement to prevent the team from changing ownership or granting access to other roles. OWNERSHIP is the highest level of control over an object and includes the ability to transfer ownership. While it would allow object management, it violates least-privilege principles and gives broader control than necessary.
- B. Correct.
Correct. To create objects in a schema, a role needs USAGE on the parent database and schema, plus the relevant CREATE
- C. Incorrect.
Incorrect. ALL PRIVILEGES on the database and schema is broader than required and may still not represent a precise least-privilege design. It also grants unnecessary capabilities beyond the stated ETL object creation needs. In Snowflake, best practice is to grant only the specific privileges required instead of broad privilege bundles, especially in controlled production environments.
- D. Incorrect.
Incorrect. CREATE TASK and CREATE STREAM are schema-level privileges, not database-level privileges, so this grant model is invalid for enabling object creation in PROD_DB.ETL. In addition, future grants do not cause a role to automatically receive ownership of objects it creates; ownership of a newly created object is assigned to the creating role at creation time. This option mixes privilege scope incorrectly and reflects a common misunderstanding of future grants.