Role Structures & Hierarchies in Snowflake | Access Controls Explained

Why do access controls matter?

Data is essential for modern businesses wanting to gain a competitive edge and, in this digital area, its security is paramount. With the increasing reliance on digital platforms and cloud-based solutions, protecting data from unauthorised access is critical. Access controls are a vital tool for ensuring that sensitive data is only available to authorised users.

One cloud-based data platform that takes access controls seriously and has created a seamless hierarchy for access is Snowflake. Snowflake’s approach to access controls is unique, as it combines both Role Based Access Control (RBAC) and Discretionary Access Control (DAC) to provide a comprehensive security model. Each securable object in Snowflake has an Owner who has all the privileges and with this comprehensive approach, businesses can rest assured that their data is in safe hands.

This blog will explore the benefits of applying access controls to data and how Snowflake’s RBACs and DACs function. It will also delve into the concept of an ’owner’ in Snowflake and why it matters for maintaining data integrity.

Snowflake Definitions 

Securable Objects

In Snowflake, a ‘Securable Object’ is an entity to which access can be granted, unless allowed by a grant, access is denied. Put simply, it refers to something that you need permission to access; without an explicit grant, you can’t use it. These objects are then organised in a hierarchy of containers with the customer’s organisation as the highest level of container, within which ‘Accounts’ exist. An Account is like a container that holds things like warehouses, databases, roles, users and other account objects.

Databases are containers within Accounts and contain things like Database Roles and Schemas. A ‘Schema’ is a container within a Database that holds objects such as tables, views, stored procedures and User-Defined Functions (UDFs).

Each securable object is owned by a single ‘Role’, which is usually the role that was used to create the object. This role has all the permissions by default, but it can delegate permissions to other roles as needed.

Roles

‘Roles’ are a way for administrators to authorise and restrict access to objects in an account. A Role is an entity that you can give or take away securable object privileges. You can assign Roles to Users and other Roles to create a ‘Role Hierarchy’.

A User can have multiple Roles assigned to them, but they can only choose one Role to be active at a time. This allows them to perform different actions that require different levels of access privileges. For example, a User might have an ‘Admin’ Role for managing the account and a ‘Developer’ Role for creating and editing database objects.

Privileges

Privilege is a defined level of access to an object. The granularity of access granted is controlled by a set of privileges granted to the object.

Users

User is associated with a person or program, it’s the identity recognised by Snowflake.

Why Roles & Privileges Are Important for Access Control

Roles and privileges enhance security against external and internal access to classified or sensitive data. Privileges allow granular access control over securable objects and roles make it easy to manage access to the data stored in Snowflake.

Snowflake provides granular control over access to objects:

  • Who can access what objects?
  • What operations can be performed on those objects?
  • Who can create or alter Access Control Policies?

System Defined Roles

Snowflake has pre-defined system roles that cannot be dropped and none of their privileges can be revoked, these include:

ACCOUNTADMIN (Account Administrator)

This is one of the highest access levels and one of the most powerful roles available; the Account Administrator manages the Snowflake account and encapsulates the SYSADMIN and SECURITYADMIN roles as well. It has privileges to view all credit and billing information in the account, so it is strongly recommended that Multi-Factor Authentication (MFA) is enabled for these users. Additionally, it’s recommended that businesses limit the assignment of this Role to only a minimum set of trusted users in the account.

SYSADMIN (System Administrator)

This role can create warehouses, databases and all objects within a database (schemas, tables, views etc). It has the ability to grant privileges on warehouses, databases and other database objects to other roles. While creating a role hierarchy, best practice is to assign all custom roles in the hierarchy to SYSADMIN.

SECURITYADMIN (Security Administrator)

This is a specially catered role to administer security and inherits the privileges of USERADMIN role. Manages granting and revoking privileges to any object globally and can create, monitor and manage users and roles.

Security privileges:

  • MANAGE GRANT privilege.

USERADMIN (User and Role Administrator)

This role has privileges to create and manage users and roles in the account. It can manage users and roles owned by the role itself using the OWNERSHIP privilege.

Security privileges:

  • CREATE USER
  • CREATE ROLE

PUBLIC

This is the default role for all users, access rights assigned to this role are generally accessible by everyone. It provides basic privileges to log in to Snowflake and access basic objects. Typical usage of this role is for cases where explicit access is not needed. Although this role can have its own securable objects, all such objects owned by the PUBLIC role are available to all users and all roles in the account, meaning it’s important to use caution when assigning ownership of objects to the PUBLIC Role.

ORGADMIN

This system role manages operations at an organisation level. Due to its unique usage, it’s not included in the hierarchy of system roles. It has privileges to create accounts in the organisation, can view usage information and can view all accounts and regions enabled.

Custom Roles

Snowflake allows for the creation of custom roles and the creation of a hierarchy of these bespoke roles. It’s recommended that the highest role in the hierarchy should be assigned to SYSADMIN. This ensures system administrator can manage all virtual warehouses, databases and objects. ACCOUNTADMIN and SECURITYADMIN roles can maintain users and roles granted.

Custom roles can be created by

  • USERADMIN or a higher role.
  • Any role that has CREATE ROLE privilege.
  • Database roles can be created by the database owner or the role that has OWNERSHIP privilege.

Role Hierarchy

Role hierarchies are a powerful tool that allows for the reuse of roles and grants privileges to multiple users within an organisation. This feature enables the granting of roles to other roles, creating a system in which parent roles inherit the privileges of their child roles automatically. This inheritance system reduces the need for excessive grants, ultimately minimising the potential for errors in the system.

Additionally, role hierarchy allows for the assignment of the least powerful role when needed, providing a safeguard against accidental data access, updates and data corruption. By limiting access to the appropriate level, the role hierarchy ensures that only authorised personnel can make changes and modifications to data, maintaining the integrity and security of the system.

Shown below is recommended structure for additional user-defined custom roles. The highest database role is inherited from the account role and it allows SYSADMIN to inherit the privileges of the custom account role as well as the database role.

Visualisation of Snowflake Roles Hierarchy

Below example shows role hierarchy and privilege inheritance:

  • Role 2 inherits Privilege C
  • Role 1 inherits Privileges B and C
  • User 1 has all three privileges
Visualisation of Snowflake Role Privileges

Creating a User

Creating a user in Snowflake can be done through the web interface or through APIs. However, if your Snowflake account is synced with your active directory (AD), creating a user manually may not be necessary. Snowflake supports System for Cross-domain Identity Management (SCIM 2.0) which works with the AD so users can be automatically provisioned and de-provisioned in Snowflake based on their AD groups.

This integration streamlines the user management process and ensures that user access to Snowflake resources is up-to-date and consistent with their roles and permissions in the AD. Nonetheless, if AD sync is not implemented, creating a user in Snowflake is a simple process that involves providing basic information such as the user’s email address and a password and assigning them the appropriate roles and privileges to access the Snowflake resources they require.

Code to create a user

Creating a Custom Role

Code to create a custom role in Snowflake

Granting or Revoking a Role

Access Control Privileges

Privileges are granted to roles and roles are granted to users. Privileges specifies the operations that users can perform on objects. Privileges must be granted on individual objects (SELECT privilege on table). A role with MANAGE GRANT privilege allows to grant additional privileges to other roles. FUTURE GRANTS allow defining an initial set of privileges on objects created in schema.

Privileges are managed using below commands:

  • GRANT <privileges>
  • REVOKE <privileges>

Grant privileges to Role:

Show privileges granted to a Role:

Code to Show Grants of Role in Snowflake

Enforcement Model: Primary and Secondary Role

Primary Role

Every active user has current role for the session, that’s referred as ‘Primary Role’, users can have only one active Primary Role.

The below command is used to set a Primary Role in user’s session:

The criteria mentioned below is used to determine user’s current role:

  • Role is specified as a part of connection and that role has been already granted to User. This role becomes user’s current role.
  • If no role specified in connection and default role is set for user, that role becomes current role for user.
  • If no role specified in connection and no default role is set for user, system role PUBLIC is used as user’s current role.

Secondary Role

A set of secondary roles can be activated in a user’s session. SQL actions on objects can be performed as aggregate privileges granted to Primary and Secondary roles. Authority to execute CREATE <OBJECT> statement comes from primary role only, and OWNERSHIP is set to currently active primary role. Secondary role authorises execution of any DDL actions on the object. Both the Primary and Secondary roles inherit all privileges from any roles lower in their role hierarchy.

Below command shows usage of secondary role:

Code to use Secondary Roles in Snowflake

To see active Secondary Roles in user’s session:

Code to see active Secondary Roles in user's session in Snowflake

Summary 

Data is crucial for businesses in the digital age, and security is paramount in protecting sensitive information from unauthorised access. Access controls play a critical role in ensuring that only authorised users can access data. Snowflake’s access controls are unique, using a combination of Role-Based Access Control (RBAC) and Discretionary Access Control (DAC) to provide a comprehensive security model. Snowflake’s hierarchy of Securable Objects, Roles and Privileges enables granular access control, making it easy to manage access to data.

With Snowflake, businesses can rest assured that their sensitive data is in safe hands. With the help of Billigence, businesses can have the peace of mind that Snowflake is integrated correctly and their roles and access controls are clearly defined for continued success.

Ready to move or improve you cloud data? Get in contact using the form below to get started.