Programmatic Config - Removal / addition of schemas and business roles

[MaRo99382]

Describe the bug While testing the programmatic config in python, whenever I am running SnowDDL with -env-prefix 'dev', I am removing two schemas defined in yaml from the config via config.remove_blueprint()and after that I am adding a couple of new schemas programatically to the config by creating a deepcopy of the SchemaBlueprints I removed from the config before. I have two business roles that have the schema_owner role set on all schemas in the database. When applying my config I have two issues currently:

1. SnowDDL is still trying to apply the schema owner role of schemas that do not exist anmyore to the business roles. Interestingly, it did not create the corresponding schema roles for the removed schemas, which was expected. But it is weird that it then tries to grant a role that it never created to the business role. Here is the error for one of the two business roles:

   2024-08-05 14:36:51.100 - WARNING - Resolved ROLE [DEV__TRANSFORMER__B_ROLE]: ERROR
   (
   message   =>  SQL compilation error:
   Role 'DEV__DWH__STAGING__OWNER__S_ROLE' does not exist or not authorized.
   errno     =>  2003
   sqlstate  =>  02000
   sfqid     =>  01b62574-0102-e6ca-0002-b4c2000ce826
   sql       =>  GRANT ROLE "DEV__DWH__STAGING__OWNER__S_ROLE" TO ROLE "DEV__TRANSFORMER__B_ROLE"
   )

2. SnowDDL does not automatically grant the schema level roles of the programatically added schemas to the business roles. SnowDDL does create the Owner, Read and Write schema roles for the programatically added schemas. But it does not assign those to the business roles. The business roles are set with - <database_name>.* configurations in the yaml file, so I would assume this is also applied to all programatically added schemas later on.

Expected behavior

See above.

Attach log

See above.

Attach YAML config (if applicable) I attached my python file from the __custom directory and the business_role.yaml file I use.

01_test.zip

[littleK0i]

Business and technical role grants are currently processed in parser: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/business_role.py#L105-L150

Custom config is applied after that. If we add / remove schemas, grants list of BusinessRoleBlueprint should be updated accordingly. You may perform these changes "in-place", since blueprint is a basic pydantic model.

Please make sure to use "build_role_ident()" for correct role naming when building grants.

Schema roles are not affected, since blueprints for schema roles are essentially generated on the fly in resolver.

In theory, we may consider adding helper functions to automate this a little bit, if changing schemas dynamically will become popular.

[MaRo99382]

Ah okay, I thought maybe SnowDDL is building the roles based on the final objects in the config, after the programmatic config changed it. But then I will manipulate the roles myself. :)

Could you show me a way to create or receive the config object that gets passed to the handler function but outside of the handler? Then I am able to test my code outside of the handler function and can e.g. write unit tests for functions I use etc.

[littleK0i]

There are many ways:

(1) The most flexible Create your own Python script, overload BaseApp class and implement custom endpoint / CLI command. For example, this is how Config object is created and filled: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/app/base.py#L296

SingleDB is an example of such overloaded endpoint with custom logic: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/app/singledb.py

(2) Testing in handler In theory, you may implement multiple handlers. Define "final" handler with prefix 99_ to make sure it is executed last. And run tests in final handler. If something is wrong with config, raise ValueError, and it will stop SnowDDL from applying such config.

(3) Generate directories and YAML configs dynamically Sometimes it might be easier to create directories for databases and YAML files for specific objects dynamically.