Dynamic table access control¶

This page explains the privileges required to create, query, manage, monitor, and drop dynamic tables. Anyone who sets up or manages dynamic table pipelines needs these grants.

The following example grants a role the minimum privileges to create a dynamic table in a specific schema:

GRANT USAGE ON DATABASE mydb TO ROLE transform_role;
GRANT USAGE ON SCHEMA mydb.myschema TO ROLE transform_role;
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE transform_role;
GRANT CREATE DYNAMIC TABLE ON SCHEMA mydb.myschema TO ROLE transform_role;
GRANT SELECT ON TABLE mydb.myschema.raw_orders TO ROLE transform_role;

After these grants, the role can create a dynamic table and query it as the owner. For a complete example, see Grant privileges to create a dynamic table.

For a full overview of the Snowflake privilege model, see Overview of Access Control and Access control privileges.

Privilege quick reference¶

When a role creates a dynamic table, that role becomes the owner. Snowflake uses the owner role to run background refreshes, so the owner role must keep USAGE and SELECT on all referenced objects at all times. If the owner role loses these privileges, refreshes fail. For details, see Understand the owner-role refresh model.

Every operation on a dynamic table requires USAGE on the containing database, schema, and (for operations that run queries) a warehouse. The examples below include these grants each time so you can copy any section independently.

The following table maps each operation to the required privilege:

OWNERSHIP includes all capabilities of OPERATE and MONITOR.

Grant privileges to create a dynamic table¶

To create a dynamic table, a role needs CREATE DYNAMIC TABLE on the schema, SELECT on every base table or view referenced in the definition (the SELECT query), and USAGE on the database, schema, and refresh warehouse.

-- Grant a role the privileges to create dynamic tables in a schema
GRANT USAGE ON DATABASE mydb TO ROLE transform_role;
GRANT USAGE ON SCHEMA mydb.myschema TO ROLE transform_role;
GRANT CREATE DYNAMIC TABLE ON SCHEMA mydb.myschema TO ROLE transform_role;
GRANT SELECT ON TABLE mydb.myschema.raw_orders TO ROLE transform_role;
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE transform_role;

After these grants, the role can create a dynamic table. For the full dt_orders definition, see Create a dynamic table.

Set up future grants¶

Grants on existing dynamic tables don’t apply to dynamic tables created later. Use FUTURE grants so that new dynamic tables automatically inherit the correct privileges:

GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA mydb.myschema TO ROLE analyst_role;
GRANT MONITOR ON FUTURE DYNAMIC TABLES IN SCHEMA mydb.myschema TO ROLE ops_role;

Grant privileges to query a dynamic table¶

To read from a dynamic table without the ability to create one, a role needs SELECT on the dynamic table and USAGE on the database, schema, and a query warehouse:

GRANT USAGE ON DATABASE mydb TO ROLE analyst_role;
GRANT USAGE ON SCHEMA mydb.myschema TO ROLE analyst_role;
GRANT USAGE ON WAREHOUSE analytics_wh TO ROLE analyst_role;
GRANT SELECT ON DYNAMIC TABLE mydb.myschema.dt_orders TO ROLE analyst_role;

With these grants, the role can query the dynamic table like any other table:

USE ROLE analyst_role;

SELECT order_id, product_name, line_total
FROM mydb.myschema.dt_orders
LIMIT 5;

+----------+--------------+------------+
| ORDER_ID | PRODUCT_NAME | LINE_TOTAL |
+----------+--------------+------------+
|     1001 | WIDGET A     |      89.97 |
|     1002 | WIDGET B     |      49.99 |
|     1003 | WIDGET A     |      59.98 |
|     1004 | GADGET X     |      62.50 |
+----------+--------------+------------+

To grant SELECT on every existing dynamic table in a schema at once:

GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA mydb.myschema TO ROLE analyst_role;

Grant OPERATE to manage a dynamic table¶

The OPERATE privilege lets a role suspend, resume, and manually refresh a dynamic table without giving it full OWNERSHIP. Grant OPERATE to roles that need to manage the refresh lifecycle, for example GRANT OPERATE ON DYNAMIC TABLE mydb.myschema.dt_orders TO ROLE pipeline_admin_role.

With OPERATE, the role can suspend, resume, and trigger a manual refresh:

USE ROLE pipeline_admin_role;

ALTER DYNAMIC TABLE mydb.myschema.dt_orders SUSPEND;
ALTER DYNAMIC TABLE mydb.myschema.dt_orders RESUME;
ALTER DYNAMIC TABLE mydb.myschema.dt_orders REFRESH;

OPERATE also allows changing the warehouse or target lag. Changing the warehouse requires USAGE on the new warehouse:

GRANT USAGE ON WAREHOUSE transform_wh_xl TO ROLE pipeline_admin_role;

ALTER DYNAMIC TABLE mydb.myschema.dt_orders SET
  WAREHOUSE = transform_wh_xl
  TARGET_LAG = '5 minutes';

Grant MONITOR to view metadata¶

The MONITOR privilege provides read-only access to operational metadata without the ability to alter the dynamic table. Grant MONITOR to roles that need observability, for example GRANT MONITOR ON DYNAMIC TABLE mydb.myschema.dt_orders TO ROLE ops_role.

With MONITOR, the role can view scheduling state in SHOW DYNAMIC TABLES, refresh history, and graph history:

USE ROLE ops_role;

-- View scheduling state and configuration
SHOW DYNAMIC TABLES LIKE 'dt_orders' IN SCHEMA mydb.myschema;

-- View recent refresh history
SELECT name, state, refresh_trigger, refresh_action
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'mydb.myschema.dt_orders',
  DATA_TIMESTAMP_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
));

-- View graph history for the current state of all dynamic tables
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY())
WHERE qualified_name = 'mydb.myschema.dt_orders';

Without MONITOR, the following fields are hidden when you query a dynamic table with only SELECT: text, warehouse, scheduling_state, last_suspended_on, and suspend_reason_code (Snowsight only).

MONITOR is read-only: it can’t suspend, resume, or refresh the table.

Grant OWNERSHIP to transfer full control¶

OWNERSHIP grants full control over a dynamic table, including the ability to drop/undrop, rename, swap, set comments, change clustering keys, and manage governance policies. Only one role can hold OWNERSHIP at a time.

You can transfer ownership using SQL or Snowsight.

GRANT OWNERSHIP ON DYNAMIC TABLE mydb.myschema.dt_orders
  TO ROLE pipeline_admin_role;

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

Understand the owner-role refresh model¶

Snowflake runs background refreshes using the dynamic table’s owner role. This means the owner role must retain the following privileges at all times, or scheduled refreshes fail:

When you transfer ownership, the new owner role must already have these privileges. If it doesn’t, refreshes fail and the dynamic table may be automatically suspended after repeated errors.

Diagnose a privilege error after ownership transfer¶

If you transfer ownership to a role that lacks USAGE on the refresh warehouse, the next scheduled refresh fails:

-- Transfer ownership to a role that lacks warehouse USAGE
GRANT OWNERSHIP ON DYNAMIC TABLE mydb.myschema.dt_orders
  TO ROLE new_owner_role;

The refresh fails with an error similar to:

SQL compilation error: Failed to refresh dynamic table with refresh_trigger SCHEDULED
at data_timestamp <ts> because of the error: SQL compilation error: Target table
failed to refresh: Dynamic Table 'MYDB.MYSCHEMA.DT_ORDERS' could not be refreshed
because warehouse 'TRANSFORM_WH' is missing.

To fix this, grant the missing privilege to the new owner role:

GRANT USAGE ON WAREHOUSE transform_wh TO ROLE new_owner_role;
GRANT SELECT ON TABLE mydb.myschema.raw_orders TO ROLE new_owner_role;
GRANT USAGE ON DATABASE mydb TO ROLE new_owner_role;
GRANT USAGE ON SCHEMA mydb.myschema TO ROLE new_owner_role;

Then resume the dynamic table:

ALTER DYNAMIC TABLE mydb.myschema.dt_orders RESUME;

Refresh with specific user privileges (EXECUTE AS USER)¶

Most dynamic table setups don’t need EXECUTE AS USER. Read this section only if your dynamic tables use row-access policies, masking policies, or need secondary roles for refresh.

By default, Snowflake refreshes a dynamic table as an internal SYSTEM user using the owner role. The EXECUTE AS USER option changes this so that refreshes run on behalf of a named user. The primary role is still the dynamic table’s owner role, but the named user’s secondary roles are also activated.

Use EXECUTE AS USER when:

• Row-access or masking policies reference CURRENT_USER(). The default SYSTEM user doesn’t match policy conditions. Setting EXECUTE AS USER causes CURRENT_USER() to evaluate as the named user during refresh.
• Secondary roles are needed. The owner role alone can’t reach all required objects, but a user’s secondary roles can.
• Audit attribution matters. Refreshes are attributed to the named user instead of the SYSTEM user.

Grant privileges for EXECUTE AS USER¶

The owner role must have IMPERSONATE on the target user, and the target user must hold the dynamic table’s owner role:

-- Grant IMPERSONATE so the owner role can act on behalf of the service user
GRANT IMPERSONATE ON USER service_user TO ROLE transform_role;

-- The service user must also hold the owner role
GRANT ROLE transform_role TO USER service_user;

Then create or alter the dynamic table with the EXECUTE AS USER clause:

CREATE OR REPLACE DYNAMIC TABLE mydb.myschema.dt_orders
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
  EXECUTE AS USER service_user
AS
  SELECT ... FROM mydb.myschema.raw_orders ...;

To grant the refresh access to all roles assigned to the specified user (not just the user’s default role), add USE SECONDARY ROLES ALL:

CREATE OR REPLACE DYNAMIC TABLE dt_orders
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    EXECUTE AS USER service_user
        USE SECONDARY ROLES ALL
AS
    SELECT ... FROM raw_orders;

For the full dt_orders definition, see Create a dynamic table.

To change the user on an existing dynamic table, run ALTER DYNAMIC TABLE <name> SET EXECUTE AS USER <user>. To revert to the default SYSTEM user, run ALTER DYNAMIC TABLE <name> UNSET EXECUTE AS USER.

Cross-product considerations for EXECUTE AS USER¶

• Data masking and row-access policies. Policies that use CURRENT_USER() evaluate as the named user, not the SYSTEM user.
• Replication and failover. The user name and role name are replicated to secondary deployments. If the user or role isn’t available on the secondary, the user is marked as INVALID and refreshes fail until the user and role are recreated.

Privileges for dual warehouses¶

When using INITIALIZATION_WAREHOUSE, all privilege requirements match those for WAREHOUSE. The role needs USAGE on both warehouses:

For more information, see Choose and size warehouses for dynamic tables.

What’s next¶

• To configure warehouses for refresh, see Choose and size warehouses for dynamic tables.
• To monitor refresh health, see Monitor dynamic tables.
• To troubleshoot refresh failures, see Troubleshoot dynamic table refresh issues.
• To revoke privileges, see REVOKE <privileges> … FROM ROLE.