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:

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

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.

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.

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 run the query.

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 metadata, 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.

  • Call the DYNAMIC_TABLE_GRAPH_HISTORY table function to view graph history.

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