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