Comment on page
🎭
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.
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.
Role names are complex identifiers with parts separated by
__
(double underscore).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>';

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 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;

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.

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.
User roles are created automatically for each user. User roles provide access to one or more business roles.
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;
Last modified 1yr ago