Permission issues when creating dynamic tables

[Github-dm-CDE]

Describe the bug When creating dynamic tables, these are created with the MY_DB__MY_SCHEMA__OWNER__S_ROLE owner. Attempts to update the dynamic tables then fail, as they cannot see either the warehouse to be used or the base table to be used from another schema. Both the warehouse to be used and the schema to be used were created with the SNOWDDL_ADMIN as owner. As soon as the MY_DB__MY_SCHEMA__OWNER__S_ROLE is manually assigned the right to use the warehouse, for example, it can be seen and used by the dynamic table. Unfortunately, it is not entirely clear to us from the documentation how we can avoid this manual intervention and automate the whole thing.

Expected behavior SnowDDL should automatically take care of the necessary permissions for the objects required to update dynamic tables.

Attach log

Attach YAML config (if applicable) PROD/LANDING/table/DIM_PAGE.yaml

columns:
  SOME_COLUMN:
    type: VARCHAR(2)
cluster_by:
  - SOME_COLUMN

PROD/MSTR/dynamic_table/DIM_PAGE_DY_V20240822.yaml

columns:
  SOME_COLUMN: ''
text: |-
  SELECT
    SOME_COLUMN
  FROM LANDING.DIM_PAGE
  QUALIFY (LEAD(SOME_COLUMN) OVER ()) IS NULL
cluster_by:
  - SOME_COLUMN
target_lag: 1 hour
warehouse: XLARGE
initialize: ON_SCHEDULE

[littleK0i]

Please take a look at large block of owner_* parameters on schema level: https://docs.snowddl.com/basic/yaml-configs/schema

• owner_schema_read would let dynamic table access objects in another schema
• owner_warehouse_usage would let dynamic table to use specific warehouse

Use these parameters to add additional grants for schema owner role.

---

At some point I considered granting it automatically, but fundamentally decided to avoid it.

1. It potentially conflicts with idea of "sandbox" schemas when schema objects are created outside of SnowDDL. Future grants is the only way to handle it.
2. Snowflake constantly adds new object types and new types of grants. Any king of "magic" with grants for specific objects eventually may become obsolete or dangerous.
3. Automatically detecting objects from SQL text is not easy. For example, you may refer to a PROCEDURE, which internally generates object names dynamically. With dynamic identifiers it is not possible to figure out which grants should be applied.

[Github-dm-CDE]

Ahh, that was the missing piece of the puzzle! Thank you very much! Of course, the permissions and why they are not generated automatically also makes sense. Perhaps a small note in the SnowDDL documentation on dynamic tables would be helpful, in which it is pointed out to ensure that the schema parameters have been set correctly?