使用引用授予对象的访问权限

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.

简介

引用是可用作标识符的字符串。标识符解析为引用的对象。

引用封装了以下内容：

• 对象名称。
• 用于创建对象引用的活动角色和任何活动辅助角色（如果适用）。
• 创建引用时指定的对象的权限。

可能需要引用的一些场景如下：

• An owner’s rights stored procedure requires access to insert data in a table owned by a different role.
• 应用程序执行数据分析，并需要对表中的数据具有读取访问权限。
• SNOWFLAKE.ML.ANOMALY_DETECTION 类的实例需要对用于训练异常检测 ML 模型具有访问权限。

按名称标识的对象

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.

在执行时验证的权限

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.

引用类型和引用生命周期

引用的生命周期可以在创建时指定。

• 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 取消设置应用程序的持久引用.

由于以下任何原因，引用可能失效：

• 它引用的对象被重命名。
• 创建引用的角色被删除。
• 创建引用的角色不再具有对象的权限。

For more information, see 按名称标识的对象 and 在执行时验证的权限.

所有者权限存储过程的引用

An owner’s rights stored procedure executes with the privileges of the owner rather than the privileges of the caller who executes the stored procedure. In order to perform actions on a table, view, or function that the caller has privileges to access, the caller must pass a reference to the table, view, or function. The reference enables the stored procedure to perform actions on the object that the reference identifies with the privileges of the creator of the reference (in this case, the caller).

应用程序和类的引用

根据设计，应用程序和类 无 权访问账户（在其中安装应用程序或创建类实例）中的对象。用户可以创建引用，从而向应用程序或类实例授予对象的访问权限。

应用程序和类的提供商和使用者

A provider creates an application and a consumer installs and uses an application in the consumer account. In the case of Snowflake classes, Snowflake is the provider, and a user with a Snowflake account who creates an instance of a class is the consumer.

Providers can create applications and classes that request and use references in their code. For more information, see 提供商的引用.

Consumers can create and pass references to applications that they install in their account or to instances of Snowflake classes. For more information, see 使用者的引用.

支持的引用目标

引用的目标可以是对象或查询。如果引用的目标是对象，则引用需要对象的权限。

支持的引用对象类型和权限

下表列出了引用可以包含的对象类型、可以创建的引用类型以及每个对象允许的权限：

查询引用

A query reference is a type of transient reference. It references a SELECT statement that can be used in the FROM clause of another SQL statement in a stored procedure. You can create a query reference by using the SYSTEM$QUERY_REFERENCE function or the TABLE keyword.

For more information, see Using query references and Using the TABLE keyword to create a reference to a table, view, or query.

提供商的引用

You can create applications as a provider by using the Snowflake Native App Framework. For detailed information on requesting references from a consumer of your application, see Request references and object-level privileges from consumers.

使用者的引用

You can create a reference by using the SYSTEM$REFERENCE function. You can pass the string identifier that the function returns to a stored procedure, application, or class instance. Alternatively, you can pass in the statement that creates the reference in place of the string identifier.

示例

Create a transient reference with session scope to table t1 with the SELECT privilege:

SELECT SYSTEM$REFERENCE('TABLE', 't1', 'SESSION', 'SELECT');

要在引用该表的作用域的生命周期内创建对同一表的引用（例如，如果将其传递给存储过程，则其生命周期将为存储过程最外层块的生命周期），请执行以下语句：

SELECT SYSTEM$REFERENCE('TABLE', 't1', 'CALL', 'SELECT');

Create a persistent reference to a table t1 with the INSERT privilege to pass to an application:

SELECT SYSTEM$REFERENCE('TABLE', 't1', 'PERSISTENT', 'INSERT');

创建要传递给存储过程的查询引用。此瞬态引用的生命周期适用于引用传递到的存储过程的最外层块：

SELECT SYSTEM$QUERY_REFERENCE('SELECT id FROM my_table', FALSE);

对于其他示例：

• Stored procedure example, see Background: The problem with passing objects and queries to stored procedures.
• Native App Framework application example, see Associating the Reference to the Application.
• Class instance example, see Training an Anomaly Detection Model with Labeled Data.

取消设置应用程序的持久引用

需要持久引用的应用程序或类还将提供用于取消设置引用的方法。方法名称可能因实现而异。

Alternatively, you can unset references by using an ALTER APPLICATION … UNSET REFERENCES statement.

1. You can use the SHOW REFERENCES command to view all references, including references that have been set for an application.

   For example, to view references for application my_app:

   Copy codeExpand code block

   SHOW REFERENCES IN APPLICATION my_app;

   Show lessSee more

   Scroll to top

2. 您可以使用 ALTER APPLICATION 命令取消设置已为应用程序设置的任何引用。

   For example, to unset the reference named table_to_read in application my_app:

   Copy codeExpand code block

   ALTER APPLICATION my_app UNSET REFERENCES('table_to_read');

   Show lessSee more

   Scroll to top

   For example, to unset all references in application my_app:

   Copy codeExpand code block

   ALTER APPLICATION my_app UNSET REFERENCES;

   Show lessSee more

   Scroll to top

使用引用时的注意事项

• 如果与引用关联的对象被重命名（或重新设置父级），则该引用将不再有效。

如果创建了同名的新对象，并且引用关联中编码的角色对新对象具有相关权限，则引用仍然有效。否则，它将失败并显示权限错误。

• If an object is swapped, and the roles encoded in the reference association have the relevant privileges on the new object that now has the swapped name, the reference remains valid. Otherwise it fails with a permissions error.

• 对象删除和取消删除：

  • 如果删除了与引用关联的对象，则引用关联将失效。

  • 如果对象取消删除，则引用关联将再次生效。

• 克隆

对于使用账户中对象的引用的类实例或其父数据库或架构，您可以进行克隆。

• 如果引用对象使用完全限定名称引用，则实例克隆引用原始对象。

• If the referenced object is referenced with a partially qualified or unqualified name, the instance clone might refer to the clone object, the original object, or to no real object depending on the cloning boundary.

• Replication is supported for an application or a database containing a class instance that uses a reference to objects in the consumer account.

只要复制以下对象，引用就会在目标账户中正常运行：

• 应用程序或类实例。
• 引用的对象。
• 创建引用的角色。

这些对象可以在不同的副本或故障转移组中复制。复制所有对象后，引用即可使用。

For providers of Snowflake Native App Framework applications, see also Considerations when using references.

监控引用的使用情况

You can view references requested by an application by using the SHOW REFERENCES command. If you have set any references for an application, the output will include information about the object, database, schema, and the identifier for each reference.

For example, to view references in application my_app:

SHOW REFERENCES IN APPLICATION my_app;