Currently, a Task can execute a single SQL statement, including a call to a stored procedure.
There is no event source that can trigger a task; instead, a task runs on a schedule, which can be defined when creating a task (using CREATE TASK) or later (using ALTER TASK).Task Security comes into the picture during the execution of Tasks.
As part of Task Security, Creating, managing, and executing tasks requires a role with a minimum of the following privileges.
- Account — > EXECUTE TASK
- Database –>USAGE
- Schema –> USAGE,CREATE TASK
- Warehouse –> USAGE
- TASK –>OWNERSHIP
As suggested by Snowflake,
- Creating a custom role (e.g. TASKADMIN) and assigning the EXECUTE TASK privilege to this role.
- Grant this custom role to any task owner role to allow altering their own tasks.
To showcase the scenario:
Use Account Admin or Security Admin Role, I would recommend use SECURITYADMIN Role and here you would realize the importance of this role .
- Create a Custom Role.
- Create Custom User.
- Assign Role to the User.
- Grant Required privileges to the Role
CREATE ROLE REP3 create or replace user REP3 password = 'REP3' default_role = REP3 must_change_password = false; grant role REP3 to user REP3; grant usage, monitor on database RBAC_DB to role REP3; grant usage on warehouse COMPUTE_WH to role REP3; grant ALL on schema RBAC_DB.public to role REP3;
Now Log into the Snowflake with new user and Create the Procedure and Task:
CREATE OR REPLACE TABLE EMPLOYEES(EMPLOYEE_ID INTEGER AUTOINCREMENT START = 1 INCREMENT = 1, EMPLOYEE_NAME VARCHAR DEFAULT 'SACHIN', LOAD_TIME DATE);
-- Task that calls the stored procedure every minute create or replace task employees_load_task warehouse = COMPUTE_WH schedule = '1 minute' as call load_employees_data(CURRENT_TIMESTAMP);
Now try to resume the Task and see what happens:
As we can see there is no EXECUTE TASK privilege assigned to the Role and hence while trying to resume the task , we got error.
Now Login to the ACCOUNTADMIN Role:
- Create a new role
- Grant EXECUTE TASK privileges to the Role
- Assign this Role to REP3
create role taskadmin; grant execute task on account to role taskadmin; grant role taskadmin to role REP3;