Dynamic object creation and granting other roles access to those objects / automated snowddl apply invocations

[acaruso7]

Dynamic Object Creation

I have a need to create new schemas on the fly, with names that aren't known beforehand

I know I can use a tech role to do this, but then how do I grant access to other roles also managed by SnowDDL?

For example, suppose I have the following tech role:

mydb_schema_creator:
  grants:
    DATABASE:CREATE SCHEMA:
      - mydb

Suppose I grant this tech role to a pre-existing business role:

transformer:
  tech_roles:
    - mydb_schema_creator

Obviously if TRANSFORMER__B_ROLE creates some schemas and objects within those schemas, it will also have read access on them because it will be the owner. However, suppose I also want to grant access to some other business role? Since the schemas aren't managed by SnowDDL, I can't simply add schema_read on other business roles

Automated snowddl apply invocations

There was some similar discussion in #58, only asking about database creation privileges (which in this case I don't think is possible with a tech role). The suggestion was to use Python config to dynamically create the dbs. This is possible of course but I don't really want to be invoking snowddl apply in an automated context. For example, suppose I have a CI workflow where some objects with names determined by the context of that workflow must be generated on the fly. I can read those names in with python config and then invoke snowddl apply, but this does not seem like best practice because of the potential side effects that may be experienced from the apply. Who knows what DDL is going to be executed? This problem is mitigated by the fact that we can apply only safe operations, and scope down to certain object types, but there are still limitations (see #90)

Is there any guidance anyone can share here? Is the general recommendation to only use SnowDDL in a context where the plans are reviewed by a human, or can automated invocations a feasible and safe option?

[littleK0i]

Creating new schema involves quite a lot of SQL commands. You may see all of them by adding a new schema and running snowddl plan.

1. Create managed schema.
2. Create schema roles.
3. Add future grants.
4. Possibly grant newly created schema roles to business roles with matched patterns in schema_owner, schema_write, schema_read.

In theory, you may copy-paste these commands, wrap into Python function and run every time you need to create a new schema. Commands should be executed by SnowDDL admin role for consistency.

With this approach you'll emulate what SnowDDL is doind without running SnowDDL every time.

---

Running SnowDDL in "safe" mode each time should be fine as well. All commands creating new schema are considered "safe".

[littleK0i]

@acaruso7 , maybe it is a bit late, but let's give it a try.

Please note, since SnowDDL version 0.27 it is now possible to define permission model which allows creation of custom schemas.

Relevant guides:
https://docs.snowddl.com/guides/permission-model
https://docs.snowddl.com/basic/yaml-configs/permission-model

Specific example for Fivetran: https://docs.snowddl.com/guides/other-guides/fivetran

Even if we internally never allow external software to create schemas, I recognize this as a perfectly valid use case. So this feature is now fully supported.