Switching roles Dynamically in snowflake procedure

Question

I have a use case in snowflake procedure, where I have two databases with different roles. I want to insert data in tables of both these databases from one stored procedure. I created procedure in one of the database and procedure is inserting data in that database but when I tried to insert in second database it says:

SQL compilation error: Database 'SecondDB' does not exist or not authorized.

Then I tried to use query before inserting in second database within procedure to change role like this:

var SwitchToLoader = "use role SecondDbRole";
try {
snowflake.execute 
(
     {sqlText: SwitchToLoader}
);
}
catch (err)  {
     return "Failed: " + err;  
}

this is still give me error:

SQL access control error: Insufficient privileges to operate on role 'SecondDbRole'

So any suggestions how to insert data in my second database that has different role from single stored procedure?

Abhi Reddy Abhi Reddy · Accepted Answer · 2020-04-20T17:53:53

For security purposes, you can't use another role inside a stored procedure. But if as an owner, you want a caller to enjoy the same privileges you can always create the privilege with the Owner's right and you won't have to do any explicit switching of roles within the procedure.

You would just need to include clause "Execute As Owner" to this effect.

More information about Caller's and Owner's right can be found on our documentation

https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#choosing-between-owner-s-rights-and-caller-s-rights

Switching roles Dynamically in snowflake procedure

1 Answers