SnowDDL
  • 👋Introduction
  • ðŸšĐGetting started
  • 📋Main features
  • ðŸŠĪSnowDDL vs. Declarative DCM
  • In-depth guides
    • 👓Object identifiers
    • 📐Data types
    • ðŸ“ĶObject types
    • 🎭Role hierarchy
    • 🚧Permission model
    • ðŸ”ĶOther guides
      • Administration user
      • Integrations
      • Inbound shares
      • Object OWNERSHIP
      • Safe & unsafe DDL
      • Dependency management
      • Short hash explained
      • Env Prefix explained
      • Team workflow
      • Limitations & workarounds
      • Fivetran
      • Airbyte
      • Encrypt user passwords
      • Iceberg Tables
  • Basic usage (CLI + YAML)
    • ðŸ’ŧCLI interface
    • ðŸ“ĶYAML configs
      • ACCOUNT PARAMETER
      • ACCOUNT POLICY
      • AGGREGATION POLICY
      • ALERT
      • AUTHENTICATION POLICY
      • BUSINESS ROLE
      • DATABASE
      • DYNAMIC TABLE
      • EVENT TABLE
      • EXTERNAL ACCESS INTEGRATION
      • EXTERNAL FUNCTION
      • EXTERNAL TABLE
      • FILE FORMAT
      • FUNCTION
      • HYBRID TABLE
      • ICEBERG TABLE
      • MASKING POLICY
      • MATERIALIZED VIEW
      • NETWORK POLICY
      • NETWORK RULE
      • PERMISSION MODEL
      • PIPE
      • PLACEHOLDER
      • PROCEDURE
      • PROJECTION POLICY
      • RESOURCE MONITOR
      • ROW ACCESS POLICY
      • SCHEMA
      • SECRET
      • SEQUENCE
      • SHARE (outbound)
      • STAGE
      • STAGE FILE
      • STREAM
      • TABLE
      • TASK
      • TECHNICAL ROLE
      • USER
      • VIEW
      • WAREHOUSE
    • 🏷ïļYAML placeholders
    • 📎YAML tag !include
    • 🔐YAML tag !decrypt
  • Single DB
    • ðŸĶ€Overview
  • Advanced usage (Python)
    • ⚙ïļProgrammatic config
    • 🐍Architecture overview
      • ðŸ”ĩBlueprints
      • ðŸŸĢConfig
      • 🟠Parsers
      • ðŸŸĒResolvers
      • ðŸ”īEngine
    • 🏗ïļQuery builder & formatter
  • Breaking changes log
    • 0.45.0 - March 2025
    • 0.41.0 - January 2025
    • 0.37.0 - December 2024
    • 0.36.0 - November 2024
    • 0.33.0 - October 2024
    • 0.27.0 - May 2024
  • Links
    • GitHub repository
    • PyPI package
    • YouTube tutorials
    • Changelog
    • LinkedIn profile
Powered by GitBook
On this page
  • General overview
  • Naming convention
  • Tier 1 roles
  • Database access roles
  • Schema access roles
  • Share access roles
  • Warehouse access roles
  • Technical roles
  • Tier 2 roles
  • Business roles
  • Tier 3 roles
  • User roles
  • Rationale
  1. In-depth guides

Role hierarchy

PreviousObject typesNextPermission model

Last updated 3 months ago

Snowflake documentation mentions benefits of , 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).

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

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

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

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

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

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.

  • 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.

Each role name starts with optional .

Database access roles are created automatically when 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.

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 .

Share access roles are created automatically for each share mentioned in or .

Technical roles are .

Business roles are .

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

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

🎭
env prefix
permission model
permission model
business role config
schema config
configured manually
configured manually
USER
secondary roles
role hierarchy