Snowflake Roles & Access: A Comprehensive Guide 101

Snowflake comes with a comprehensive collection of SQL commands for user and security management. These commands can only be run by users who have been given the OWNERSHIP privilege on the managed object as part of their role. The ACCOUNTADMIN and SECURITYADMIN roles are usually the only ones who can do this.

Nonetheless, it’s also possible for the users to perform the following tasks themselves:

• Changing their password (only through the web interface).
• Checking out their user profile (via DESCRIBE USER).
• Default roles, virtual warehouses, and namespaces can all be changed (via ALTER USER).
• Changing the parameters of their session (via ALTER SESSION).

In this blog post, we will discuss, in detail, Snowflake Roles and an overview of Access Control. Later we will discuss Secure Objects, types of Roles, types of Privileges, Role Hierarchy & Privilege Inheritance, and the Enforcement Model.
Let’s begin.

What is Snowflake?

Snowflake is a cloud-based Data Warehouse solution that supports ANSI SQL and is available as a SaaS (Software-as-a-Service). It also offers a unique architecture that allows users to quickly build tables and begin querying data with no administrative or DBA involvement. Here’s where you can learn about Snowflake pricing.

Key Features

Let’s take a look at some of the key features of the Snowflake data warehouse:

• The snowflake data warehouse provides Multi-Factor Authentication (MFA), federal authentication, Single Sign-on (SSO), and OAuth for increased security and data protection. TLS encrypts all communication between the client and the server.
• SQL Support (Standard and Extended): The Snowflake data warehouse supports the majority of SQL DDL and DML operations. Advanced DML, transactions, lateral views, stored procedures, and other features are also supported.
• Snowflake data warehouse offers a wide range of client connections and drivers, including Python connectors, Spark connectors, Node.js drivers, and.NET drivers, among others.
• You can share your data with other Snowflake accounts in a secure manner.

Framework to Access Control in Snowflake Roles

In Snowflake, there are two approaches/methods to establish access control:

1. Discretionary Access Control (DAC): With each object, an Owner exists, responsible for granting access to the object.
2. Role-based Access Control (RBAC): The privileges to access the object is defined based on the Snowflake roles, assigned to the user by the company.

Some key elements to Access Control in Snowflake Roles are as follows:

• Securable object: A secure object is one to which permission can be granted. Access will be refused unless a grant by the admin allows it.

• Role: A role is a type of entity to which privileges can be assigned. It’s worth noting that roles can be given to other objects, forming a hierarchy.

• Privilege: Privilege is a level of access to an object that is defined. To manage the granularity of access allowed, multiple separate privileges might be employed.

• User: Snowflake recognizes a user’s identity, whether it’s affiliated with a person or a program.

Access to secure objects is granted in the Snowflake model via privileges allocated to Snowflake roles, which are then assigned to other roles or users. Furthermore, each securable item has an owner who has the authority to grant access to other Snowflake roles. This concept differs from a user-based access control model, in which each user or group of users is allocated rights and privileges. The Snowflake approach is intended to provide you with a lot of control as well as a lot of flexibility.

You can explore more about : Methods to load data into Snowflake

What are Securable Objects?

In a hierarchy of containers, every securable item is contained within a logical container. The customer organization is in the top-most container. TABLE, FUNCTION, FILE FORMAT, STAGE, SEQUENCE, and other securable objects are housed within a SCHEMA object within a DATABASE. The following diagram depicts the hierarchy of items and containers:

Every securable object is held by a single role, which is usually the role that created the object in the first place. When users are given this position, they effectively share control over the secure object. By default, the owner role has full access to the object, including the ability to grant and cancel access to other Snowflake roles. Furthermore, ownership can be passed along from one job to the next.

Snowflake Roles privilege determines which objects are accessible.
The following are some examples of privileges on various Snowflake objects:

• The ability to build a warehouse.
• The ability to list the tables in a schema.
• Data can be added to a table.

What are Snowflake Roles?

• Snowflake Roles: System-defined Roles
• Snowflake Roles: Custom Roles

Snowflake Roles are the entities that can grant and revoke privileges on securable objects. Users are assigned roles to allow them to undertake actions required for their organization’s business functions. Users can have several roles assigned to them. Users can switch roles (select which role is active in the current Snowflake session) to conduct different actions with different sets of privileges.

Snowflake Roles can be assigned to other roles, resulting in a role hierarchy. Custom roles can be created by users with suitable access. Any roles above that role in the hierarchy inherit the privileges associated with that role.

In a Snowflake account, there are just a few system-defined roles. Snowflake Roles that are defined by the system cannot be removed. Furthermore, Snowflake’s rights granted to these positions cannot be removed.

Additional privileges for system-defined roles can be granted, although this is not encouraged. System-defined roles are formed with account-management privileges. Mixing account-management privileges and entity-specific privileges in the same job is not suggested as a best practice. Snowflake suggests granting more privileges to a custom role and assigning the custom role to the system-defined role if additional privileges are required.

System-defined Roles

ORGADMIN: This role has operations authorization at a much higher level, and includes:

• Creation of new accounts in the organization
• Reviewing accounts and managing them based on and outside of the organization.
• Viewing the usage information on multiple accounts in the organization.

ACCOUNTADMIN: Role that encapsulates the system-defined roles SYSADMIN and SECURITYADMIN.

• It is the highest-level role in the Snowflake system.
• Should be granted only to a limited number of trusted users in the account due to its extensive privileges.

SECURITYADMIN: Create, monitor, and manage users and roles, as well as manage any object grant globally. In more detail:

• The MANAGE GRANTS security privilege given to this role allows to change or revoke any grant.

• Through the system role hierarchy, the role inherits the rights of the USERADMIN role (e.g. USERADMIN role is granted to SECURITYADMIN).

USERADMIN: This role is limited to only user and role management. In more detail:

• The role grants the user give the CREATE USER and CREATE ROLE security access.
• The role can create new users and roles

SYSADMIN: In an account, this role has the ability to construct warehouses, databases, and other objects.

• Recommended to be at the top of the role hierarchy, with all custom roles assigned under it.
• Can grant privileges to other roles on objects like warehouses and databases.

Public: Every user and role in your account is immediately assigned a pseudo-role.

• It can own secure objects, but these objects are accessible to all users and roles within the account.
• Often used when explicit access control isn’t needed and equal access is granted to all users.

Custom Roles

• SECURITYADMIN and roles with the Establish ROLE privilege can create custom roles (non-system-defined roles).
• Newly created roles are not assigned to any users or roles by default and do not inherit privileges from other roles.
• Snowflake recommends building a hierarchy of custom roles, with the top custom role assigned to the SYSADMIN role for effective management.
• This hierarchy allows system administrators to manage account objects, including warehouses and databases.
• Without assignment to SYSADMIN, custom roles’ objects cannot be controlled by system administrators.
• Only roles with the MANAGE Grants privilege (typically SECURITYADMIN) can see and modify access for objects owned by custom roles.

What are Snowflake Roles Privilege?

There is a set of rights that can be assigned to each securable object. Privileges must be issued on an individual basis for existing objects (e.g. the SELECT privilege on the mytable table). Future grants allow you to define an initial set of privileges on objects generated in a schema to make grant management easier (i.e. the SELECT privilege on all new tables created in the myschema schema).

The following privileges are used with the GRANT and REVOKE commands:

• The commands are available to Snowflake roles that own an object (i.e. having the OWNERSHIP privilege on the object) or roles that have the MANAGE GRANTS global privilege for the object in conventional (i.e. non-managed) schemas (typically the SECURITYADMIN role).

• Object owners lose the power to make grant decisions under a managed access schema. Only the schema owner or a role with the MANAGE GRANTS privilege can grant privileges on schema objects, including future grants, allowing privilege administration to be centralized.

Role Hierarchy and Privilege Inheritance

The hierarchy for system-defined Snowflake roles, as well as the recommended structure for extra, user-defined custom roles, is depicted in the diagram below:

Consider the following scenario for a more detailed example of role hierarchy and privilege inheritance:

• Role 2 has been assigned Role 3.

• Role 1 has been given the second role.

• User 1 has been assigned Role 1.

In the following scenario:

• Role 2 incorporates the privileges for C.
• Role 1 incorporates the privileges of B and C.
• User 1 will get all three privileges.

Enforcement Model in Snowflake Roles: The Primary Role & Secondary Roles

A “current role,” sometimes known as a principal role, is assigned to each active user session. The current role is decided when a session is started (for example, when a user connects through JDBC/ODBC or logs in to the Snowflake web interface).

• The stated role becomes the current role if a role was supplied as part of the connection and that role has already been granted to the connecting user.

• If no role was supplied and the connecting user’s default role was defined, the default role becomes the current role.

• If no role was supplied and the connecting user’s default role was not configured, the system role PUBLIC is used.

In addition, in a user session, a set of supplementary roles can be engaged. The aggregate rights granted to the primary and secondary roles allow a user to perform SQL actions on objects in a session. Before the Snowflake roles may be active in a session, they must first be provided to the user. While only one principal role can be engaged at a time in a session, any number of auxiliary Snowflake roles can be active at the same time.

The principal role is the only one who can execute CREATE object> commands. When an object is created, its ownership is set to the primary role that is now active. Any permission granted to any active primary or secondary role can, however, be used to allow any other SQL activity. If any role in a secondary role hierarchy owns an object (i.e. has the OWNERSHIP privilege on the object), the secondary roles can perform any DDL actions on it. All secondary roles, as well as the principal role, gain advantages from positions lower in the hierarchy.

Conclusion

In this article, we parse our way through Snowflake Roles and gave an overview of Access Control which proves vital when defining roles and giving access to the users within an organization. The process is important for the organization’s own benefit, as it establishes clear boundaries in terms of what information is accessible and what’s not. And, if you want to learn more about Snowflake Roles and Access Control, either of these two articles can help.

1. Access Control in Snowflake
2. Overview of Access Control

Hevo Data is a No-code Data Pipeline and has awesome 150+ pre-built integrations that you can choose from. Hevo can help you integrate data from numerous free data sources and load it into a destination to analyze real-time data with a BI tool and create your Dashboards. It will make your life easier and make data migration hassle-free. It is user-friendly, reliable, and secure.

FAQ