Chapter 5: Data Protection and Security in Snowflake

For any data analytics solution, securing access to data is of paramount importance. There are two components to this security: authentication (that is, letting a user connect) and authorization (that is, what objects a connected user has access to). Snowflake provides discretionary access control and role-based access control through out-of-the-box and custom roles. This chapter will explore techniques for setting up role hierarchies, adding custom roles, and setting default roles for users.

The following recipes will be covered in this chapter:

• Setting up custom roles and completing the role hierarchy
• Configuring and assigning a default role to a user
• Delineating user management from security and role management
• Configuring custom roles for managing access to highly secure data
• Setting up development, testing, pre-production, and production database hierarchies and roles
• Safeguarding the ACCOUNTADMIN...

Technical requirements

The chapter assumes that you have a Snowflake account already set up.

The code for this chapter can be found at https://github.com/PacktPublishing/Snowflake-Cookbook/tree/master/Chapter05.

Setting up custom roles and completing the role hierarchy

In this recipe, we will introduce new custom roles and learn how and why to complete the role hierarchy. We will also understand how the role hierarchy works in Snowflake.

Getting ready

Before proceeding with this recipe, please ensure that the user you will use has access to the SECURITYADMIN role. Note that this recipe's steps can be run either in the Snowflake web UI or the SnowSQL command-line client.

How to do it…

To create a new custom role, we need to have access to the SECURITYADMIN role, using which we will create a new custom role:

1. We will start by creating a database called DEV:

   USE ROLE SYSADMIN;
   CREATE DATABASE DEV;

   The database should be created successfully.

2. Next, we will create a table called CUSTOMER, which we will use to grant privileges on:

   USE DATABASE DEV;
   CREATE TABLE CUSTOMER
   ( ID STRING,
   NAME STRING);

   The table should be created successfully.

3. Now, change your role to...

Configuring and assigning a default role to a user

In this recipe, we will assign a default role to a user and understand the sequence in which the default role is determined for a session.

Getting ready

Before proceeding with this recipe, please ensure that the user you will use has access to the SECURITYADMIN role. Note that this recipe's steps can be run in either the Snowflake web UI or the SnowSQL command-line client.

How to do it…

To create a new user and grant a default role, we need to log in as a user who has access to the SECURITYADMIN role, using which we will create a new user:

1. Create a new user, which we will call marketing_user1. Notice that no role has been granted to the user, and no default role has been specified:

   USE ROLE SECURITYADMIN;
   CREATE USER marketing_user1 PASSWORD='password123' MUST_CHANGE_PASSWORD = TRUE;

   The user will be created successfully

2. Now, log in as marketing_user1 and run the following query to view what...

Delineating user management from security and role management

In this recipe, we will be using Snowflake's built-in roles to isolate access for operational teams that create users and roles and security teams that manage and control the access rights. The USERADMIN Snowflake role provides privileges for creating and managing users without requiring a higher privilege as with SECURITYADMIN. Therefore, the USERADMIN role can be granted to the operations team responsible for onboarding and offboarding users to your Snowflake instance.

Getting ready

Before proceeding with this recipe, please ensure that the user you will modify can use the SECURITYADMIN role. Note that this recipe's steps can be run in either the Snowflake web UI or the SnowSQL command-line client.

How to do it…

We will create a new user to whom we will grant the USERADMIN role:

1. Create a new database that will be used to demonstrate privilege management:

   USE ROLE SYSADMIN;
   CREATE DATABASE...

Configuring custom roles for managing access to highly secure data

In this recipe, we will explore securing access to highly confidential data through a custom role. We will also explore how we can deny access to specific datasets to even the administrative users.

Getting ready

Before proceeding with this recipe, please ensure that the user you will use can use the SECURITYADMIN role. Note that this recipe's steps can be run in either the Snowflake web UI or the SnowSQL command-line client.

How to do it…

We will create a new custom role and demonstrate how it can be configured to control access to sensitive data:

1. Let's start by creating the database that will hold the sensitive data. In this database, we will also create a table that contains salary information:

   USE ROLE SYSADMIN;
   CREATE DATABASE sensitive_data;
   CREATE TABLE SALARY
   (
     EMP_ID INTEGER,
     SALARY NUMBER
   );

   You should see the database and the table created successfully...

Setting up development, testing, pre-production, and production database hierarchies and roles

This recipe will walk you through the setup of different environments, such as development, testing, pre-production and production databases, and schemas, and configuring custom roles for managing access.

Getting ready

Before proceeding with this recipe, please ensure that the user you will use can use the SECURITYADMIN role. Note that this recipe's steps can be run in either the Snowflake web UI or the SnowSQL command-line client.

How to do it…

We will demonstrate the creation of development and production databases, roles, and users. The same concept can be extended if other environments are needed:

1. Let's start by creating a new user who will act as a DBA for the development environment:

   USE ROLE SECURITYADMIN;
   CREATE USER dev_dba_1
   PASSWORD = 'password123' 
   DEFAULT_ROLE = DEV_DBA_ROLE
   MUST_CHANGE_PASSWORD = TRUE;

   The user is created with a success...

Safeguarding the ACCOUNTADMIN role and users in the ACCOUNTADMIN role

This recipe will walk you through techniques to safeguard the ACCOUNTADMIN role from unauthorized access and introduce redundancy in the role membership. The recipe will then walk you through the process of securing users in the ACCOUNTADMIN role through multi-factor authentication (MFA).

Getting ready

Before proceeding with this recipe, please ensure that you have access to the ACCOUNTADMIN user. Note that the steps in this recipe should be run through the Snowflake web UI.

How to do it…

To introduce redundancy in the ACCOUNTADMIN role, we will create a new user and grant that user the ACCOUNTADMIN role. Next, we will enable MFA for the newly created user:

1. Create a new user that we will give the ACCOUTNADMIN role using the syntax that follows. Make sure that you provide a valid email address for the user. The email address will be used in the next steps to set up MFA:

   USE ROLE SECURITYADMIN...