August 17, 2021

How Do I Grant a Role to a User in Snowflake? Pt.1

By Tony Foerster

Welcome to the first blog of our multi-part series on how to better understand access controls in the Snowflake Data Cloud. In this series, we’ll provide how-to’s on access control topics in Snowflake.

How to Grant a Role to a User in Snowflake

The below SQL example grant the role ‘engineer’ to the user ‘sally_ride’:

					GRANT ROLE engineer TO USER sally_ride;

The statement creates an inheritance relationship where the user sally_ride has the role engineer:

Image created by Traverse

Roles are special in Snowflake because they are the only objects privileges can be granted to. It is not possible to grant any privileges directly to a user. Before a user does any work in Snowflake, they must have a role assigned to them, and the role must have privileges granted to it.

How are Roles Used in Snowflake?

Once a user has been assigned a role, they can select the role to be used in their session with the ‘use’ statement:

					USE ROLE engineer;

Users may be granted multiple roles, but a user can only ‘use’ one role at a time.

Each user has a ‘default’ role. This is the role that will be used when no other role is specified. This statement will set a default role for the ‘sally_ride’ user:

					ALTER USER sally_ride SET DEFAULT_ROLE=engineer;

If no other role is selected in the session by executing a ‘use’ statement, the default_role will be used.

This post has shown some of the basics of granting roles to users and how roles are used. Keep an eye out for more posts on access control topics, including role management, role hierarchies, granting privileges, and understanding existing privileges in Snowflake.

Next up in Series

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit