雪花任务返回必须授予所有者角色的使用错误 [英] Snowflake task is returning a usage error that must be granted to owner role

问题描述

我有一个由 prod_admin 所有的测试程序:

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;
$$;

还有一个任务来运行这个程序并由 task_admin 拥有:

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 权限授予所有者角色

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

两个角色都拥有对同一个仓库的使用权限.我们已经为这两个角色运行了 show grants，他们确实有访问权限.

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

当使用 CALL test() 运行该过程时，它同时使用两个角色.

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

推荐答案

我的任务有 2 个问题:

时间戳会话格式:

原来我们任务中的这一行:

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();

如Snowflake 参数 文档中所述，此行将将整个会话期间的格式设置为任务中指定的格式.

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

通过不指定此参数的格式，它将设置为AUTO，这将留给Snowflake根据字段类型进行必要的时间戳转换.

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.

对程序和任务的所有权和使用权:

为了正确处理调用过程的任务，该过程调用了许多其他过程，我们应该将所有使用过程的使用权授予拥有任务的角色.即使我们正在调用一个过程.

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;

这篇关于雪花任务返回必须授予所有者角色的使用错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！