Recommended approach for managing grants on cloned db / schemas / tables, when source is managed by SnowDDL

[acaruso7]

I have a database RAW which is fully managed by SnowDDL, thus I have a schema tech role for every schema in this db. I also create a clone of this database, RAW_SNAPSHOT, once daily, using templated SQL executed by an orchestration tool. Clones may occur at the db level, schema level, or table level, depending on configuration of this workflow. When the clone occurs at the database or schema level, I also copy all grants from the source to target (this is necessary because by default Snowflake doesn't copy grants from the container objects, only children objects - see https://docs.snowflake.com/en/user-guide/object-clone#access-control-privileges-for-cloned-objects). This code works and is reliable

I was hoping to let the orchestrator tool fully own and manage this cloned snapshot db, but now I've discovered that this workflow is creating resource drift with the grants for schema tech roles in the source db. For example, suppose I have a schema RAW.MYSCHEMA and associated schema tech role RAW__MYSCHEMA__OWNER_S_ROLE that has USAGE ON DATABASE RAW grant. When I clone the schema to RAW_SNAPSHOT, I also clone the grants - so RAW__MYSCHEMA__OWNER_S_ROLE now has USAGE ON DATABASE RAW_SNAPSHOT grant. Then the next time I run snowddl plan, the tool notices all these additional grants and tries to revoke them from the schema role - REVOKE USAGE ON DATABASE "RAW_SNAPSHOT" FROM ROLE "RAW__MYSCHEMA__OWNER__S_ROLE";

Is there any way to tell SnowDDL "ignore additional grants on schema tech roles which were not granted by SNOWDDL_ADMIN"? Or is my approach of letting the orchestrator handle this clone and it's grants generally not going to work? I know SnowDDL has a --clone-table CLI flag, but this doesn't really seem like the appropriate use case, because I am not separating environments here, but just performing one more step in an ETL pipeline. Furthermore the code being run by my orchestrator tool is reliable and very performant because it clones the objects async, which is necessary because there are a large number of objects to clone and we have a strict SLA on the clone time. So I would prefer to let the orchestrator fully manage this clone, and just let the privileges granted on the source objects by SnowDDL just "propagate" over into the clone

[littleK0i]

In my opinion, cloning is a big trap in general. It seems like a good idea at first, but it creates a lot of various sneaky problems which are difficult to solve.

But let's see what we can do here.. Depending on the end goal, we may consider two possible solutions:

(1) If clones are an integral part of production environment and used for business logic
You may consider creating all "snapshot" databases and schemas in SnowDDL in advance and assigning is_sandbox: true flag. You may do it dynamically using python modules in __custom config sub-directory.

When the time comes, you only "clone" individual objects from RAW to RAW_SNAPSHOT without COPY GRANTS option. In this case fresh future grants will be applied for each cloned object instead, and you'll get new objects with clean grants for RAW_SNAPSHOT only and without any relations to RAW.

Do not clone entire databases or schemas, it creates too many issues with ownership, grants, sequences, etc.

(2) If clones are a part of separate environment in any shape or form, or should be managed by other tools
You may run your orchestration job with role which is not related to SnowDDL. You may extend this job to run CREATE DATABASE, CREATE SCHEMA, and create future grants with GRANT ... ON FUTURE.

After that you may clone objects from RAW to newly created RAW_SNAPSHOT. Again, future grants will be applied, and you'll get objects with clean permissions.

More importanly, SnowDDL will not try to manage any objects which are not explicitly owned by role other than SnowDDL admin role. Your objects will be safe from intervention.

[acaruso7]

(1) If clones are an integral part of production environment and used for business logic

You may consider creating all "snapshot" databases and schemas in SnowDDL in advance and assigning is_sandbox: true flag.
. . .
When the time comes, you only "clone" individual objects from RAW to RAW_SNAPSHOT without COPY GRANTS option. In this case fresh future grants will be applied for each cloned object instead, and you'll get new objects with clean grants for RAW_SNAPSHOT only and without any relations to RAW.

This is assuming the individual schemas are defined in SnowDDL (so the schema roles get autogenerated on apply), and is_sandbox is defined at the schema level (so objects within that schema get ignored by SnowDDL), right? The only problem with this is that every time I add a new schema to RAW, I also need to add it to RAW_SNAPSHOT in the config, which is a bit of a maintainability / "toil" issue. But I can see this working, maybe I can automate that "duplexing" of the config

Do not clone entire databases or schemas, it creates too many issues with ownership, grants, sequences, etc.

I see what you mean with respect to privilege management, though I think there are also a lot of benefits. For example, when objects get "hard deleted" from the source, the easiest way to propagate those changes to the target is to reclone at the database or schema level, thus "automating" some of the object cleanup. Right now my primary method of cloning is at the table level, though I also support schema level cloning. It would be great if I could continue to support this, because I would prefer not to have to intervene immediately with a deployment every time a new schema gets created in the source. Ideally, that new schema gets cloned into RAW_SNAPSHOT on a schedule without any intervention, and is immediately available for consumers once that clone occurs. Though, I suppose until the schema is added to SnowDDL config, consumers using business roles won't have the appropriate read access anyway, so a snowddl apply might be a requirement anyway

(2) If clones are a part of separate environment in any shape or form, or should be managed by other tools

You may run your orchestration job with role which is not related to SnowDDL. You may extend this job to run CREATE DATABASE, CREATE SCHEMA, and create future grants with GRANT ... ON FUTURE.

In this case, are you suggesting everything in the clone be managed outside of SnowDDL, including the roles to which the future grants will be applied? Suppose I have a business role TRANSFORMER__B_ROLE which is managed by SnowDDL, is responsible for data transformation jobs, and has USAGE ON FUTURE SCHEMAS IN DATABASE RAW_SNAPSHOT and SELECT ON FUTURE TABLES IN SCHEMA RAW_SNAPSHOT.*. The latter is obviously not compilable SQL, and to accomplish such grants, we would need to know the schema names upfront, so that when they are created we can grant privileges on future objects. So if my orchestrator creates a new schema and applies future grants on that schema to this SnowDDL managed TRANSFORMER__B_ROLE, the next time SnowDDL runs it will try to revoke those grants because they are missing from the config. So I think I would need to maintain a custom role outside of SnowDDL in this case, which kindof defeats the purpose of using the tool

So I am leaning more towards option 1 here, and leaving the grant management to be the responsibility of SnowDDL. As I use the tool more I'm learning that this seems to be it's strong point and the intended use of the tool - leverage the autogenerated roles, grants, and role hierarchy as much as possible

[acaruso7]

The only problem with this is that every time I add a new schema to RAW, I also need to add it to RAW_SNAPSHOT in the config, which is a bit of a maintainability / "toil" issue. But I can see this working, maybe I can automate that "duplexing" of the config

Nevermind, I see now that this is pretty trivial to handle programmatically with the python templating feature. I just get copies of the blueprint objects for the RAW db and it's schemas, change the DatabaseIdent name to RAW_SNAPSHOT, and apply those blueprints to the config

[littleK0i]

Precisely.

I generally prefer to create new blueprint objects and fill each property individually. It is a bit easier to maintain in the long run as more properties start to diverge over time.

But model_copy() + change one property is perfectly viable. Blueprints are basic pydantic v2 objects.