Dynamic table access control¶

This topics discusses the privileges needed to perform operations with dynamic tables, such as creating, querying, altering, viewing, and dropping.

To provide a user full access to a dynamic table, you can do either of the following:

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

Privileges to create a dynamic table¶

To create a dynamic table, you must use a role that has the following privileges:

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

Database and schema that you plan to use for the new dynamic table.

Warehouse that you plan to use to refresh the table.

To create a dynamic table that depends on another dynamic table, you must use a role that has the following privileges:

Privilege

Object

SELECT

Dynamic table you plan to query from to create the new dynamic table.

OPERATE

All upstream dynamic tables the new dynamic table depends on. Only required if you set the dynamic table to refresh synchronously at creation.

Privileges to query a dynamic table¶

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

Database and schema that contains the dynamic table.

Warehouse used to refresh the table.

SELECT

The dynamic table being queried.

Privileges to alter a dynamic table¶

To alter a dynamic table, you must use a role that has either the OWNERSHIP or OPERATE privilege on that dynamic table.

If you have the OPERATE privilege on a dynamic table, you can do the following with the ALTER DYNAMIC TABLE command:

Privileges to view a dynamic table’s metadata¶

To view the metadata and Information Schema of a dynamic table, you must use a role that has the MONITOR privilege on that dynamic table.

While the OPERATE privilege grants this access, it also includes the capability to alter dynamic tables, making MONITOR the more suitable option for scenarios where a user does not need to alter a dynamic table. For example, roles held by data scientists.

If you have the MONITOR privilege on a dynamic table, you can do the following:

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

  • Navigate to the DYNAMIC_TABLE_GRAPH_HISTORY table function to view graph history.

  • Navigate to the DYNAMIC_TABLE_REFRESH_HISTORY table function to view refresh history.

Privileges to drop a dynamic table¶

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

Language: English