How to run snowflake side effect functions like SYSTEM$GENERATE_SCIM_ACCESS_TOKEN within a procedure with owner rights?

Question

Basically I want to do SCIM integration in snowflake. For that I have to use this command for getting the token which will be passed to Azure AD:

call system$generate_scim_access_token('<value>');

This command can only run with AccountAdmin. And running it with AccountAdmin I am able to get token but In future I will not be having rights of AccountAdmin, so for that what I did, I created a procedure with AccountAdmin and execute it as owner. So that, when ever any other role which is having the usage permission of this procedure calls this procedure it will be executed with AccountAdmin rights (because it was created with AccountAdmin) and hence in that procedure I can run system$generate_scim_access_token(''); and return result:

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

But when ever i call this It gives me this error:

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

Basically it says that the procedure is having a side effect function (SCIM is a side effect function) and running it with Owner rights is giving exception (There must be a restriction by snowflake for running side effect functions within procedure with owner rights). If we run it with caller it runs fine but we want to run it with owner.

Haseeb Haseeb · Accepted Answer · 2020-07-14T13:58:29

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

How to run snowflake side effect functions like SYSTEM$GENERATE_SCIM_ACCESS_TOKEN within a procedure with owner rights?

1 Answers