ADA-C01 Question 260
Single answerSet permissions for creating and executing tasksA data engineering team wants to let developers in the role DEV_TASK_ADMIN create and manage scheduled tasks in schema PROD_DB.ETL without granting them broad administrative access. The tasks will run SQL statements that load and transform data using warehouse ETL_WH. A security review also requires that scheduled runs succeed without relying on the task owner's personal access. Which set of privileges should the Snowflake administrator grant to DEV_TASK_ADMIN to meet these requirements with least privilege?
- A
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.ETL, CREATE TASK on schema PROD_DB.ETL, USAGE on warehouse ETL_WH, and EXECUTE TASK at the account level to DEV_TASK_ADMIN.
- B
Grant OWNERSHIP on database PROD_DB and warehouse ETL_WH to DEV_TASK_ADMIN so task execution inherits all required permissions automatically.
- C
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.ETL, CREATE PROCEDURE on schema PROD_DB.ETL, OPERATE on warehouse ETL_WH, and EXECUTE MANAGED TASK at the account level to DEV_TASK_ADMIN.
- D
Grant USAGE on database PROD_DB, USAGE on schema PROD_DB.ETL, CREATE TASK on schema PROD_DB.ETL, MONITOR on warehouse ETL_WH, and EXECUTE TASK on warehouse ETL_WH to DEV_TASK_ADMIN.
Show answer and explanation
Correct answer: A
Explanation
For task administration, Snowflake separates object creation privileges from execution privileges. To create a task, a role needs USAGE on the database and schema and CREATE TASK on the schema. For a task that uses a named warehouse, the owning role must also have USAGE on that warehouse so the task can run queries with that compute resource. Separately, Snowflake requires the global EXECUTE TASK privilege to execute tasks. This is important in production because scheduled task runs should rely on role-based privileges rather than a user's interactive session or personal access. If the design used serverless tasks instead of a named warehouse, EXECUTE MANAGED TASK would be relevant, but that is not the case here. These requirements align with Snowflake documentation for access control and task privileges, including CREATE TASK, EXECUTE TASK, warehouse USAGE, and the distinction between user-managed and serverless tasks.
- A. Correct.
Correct. To create a task in a schema, the role needs USAGE on the parent database and schema plus CREATE TASK on the schema. For user-managed tasks that run on a specified warehouse, the task owner role also needs USAGE on that warehouse. In addition, a role must have the global EXECUTE TASK privilege to run tasks. This combination enables creation and scheduled execution while avoiding excessive privileges.
- B. Incorrect.
Incorrect. OWNERSHIP is far broader than necessary and violates least privilege. Task execution does not require database ownership or warehouse ownership. Granting OWNERSHIP would allow unrestricted control over those objects, which is unnecessary for simply creating and running tasks.
- C. Incorrect.
Incorrect. CREATE PROCEDURE is unrelated to creating tasks. OPERATE on a warehouse is for suspending, resuming, or otherwise operating the warehouse, not for using it to run queries. EXECUTE MANAGED TASK applies to serverless tasks, whereas this scenario explicitly states the tasks will use warehouse ETL_WH, indicating user-managed tasks.
- D. Incorrect.
Incorrect. CREATE TASK is needed, but MONITOR on the warehouse does not allow the warehouse to be used for task execution. Also, EXECUTE TASK is not a warehouse-level privilege; it is a global privilege granted at the account level. This option reflects a common misunderstanding about the scope of task privileges.