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_ROLESCHEMA_ROLE=>__S_ROLETECH_ROLE=>__T_ROLEUSER_ROLE=>__U_ROLEWAREHOUSE_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