Understanding Caller’s Rights and Owner’s Rights Stored Procedures

A stored procedure runs with either the caller’s rights or the owner’s rights. It cannot run with both at the same time. This topic describes the differences between a caller’s rights stored procedure and an owner’s rights stored procedure.

In this Topic:

Introduction

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.

An owner’s rights stored procedure runs mostly with the privileges of the stored procedure’s owner. The primary advantage of an owner’s rights stored procedure is that the owner can delegate specific administrative tasks, such as cleaning up old data, to another role without granting that role more general privileges, such as privileges to delete all data from a specific table.

At the time that the stored procedure is created, the creator specifies whether the procedure runs with owner’s rights or caller’s rights. The default is owner’s rights.

The owner can change the procedure from an owner’s rights stored procedure to a caller’s rights stored procedure (or vice-versa) by executing an ALTER PROCEDURE command.

Privileges on Database Objects

A caller’s rights stored procedure runs with the database privileges of the role that called the stored procedure. Any statement that the caller could not execute outside the stored procedure cannot be executed inside the stored procedure, either. For example, if the role named “Nurse” does not have privileges to delete rows from the medical_records table, then if a user with the role “Nurse” calls a caller’s rights stored procedure that tries to delete rows from that table, the stored procedure will fail.

An owner’s rights procedure runs with the rights of the procedure owner. This means that if the owner has the privileges to perform a task, then the stored procedure can perform that task even when called by a role that does not have privileges to perform that task directly. For example, if the role named “Doctor” has the database privileges to delete rows from the medical_records table, and the “Doctor” role creates a stored procedure that deletes rows older than 7 years from that table, then if the “Doctor” role grants the “Nurse” role appropriate privileges on the stored procedure, then the “Nurse” role can run the stored procedure (and delete old rows from the table via that stored procedure), even if the “Nurse” role doesn’t have delete privileges on the table.

Session State

As with other SQL statements, a CALL statement runs within a session, and inherits context from that session, such as session-level variables, current database, etc. The exact context that the procedure inherits depends upon whether the stored procedure is a caller’s rights stored procedure or an owner’s rights stored procedure.

If a caller’s rights stored procedure makes changes to the session, those changes can persist after the end of the CALL. Owner’s rights stored procedures are not permitted to change session state.

Caller’s Rights Stored Procedures

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.

The section below provides more details about how caller’s rights stored procedures can read and write the caller’s session-level variables.

Session Variables

Suppose that the stored procedure named MyProcedure executes SQL statements that read and set session-level variables. In this example, the details of the read and set commands are not important, so the statements are represented as pseudo-code:

  • READ SESSION_VAR1

  • SET SESSION_VAR2

The stored procedure looks similar to the following pseudocode:

CREATE PROCEDURE MyProcedure()
...
$$
   READ SESSION_VAR1;
   SET SESSION_VAR2;
$$
;

Suppose that you execute the following sequence of statements in the same session:

SET SESSION_VAR1 = 'some interesting value';
CALL MyProcedure();
SELECT *
    FROM table
    WHERE column1 = $SESSION_VAR2;

This is equivalent to executing the following sequence:

SET SESSION_VAR1 = 'some interesting value';
READ SESSION_VAR1;
SET SESSION_VAR2;
SELECT *
    FROM table
    WHERE column1 = $SESSION_VAR2;

In other words:

  • The stored procedure can see the variable that was set by statements before the procedure was called.

  • The statements after the stored procedure can see the variable that was set inside the procedure.

For a complete example that does not rely on pseudo-code, see Using Session Variables with Caller’s Rights and Owner’s Rights Stored Procedures (in this topic).

In many stored procedures, you want to inherit context information such as the current database and the current session-level variables.

However, in some cases, you might want your stored procedure to be more isolated. For example, if your stored procedure sets a session-level variable, you might not want the session-level variable to influence future statements outside your stored procedure.

To better isolate your stored procedure from the rest of your session:

  • Avoid using session-level variables directly. Instead, pass them as explicit parameters. This forces the caller to think about exactly which session-level variables the stored procedure will use.

  • Clean up any session-level variables that you set inside the stored procedure (and use names that are not likely to be used anywhere else, so that you don’t accidentally clean up a session variable that existed prior to the stored procedure call).

The following stored procedure uses the value of a session variable by receiving it as a parameter, not by using the session variable directly:

SET Variable_1 = 49;
CREATE PROCEDURE sv_proc2(PARAMETER_1 FLOAT)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
        var rs = snowflake.execute( {sqlText: "SELECT 2 * " + PARAMETER_1} );
        rs.next();
        var MyString = rs.getColumnValue(1);
        return MyString;
    $$
    ;
  CALL sv_proc2($Variable_1);

The following stored procedure creates a temporary session variable with an unusual name and cleans up that variable before the stored procedure finishes. When a statement after the procedure call tries to use the session variable that was cleaned up, that statement will fail:

CREATE PROCEDURE sv_proc1()
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
        var rs = snowflake.execute( {sqlText: "SET SESSION_VAR_ZYXW = 51"} );

        var rs = snowflake.execute( {sqlText: "SELECT 2 * $SESSION_VAR_ZYXW"} );
        rs.next();
        var MyString = rs.getColumnValue(1);

        rs = snowflake.execute( {sqlText: "UNSET SESSION_VAR_ZYXW"} );

        return MyString;
    $$
    ;

CALL sv_proc1();
-- This fails because SESSION_VAR_ZYXW is no longer defined.
SELECT $SESSION_VAR_ZYXW;

Note

If you program in the C language (or similar languages such as Java), note that session variables you set inside a stored procedure are not like the local variables in C that disappear when a C function finishes running. Isolating your stored procedure from its environment requires more effort in SQL than in C.

Owner’s Rights Stored Procedures

Owner’s rights stored procedures adhere to the following rules within a session:

  • Run with the privileges of the owner, not the privileges of the caller.

  • Inherit the current warehouse of the caller.

  • Use the database and schema that the stored procedure is created in, not the database and schema that the caller is currently using.

  • Cannot access most caller-specific information. For example:

    • Cannot view, set, or unset the caller’s session variables.

    • Can read only specific session parameters (listed here), and cannot set or unset any of the caller’s session parameters.

    • Cannot query INFORMATION_SCHEMA table functions, such as AUTOMATIC_CLUSTERING_HISTORY, that return results based on the current user.

  • Do not allow non-owners to view information about the procedure from the PROCEDURES view.

Restrictions on session variables and session parameters are described in more detail below.

Session Variables

A stored procedure does not have access to SQL Variables created outside the stored procedure. This restriction prevents a stored procedure written or owned by one user from reading SQL variables created by another user (the stored procedure caller).

If your stored procedure needs values that are stored in the current session’s SQL variables, then the values in those variables should be passed as explicit arguments to the stored procedure. For example:

SET PROVINCE = 'Manitoba';
CALL MyProcedure($PROVINCE);

Session Parameters

In some cases, allowing the stored procedure to read the caller’s session parameters is helpful; the stored procedure can customize the behavior for that caller or session. For example, the stored procedure could use the caller’s preferred DATE_OUTPUT_FORMAT.

However, in other cases, reading the caller’s session parameters might not be desirable:

  • If the author (owner) of a stored procedure has set a specific session parameter, but callers of the stored procedure have not set that parameter, then the stored procedure might fail or behave differently when called by users other than the author.

  • If one user wrote a stored procedure, and another user called that stored procedure, and if the stored procedure were allowed to read all session parameters, then it would be possible for the author (owner) of the stored procedure to read session parameters set by the caller without the caller knowing it.

To reduce these potential issues, Snowflake allows a stored procedure to use only a specific subset of the caller’s session parameters (see list below).

If a statement inside a stored procedure references an unsupported parameter, then the stored procedure uses the value of the owner’s account-level parameter, not the caller’s session level parameter.

If the stored procedure’s owner never set the account parameter explicitly, then the stored procedure uses the default value for the account parameter.

The list of supported parameters currently includes the following (the list might change over time):

  • AUTOCOMMIT

  • BINARY_INPUT_FORMAT

  • BINARY_OUTPUT_FORMAT

  • DATE_INPUT_FORMAT

  • DATE_OUTPUT_FORMAT

  • ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION

  • ERROR_ON_NONDETERMINISTIC_MERGE

  • ERROR_ON_NONDETERMINISTIC_UPDATE

  • JDBC_TREAT_DECIMAL_AS_INT

  • JSON_INDENT

  • LOCK_TIMEOUT

  • MAX_CONCURRENCY_LEVEL

  • ODBC_USE_CUSTOM_SQL_DATA_TYPES

  • PERIODIC_DATA_REKEYING

  • QUERY_TAG

  • QUERY_WAREHOUSE_NAME

  • ROWS_PER_RESULTSET

  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

  • STATEMENT_TIMEOUT_IN_SECONDS

  • STRICT_JSON_OUTPUT

  • TIMESTAMP_DAY_IS_ALWAYS_24H

  • TIMESTAMP_INPUT_FORMAT

  • TIMESTAMP_LTZ_OUTPUT_FORMAT

  • TIMESTAMP_NTZ_OUTPUT_FORMAT

  • TIMESTAMP_OUTPUT_FORMAT

  • TIMESTAMP_TYPE_MAPPING

  • TIMESTAMP_TZ_OUTPUT_FORMAT

  • TIMEZONE

  • TIME_INPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TRANSACTION_ABORT_ON_ERROR

  • TRANSACTION_DEFAULT_ISOLATION_LEVEL

  • TWO_DIGIT_CENTURY_START

  • UNSUPPORTED_DDL_ACTION

  • USE_CACHED_RESULT

  • WEEK_OF_YEAR_POLICY

  • WEEK_START

For more information about session parameters, see Parameters, ALTER SESSION, and SHOW PARAMETERS.

Additional Restrictions on Owner’s Rights Stored Procedures

Owner’s rights stored procedures have several additional restrictions, besides the restrictions related to session variables and session parameters. These restrictions affect the following:

  • Ability to call the GET_DDL function to get the stored procedure’s DDL.

  • Ability to execute ALTER USER statements.

  • Monitoring stored procedures at execution time.

  • SHOW and DESCRIBE commands.

  • The types of SQL statements that can be called from inside a stored procedure.

The following sections explain these restrictions in more detail.

Note

Most restrictions on an owner’s rights stored procedure apply to all callers, including the owner.

GET_DDL and Stored Procedures

If a stored procedure is created as an owner’s rights stored procedure, then callers (other than the owner) cannot view the body of the stored procedure by calling GET_DDL( <procedure_name>, ... ).

This allows the stored procedure’s author to protect confidential information in the source code of the stored procedure.

ALTER USER

The following restrictions apply to ALTER USER statements in an owner’s rights stored procedure:

  • Owner’s rights stored procedures cannot execute ALTER USER statements that implicitly use the current user for the session. (However, an owner’s rights stored procedures can execute ALTER USER statements that explicitly identify the user, as long as the user is not the current user.)

Monitoring Stored Procedures at Execution Time

Neither the owner nor the caller of an owner’s rights stored procedure necessarily has privileges to monitor execution of the stored procedure.

A user with the WAREHOUSE MONITOR privilege can monitor execution of the individual warehouse-related SQL statements within that stored procedure. Most queries and DML statements are warehouse-related statements. DDL statements, such as CREATE, ALTER, etc. do not use the warehouse and cannot be monitored as part of monitoring stored procedures.

SHOW and DESCRIBE Commands

An owner’s rights stored procedure does not have sufficient privileges to read information about users other than the caller. For example, running SHOW USERS LIKE <current_user> will show information about the current user, but the more general SHOW USERS does not work unless the current user is the only user.

The following SHOW commands are permitted:

  • SHOW DATABASES.

  • SHOW SCHEMAS.

  • SHOW WAREHOUSES.

Restrictions on SQL Statements

Although caller’s rights stored procedures can execute any SQL statement that the caller has sufficient privileges to execute outside a stored procedure, owner’s rights stored procedures can call only a subset of SQL statements.

The following SQL statements can be called from inside an owner’s rights stored procedure:

  • SELECT.

  • DML.

  • DDL. (See above for restrictions on the ALTER USER statement.)

  • GRANT/REVOKE.

  • Variable assignment.

  • DESCRIBE and SHOW. (See limitations documented above.)

Other SQL statements cannot be called from inside an owner’s rights stored procedure.

Nested Stored Procedures with Different Rights

If an owner’s rights stored procedure is called by a caller’s rights stored procedure, or vice-versa, the following rules apply:

  • A stored procedure behaves as a caller’s rights stored procedure if and only if the procedure and the entire call hierarchy above it are caller’s rights stored procedures.

  • An owner’s rights stored procedure always behaves as an owner’s rights stored procedure, no matter where it was called from.

  • Any stored procedure called directly or indirectly from an owner’s rights stored procedure behaves as an owner’s rights stored procedure.

Choosing Between Owner’s Rights and Caller’s Rights

Create a stored procedure as an owner’s rights stored procedure if all of the following are true:

  • You want to delegate a task(s) to another user(s) who will run with the owner’s privileges, not the caller’s own privileges. For example, if you want 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. That procedure will contain a DELETE statement that includes a filter (a WHERE clause) to control which data can be deleted through the filter.

  • The restrictions in owner’s rights stored procedures will not prevent the stored procedure from working properly.

Create a stored procedure as a caller’s rights stored procedure if the following are true:

  • The stored procedure operates only on objects that the caller owns or has the required privileges on.

  • 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).

If a particular procedure can work correctly with either caller’s rights or owner’s rights, then the following rule might help you choose which rights to use:

  • If a procedure is an owner’s rights procedure, the caller does not have the privilege to view the code in the stored procedure (unless the caller is also the owner). If you want to prevent callers from viewing the source code of the procedure, then create the procedure as an owner’s rights procedure. Conversely, if you want callers to be able to read the source code, then create the procedure as a caller’s rights prodecure.