1
0
In continuation of previous post,we will be talking about the Session variables inside the Stored Procedure, Caller’s rights stored procedures adhere to the following rules within a session:
- Run with the privileges of the caller, not the privileges of the owner.
- Inherit the current warehouse of the caller.
- Use the database and schema that the caller is currently using.
- Can view, set, and unset the caller’s session variables.
- Can view, set, and unset the caller’s session parameters.
Create a stored procedure as an owner’s rights stored procedure if:
A user without DELETE privilege on a table to be able to call a stored procedure that deletes old data, but not current data, then you probably want to use an owner’s rights stored procedure
Create a stored procedure as a caller’s rights stored procedure if:
The restrictions in owner’s rights stored procedures would prevent the stored procedure from working. For example, use a caller’s rights procedure if the caller of the stored procedure needs to use that caller’s environment (e.g. session variables or account parameters).
Is it possible to SET a variable inside a stored procedure so that a view uses it?
create or replace view test as
(
select getvariable(‘MY_VAR’)::int as col1
) ;
set MY_VAR=5;
select * from test;
Now I want to do the same query from a stored procedure:
create or replace procedure myproc()
returns VARCHAR
language javascript
as $$ var stmt =
snowflake.createStatement({sqlText: "select * from test"});
var rs = stmt.execute();
rs.next();
return rs.getColumnValue(1);
$$;
call myproc();
This work as long as MY_VAR is set outside before the call proc.
Now if we want to set the session variable from within the procedure and which does not allow:
Stored procedure execution error: Unsupported statement type ‘SET’. At Snowflake.execute
The reason that you are unable to SET a session variable inside of your stored procedure is that by default, the execution mode is set to EXECUTE AS OWNER, which is a more restrictive mode due to security concerns.
So, you are able to SET (and use) session variable inside of your stored procedure only if you set the execution mode of the stored procedure to EXECUTE AS CALLER. Here is a revised version of your stored procedure that demonstrates the use of session variables (using an arbitrary value of 7):
