ð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,DATABASE_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_ROLEDATABASE_ROLE=>__D_ROLESCHEMA_ROLE=>__S_ROLETECH_ROLE=>__T_ROLEUSER_ROLE=>__U_ROLEWAREHOUSE_ROLE=>__W_ROLE
Ultimately, this naming convention makes it easier to select specific subsets of roles using command:
Tier 1 roles
Schema roles
Schema roles are created automatically for each schema which is present in config. Grants and future grants to schema roles are applied automatically based on permission model.
SnowDDL creates 3 types of schema 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;
Database roles
Database 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 roles, SnowDDL creates 3 types of database 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;
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.
Model is easy to understand and explain for humans. Not only for database administrators and software engineers, but also for business users and auditors.
Typical changes produce a very small SQL footprint, which is easier to review. For example, when you want to grant access for a new schema to 50 analysts, SnowDDL produces 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, users are dropped automatically. No more "orphan" roles, no more clutter.
Last updated
