🎭Role hierarchy

Snowflake documentation mentions benefits of role hierarchy, but it does not provide any real world examples.

SnowDDL offers a well thought 3-tier role hierarchy model. It is easy yo understand, largely automated and requires minimal configuration.

General overview

All roles are separated into 3 major tiers.

• Tier 1: roles granting access privileges for specific objects in Snowflake account. It includes: DATABASE_ACCESS_ROLE, SCHEMA_ACCESS_ROLE, SHARE_ACCESS_ROLE, WAREHOUSE_ACCESS_ROLE, TECHNICAL_ROLE.

• Tier 2: roles granting access for Tier 1 roles according to specific business functions. It includes: BUSINESS_ROLE.

• Tier 3: roles granting access for Tier2 business roles to specific users. It includes: USER_ROLE.

BUSINESS_ROLE and TECHNICAL_ROLE are configured manually.

Other role types are created automatically based on configuration of corresponding objects.

Naming convention

Role names are complex identifiers with parts separated by __ (double underscore).

Each role name starts with optional env prefix.

The next part is a role name (for manually configured roles) or name of entity (for automatically generated roles).

The next optional part is a sub-type of role, which may or may not exist, depending on role type.

And the final part is a role type suffix.

• DATABASE_ACCESS_ROLE => __D_ROLE

• SCHEMA_ACCESS_ROLE => __S_ROLE

• SHARE_ACCESS_ROLE ⇒ __SH_ROLE

• WAREHOUSE_ACCESS_ROLE => __W_ROLE

• BUSINESS_ROLE => __B_ROLE

• TECHNICAL_ROLE => __T_ROLE

• USER_ROLE => __U_ROLE

Ultimately, this naming convention makes it easier to select specific subsets of roles using command:

SHOW ROLES LIKE '<pattern>';

Tier 1 roles

Database access roles

Database access roles are created automatically when permission model is configured with ruleset DATABASE_OWNER. This is helpful when you have external software which is hardcoded to create its own schemas, like Fivetran or Airbyte. In this case we do not know names of schemas beforehand, so schema roles cannot be created in advance. But we know database name, and permission can be managed on database level.

Similar to schema access roles, SnowDDL creates 3 types of database access roles:

• __OWNER__D_ROLE - provides OWNERSHIP privileges for objects in database;

• __READ__D_ROLE - provides generic READ and USAGE access for objects in database;

• __WRITE__D_ROLE - provides generic WRITE access for objects in database;

Schema access roles

Schema access roles are created automatically for each schema which is present in config. Grants and future grants to schema access roles are applied based on permission model.

SnowDDL creates 3 types of schema access roles:

• __OWNER__S_ROLE - provides OWNERSHIP privileges for objects in schema;

• __READ__S_ROLE - provides generic READ and USAGE access for objects in schema;

• __WRITE__S_ROLE - provides generic WRITE access for objects in schema;

Share access roles

Share access roles are created automatically for each share mentioned in business role config or schema config.

ShowDDL creates 1 type of share access roles:

• __SH_ROLE - provides IMPORTED PRIVILEGES for inbound share;

Warehouse access roles

Warehouse access roles are created automatically for each warehouse.

SnowDDL creates 2 types of warehouse access roles:

• __USAGE__W_ROLE - USAGE and OPERATE privileges for warehouse;

• __MONITOR__W_ROLE - MONITOR and OPERATE privileges for warehouse;

Technical roles

Technical roles are configured manually.

Each tole role provides specific privileges to specific objects. Only normal grants are supported, not future grants.

Tech roles are useful when you need to provide specific privileges for a few specific objects. Schema roles should be preferred to tech roles if possible.

Tier 2 roles

Business roles

Business roles are configured manually.

Business roles combines multiple Tier 1 roles into specific business function. For example: ANALYST, DEVELOPER, EXTERNAL_AUDITOR, ETL_SCRIPT, DBT, FIVETRAN, etc.

Normally each business role should have access to at least one schema and be able to use at least one warehouse.

Also, it is possible to assign a "global role" to business role, which is created outside of SnowDDL. If you have a very specific use-case which is not covered by SnowDDL, you may always implement it manually by creating a custom ROLE using ACCOUNTADMIN and assigning this role to business role via global_roles config option.

Tier 3 roles

User roles

User roles are created automatically for each user. User roles provide access to one or more business roles.

Specific business roles provided to user are configured in USER object type.

Rationale

This 3-tier role model provides a tremendous advantage when it comes to real world maintenance of permissions in Snowflake.

• Model is easy to understand and explain to non-technical users. It helps to pass security audits.

• Typical changes produce a very small SQL footprint. For example, when you want to grant access for a schema to 50 analysts, SnowDDL may produce only 1 line of SQL.

• Native Snowflake secondary roles are not needed thanks to each user having a dedicated user role by design.

• You may easily "impersonate" specific user and test their permissions by running the following command: USE ROLE <USER_NAME>__U_ROLE;

• Unused roles for non-existent schemas, warehouses, shares, users are dropped automatically. It helps to reduce amount of "orphan" roles in account.