动态表访问控制

本主题讨论对动态表执行创建、查询、更改、查看和删除等操作所需的权限。

For more information about the Snowflake privilege model, see Overview of Access Control and Access control privileges.

转移所有权

要让用户完全访问动态表，可以执行以下任一操作：

When assigning grants, ensure that you specify the object type as DYNAMIC TABLE because dynamic tables have a different set of privileges than regular tables.

要授予动态表的 OWNERSHIP 权限，请确保接收角色在以下对象上具有 USAGE 权限。否则，后续的计划刷新将失败。

包含动态表的数据库和架构。
用于刷新表的仓库。

To transfer ownership of a dynamic table, you can use either the GRANT OWNERSHIP command or Snowsight.

The following example uses the GRANT OWNERSHIP command to grant ownership privileges on my_dynamic_table to the budget_admin role.

GRANT OWNERSHIP ON DYNAMIC TABLE my_dynamic_table TO ROLE budget_admin;

The following example uses the GRANT OWNERSHIP command to grant ownership privileges on all future dynamic tables created in the mydb.myschema schema to the budget_admin role.

GRANT OWNERSHIP ON FUTURE DYNAMIC TABLES IN SCHEMA mydb.myschema TO ROLE budget_admin;

To learn more about the Snowflake privilege model, see Overview of Access Control and Access control privileges.

使用特定用户权限和次要角色来刷新动态表

除了任务所有者角色的权限外，动态表还可以配置为使用特定用户的权限运行。指定 EXECUTE AS USER 的动态表以指定用户的身份（而非系统用户）运行。

例如，您可以授予用户一个主要角色以访问表，以及一个次要角色以访问虚拟仓库。然后，用户可以创建一个动态表，使用两个角色的组合权限进行操作，从而简化权限管理并增强数据操作的灵活性。

虽然 EXECUTE AS USER 选项允许在用户角色下刷新动态表，对这些动态表的所有其他操作都遵循标准权限模型。

关键用例

Manage multi-role privileges: In situations where users have secondary roles, they can create and refresh a dynamic table using the combined privileges of their primary and secondary roles. This configuration ensures that the user who is refreshing the dynamic table has the necessary permissions to access all required resources, while maintaining consistency with existing role-based access controls.
Granular security and governance controls: Users can configure optional security measures with additional options such as REQUIRE USER, where a dynamic table can’t run unless a user is specified.
Accountability for all operations: All refreshes on an EXECUTE AS USER dynamic table are attributed to the configured user instead of the SYSTEM user. This attribution helps maintain a clear audit trail for all operations.

访问控制

The owner role of the dynamic table must be granted the IMPERSONATE privilege on the user specified by EXECUTE AS USER, and the specified user must be granted the owner role of the dynamic table. If the IMPERSONATE privilege is revoked, the dynamic table refresh will fail and the dynamic table might be auto suspended.

当动态表刷新时，刷新会话的主要角色是动态表的所有者角色，用户的默认次要角色将被激活。用户将能够使用 USE ROLE 命令切换主要角色，并使用 USE SECONDARY ROLES 命令在刷新会话中调整次要角色。

跨产品注意事项

Data masking and row access policies: Policies—for example, those using CURRENT_USER()—evaluate based on the specified user and roles rather than the SYSTEM user.
复制和故障转移： 将用户名和角色名称复制到次要部署。

如果用户或角色在次要部署中不可用，则该用户将被标记为 INVALID，而且在修复之前，刷新将失败。

如果其余角色提供足够的权限，则在执行期间将跳过无效的次要角色。

示例

配置动态表以用户身份运行刷新

以下示例创建了一个以指定用户身份执行刷新的动态表，并将主要角色设置为动态表的所有者角色。使用用户设置的任意用户沿袭参数执行刷新。

如果未显式指定次要角色的选项，则默认刷新为用户的当前会话设置。

CREATE DYNAMIC TABLE my_dynamic_table
  [ EXECUTE AS USER my_user_name
    [ USE SECONDARY ROLES { ALL | NONE | (<role1>, <role2>, ... ) } ]
  ]

为现有动态表设置次要角色

The following example configures a dynamic table to execute as the specified user. If no specific secondary roles are selected, the refresh process defaults to the current session’s active secondary roles. If the dynamic table is already set to execute as a specific user, this command will update the configuration to execute as the user executing the ALTER DYNAMIC TABLE command.

执行此命令需要动态表的 OWNERSHIP 权限。

ALTER DYNAMIC TABLE my_dynamic_table SET
  EXECUTE AS USER my_user_name
  [ USE SECONDARY ROLES { ALL | NONE | (<role1>, <role2>, ... ) } ]

切换动态表，以 SYSTEM 用户的身份执行¶

以下示例将动态表恢复为由使用动态表的所有者角色的 SYSTEM 用户执行。

执行此命令需要动态表的 OWNERSHIP 权限。

ALTER DYNAMIC TABLE my_dynamic_table UNSET EXECUTE AS USER;

创建动态表的权限

要创建动态表，必须使用具有以下权限的角色：

Privilege Object

CREATE DYNAMIC TABLE Schema in which you plan to create the dynamic table.

SELECT Existing tables and views that you plan to query for the new dynamic table.

USAGE

Privilege	Object
CREATE DYNAMIC TABLE	Schema in which you plan to create the dynamic table.
SELECT	Existing tables and views that you plan to query for the new dynamic table.
USAGE	计划用于新动态表的数据库和架构。计划用于刷新表的仓库。 Note Although you can execute `CREATE DYNAMIC TABLE ... INITIALIZE = ON_SCHEDULE` with a secondary role that has the USAGE privilege, the dynamic table won’t successfully refresh if the primary role lacks this privilege, and therefore it won’t be initialized.

计划用于新动态表的数据库和架构。

计划用于刷新表的仓库。

Note

Although you can execute CREATE DYNAMIC TABLE ... INITIALIZE = ON_SCHEDULE with a secondary role that has the USAGE privilege, the dynamic table won’t successfully refresh if the primary role lacks this privilege, and therefore it won’t be initialized.

要创建依赖于另一个动态表的动态表，必须使用具有以下权限的角色：

权限	对象
SELECT	创建新的动态表时计划查询的动态表。
OPERATE	All upstream dynamic tables the new dynamic table depends on. Only required if you set the dynamic table to refresh synchronously at creation and the upstream dynamic table is referenced directly in the definition, not through DYNAMIC_TABLE_REFRESH_BOUNDARY().

查询动态表的权限

To query a dynamic table, you can use a role that has the privileges to create a dynamic table. For scenarios where a user only needs to query a dynamic table - for example, a data analyst - use a role that has the following privileges:

Privilege	Object
USAGE	包含动态表的数据库和架构。用于运行查询的仓库。
SELECT	The dynamic table being queried.

Privilege

Object

USAGE

包含动态表的数据库和架构。

用于运行查询的仓库。

SELECT

The dynamic table being queried.

更改动态表的权限

要更改动态表，必须使用拥有该动态表的 OWNERSHIP 或 OPERATE 权限的角色。

如果拥有动态表的 OPERATE 权限，可以使用 ALTER DYNAMIC TABLE 命令执行以下操作：

Suspend a dynamic table using ALTER … SUSPEND.
Resume a dynamic table using ALTER … RESUME.
Refresh a dynamic table using ALTER … REFRESH.
Set or change the warehouse and/or target lag using ALTER … SET.

如果您拥有动态表的 OWNERSHIP 权限，则除了上面列出的操作外，还可以执行以下操作：

Set or unset a comment using ALTER … SET | UNSET COMMENT.
Rename a dynamic table using ALTER … RENAME TO.
Swap a dynamic table with another using ALTER … SWAP WITH
Set a new parameter using ALTER … SET
Specify or drop clustering keys. See Clustering actions (`clusteringAction`).
Change governance policies. See Data Governance policy and tag actions (`dataGovnPolicyTagAction`).
Change search optimization. See Search optimization actions (`searchOptimizationAction`).

查看动态表元数据的权限

要查看元数据，必须使用具有该动态表 MONITOR 权限的角色。

对于用户只需要查看动态表的元数据和 Information Schema 的情况（例如，数据科学家持有的角色），请使用对该动态表具有 MONITOR 权限的角色。虽然 OPERATE 权限授予了这一访问权限，但它也包括更改动态表的功能，因此 MONITOR 更适合用户不需要更改动态表的情况。

如果拥有动态表的 MONITOR 权限，则可以执行以下操作：

Use the DESCRIBE DYNAMIC TABLE command and Snowsight dynamic tables details page to view the specific details for a dynamic table. The following fields are hidden if you only have the SELECT privilege on a dynamic table: text, warehouse, scheduling_state, last_suspended_on, and suspend_reason_code (UI-only).
Use the SHOW DYNAMIC TABLES command to view which dynamic tables you have access to.
Call the DYNAMIC_TABLE_GRAPH_HISTORY table function to view graph history.
Call the DYNAMIC_TABLE_REFRESH_HISTORY table function to view refresh history.

删除动态表的权限

To drop a dynamic table, you must use a role that has the OWNERSHIP privilege on that dynamic table.

使用双仓库的权限

使用 INITIALIZATION_WAREHOUSE 所需的所有权限要求与 WAREHOUSE 相同。

操作	权限
使用 INITIALIZATION_WAREHOUSE 的 CREATE DYNAMIC TABLE	两个数据仓库的 CREATE DYNAMIC TABLE 和 USAGE，即 WAREHOUSE 和 INITIALIZATION_WAREHOUSE。
ALTER DYNAMIC TABLE … SET / UNSET INITIALIZATION_WAREHOUSE	动态表的 OWNERSHIP 或 OPERATE，以及适用仓库的 USAGE。
使用 INITIALIZATION_WAREHOUSE 的动态表的 ALTER DYNAMIC TABLE … REFRESH	动态表的 OPERATE，以及适用仓库的 USAGE。

For more information, see Understand warehouse usage for dynamic tables.