Different Permissions on Dev, UAT and Prod accounts

[leopasta-enable]

We are setting up our snowflake warehouse, and decided to create different accounts for Dev, UAT and Live. We plan to deploy the same configuration on each account.

That seems pretty standard, but I am not sure how we manage the different permissions in those environments.
For example, our team's DATA_TEAM__B_ROLE would have permission to deploy SnowDDL directly in dev, as well as read and write to all schemas. But when deploying the same project on UAT, that group should only have read access (with snowddl being deploying in CI/CD and writing being done only by the pipelines/services).

I thought on using placeholders for this, but I can't think how we can implement this without some level of if/then/else expression in the business_roles.yaml.

What am I missing? How have other users implemented this type of "same project deployed with different permission profile in different accounts" pattern?

[littleK0i]

According to original idea of the tool, using exactly the same or almost the same config for DEV and PROD accounts is crucial for proper testing.

For example, if you try to test a script which should run by user with "DATA_TEAM" business role, it may succeed on DEV, but fail on PROD due to major differences in permissions. The more differences in config you have, the less useful testing becomes in finding actual problems.

I suspect, members of "data team" effectively act as administratos on DEV account. So maybe they could do development and debugging using special "admin" user(s) created outside of SnowDDL and inheriting SnowDDL admin role privileges. They should be able to see and run anything while still being able to test restricted "DATA_TEAM" business role if necessary.

If you use env prefix for sub-environments, you may find option --env-admin-role useful for this type of setup.

---

However, we also recognize that every organization is different, and you may have a strong business cast to modify config depending on environment. You may do so using programmatic config. It helps to adjust "blueprints" on the fly and apply any kind of "if/then/else" logic.

In this specific case, you may iterate over business role blueprints and make necessary changes.

from snowddl import *
from os import getenv


def handler(config: SnowDDLConfig):
    if getenv("ENV") == "DEV":
        # Iterate over business roles
        for full_name, bp in config.get_blueprints_by_type(BusinessRoleBlueprint).items():
            bp.grants = ... <modify grants here> ...

Parser code describes how BusinessRoleBlueprints are built from YAML configs:
https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/business_role.py#L73-L149

[littleK0i]

A few more notes about users.

For example, we have a developer with name John Doe. He has a user entry in SnowDDL config named JOHN_DOE and belongs to "data team" business role. This users has restricted access and exists in all environments.

However, this person also acts as administrator specifically on DEV account. And we may create a separate user with different name (e.g. ADMIN_JOHN_DOE) and use it for a different purpose. This user has unrestricted access and exists on DEV account only. It is also managed outside of SnowDDL, since it may need to see objects created by other tools, like Terraform, Fivetran or Airbyte.

In our view, it is generally a better way to structure development process without compromising testing quality.

[leopasta-enable]

Thanks for the quick reply @littleK0i.

In our scenario the different users is not ideal because we want to leverage SSO, so he would always connect as [email protected]

Maybe we can leverage "dev" and "uat"roles managed outside of SnowDDL, but my initial gut feel is that the blueprint is a more elegant solution, we could have different "-business_role.yaml" files, loading the environment-specific one in the load_blueprints or replacing it in a bash script prior to call snowddl

[leopasta-enable]

What do you think @josephniblo ?