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

Session Variables for Caller’s Rights Procedures¶

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;
$$
;

Copy

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

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

Copy

This is equivalent to executing the following sequence:

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

Copy

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 호출자 권한 및 소유자 권한 저장 프로시저에서 세션 변수 사용하기 (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);

Copy

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
    EXECUTE AS CALLER
    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;

Copy

Session Variables for Owner’s Rights Procedures¶

A stored procedure does not have access to SQL 변수 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);

Understanding the Effects of a Caller’s Session Parameters on an Owner’s Rights Procedure¶

The value of a session parameter can affect the behavior of commands and functions. For example, commands that output date values use the format specified by the DATE_OUTPUT_FORMAT session parameter.

In a caller’s session, the caller can set or override a session parameter. In a caller’s rights stored procedure, session parameters can affect the execution of any queries and expressions executed inside the procedure. For example, the TIMESTAMP_OUTPUT_FORMAT parameter affects the output format of a child query like select current_timestamp::string.

However, for an owner’s rights stored procedure, the values from the caller’s session are used only for the following parameters:

• 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 other parameters (not listed above):

• The value of the owner’s account-level parameter is used.

• If the account-level parameter is not set for owner’s account, the default value for the account parameter is used.

This restriction is in place to avoid potential issues that could occur if an owner’s rights stored procedure used the caller’s session parameters. For example:

• 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, the stored procedure might fail or behave differently when called by users other than the author.

• If a stored procedure were allowed to use the value of any session parameter set by the caller, the owner of a stored procedure might be able to determine those values without the caller’s knowledge.

Restrictions on Built-in Functions¶

If a stored procedure is created as an owner’s rights stored procedure, then callers (other than the owner) cannot call the following built-in functions:

• GET_DDL()

  This prevents users other than the stored procedure owner from viewing the source code of the stored procedure.

• SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE()

• SYSTEM$DISABLE_BEHAVIOR_CHANGE_BUNDLE()

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.