Dynamic table access control¶

Transfer ownership¶

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

• Grant OWNERSHIP on the dynamic table to a role.

• Grant all privileges, except OWNERSHIP, on the dynamic table to a role.

• Grant the OWNERSHIP privilege or ALL PRIVILEGES on future dynamic tables to a role.

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.

To grant the OWNERSHIP privilege on dynamic tables, ensure the receiving role has the USAGE privilege on the following. Otherwise, subsequent scheduled refreshes fail.

• The database and schema that contains the dynamic table.

• The warehouse used to refresh the table.

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

GRANT OWNERSHIP ON DYNAMIC TABLE my_dynamic_table TO ROLE budget_admin;

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.

Refresh dynamic tables with specific user privileges and secondary roles¶

Dynamic tables can be configured to refresh with the privileges of a specific user, in addition to privileges of the owner role. Dynamic tables that specify EXECUTE AS USER run on behalf of the named user, instead of the system user.

For example, you can grant a user a primary role that provides access to a table and a secondary role that provides access to a virtual warehouse. The user can then create a dynamic table that operates with the combined privileges of both roles, simplifying privilege management and enhancing the flexibility of your data operations.

While the EXECUTE AS USER option enables dynamic tables to refresh under the user’s role, all other operations on these dynamic tables adhere to the standard privilege model.

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

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

ALTER DYNAMIC TABLE my_dynamic_table UNSET EXECUTE AS USER;

Privileges to create a dynamic table¶

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

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

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:

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:

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

If you have the OWNERSHIP privilege on a dynamic table, you can do the following in addition to the operations listed above:

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

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.

For scenarios where the user only needs to view the metadata and Information Schema of a dynamic table (for example, roles held by data scientists), 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.

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.

Privileges to use dual warehouses¶

All privilege requirements for using INITIALIZATION_WAREHOUSE are the same as WAREHOUSE.

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