如何在具有所有者权限的过程中运行雪花副作用功能,例如SYSTEM $ GENERATE_SCIM_ACCESS_TOKEN? [英] How to run snowflake side effect functions like SYSTEM$GENERATE_SCIM_ACCESS_TOKEN within a procedure with owner rights?
问题描述
基本上,我想在雪花中进行SCIM集成.为此,我必须使用此命令来获取将传递给Azure AD的令牌:
call system$generate_scim_access_token('<value>');
此命令只能与AccountAdmin一起运行.并使用AccountAdmin运行它,我可以获取令牌,但是以后我将不再具有AccountAdmin的权限,因此,为此,我使用AccountAdmin创建了一个过程并将其作为所有者执行.这样,当拥有此过程使用权限的任何其他角色调用此过程时,它将以AccountAdmin权限执行(因为它是使用AccountAdmin创建的),因此在该过程中,我可以运行system $ generate_scim_access_token('') ;并返回结果:
Create or Replace Procedure DB.mySchema.GenerateScimAccessToken()
RETURNS string
LANGUAGE javascript
execute as owner
AS
$$
var stmt = snowflake.createStatement( {sqlText: "select system$generate_scim_access_token('<value>');"} );
var resultSet = stmt.execute();
// moving pointer to row.
resultSet.next();
// returning column value
return resultSet.getColumnValue(1);
$$
但是,只要我称呼它,它就会给我这个错误:
Execution error in store procedure GENERATESCIMACCESSTOKEN: SQL compilation error: Query called from a stored procedure contains a function with side effects [SYSTEM$GENERATE_SCIM_ACCESS_TOKEN]. At Statement.execute, line 3 position 24
基本上说该过程具有副作用功能(SCIM是一种副作用功能),并且以所有者权限运行该过程将赋予异常(雪花必须限制在具有所有者权限的过程中运行副作用功能).如果我们使用调用程序来运行它,则可以正常运行,但是我们想要使用所有者来运行它.
有许多这样的命令,它们仅在调用者的上下文中运行,而不是在所有者的上下文中运行(例如,诸如Show Tables之类的Show命令)./p>
以下kb文章中详细介绍了主叫权与所有者权所支持的内容:
https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html
从最终用户的角度来看,这里的要求是合理的.因此,您可以通过在Snowflake支持下提出案例或在Snowflake的Idea门户上提出想法来向Snowflake提出改进请求: 解决方案
There are many such commands which run only in the context of the caller as against the context of owner (for example the Show commands such as Show Tables).
A detailed description of what is supported in callers right vs owners rights is documented in the below kb article:
https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html
From an end-users perspective, the requirement here is reasonable. So you can make an improvement request to Snowflake by either raising a case with Snowflake support or raising an idea on the Snowflake's Idea portal: https://community.snowflake.com/s/ideas
这篇关于如何在具有所有者权限的过程中运行雪花副作用功能,例如SYSTEM $ GENERATE_SCIM_ACCESS_TOKEN?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!