Using references to authorize access on objects¶

A reference can be used to authorize access on objects to a stored procedure, Snowflake Native App, or class instance that does not have access to those objects by default.

Introduction¶

A reference is a string that can be used as an identifier. The identifier resolves to the object being referenced.

A reference encapsulates the following:

• The object name.
• The active role used to create the object reference and any active secondary role(s) if applicable.
• The privilege(s) on the object that are specified when the reference is created.

Some scenarios where a reference might be required include:

• An owner’s rights stored procedure requires access to insert data in a table owned by a different role.
• An application performs data analytics and requires read access to data in tables.
• An instance of the SNOWFLAKE.ML.ANOMALY_DETECTION class requires read access to a view for training the anomaly detection ML model.

Objects identified by name¶

A reference identifies an object by name. This means if an object is renamed after a reference is created, the reference is invalid. However, if a new object with the same name is created, the reference might be valid. For example, a role my_role creates a reference my_ref1 for table my_table1 with the SELECT privilege. After the reference is created, table my_table1 is dropped and a new table named my_table1 is created. The reference my_ref1 identifies a table with the name my_table1. In this case, it identifies the new table my_table1.

If the role used to create the reference, and the privilege(s) granted on my_table1 are still valid, access to the new my_table1 is authorized when using the reference.

If the role and privilege(s) encapsulated in the reference are no longer valid, access to table my_table1 cannot be authorized and a new reference must be created for the new table.

Privileges verified at execution time¶

The privileges granted to the role that created the reference are verified at the time the reference is used. For example, a role my_role creates a reference to a table t1 with the SELECT privilege. If my_role is dropped or the SELECT privilege on table t1 is revoked from my_role, the privileges encapsulated in the reference are no longer valid. When the reference is passed to a stored procedure that requires the SELECT privilege on the table, the stored procedure fails with a permissions error.

Types of references and reference lifespan¶

The lifespan of a reference can be specified at creation time.

• A transient reference has a limited lifespan, either for the duration of the call in which the reference is passed, or for the duration of the session.

• A persistent reference has an unlimited lifespan. The reference remains valid until the object it references is dropped, the reference is unset, or the reference becomes invalid.

  For examples of unsetting references, see Unset a persistent reference for an application.

  A reference can become invalid for any of the following reasons:

  • The object it references is renamed.
  • The role that created the reference is dropped.
  • The role that created the reference no longer has privileges on the object.

  For more information, see Objects identified by name and Privileges verified at execution time.

References for owner’s rights stored procedures¶

An owner’s rights stored procedure executes with the privileges of the