I am new to snowflake and have been tasked to automate roles and permission using snowDDL

[raginc]

I am new to snowflake and have been tasked to automate roles and permission using snowDDL. had used terraform to create db and roles but manageability seems to be complex.

I see roles and permissions are created based on configs with business_role / tech_role and user_role yml using snowDDL,

can you please answer to the following question

1. if database is created outside snowDDL, do the roles created by snowDDL can be granted permission on these database? if you can provide any example would be helpful
2. how to authenticate snowflake account with SSO using snowDDL
3. what are the things I need to consider if I just need to focus on automating roles and permission via snowDDL, do I need additional blueprint to be created?
4. what are prerequisite to run snowDDL from github action?

[littleK0i]

1. SnowDDL generally needs to create both database and roles for it. Applying only roles and permissions to existing database is not supported, since it is quite complex to implement. Ownership of every object should be changed, all existing permissions should be revoked and new permissions should be created.
2. You may add CLI option --authenticator externalbrowser to auth via SSO. But normally I would suggest to create & use RSA key for auth from scripts.
3. Normally both objects and permissions should be managed by SnowDDL. You may try to create objects externally by specifying is_sandbox: true parameter for schemas, but removes most of benefits of having an object management tool.
4. RSA key for auth. Naturally, you will not be able to use browser for auth in GitHub action. Everything else should be the same. SnowDDL is just a Python script.

[raginc]

Thank you for the response,
Now if I start in with snowDDL, do I try repo with config directly without any modification except using is_sandbox: true so it creates a different database with objects databases / schemas and so it doesn't affect existing db's.

is it mandatory to use same naming convention like business_role.yml or I can use any name and modify config files to use them?

[littleK0i]

I highly suggest to run all tests using a separate empty Snowflake account. It applies to all object management tools, not only SnowDDL. It is relatively easy to drop existing objects accidentally.

Config file names and suffixes for roles are fixed, but you may still choose role names.

For example, business role my_test in config will be translated into MY_TEST__B_ROLE in Snowflake.

[raginc]

I was looking for dynamically creating resource based on env and I see snowDDL support env_prefix
I have not find it usage.

Do we need to create multiple custom config for each enviroment, for e.g. dev, stage and prod?

[raginc]

can you please share the example of env-prefix, how do I use this parameter to create different params for each env?

[littleK0i]

You may check documentation page explaining env prefix: https://docs.snowddl.com/guides/other/env-prefix

It can be utilized to create separate sets of objects in Snowflake for each developer and each CI/CD script using the same config. Ultimately, it helps to avoid collisions and conflicts during development and testing process, when multiple entities try to change the same object.

You may try adding --env-prefix TEST CLI option and see what happens.

P.S. Please remember to do all experiments on a separate Snowflake account, not on prod.