Understanding caller’s rights and owner’s rights stored procedures¶

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.

Accessing and setting the 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.

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

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

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

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

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;

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.

  Tip

  If you need an owner’s rights stored procedure to perform actions on a table, view, or function that the caller has the privileges to access, you can have the caller pass a reference to that table, view, or function.

  For details, refer to Passing references for tables, views, functions, and queries to stored procedures.

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

  • Cannot execute SHOW PARAMETERS to list parameters.

  • Can use only a subset of session parameters set by the caller. For example, SQL commands that output date values can use the DATE_OUTPUT_FORMAT parameter that is set for the caller’s session).

    For the list of these parameters, see Understanding the effects of a caller’s session parameters on an owner’s rights procedure.

  • Cannot set or unset any of the caller’s session parameters.

  • Cannot query INFORMATION_SCHEMA table functions (for example, 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.

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

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:

• The built-in functions that can be called from inside a stored procedure.

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

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.

  Tip

  If you need an owner’s rights stored procedure to perform actions on a table, view, or function that the caller has the privileges to access, you can have the caller pass a reference to that table, view, or function.

  For details, refer to Passing references for tables, views, functions, and queries to stored procedures.

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