ADA-C01 Question 261
Single answerSet permissions for creating and executing tasksA data engineering team wants to let analysts manage a scheduled data quality task in the ANALYTICS database without giving them broad administrative privileges. The task runs every hour and inserts failed validation results into ANALYTICS.DQ.INVALID_ROWS. A custom role, ANALYST_TASK_ADMIN, should be able to create, resume, suspend, and manually execute the task. The warehouse COMPUTE_WH is used when the task runs. Which privilege set is the minimum required to meet this requirement?
- A
Grant USAGE on database ANALYTICS, USAGE on schema ANALYTICS.DQ, CREATE TASK on schema ANALYTICS.DQ, USAGE on warehouse COMPUTE_WH, and INSERT on table ANALYTICS.DQ.INVALID_ROWS to ANALYST_TASK_ADMIN.
- B
Grant OWNERSHIP on database ANALYTICS, OPERATE on warehouse COMPUTE_WH, and INSERT on table ANALYTICS.DQ.INVALID_ROWS to ANALYST_TASK_ADMIN.
- C
Grant CREATE TASK on database ANALYTICS, MODIFY on schema ANALYTICS.DQ, and USAGE on warehouse COMPUTE_WH to ANALYST_TASK_ADMIN.
- D
Grant USAGE on database ANALYTICS, USAGE on schema ANALYTICS.DQ, CREATE TASK on schema ANALYTICS.DQ, OPERATE on warehouse COMPUTE_WH, and INSERT on table ANALYTICS.DQ.INVALID_ROWS to ANALYST_TASK_ADMIN.
Show answer and explanation
Correct answer: A
Explanation
For Snowflake tasks, the permissions must cover both task object administration and execution of the SQL body. To create a task, a role needs USAGE on the database and schema and CREATE TASK on the schema. For a user-managed task that specifies a warehouse, the task owner role must have USAGE on that warehouse. The task runs with the privileges of the task owner role, so that role must also have the privileges needed by the SQL statement, such as INSERT on the target table. Once the role owns the task, it can manage lifecycle actions like ALTER TASK ... RESUME or SUSPEND and can manually trigger execution with EXECUTE TASK. This reflects Snowflake best practice of granting the minimum object and warehouse privileges needed rather than broad privileges like OWNERSHIP on the database.
- A. Correct.
Correct. To create a task in a schema, the role needs USAGE on the parent database and schema plus the CREATE TASK privilege on the schema. Because this is a user-managed task that runs on a named warehouse, the task owner role must have USAGE on the warehouse used by the task. Since the task statement inserts into ANALYTICS.DQ.INVALID_ROWS, the task owner role also needs the object privileges required by the SQL statement itself, such as INSERT on the target table. A role that owns the task can resume, suspend, and manually execute it.
- B. Incorrect.
Incorrect. OWNERSHIP on the database is far more privilege than required and does not by itself grant the needed schema-level CREATE TASK privilege in the intended least-privilege design. In addition, OPERATE on a warehouse is not the key privilege required for a task to use a user-managed warehouse; USAGE is required so the task owner can run statements on that warehouse. This option violates the requirement to avoid broad administrative privileges.
- C. Incorrect.
Incorrect. CREATE TASK is granted on schemas, not on databases, so this privilege assignment is not valid for enabling task creation in the target schema. MODIFY on a schema does not replace CREATE TASK. This option also omits the necessary INSERT privilege on the target table, which the task owner role must have for the task SQL to succeed.
- D. Incorrect.
Incorrect. The database and schema USAGE plus schema-level CREATE TASK are correct, and INSERT on the target table is also needed. However, OPERATE on the warehouse is not sufficient for a user-managed task to execute statements on that warehouse; the relevant privilege is USAGE on the warehouse. OPERATE is used for actions such as suspend and resume of the warehouse itself, not for running task queries.