Snowflake Account setup using System defined roles

This is the first blog in a series that will focus on Snowflake, where we’ll cover best practices for using Snowflake, explore various Snowflake functionalities, discuss how to maximize the benefits of Snowflake, and address the challenges that come with its implementation or migration.

In this blog, we’ll start by discussing setting up a Snowflake account, especially for those new to the Snowflake ecosystem. With a Snowflake account readily available and a limited understanding of its system-defined roles, it usually becomes a challenge for a team lead or an admin to set up the environments with proper access controls to its developers or users.

To start with the account setup, first, you would need a user with ACCOUNTADMIN role access for the Snowflake account. This can be provided by a user who has ORGADMIN Snowflake account access.

This is understood by the example below:

An organization has one Snowflake organization-wide account and is managed by ORGADMIN. ORGADMIN can create multiple accounts under the same organization in Snowflake, which different teams can separately manage.

Before creating users, roles, warehouses, databases, etc., you must first understand the System Defined Roles in Snowflake below and what Snowflake recommends as a best practice while setting up the account.

System-Defined Roles

USERADMIN:

• The initial part of the account creation process is creating users and roles within an account.
• USERADMIN roles’ purpose is users and role creation. This role is granted with CREATE USER and CREATE ROLE security privileges.

SECURITYADMIN:

• A role is incomplete without any grants, and the SECURITYADMIN role is solely used for granting.
• Anything relating to grants in Snowflake is completely managed by SECURITYADMIN role.
• Once USERADMIN creates users and roles, you can use SECURITYADMIN to grant the users appropriate roles.
• You can grant warehouses, databases, schemas, integration objects, and access to create tables, stages, views, etc., to a role using SECURITYADMIN role.
• SECURITYADMIN role inherits the privileges of the USERADMIN role via the system role hierarchy.
• Note that Snowflake doesn’t have the concept of user groups. Instead, the Users are created, and necessary roles are granted to the user.

SYSADMIN:

• SYSADMIN creates the objects like databases, warehouses, schemas, etc., in an account.
• Although it creates objects like databases, warehouses, etc., it doesn’t grant access to these objects to the roles. It’s done by SECURITYADMIN.

ACCOUNTADMIN:

• ACCOUNTADMIN role encapsulates the SYSADMIN and SECURITYADMIN system-defined roles. It is the top-level role in the system and should be granted only to a limited/controlled number of users in your account.
• Other than this, ACCOUNTADMIN only has access to CREATE INTEGRATION objects in Snowflake.
• As a best practice, enable Users with ACCOUNTADMIN roles should have MFA enabled.

ORGADMIN:

• This role is mainly used to create accounts within an organization.
• Each account acts as a separate entity and will have its own databases, warehouses, and other objects.

PUBLIC:

• As the name suggests, this role can be accessed by every other user in an account.
• Objects created as a part of a PUBLIC role can be accessed by anyone and used when there is no need for access controls over the objects, and can be shared across the account.
• Generally, non recommended to use this role for production purposes.

 Setting up an Account With an Example

Since now it’s clear what every system-defined role is meant to do in Snowflake, let’s see some basic examples of setting up an account using them.

Assuming that you have logged in using a user having ACCOUNTADMIN access, let’s see below the use case:

• There are four users named Meghna, Adnan, Kaushik, and Shushant
• Meghna and Adnan are from an analytics team who builds reports using reporting tools. Hence, they only need the read access for the objects created.
• Kaushik and Shushant are from the data engineering team and build pipelines to load the data into the Snowflake databases. Since it’s a development environment, they will have read-and-write access to the objects created.

So, let’s use the usernames as their first names: Meghna, Adnan, Kaushik, Shushant

Since they are working in an analytics project and dev environment, we can create two roles:

• One for read named ROLE_DEV_ANALYTICS_RO
• One for read/write access named ROLE_DEV_ANALYTICS_RW

Steps:

First, as discussed, let’s create the users using the USERADMIN role.

use role USERADMIN;

– Create the roles

create role ROLE_DEV_ANALYTICS_RO;

create role ROLE_DEV_ANALYTICS_RW;

– create the users

create user meghna password='abc123' default_role = ROLE_DEV_ANALYTICS_RO default_secondary_roles = ('ALL') must_change_password = true;

create user adnan password='abc123' default_role = ROLE_DEV_ANALYTICS_RO default_secondary_roles = ('ALL') must_change_password = true;

create user kaushik password='abc123' default_role = ROLE_DEV_ANALYTICS_RW default_secondary_roles = ('ALL') must_change_password = true;

create user shushant password='abc123' default_role = ROLE_DEV_ANALYTICS_RW default_secondary_roles = ('ALL') must_change_password = true;

Note that all four users are created using the same password with the argument must_change_password = true, which will force them to change the passwords upon the first login.

 Use SECURITYADMIN to grant users their respective roles:

use role SECURITYADMIN;

– Grant the Roles created to SYSADMIN

grant role ROLE_DEV_ANALYTICS_RO to role SYSADMIN;

grant role ROLE_DEV_ANALYTICS_RW to role SYSADMIN;

This is done so that objects like tables, stages, views, etc., created using the roles should be accessible by SYSADMIN as well. If this is not granted, SYSADMIN won’t be able to access or manage the objects created by these roles.

use role SECURITYADMIN;

– Grant the users to the roles

grant ROLE ROLE_DEV_ANALYTICS_RO to user meghna;

grant ROLE ROLE_DEV_ANALYTICS_RO to user adnan;

grant ROLE ROLE_DEV_ANALYTICS_RW to user kaushik;

grant ROLE ROLE_DEV_ANALYTICS_RW to user shushant;

Now let’s use SYSADMIN to create the warehouse, databases, schemas, etc.

use role SYSADMIN;

-- Create database and schemas

create database analytics_dev;

create schema analytics_dev.analytics_master;

create schema analytics_dev.analytics_summary;

-- Create warehouse

create warehouse analytics_small with

warehouse_size = 'SMALL'

warehouse_type = 'STANDARD'

auto_suspend = 60

auto_resume = TRUE ;

The above SQL is creating a small warehouse that can suspend in 60 seconds of inactivity and auto resume whenever queries are triggered. Now, since the database, schema, and warehouse is ready, it is time to grant the roles the necessary accesses using SECURITYADMIN.

Let’s assume that only tables and views are used for this project.

use role SECURITYADMIN;

– Granting the usage access to ROLE_DEV_ANALYTICS_RO

grant usage on database analytics_dev to role ROLE_DEV_ANALYTICS_RO;

grant usage on all schemas in database analytics_dev to role ROLE_DEV_ANALYTICS_RO;

grant select on future tables in database analytics_dev to role ROLE_DEV_ANALYTICS_RO;

grant select on all tables in database analytics_dev to role ROLE_DEV_ANALYTICS_RO;

grant select on future views in database analytics_dev to role ROLE_DEV_ANALYTICS_RO;

grant select on all views in database analytics_dev to role ROLE_DEV_ANALYTICS_RO;

– Granting the usage access to ROLE_DEV_ANALYTICS_RW

grant usage on database analytics_dev to role ROLE_DEV_ANALYTICS_RW;

grant usage on all schemas in database analytics_dev to role ROLE_DEV_ANALYTICS_RW;

grant select on future tables in database analytics_dev to role ROLE_DEV_ANALYTICS_RW;

grant select on all tables in database analytics_dev to role ROLE_DEV_ANALYTICS_RW;

grant select on future views in database analytics_dev to role ROLE_DEV_ANALYTICS_RW;

grant select on all views in database analytics_dev to role ROLE_DEV_ANALYTICS_RW;

grant create table on schema analytics_dev.analytics_master to role ROLE_DEV_ANALYTICS_RW;

grant create view on schema analytics_dev.analytics_master to role ROLE_DEV_ANALYTICS_RW;

grant create table on schema analytics_dev.analytics_summary to role ROLE_DEV_ANALYTICS_RW;

grant create view on schema analytics_dev.analytics_summary to role ROLE_DEV_ANALYTICS_RW;

As seen above, ROLE_DEV_ANALYTICS_RO has been granted read access only, and ROLE_DEV_ANALYTICS_RW is granted both read and write access.

 Finally, let’s grant the warehouse to the roles.

use role SECURITYADMIN;

grant USAGE , OPERATE on warehouse analytics_small to role ROLE_DEV_ANALYTICS_RO;

grant USAGE , OPERATE on warehouse analytics_small to role ROLE_DEV_ANALYTICS_RW;

Users with appropriate permissions should now be able to log in to Snowflake and should be able to use only the roles associated with proper permissions.

Thank you for reading through the entire article.

In the next installment of this series, we will delve into some of the most effective practices for loading files into Snowflake.