Difference between execute as a CALLER and OWNER in Snowflake procedure:
By default, when a stored procedure is created in Snowflake, it runs with the owner’s rights which is also known as the “execute as owner” option.
A caller’s rights stored procedure runs with the privileges of the caller. The primary advantage of a caller’s rights stored procedure is that it can access information about that caller or about the caller’s current session. For example, a caller’s rights stored procedure can read the caller’s session variables and use them in a query.
For this POC I have created two roles in snowflake — ROLE 1 & ROLE 2.
Database: CITIBIKE -> Schema : PUBLIC -> Tables : TRIPS
- ROLE 1 -> Following grants provided to the Database and Tables.
- ROLE 2 -> Following grants provided to the Database and Tables.
Scenario 1:
Used ROLE1 to create a procedure in snowflake [Execute as a OWNER]
Step 1 : Creation of stored procedure:
CREATE OR REPLACE PROCEDURE sv_proc1()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
var rs = snowflake.execute({sqlText: `select dayname(starttime) as "day of week", count(*) as "num trips" from trips where dayname(starttime) = 'Wed' group by 1 order by 2 desc`});
rs.next();
result = rs.getColumnValue(2)
return result;
$$
;
Step 2 : Grant usage to Stored Procedure:
grant usage on procedure sv_proc1() to role role_2;
Result:
- ROLE 1 have permission to execute the procedure [ CALL sv_proc1(); ]
- ROLE 1 have permission to describe the procedure as well [ Role 1 can view the body of the procedure]
3. ROLE 2 have permission to execute the procedure [ CALL sv_proc1();]
4. ROLE 2 have permission to describe the procedure but it doesn’t have permission to view the body of the procedure. [Highlighted in the below screenshot]
Scenario 2:
By using ROLE 1 I have created Stored procedure [Execute as a CALLER]
Step 1 : Creation of stored procedure:
CREATE OR REPLACE PROCEDURE sv_proc1()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var rs = snowflake.execute({sqlText: `select dayname(starttime) as "day of week", count(*) as "num trips" from trips where dayname(starttime) = 'Wed' group by 1 order by 2 desc`});
rs.next();
result = rs.getColumnValue(2)
return result;
$$
;
Step 2 : Grant usage to Stored Procedure:
grant usage on procedure sv_proc1() to role role_2;
Result:
- ROLE 1 have permission to execute the procedure [ CALL sv_proc1(); ]
- ROLE 1 have permission to describe the procedure as well [ Role 1 can view the body of the procedure]
3. ROLE 2 don’t have permission to execute the procedure [ CALL sv_proc1(); ]
4. ROLE 2 have permission to describe the procedure and to view the body of the procedure.
Summary (Most Cases) : Why support team need the procedure to be executed as a CALLER ?
Basically support role need access to view the body of the procedure and they don’t want to execute [call] the procedure. For any procedure call, support team should reach out to Snowflake admin to execute the same.
Stored Procedure Privileges :
[only 3 privileges are available in snowflake for procedure — Click here for snowflake documentation)
Note:
- Operating on a stored procedure also requires the USAGE privilege on the parent database and schema.
- If a stored procedure runs with caller’s rights, the user who calls the stored procedure must have privileges on the database objects (e.g. tables) accessed by the stored procedure.