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...