Grant role to user in Snowflake

Grant role to user in Snowflake

Having good control over which users have which roles in Snowflake is a key part of maintaining good data practices. Being able to grant the correct role to a user is therefore important to know. Thankfully this is incredibly easy to do in Snowflake.

Stephen Allwright
Stephen Allwright

Having good control over which users have which roles in Snowflake is a key part of maintaining good data practices. Being able to grant the correct role to a user is therefore important to know. Thankfully this is incredibly easy to do in Snowflake.

This is how to you grant role to a user in Snowflake:

GRANT ROLE <name> TO { ROLE <parent_role_name> | USER <user_name> }

Grant role to a user in Snowflake

Granting a role to a user enables the user to undertake all the operations and privileges that are permitted by the role. An example of granting a role to a user can be seen here:

grant role engineer to user user1

Grant role to a role in Snowflake

It’s also possible to grant a role to another role, which creates a “role hierarchy” between the two. For example, if you have a main sysadmin role and want to grant a newly created role to that role, then you could do that like so:

grant role engineer to role sysadmin;

This example would make the role sysadmin the “parent” to the role engineer in this role to role hierarchy.

Snowflake privileges, roles, and users

A Snowflake privilege is an action that a role can undertake, and that role is then granted to a user. A way to visualise these relationships is:

Privilege → Role → User

There are many different types of privileges that a role can receive, but the key ones to know are:

  • SELECT: Makes it possible to run select commands
  • UPDATE: Makes it possible to run update commands
  • TRUNCATE: Makes it possible run a truncate table command
  • CREATE: Make it possible to run a create table or create view command
  • INSERT: Make it possible to run an insert command

Creating tables and views

Create table in Snowflake
Create or replace table in Snowflake

Editing tables and views

Drop table in Snowflake
Delete table in Snowflake

References

Snowflake grant role documentation
Snowflake access control privileges

Snowflake

Stephen Allwright Twitter

I'm a Data Scientist currently working for Oda, an online grocery retailer, in Oslo, Norway. These posts are my way of sharing some of the tips and tricks I've picked up along the way.

Comments