Snowflake Cheatsheet | 3 Approaches to Creating Users

One of the most basic tasks available in Snowflake is the ability to create and manage users. This is a core pillar of creating a secure and auditable instance that requires minimal maintenance work.

User creation is also one of the areas that I see people struggle with early on.

Today, we’re going to examine the three most common approaches for user creation and provide simple SQL code to make the process painless.

Password-based User Creation

This approach is the most common I see for new Snowflake users. It has its drawbacks, but it is simple and effective.

To create a user with password-based authentication, run the following SQL in Snowflake:

// set context
USE ROLE SECURITYADMIN;
// create user with password authentication
CREATE USER PEGGY_OLSON
  PASSWORD             = 'randomly-generated-password'
  LOGIN_NAME           = 'peggy.olson@scdp.com'
  DISPLAY_NAME         = 'Peggy Olson'
  FIRST_NAME           = 'Peggy'
  LAST_NAME            = 'Olson'
  EMAIL                = 'peggy.olson@scandp.com'
  MUST_CHANGE_PASSWORD = TRUE
  DEFAULT_ROLE         = COPY_WRITER;
// grant usage on the default role
GRANT ROLE COPY_WRITER TO USER PEGGY_OLSON;

There are a few things to note about this script:

• Snowflake recently added the USERADMIN role to their list of default roles that SECURITYADMIN inherits and is intended to be used to create users. I don’t particularly need this added level of granularity, so I stick to SECURITYADMIN. Feel free to modify for your needs.
• I encourage you to find a user naming convention that works for you. I would not use emails for this as the @ in the email address will require constant double-quoting of your user object names. Instead, I find FIRSTNAME_LASTNAME works well for almost all use cases.
• Specifying the LOGIN_NAME and DISPLAY_NAME is not necessary as both will automatically inherit the value you use to name the user object. This is a tricky idea for new users, so I prefer to specify each one explicitly — even if you don’t intend to use different values as I have above.
• For reasons that become obvious in the SSO section below, it is a good idea to make your user LOGIN_NAME the same as their email address. This is a nice way to save them from having to memorize a new username for logging in, but it also will save you some headaches if you ever do implement SSO on your Snowflake account.
• I like to set default roles for all new users, but this is optional. A user can always set their own default context on their own, but many won’t realize this until later on in their Snowflake journey.
• You must grant usage on a user’s default role to that user after the user is created. Snowflake does not automatically grant access to a user’s default role. A user will not be able to log in if they cannot access their default role. Avoid giving a new user a bad first impression by remembering to always grant them access to their default role.

Single Sign-On (SSO) User Creation

This method is the most popular for enterprise users and is the most convenient as a user, but it requires the use of an external Identity Provider or “IdP” as the security hipsters call it.

Some common IdP’s include:

• Azure Active Directory (AD)
• Google GSuite
• Okta

The simpler SQL for creating an SSO user is below:

// set context
USE ROLE SECURITYADMIN;
// Create user with SSO authentication
CREATE USER LANE_PRYCE
  LOGIN_NAME   = 'lane.pryce@scdp.com'
  DISPLAY_NAME = 'Lane Pryce'
  FIRST_NAME   = 'Lane'
  LAST_NAME    = 'Pryce' 
  EMAIL        = 'lane.pryce@scdp.com'
  DEFAULT_ROLE = FINANCIAL_CHIEF;
// grant usage on the default role
GRANT ROLE FINANCIAL_CHIEF TO USER LANE_PRYCE;

Again, a few notes about this script:

• The LOGIN_NAME absolutely must match the email that your Identity Provider uses. Any mismatch or new email will result in a failed SSO attempt when the user tries to log in. I’ve even accidentally included a single space at the end of this and found it to result in login errors, so be meticulous here.
• By not specifying a password, you are removing the ability for this user to login with a password at all. If you want to grant the ability to use a password in the future, simply alter the user and set a password that they must change. This will allow them to log in with either SSO or password authentication, which can be useful if your client tool doesn’t support SSO (like PowerBI, until recently).
• If your user is authenticating with SSO through non-Snowflake client tools, it isn’t a bad idea to also set a DEFAULT_WAREHOUSE right next to the DEFAULT_ROLE. Some tools that support SSO aren’t the best about allowing users to select their warehouse after connecting, so this can save some headaches (it’s also what I do internally at Hashmap).
• Again, do not forget to grant role access after creating your user.

Keypair User Creation

This last approach is rarely used alone (and often used with service accounts), but it is an important security feature for Snowflake users that want a secure connection method from a programmatic source.

For example, if you are a dbt user (and you should be), keypair authentication is a great complement to a Snowflake instance with SSO support to avoid constant external browser popups during development.

To create a user with keypair authentication, use the following SQL:

// set context
USE ROLE SECURITYADMIN;
// Create user with SSO authentication
CREATE USER BERTRAM_COOPER
  LOGIN_NAME     = 'bertram.cooper@scdp.com'
  DISPLAY_NAME   = 'Bert Cooper'
  FIRST_NAME     = 'Bertram'
  LAST_NAME      = 'Cooper'
  EMAIL          = 'bertram.cooper@scdp.com'
  RSA_PUBLIC_KEY = 'your private key here - no newlines please'
  DEFAULT_ROLE   = SENIOR_PARTNER;
// grant usage on the default role
GRANT ROLE SENIOR_PARTNER TO USER BERTRAM_COOPER;

A few things to note about this process:

• If you have SSO set up, your user will be able to log in with SSO as well. They will not be able to log in with password authentication, however, until you set a password for them.
• If your user already exists and you are simply adding keypair authentication, use ALTER USER BERTRAM_COOPER SET RSA_PUBLIC_KEY='your key'; instead.
• Key rotation is achieved by setting a new public key using ALTER USER RODGER_STERLING SET RSA_PUBLIC_KEY_2='your new key';. This allows you to rotate your keys without suddenly denying access to the previous key.
• I have had all kinds of pain trying to format the RSA_PUBLIC_KEY value in the past. The big thing to know is that newlines should be removed and only include the actual key information. In the picture below, you’d want to grab only lines 2 through 8, remove all new lines, and paste the line directly into your SQL.

It seems so simple

• I have not been able to successfully use an RSA key pair generated withssh-keygen on Snowflake. It appears that Snowflake strictly likes keys generated with openssl. The code I used in this example is available below, but you should google this before using it so you know what you’re getting in to.

#!/bin/bash
# generate private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_bertram_cooper_keypair
# generate public key
openssl rsa -in snowflake_bertram_cooper_keypair -pubout -out snowflake_bertram_cooper_keypair.pub

Closing Thoughts

These three approaches to user authentication should cover most of your needs as a Snowflake administrator. A few other sensible policies like banning password sharing and IP whitelisting are worth implementing as well to ensure you have the best level of security for your Snowflake instance.

You can also watch my Hashmap Megabytes Episode 3 video (11 min) on this same topic if you’d like a more interactive review of the 3 approaches!

Need Help with Snowflake?

If you are considering the cloud and Snowflake for migrating or modernizing data and analytics products and applications or if you would like help and guidance and a few best practices in delivering higher value outcomes in your existing cloud program, then please contact us.

Hashmap offers a range of Snowflake enablement workshops and assessment services, cloud modernization and migration services, and consulting service packages as part of our Cloud (and Snowflake) service offerings.

How does Snowflake compare to other data platforms? Our technical experts have implemented over 250 cloud/data projects in the last 3 years and conducted unbiased, detailed analyses across 34 business and technical dimensions, ranking each cloud data platform.

See How Snowflake Compares to Redshift Synapse Big Query & CDP | Hashmap

Other Tools and Content For You

Snowflake Utilities & Accelerators | Do more with Snowflake | Hashmap

The Hitchhiker’s Guide to Timestamps in Snowflake: Part 1

5 Things I Wish I Knew Before Learning Snowflake

30 Second Snowflake Cloud Data Warehouse Cheat Sheet

Feel free to share on other channels and be sure and keep up with all new content from Hashmap here.

Also, you can catch Randy as a host on Data Rebels on Tap, a podcast focused on all things data engineering and the cloud — available on Spotify, Apple, and other popular audio apps.

Data Rebels on Tap | Hashmap Podcast

Randy Pitcher is a Cloud and Data Engineer (and OKC-based Regional Technical Expert) with Hashmap providing Data, Cloud, IoT, and AI/ML solutions and consulting expertise across industries with a group of innovative technologists and domain experts accelerating high-value business outcomes for our customers.

Be sure and connect with Randy on LinkedIn and reach out for more perspectives and insight into accelerating your data-driven business outcomes or to schedule a hands-on workshop to help you go from Zero to Snowflake.