1
0
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);
create or replace procedure load_employees_data(TODAY_DATE varchar)
returns string not null
language javascript
as
$$
var sql_command = 'INSERT INTO EMPLOYEES(LOAD_TIME) VALUES(:1);'
snowflake.execute(
{
sqlText: sql_command,
binds: [TODAY_DATE]
}
);
return "SUCCEEDED";
$$;
-- 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;
