Snowflake Roles and Access Control: What You Need to Know 101

In this article, we'll cover everything you need to know about Snowflake roles and access control, what default roles exist in Snowflake when an instance is created, what the role hierarchy is, explain how they work, and provide examples to help you better understand their capabilities and usefulness.

Overview of Snowflake Roles & Access Control

Snowflake access control system is meant to make sure that only authorized users and applications can access data and perform actions in the Snowflake environment.

Access Control Framework in Snowflake

Snowflake uses a combination of Role-Based Access Control (RBAC) and Discretionary Access Control (DAC) to provide a flexible and granular access control. We cover these concepts in detail later in the article.

Key elements of Snowflake access control framework

Securable object:

• It is an entity that can be secured and to which access can be granted.
• Access to a securable object is, by default, denied unless allowed by a grant.
• Examples of securable objects are databases, schemas, tables, views, and functions in Snowflake.

Role:

• It is an entity to which privileges can be granted.
• Roles are used to manage and control access to securable objects in Snowflake.
• Roles are assigned to users, and a user can have multiple roles.
• Roles can also be assigned to other roles, creating a role hierarchy that enables more granular level control.

Privilege:

• It is a defined level of access to a securable object.
• Privileges are used to control the granularity of access granted.
• Multiple distinct privileges can be used to control access to a securable object, such as the privileges of selecting, updating or deleting from a table.

User:

• It is an entity to which you can define privileges.
• Users are granted privileges through roles assigned to them.
• Users can be assigned to one or more roles, granting them access to securable objects in Snowflake.

Understanding Access Control and its Relationships in Snowflake

Key points to understand the Access control relationships in Snowflake:

• Access to securable objects is allowed via privileges assigned to roles
• Roles can be assigned to other roles or individual users
• Each securable object in Snowflake has an owner who can grant access to other roles.
• Snowflake model differs from a user-based access control model, where rights and privileges are assigned to each user or group of users.

To explain it at a very high-level term, in Snowflake, there are things called "securable objects" that you can easily access it (as we have discussed briefly before). These objects can be things like databases, tables, schemas, tables, or views. But remember that you can't just access these objects without permission! You have to be given special rights, called "privileges", in order to access them.

Now, instead of giving each user their own privileges, Snowflake gives privileges to groups called "roles". So, for example, a role could be anything like "Data Scientist", "Data Analysts"..so on.. and that role would have certain privileges to access certain securable objects.

But it doesn't just stop there! Roles can also be assigned to other roles or even individual users. So, if a user is assigned to a role that has the right privileges to access a securable object, then that user can access that object too.

And lastly, also note that each securable object has an owner, and that owner can choose to grant access to other roles or individual users.

What are Securable Objects in Snowflake?

Every securable object is nested within a logical container in a hierarchy of containers. The ORGANIZATION is at the topmost container, while individual secure objects such as TABLE, VIEW, STAGE, UDF, FUNCTIONS, and other objects are stored within a SCHEMA object, which is contained in a DATABASE, and all of the DATABASE are contained within the ACCOUNT object.

Each securable object is associated with a single role, usually the role that created it. Users who are in control of this particular role can control over the securable object. The owner role has all privileges on the object by default, including granting or revoking privileges on the object to other roles. Also, note that ownership can be transferred from one role to another.

Source: Snowflake documentation

What are Snowflake Roles?

Roles are the entities to which privileges on securable objects can be granted and revoked. Their main purpose is to authorize users to carry out necessary actions within the organization. A user can be assigned multiple roles, which permits them to switch between roles and execute multiple actions using distinct sets of privileges. Each role is assigned a set of privileges, allowing users assigned to the role to access the resources they need. Roles can also be nested, allowing for more granular control over access to securable objects.

What types of Roles are available in Snowflake?

1) System-defined roles

System-defined roles in Snowflake are predefined roles that are automatically created when a Snowflake account is provisioned. These kinds of roles are designed to provide built-in access controls and permissions for Snowflake objects and resources.

ORGADMIN (Organization Administrator):

• This role manages the operations at the organization level.
• It has the ability to create accounts at the organization level.
• It can view all accounts in the organization as well as all regions enabled for the organization.
• It can also view usage information across the organization.

ACCOUNTADMIN (Account Administrator):

• This role combines the power of SYSADMIN and SECURITYADMIN roles.
• It Is considered as the top-level role in the Snowflake.
• It should only be granted to a limited/controlled number of users in the account.

SECURITYADMIN (Security Administrator):

• This role can manage any object grant globally.
• It has the ability to create, monitor, and manage users and roles.
• It is granted the MANAGE GRANTS security privilege to be able to modify any grant, including revoking it.
• It inherits the privileges of the USERADMIN role via the system role hierarchy.

USERADMIN (User and Role Administrator):

• This particular role is dedicated to user and role management only.
• It is granted the CREATE USER and CREATE ROLE security privileges.
• It can create users and roles in the account.
• It can manage users and roles that it owns.

SYSADMIN (System Administrator):

• This role has privileges to create warehouses, databases, and various other objects in the account.
• It can grant privileges on warehouses, databases, and other objects to other roles if all custom roles are ultimately assigned to the SYSADMIN role.

PUBLIC:

• This role is automatically granted to every user and every role in the account.
• It can own securable objects, but the objects are available to every other user and role in the account.
• It is typically used when explicit access control is not needed.

2) Custom Roles

Custom role in Snowflake is a role that is created by users with appropriate privileges to grant the role and user ownership on specific securable objects. Custom roles can be created using the USERADMIN role or higher, as well as by any role that has been granted the CREATE ROLE privilege.

Note: Whenever a custom role is created, it is not assigned to any user or granted to any other role

It is recommended to create a hierarchy of custom roles with the top-most custom role assigned to the system role SYSADMIN when creating roles that will serve as the owners of securable objects, which allows SYSADMIN to manage all objects in the account while restricting management of users and roles to the USERADMIN role. If a custom role is not assigned to SYSADMIN through a role hierarchy, then the SYSADMIN role cannot manage the objects owned by that role.

Source: Snowflake documentation

What is Privileges in Snowflake ?

Privileges define specific actions that users or roles are allowed to perform on securable objects in Snowflake.

Privileges are managed using the GRANT and REVOKE commands.

In non-managed schemas, these GRANT and REVOKE commands can only be used by the role that owns an object or any Snowflake roles with the MANAGE GRANTS privilege for that particular object whereas, in managed schemas, only the schema owner or a role with the MANAGE GRANTS privilege can grant privileges on objects in the schema, including future grants, which centralizes privilege management.

Understanding Snowflake Roles Hierarchy and Privileges

As you can see in the diagram below, which shows the full structure of system-defined and user-defined roles in Snowflake, the highest-level role is given to a custom account role, which is then granted to another custom role, allowing the SYSADMIN role to inherit all their privileges.

Let's explore a real-world example to fully understand what Snowflake access control really is. Okay, then let's first start by creating a User in Snowflake!

Creating a User in Snowflake: Step-by-Step Guide

First, head over to your Snowsight or Snowflake UI and then proceed to create an account using **ACCOUNTADMIN **profile.

Step 1: Login or Signup to your Snowflake account.

Step 2: Check and validate your role. To do that, you can check the role by clicking on the drop-down role option above, located at the top of the Snowflake web UI, or you can simply type the command mentioned below to check it.

SELECT current_role()

Step 3: Creating a Snowflake User Without Role/default role

Let's create a new user for this demo; for that we need to provide a password and an attribute called MUST_CHANGE_PASSWORD. There are two ways to create a user: you can either use the Snowflake web UI (by navigating to the Admin tab, then Users and Roles, and selecting "+ Users"),

or you can write a SQL command like the one below.

CREATE USER pramit_default_user 
    PASSWORD = 'pramit123' 
    COMMENT = 'Snowflake User Without Role/default role' 
    MUST_CHANGE_PASSWORD = FALSE;

Note: we haven't assigned any Snowflake roles to this user

Step 5: Now, login to that particular user and to do that all you have to do is simply open a new tab and add the credentials which you just created.

Once you have logged in you can see that by default you are assigned with the role called PUBLIC

or you can simply type the command mentioned below to check it.

SELECT current_role()

Step 6: Now, let's write some queries to see what kinds of privileges this role has. To do so, copy and paste the command below.

SHOW GRANTS TO role PUBLIC;

As shown in the screenshot above, the user "pramit_default_user" has very limited privileges, including only basic access to sample data and no access to any warehouse associated with this role. Therefore, you cannot run any queries that require compute resources, except for those queries that run only in the cloud services.

Before moving on to the next step, let's test if this privilege allows us to create a database. Let's find out! To do so, simply copy pasta the following command:

CREATE DATABASE test_db

Nope! It doesn't work! It throws error like "Insufficient privileges to operate on account 'FM33694'" meaning that "pramit_default_user" does not have any privileges to do anything in this profile.

Step 7: Finally, let's check how our user profile will look likeFirstly, get the details of the user. To do so, you need to type "DESCRIBE USER" followed by the username, as shown in the command below. When you execute this command, it displays and describes all the properties of the user.

DESCRIBE USER pramit_default_user 

Secondly, lets get the grants that are currently available to this particular user named “**pramit_default_user”. **So for that simply type in the following command:

SHOW GRANTS ON USER pramit_default_user 

By doing this, you can easily find out who created your account, what grants you have on your user profile, and what properties are associated with your user profile.

Always keep in mind that only ACCOUNTADMIN and SECURITYADMIN can create users in Snowflake. It is recommended that users be created with the SECURITYADMIN role and that no objects be created with the ACCOUNTADMIN role.

Creating/Assigning Snowflake Roles and Privilege to Users: Step-by-Step Guide

Creating a new user and assigning a default role as a SYSADMIN role:

Step 1: Navigate to the "Admin" Sidebar and click on the "Users & Roles" menu.

Step 2: Click on the "+ user" button to create a new user through the web UI (without using SQL commands).

**Step 3: **Uncheck the box named “Force user to change password on first time login” to skip changing the password

**Step 4: **Click the advance option dropdown menu and choose the default role as a system admin for the new user and add all the details.

Step 5: Click "Create user" to save the user details and default role.

Let's assign Snowflake roles to the new user using SQL commands:

Step 1: In the SQL worksheet, enter the "CREATE USER" SQL command to create the new user with password and add attributes called DEFAULT_ROLE **and **MUST_CHANGE_PASSWORD

CREATE USER pramit_default_user_02
    PASSWORD = 'pramit123' 
    DEFAULT_ROLE = "SYSADMIN" 
    MUST_CHANGE_PASSWORD = FALSE;

Step 2: Add a "GRANT ROLE" SQL statement to grant the system admin role to the new user.

GRANT ROLE "SYSADMIN" TO USER pramit_default_user_02;

Step 3: Log in with the new user's credentials.

Step 4: Check the profile tab to view the default role (SYSADMIN) and the public role or click on the drop-down role option above, located at the top of the Snowflake web UI, or you can simply type the command mentioned below to check it.

SELECT current_role()

Step 5: Run the "SHOW GRANTS TO USER" SQL command to view any additional Snowflake roles assigned to the new user.

SHOW GRANTS TO USER pramit_default_user_02

Now finally let's assign additional Snowflake roles to the new user to do so follow along the steps outlined below:

Step 1: In the SQL worksheet, enter "GRANT ROLE" SQL statements to assign additional Snowflake roles to the new user and run the SQL commands to assign the new roles to the user.

GRANT ROLE "ORGADMIN" TO USER pramit_default_user_02;
GRANT ROLE "SECURITYADMIN" TO USER pramit_default_user_02;
GRANT ROLE "USERADMIN" TO USER pramit_default_user_02;

Step 2: Refresh the user's roles in the UI

So this is how we can create a user and assign different Snowflake roles and privileges to the user. Suppose if you do not assign any role to the user, remember that the Snowflake automatically applies the default PUBLIC role.

Finally, we arrived at the main juice of the article! Let us now get into the guts of what Snowflake DAC is all about.

Role Hierarchy in Snowflake

Discretionary Access Control (DAC)

Every object in Snowflake is associated with an owner who has the authority to grant access to that object to other roles. For instance, in the screenshot below, **pramit_default_user_02 **is created by the **ACCOUNTADMIN **role and is assigned ownership of this object.

Let's delve even further into the topic!

Suppose we have a user USER_FIRST who has an ORGADMIN role and has created a db, a schema, and a table. Since USER_FIRST belongs to the ORGADMIN role, the ORGADMIN eventually becomes the owner of this object. Although USER_FIRST created the object within the Snowflake instance, they are not the owner of the object; the ORGADMIN role is the owner.

Any new user who gets the ORGADMIN role can also perform any action on this object because they also represent ownership of it under that role.

So, even if you delete USER_FIRST, you will still be able to access the objects. Any other user with the ORGADMIN role can act as the owner of this object. As an owner, the individual user can alter, drop, or perform any action with them. Owners can also easily grant different privileges or access as they wish and at their own discretion, which is why it is called Discretionary Access Control.

In Snowflake, a number of objects can exist under a schema or at the account level, and these objects may have been created by multiple users at various periods. As these users are part of a role, the ultimate owner of these objects is the role, not the individual users who created ‘em.

Ever thought about how Snowflake keeps track of who owns the objects and entities that users make? Snowflake follows a unique ownership concept that allows any user with the same role to operate on an object.

Let's dive deep into this concept and understand it even better.

To begin with, we will head back to our previous worksheet and execute three context functions: current account and current role. These functions will help us determine our current account and role.

select current_account(),current_role()

As you can see in the above screenshot that we are currently logged in as the ACCOUNTADMIN **role, and our account is **FM33694, and our role allows us to perform various actions on the account.

Now, to see a list of all the users and who created them, we will run the "show users" command.

SHOW users;

Note: This command can only be executed by the ACCOUNTADMIN role. In case you are currently logged in with a different role, you can easily switch to the ACCOUNTADMIN role by running the command "USE role ACCOUNTADMIN"

Next, we will create a database, a schema, and a table to understand the ownership concept with respect to other objects. To do so, let's switch back to the role of SYSADMIN and try out some examples

USE ROLE SYSADMIN

create database some_awesome_db;
create schema some_awesome_schema;
CREATE TABLE some_awesome_table_1(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

SHOW DATABASE;

SHOW SCHEMAS;

SHOW TABLES;

After successfully creating these objects, we noticed that they were all owned by the SYSADMIN role. This means any user with the SYSADMIN role can operate on these objects.

To verify this let's log in as another user which we previously created pramit_default_user_02 in another tab and executed the same context functions.

select current_user(), current_role();

SHOW DATABASE;

SHOW SCHEMAS;

SHOW TABLES;

As you can see from the screenshot above we found that we could see all the databases, schemas, and tables created by the SYSADMIN role.

Also, remember that we can even drop the schema and table we had created as pramit_default_user_02. . This serves as an best example of the ownership concept.

drop schema SOME_AWESOME_SCHEMA;

This is the core principle that Snowflake follows: every object or entity created by a user is owned by a role, and any user with that role has the power to change that object and grant various permissions and privileges to other roles.

Okay, now let's get into the guts of what Snowflake RBAC is all about!

Roles-based Access Control (RBAC)

In Snowflake, roles are used to group users with similar access requirements. Each role is assigned a set of privileges, allowing users assigned to the role to access the resources they need. Roles can also be nested, allowing for more granular control over access to securable objects.

To create a new Snowflake roles, you can use the following command:

CREATE ROLE <role-name>

Once a Snowflake role is created, you can grant system or object privileges to the role using the GRANT command. For example, to grant a role the privilege to create a table, you can use the following query:

GRANT CREATE TABLE ON DATABASE <database_name> TO ROLE <role_name>;

To assign a Snowflake role to a user, you can use the following query:

GRANT ROLE <role_name> TO USER <user_name>;

To view the Snowflake roles assigned to a user, you can use the following query:

SHOW GRANTS TO USER <user_name>;

To view the privileges granted to a role, you can use the following query:

SHOW GRANTS TO ROLE <role-name>

To revoke a privilege from a role, you can use the REVOKE command. For example, to revoke the privilege to create a table from a role, you can use the following query:

REVOKE CREATE TABLE ON DATABASE <database_name> FROM ROLE <role_name>;

Let's say you want to create a Snowflake role hierarchy for your data warehouse and assign different roles to different users.

First, head over to your Snowflake web UI and check your current account user and role. Let's assume that your current account user is "PRAMIT_DEFAULT_USER_02" and your role is "ACCOUNTADMIN".

Note: Snowflake recommends creating all roles with the "SECURITYADMIN" role.

You need to start by creating roles and granting privileges. To understand how the Snowflake hierarchy works, you can create multiple roles and assign multiple users to them.

Step 1: Create roles.

Start by creating roles for different types of users. For example, you might create sales managers, sales reps, and finance roles. Here are some example queries:

use role securityadmin;

create role "SALES_MANAGER_ROLE" comment = 'This is the role for sales managers';
create role "SALES_REP_ROLE" comment = 'This is the role for sales representatives';
create role "FINANCE_ROLE" comment = 'This is the role for finance team';

Step 2: Grant privileges to roles and create a role hierarchy

Next, grant appropriate privileges to each role. For example, Create a hierarchy of roles by granting roles to other roles. For example, you might create a "department manager" role that includes both the "project manager" and "development team" roles. Here are some example queries:

grant role "SALES_MANAGER_ROLE" to role "SECURITYADMIN";
grant role "SALES_REP_ROLE" to role "SALES_MANAGER_ROLE";
grant role "FINANCE_ROLE" to role "SALES_MANAGER_ROLE";

These above commands will first assign the "SALES_MANAGER_ROLE" role to "SECURITYADMIN", which means that the latter will inherit all the privileges associated with the former. Then, the "SALES_REP_ROLE" and "FINANCE_ROLE" roles will be assigned to "SALES_MANAGER_ROLE", which will also pass on their respective privileges to "SECURITYADMIN"

Step 3: Accessing the Graph

To see the visualization of the role hierarchy, head over to the Snowflake home dashboard, click on the admin sidebar panel, select "Users & Roles".

Once you have done that, navigate to the "Roles" tab. Here, you can see your role hierarchy represented in a graphical format.

Step 4: Create users

Create users and assign them to roles. For example, you might create users for sales managers, finance manager and slaes rep members. Here is how you can do it:

Note: Snowflake recommends creating all users with the "USERADMIN" role.

use role USERADMIN;
create user sales_manager_1 password = 'salesmanager123' comment = 'sales manager' must_change_password = false; 

create user finance_user password = 'finance123' comment = 'finanace user' must_change_password = false; 

create user sales_rep_user password = 'salesrep123' comment = 'finanace user' must_change_password = false; 

Step 5: Assign roles to users

Finally, assign/grant appropriate roles to each user. For example, you might grant the "sales manager" role to the sales_manager_1 user and so on:

use role securityadmin;
-- Grant the sales_manager_role role to the user
GRANT ROLE sales_manager_role TO USER sales_manager_1;

-- Grant the sales_rep_role role to the user
GRANT ROLE sales_rep_role TO USER sales_rep_user;

-- Grant the finance_role role to the user
GRANT ROLE finance_role TO USER finance_user;

So by following these steps, you can easily create a Snowflake role hierarchy and assign different roles to different users according to their needs and responsibilities.

This is how the Snowflake role hierarchy works. By creating and assigning roles to users, you can control their access to your data warehouse, allowing them to perform only the relevant tasks according to their assigned roles.

Conclusion

Snowflake role management and access control features play a huge role in securing and managing access to resources in Snowflake.

In this article, we covered the following topics:

• Access Control Framework
• Key elements of Snowflake access control framework
• Securable objects
• Snowflake roles, default roles and types of Snowflake roles
• Snowflake privileges
• Snowflake Discretionary Access Control
• Snowflake Role-Based Access Control
• Role hierarchy and how it works
• Examples of how to use roles to manage access privileges effectively

So, by using these features, you can create and implement a security architecture for your Snowflake that fits your needs and requirements.

Don't leave your Snowflake access controls and roles up in the air—take control! As they say, "Better safe than sorry, because when it comes to security, the sorry part can be very expensive!"