In Snowflake objects are hierarchically ordered. This means that at the top you will find the organization, then the account all the way down until you’re at the level of tables. A graphical representation of this hierarchy is provided at the end of the post.

This hierarchy is important when discussing the security of Snowflake. The security models used in Snowflake are the Role-Based Access Control (RBAC) and the Discretionary Access Control models.

All objects are individually securable. Privileges (a defined level of access to an object e.g. if you can only read, or also edit the object) can be given to a role. Formulated differently: a role is an object to which privileges can be given. Here is an example of how this would look in Snowflake:

GRANT CREATE DATABASE ON ACCOUNT TO ROLE ROLE_1

A role in its turn, can be given to a user, or to another role. When you give a role to another role, you can effectively create a hierarchy of roles. E.g. ROLE_1 can see edit everything in a certain account, ROLE_2 is at a lower level in the hierarchy and can only see and edit certain databases, etc.

Now, why would you care about this? Why is this important?

Because, this gives you the opportunity to have granular control over who sees what! If you want really granular control, you could even create a role that only has access to a certain table, and create a row access policy (similar to Power BI Row-Level Security) that allows the role to only see certain records in that table.

Another key advantage is that because of the DAC model, users are by default in control of the objects they have created, which helps with self service BI.

One last advantage that I’ll mention is that privilege inheritance in Snowflake’s role hierarchy is a powerful feature that simplifies access management and reduces administrative overhead. Put simply, when you create a role at a lower level (ROLE_LOWER) that has access to certain databases and you assign this role to a role higher in the hierarchy (ROLE_HIGHER), the ROLE_HIGHER inherits all privileges from ROLE_LOWER and you do not need to assign all the privileges all over again to ROLE_HIGHER. The privileges will simply be inherited by virtue of assigning ROLE_LOWER to ROLE_HIGHER which saves a lot of work. I will give an example of this in the next post.

Organization

│

Account

│

├── Users

├── Roles

├── Warehouses

├── Resource Monitors

├── Shares

├── Account Parameters

├── Network Policies

├── Account Usage

├── Storage Integrations

├── Notification Integrations

├── Security Integrations

│

└── Databases

    │

    └── Schemas

        │

        ├── Tables

        ├── Views

        ├── Materialized Views

        ├── External Tables

        ├── Sequences

        ├── File Formats

        ├── Stages

        │   ├── Internal Stages

        │   └── External Stages

        ├── Pipes

        ├── Streams

        ├── Tasks

        ├── Procedures

        ├── Functions

        │   ├── User-Defined Functions (UDFs)

        │   └── External Functions

        └── Masking Policies