Skip to main content

How to Mix and Match Database Roles with Account Roles

Snowflake provides robust access control mechanisms through its role-based access control model.

This article explains how to effectively mix and match database roles with account roles to manage access to your Snowflake environment efficiently. By using database roles to complement schema access roles, you can simplify permissions management and enhance security.

Database Roles​

Database roles are designed to manage access within a specific database. These roles can be used to control access to schema-level objects without cluttering the account role space.

What is the difference between an account role and a database role?​

  • Account level role resides outside any database, whereas database role resides within a particular designated database.

  • An account-level role can grant or privilege from any or all objects in an account, whereas a database role can only have access to database-level objects.

  • Account role can be granted to a user, whereas database role can only be granted to the account role, not a user.

  • The account-level role scope is the entire account, whereas the database role scope is limited to the designated database only.

  • An account-level role can be assigned to another account-level role to create a role hierarchy, whereas a database role cannot be granted to another role outside the same one. Database roles can only be granted to an account-level role to create a role hierarchy.

  • An account-level role cannot be granted to a database role, whereas a database role can be granted to an account role.

  • Database role ownership can be only held by an account role, whereas account-level role ownership cannot be held by any database role

Role hierarchy diagram​

role hierarchy !!shadow!!

Naming conventions​

  • Account Roles - follow a structured naming convention, typically including a prefix and/or suffix to indicate their scope and environment.

  • Database Roles - unlike account roles, prefixes and suffixes are not used in database role names. Instead, these conventions are applied to the database names to ensure clarity and avoid conflicts.

Use case: combining database and account roles​

Consider a situation where you have multiple schemas within a database and need to control access to schema-level objects without granting permissions on individual objects. You can manage access more efficiently by creating READ, WRITE, and CREATE database roles for each schema. These database roles are advantageous because they do not clutter the account role space and can be shared and cloned with their database, serving as a powerful built-in access control mechanism.

Step-by-step guide​

Ensure that the databases and schemas are present in the configuration or have already been created before moving forward.

Step 1 - define database roles in SOLE​

  • If the hooks.template.yml file exists:

    Verify the presence of the database_level_hooks object group section in hooks.template.yml. If it does not exist, create this section and include database_level_hooks to add commands.

  • If the hooks.template.yml file does not exist:

    Create a new file named hooks.template.yml within the dataops/snowflake directory.

    Ensure the newly created file includes a database_level_hooks object group section.

    Place the post_hooks under the database_level_hooks section to add commands.

    hooks.template.yml
    database_level_hooks:
      post_hooks:
        - environment: snowflake
          commands:
            - "CREATE DATABASE ROLE IF NOT EXISTS {{ env.DATABASE_NAME_PREFIX }}_<database-name>_{{ env.DATAOPS_ENV_NAME }}.<database_role-name>;"

    Here, {{ env.DATABASE_NAME_PREFIX }} serves as the prefix, and {{ env.DATAOPS_ENV_NAME }} acts as the suffix.

  • Create the necessary database roles for each schema.

    hooks.template.yml
    database_level_hooks:
      post_hooks:
        - environment: snowflake
          commands:
            - "CREATE DATABASE ROLE IF NOT EXISTS {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_READ;"
            - "CREATE DATABASE ROLE IF NOT EXISTS {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_WRITE;"
            - "CREATE DATABASE ROLE IF NOT EXISTS {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_CREATE;"

  • Specify the hooks file path in the dataops_config.yml file in the dataops/snowflake directory

    dataops_config.yml
    config-version: 2
    hook-paths: [hooks.yml]

Step 2 - grant privileges to database roles in SOLE​

  • If the hooks.template.yml file exists:

    Verify the presence of the grants_hooks object group section in hooks.template.yml. If it does not exist, create this section and include post_hooks to add commands.

  • If the hooks.template.yml file does not exist:

    Create a new file named hooks.template.yml within the dataops/snowflake directory.

    Ensure the newly created file includes a grants_hooks object group section.

    Place the post_hooks under the grants_hooks section to add commands.

    hooks.template.yml
    grants_hooks:
      post_hooks:
        - environment: snowflake
          commands:
            - "GRANT <privilege> ON ALL TABLES IN SCHEMA {{ env.DATABASE_NAME_PREFIX }}_<database-name>_{{ env.DATAOPS_ENV_NAME }}.<schema-name> TO DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_<database-name>_{{ env.DATAOPS_ENV_NAME }}.<database_role-name>;"

    Here, {{ env.DATABASE_NAME_PREFIX }} serves as the prefix, and {{ env.DATAOPS_ENV_NAME }} acts as the suffix.

  • Grant the necessary privileges to these database roles for managing access at the schema level rather than on individual objects.

    hooks.template.yml
    grants_hooks:
      post_hooks:
        - environment: snowflake
          commands:
            - "GRANT SELECT ON ALL TABLES IN SCHEMA {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1 TO DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_READ;"
            - "GRANT INSERT ON ALL TABLES IN SCHEMA {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1 TO DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_WRITE;"
            - "GRANT CREATE TABLE ON SCHEMA {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1 TO DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_CREATE;"

  • Ensure the hooks file path is specified in dataops_config.yml file in the dataops/snowflake directory

    dataops_config.yml
    config-version: 2
    hook-paths: [hooks.yml]

Step 3 - define account-level roles in SOLE​

  • If using pre-existing Roles:

    If a roles.template.yml file is already present in the dataops/snowflake directory, or if account-level roles are already defined in the production environment, you may use those existing roles in the next section to group the database roles.

  • If creating new account-level roles:

    Add roles to the existing roles.template.yml file. If it does not exist, create a new file named roles.template.yml in the dataops/snowflake directory. Create Account-level roles that will be used to group the database roles.

    This file should include the following content:

    roles.template.yml
    - role:
        name: READER
        environment: PROD
        namespacing: prefix
        roles:
          - SYSADMIN

    - role:
        name: WRITER
        environment: PROD
        namespacing: prefix
        roles:
          - SYSADMIN
    - role:
        name: ADMIN
        environment: PROD
        namespacing: prefix
        roles:
          - SYSADMIN

Step 4 - grant database roles to account-level roles​

  • If the hooks.template.yml file exists:

    Verify the presence of the grants_hooks object group section in hooks.template.yml. If it does not exist, create this section and include post_hooks to add commands.

  • If the hooks.template.yml file does not exist:

    Create a new file named hooks.template.yml within the dataops/snowflake directory.

    Ensure the newly created file includes a grants_hooks object group section.

    Place the post_hooks under the grants_hooks section to add commands.

    hooks.template.yml
    grants_hooks:
      post_hooks:
        - environment: snowflake
          commands:
            - "GRANT DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_<database-name>_{{ env.DATAOPS_ENV_NAME }}.<database_role-name> TO ROLE {{ env.DATABASE_NAME_PREFIX }}_<account_role-name>;"

    Here, {{ env.DATABASE_NAME_PREFIX }} serves as the prefix, and {{ env.DATAOPS_ENV_NAME }} acts as the suffix.

  • Grant the database roles to the appropriate account roles.

    hooks.template.yml
    grants_hooks:
      post_hooks:
        - environment: snowflake
          commands:
            - "GRANT DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_READ TO ROLE {{ env.DATABASE_NAME_PREFIX }}_READER;"
            - "GRANT DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_WRITE TO ROLE {{ env.DATABASE_NAME_PREFIX }}_WRITER;"
            - "GRANT DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_CREATE TO ROLE {{ env.DATABASE_NAME_PREFIX }}_ADMIN;"
            - "GRANT DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_WRITE TO ROLE {{ env.DATABASE_NAME_PREFIX }}_ADMIN;"
            - "GRANT DATABASE ROLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA1_READ TO ROLE {{ env.DATABASE_NAME_PREFIX }}_ADMIN;"

  • Ensure the hooks file path is specified in the dataops_config.yml file in the dataops/snowflake directory

    dataops_config.yml
    config-version: 2
    hook-paths: [hooks.yml]
info

Please note that roles are not automatically revoked if the hook is removed. The grant will still exist.

Benefits​

Easy management of database level access: database owners can easily manage the database level access within the respective database. All securable objects within that database can be granted to database roles, which then can be rolled up to an account role.

Strengthen security: with database roles, the security perimeter can be more secure, as database roles are not accessible outside the database unless explicitly granted to an account role. Also, it cannot be granted to another database role outside the current database, which reduces the role explosion created due to the need for fined-grained privileges in any Snowflake account.

Conclusion​

Mixing and matching database roles with account roles in Snowflake provides a powerful and flexible way to manage access to your Snowflake environment. By following the steps outlined in this article, you can ensure that your access control mechanisms are both efficient and secure.

Last updated on