CODEWITHSUNDEEP
scheduled-taskssnowflake-cloud-data-platformdata-warehouse
alim1990
alim1990

Reputation: 4972

Snowflake task is returning a usage error that must be granted to owner role

I am having a test procedure owned by prod_admin:

CREATE OR REPLACE PROCEDURE test()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
query = "SELECT * FROM DWH_OPS.CHANGE_HISTORY";
stmt = snowflake.createStatement({sqlText: query}).execute();
stmt.next();
return stmt;
$$;

And a task to run this procedure and owned by task_admin:

CREATE OR REPLACE TASK test_procedure
    WAREHOUSE = 'COMPUTE_WH'
    TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
    SCHEDULE = '5 MINUTE'
AS
   call PROD_DB.DWH_OPS.TEST();

Once the task is running, we are receiving the following error:

USAGE privilege on the task's warehouse must be granted to owner role

Both roles are having usage privileges over the same warehouse. We've ran show grants to both roles, and they do have access.

When running the procedure using CALL test(), it worked using both roles.

Upvotes: 0

Views: 2355

Answers (1)

alim1990
alim1990

Reputation: 4972

There was 2 issues with my task:

Timestamp session format:

It turned out that this line in our task:

TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'

is setting the timestamp of the session into a format that is different than the format within our data.

CREATE OR REPLACE TASK DATA_LOAD_TASK
    WAREHOUSE = 'COMPUTE_WH'
    // TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
    SCHEDULE = 'USING CRON 00 18 * * * Europe/Zurich'
AS
    CALL proc1();

As mentioned in the Snowflake parameters documentation, this line will set the format during the whole session into the specified format in the task.

By not specifying the format of this parameter, it will set to AUTO, which will leave to Snowflake to make necessary timestamp conversions according to field types.

Ownership and usage over procedures and tasks:

To work properly with task that is calling a procedure which calling many other, we should grant usage over all used procedures to the role owning the task. Even if we are calling one procedure.

grant usage on procedure proc1(varchar, varchar, varchar, array) to role prod_taskadmin;
grant usage on procedure proc2(varchar, varchar, varchar, varchar) to role prod_taskadmin;
grant usage on procedure proc3(varchar, varchar, varchar, varchar) to role prod_taskadmin;

And the procedures should be owned by a role who have permissions over all the objects in a schema:

grant ownership on procedure proc1(string, string, string, array) to role prod_sysadmin;
grant ownership on procedure proc2(string, string, string, array) to role prod_sysadmin;
grant ownership on procedure proc3(string, string, string, array) to role prod_sysadmin;

Upvotes: 3

Related Questions