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.