Restricted caller’s rights

An executable such as a stored procedure or Snowpark Container Services service can run with privileges from the owner of the executable (owner’s rights) or from the caller of the executable (caller’s rights). If an executable runs with caller’s rights, the executable can perform an action only if the caller has privileges to perform that action outside the context of the executable.

Restricted caller’s rights allows an executable to run with caller’s rights, but restricts which of the caller’s privileges the executable runs with. With restricted caller’s rights, an executable cannot run with a specific privilege unless an administrator expressly allows it.

About caller grants

Administrators use caller grants to define which of the caller’s privileges an executable can run with. For example, if a caller has SELECT and INSERT privileges on a table, but there isn’t a caller grant that allows the executable to run with the INSERT privilege, then the executable with restricted caller’s rights cannot run with the INSERT privilege when acting upon the table.

A caller grant doesn’t give any privileges but rather restricts which of the caller’s existing privileges are used when they run the executable. For example, if a caller runs a stored procedure to select from a table, the caller must already have the SELECT privilege on the table and the caller grant must allow the stored procedure to run with the SELECT privilege.

Caller grants are granted by the administrator to the role that owns an executable. The caller grants are granted on objects such as tables and warehouses that the executable accesses. When the executable attempts to access the objects, the caller grants associated with the owner of the executable are used to determine which of the caller’s privileges can be used for the operation.

Executables that run with restricted caller’s rights

The user who creates an executable defines whether the executable runs with owner’s rights, caller’s rights, or restricted caller’s rights. If they choose restricted caller’s rights, every privilege required by the executable must be specified in one or more caller grants that are granted to the owner of the executable.

For a stored procedure, the EXECUTE AS parameter defines whether the procedure runs with owner’s rights, caller’s rights, or restricted caller’s rights. The following is an example of defining the procedure to run with restricted caller’s rights:

CREATE OR REPLACE PROCEDURE sp_pi()
  RETURNS FLOAT NOT NULL
  LANGUAGE JAVASCRIPT
  EXECUTE AS RESTRICTED CALLER
  AS
  $$
  RETURN 3.1415926;
  $$
  ;
Copy

For a list of restrictions on executables that run with restricted caller’s rights, see Limitations of an executable with restricted caller’s rights.

Grant caller grants

Caller grants are granted on objects such as tables and databases that an executable accesses. The caller grants are granted to the role or database role that owns the executable.

The GRANT statement that an administrator uses to grant a caller grant has different variations, depending on how you want to grant caller grants. The variations are as follows:

  • GRANT CALLER — Grant caller grants on a specific object. Each caller grant created by the statement allows the executable to run with a specified privilege.

  • GRANT ALL CALLER PRIVILEGES — Grant caller grants on a specific object. The caller grants created by the statement allow the executable to run with all of the caller’s privileges.

  • GRANT INHERITED CALLER — Grant caller grants on all current and future objects of the same type when they share a common schema, database, or account. Each caller grant created by the statement allows the executable to run with a specified privilege.

  • GRANT ALL INHERITED CALLER PRIVILEGES — Grant caller grants on all current and future objects of the same type when they share a common schema, database, or account. The caller grants created by the statement allow the executable to run with all of the caller’s privileges.

A single GRANT statement can result in multiple caller grants being granted to the executable owner. For example, GRANT CALLER INSERT, SELECT … results in two caller grants, one for the INSERT privilege and another for the SELECT privilege. Similarly, a GRANT ALL INHERITED CALLER PRIVILEGES statement results in a caller grant for every privilege that can be granted on the specified object type.

For the complete syntax, including parameters, for granting a caller grant, see GRANT CALLER.

Examples

The following are examples of how an administrator can use caller grants to control which of the caller’s privileges an executable can run with.

Executables owned by owner_role that access a v1 view can run with the SELECT privilege on the view:

GRANT CALLER SELECT ON VIEW v1 TO owner_role;
Copy

Executables owned by owner_role that access any table in the db.sch schema can run with the caller’s SELECT and INSERT privileges.

GRANT INHERITED CALLER SELECT, INSERT ON ALL TABLES IN SCHEMA db.sch TO ROLE owner_role;
Copy

Executables owned by owner_role that access schemas in the current account can run with all of the caller’s privileges on the schemas.

GRANT ALL INHERITED CALLER PRIVILEGES ON ALL SCHEMAS IN ACCOUNT TO ROLE owner_role;
Copy

Executables owned by the db.r database role that access the db.sch1.t1 table can run with the SELECT privilege on the table.

GRANT CALLER SELECT ON TABLE db.sch1.t1 TO DATABASE ROLE db.r;
Copy

Executables owned by owner_role that access the my_db database can run with all of the caller’s privileges on the database.

GRANT ALL CALLER PRIVILEGES ON DATABASE my_db TO ROLE owner_role;
Copy

Revoke a caller grant

Administrators use a REVOKE statement to revoke privileges that were previously granted to an executable owner through a caller grant. This statement has different variations, depending on how you want to revoke caller grants.

  • REVOKE CALLER — Revoke specific privileges on a specific object.

  • REVOKE ALL CALLER PRIVILEGES — Revoke all privileges on a specific object. The executable will not be able to run with any privileges from the caller when it tries to access the object.

  • REVOKE INHERITED CALLER — Revoke caller grants on all current and future objects of the same type when they share a common schema, database, or account. Only privileges in a specified list are revoked.

  • REVOKE ALL INHERITED CALLER PRIVILEGES — Revoke caller grants on all current and future objects of the same type when they share a common schema, database, or account. All privileges are revoked; the executable will not be able to run with any privileges from the caller.

Executing a REVOKE INHERITED CALLER or REVOKE ALL INHERITED CALLER PRIVILEGES command does not revoke a caller grant that was granted on a specific object within the account, database, or schema using a GRANT CALLER statement. For example, if you granted a caller grant on table my_db.sch1.t1 directly, executing REVOKE INHERITED CALLER SELECT ON ALL TABLES IN DATABASE my_db ... does not revoke the caller grant on t1.

For the complete syntax, including parameters, of revoking a caller grant, see REVOKE CALLER.

Examples

Executables owned by owner_role can no longer run with the caller’s privileges when they access views in the current account.

REVOKE ALL INHERITED CALLER PRIVILEGES ON ALL VIEWS IN ACCOUNT FROM ROLE owner_role;
Copy

Executables owned by owner_role can no longer run with the USAGE privilege when they access the db.sch1 schema.

REVOKE CALLER USAGE ON SCHEMA db.sch1 FROM ROLE owner_role;
Copy

List caller grants

Users can use the SHOW CALLER GRANTS command to list caller grants. You can use this command to list all caller grants that have been granted to a specific owner (SHOW CALLER GRANTS TO …) or to list all caller grants on a specific object (SHOW CALLER GRANTS ON …).

If you execute a SHOW CALLER GRANTS ON … command for a specific object, each row could indicate any of the following:

  • A caller grant was granted directly on the object.

    For example, the output of SHOW CALLER GRANTS ON TABLE db.sch.t1 contains a row if the administrator executed GRANT CALLER SELECT ON TABLE db.sch.t1.

  • The object inherited a caller grant.

    For example, the output of SHOW CALLER GRANTS ON TABLE db1.sch.t1 contains a row if the administrator executed GRANT INHERITED CALLER SELECT ON ALL TABLES IN SCHEMA db1.sch.

  • The object was specified with an IN clause so other objects that it contains inherited caller grants.

    For example, the output of SHOW CALLER GRANTS ON ACCOUNT contains a row if the administrator executed GRANT INHERITED CALLER SELECT ON ALL TABLES IN ACCOUNT.

  • The object is an ancestor of an object with an inherited caller grant as well as the descendant of the object that was specified with an IN clause that resulted in the inheritance.

    For example, SHOW CALLER GRANTS ON SCHEMA my_db.sch1 contains a row if the administrator executed GRANT INHERITED CALLER SELECT ON ALL TABLES IN DATABASE my_db.

Conditional output

The output of the SHOW CALLER GRANTS command varies depending on the privileges of the executing role. When a user executes SHOW CALLER GRANTS, the results only contain objects on which they have at least one privilege; they cannot discover the existence of an object unless they can access it, even if there is a caller grant on it.

For example, suppose there is a caller grant on databases DB1 and DB2. Now suppose role R2 has the USAGE privilege on DB1, but no privileges on DB2. When R2 executes SHOW CALLER GRANTS, the output shows that there is a caller grant on DB1, but does not list DB2. If R2 had privileges on both databases, then the output would show that the caller grant is on both databases.

Examples

List caller grants that have been granted on the table t1.

SHOW CALLER GRANTS ON TABLE t1;
Copy

List all of the caller grants that have been granted for the current account. This includes grants directly on the account (GRANT CALLER … ON ACCOUNT) and grants to all objects in an account (GRANT INHERITED CALLER … IN ACCOUNT).

SHOW CALLER GRANTS ON ACCOUNT;
Copy

List all of the caller grants that have been granted to the database role db.owner_role.

SHOW CALLER GRANTS TO DATABASE ROLE db.owner_role;
Copy

Limitations on caller grants

Caller grants are not replicated or cloned.

Limitations of an executable with restricted caller’s rights

If an executable runs with restricted caller’s rights, then it is subject to the following restrictions.

External stages

  • Executable cannot create an external stage without specifying a storage integration.

  • Executable cannot copy into an external stage.

  • Executable cannot copy into an external URL without specifying a storage integration.

Stored procedures

  • Executable cannot create Snowflake objects that run with owner’s rights, caller’s rights, or restricted caller’s rights. For example, it cannot create a stored procedure.

  • Executable cannot change the rights with which a stored procedure runs. For example, the executable cannot change a stored procedure from owner’s rights to caller’s rights.

Roles and privileges

  • Executable cannot execute the USE ROLE and USE SECONDARY ROLES commands.

  • Executable cannot use GRANT statements to grant privileges and caller grants.

  • Executable cannot use REVOKE statements to revoke privileges and caller grants.

References

  • Executable cannot create transient and persisted references.

Session-related operations

  • Executable cannot execute SET or UNSET commands.

  • Executable cannot execute SHOW VARIABLES or SHOW PARAMETERS.

  • Executable cannot use or read session variables.

  • Executable cannot execute ALTER SESSION.

  • Executable cannot create session-scoped temporary objects.

  • Executable cannot execute USE DATABASE, USE SCHEMA, or USE WAREHOUSE.

Language: English