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:

1. ROLE 1 have permission to execute the procedure [ CALL sv_proc1(); ]
2. 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:

1. ROLE 1 have permission to execute the procedure [ CALL sv_proc1(); ]
2. 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.

References:-

• https://www.snowflake.com/