SnowDDL
Search…
🎭
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 privileges for specific Snowflake objects. It includes SCHEMA_ROLE, WAREHOUSE_ROLE, TECH_ROLE.
  • Tier 2: roles granting access to Tier 1 roles according to specific business functions. It includes BUSINESS_ROLE.
  • Tier 3: roles granting access to one or more business roles. It includes USER_ROLE.
BUSINESS_ROLE and TECH_ROLE are configured manually.
SCHEMA_ROLE, USER_ROLE, WAREHOUSE_ROLE are created automatically.

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.
  • BUSINESS_ROLE => __B_ROLE
  • SCHEMA_ROLE => __S_ROLE
  • TECH_ROLE => __T_ROLE
  • USER_ROLE => __U_ROLE
  • WAREHOUSE_ROLE => __W_ROLE
Ultimately, this naming convention makes it easier to select specific sub-sets of roles using command:
SHOW ROLES LIKE '<pattern>';

Tier 1 roles

Schema roles

Schema roles are created automatically for each schema. All grants to schema roles are applied automatically for all objects in schema via FUTURE GRANTS.
SnowDDL creates 3 types of schema roles:
  • __OWNER__S_ROLE - OWNERSHIP privileges for all objects in schema;
  • __READ__ROLE - general READ and USAGE permissions for objects in schema;
  • __WRITE__ROLE - general WRITE permissions for objects in schema;

Warehouse roles

Warehouse roles are created automatically for each warehouse.
SnowDDL creates 2 types of warehouse roles:
  • __USAGE__W_ROLE - USAGE and OPERATE privileges for warehouse;
  • __MONITOR__W_ROLE - MONITOR and OPERATE privileges for warehouse;

Tech roles

Tech 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.
  • Add new user? => Assign appropriate business role(s) to user. Done!
  • Add new schema? => Assign schemas to appropriate business role(s). Done!
  • User started working in cross-functional team? => Add more business role(s) to user. Done!
  • Access to one specific view is required for external tool? => Add tech role, grant it to business role for external tool. Done!
  • Some schemas, warehouses or users were dropped? => Automatically created roles will be dropped automatically. No more "orphan" roles, no more mess.
On top of that, it will be possible to "impersonate" any business user by using a simple command:
USE ROLE <USER_NAME>__U_ROLE;
​
Copy link
On this page
General overview
Naming convention
Tier 1 roles
Schema roles
Warehouse roles
Tech roles
Tier 2 roles
Business roles
Tier 3 roles
User roles
Rationale