Welcome back to our blog series covering access controls in the Snowflake Data Cloud. If you missed the first blog on how to grant a role to a user, be sure to check it out. Today, we’ll cover role grants and how they can be beneficial to creating a role hierarchy in Snowflake.
How to Grant a Role to Another Role in Snowflake
In Snowflake, a role can be granted to another role, creating a role hierarchy. The below example grants the analyst role to the administrator role.
GRANT ROLE analyst TO ROLE administrator;
The syntax can be read as:
GRANT ROLE <from_role> TO ROLE <to_role>;
What Does the GRANT ROLE Statement Do?
The GRANT ROLE statement acts like other GRANT statements in Snowflake, it attaches a privilege to an object. In this case, the role administrator is given the USAGE privilege on the analyst role.
Image created by Traverse
This is similar to a grant on a database, for example. A USAGE grant on a database allows a user to ‘use’ the database, and a USAGE grant on a role allows another role to ‘use’ the role:
USE ROLE engineer;
Role grants are special though, because all the privileges of the inherited (granted from) role are always available to the inheriting (granted to) role. If a role is granted another role, it at all times has the privileges of that role, or, the role inherits the privileges of another role.
What are Role Grants Good For?
The previous section talked about two things role grants allow:
- The inherited role always has the privileges of the inheriting role
- The inheriting role can ‘use’ the inheriting role
A role always having the privileges of an inherited role allows reuse of a role. If for example we wanted, from the previous grant statement, all privileges of the ‘analyst’ role’ are now available to (inherited by) the ‘administrator’ role’. The analyst role can be re-used wherever it’s needed, for example granting it to an ‘engineer’ role.Â
GRANT ROLE analyst TO ROLE engineer;
Image created by Traverse
Reuse of a role allows us to add additional privileges to the role without granting all those additional privileges to each of the inheriting roles. For a complete example of role inheritance, visit our blog on how to structure Snowflake roles.Â
Secondly, the ability to ‘use’ an inherited role (like administrator can ‘use’ analyst) allows users to take advantage of the principle of least privilege. Users might be granted full administrative rights to some objects, but if they are just doing some analytics work they will want to use the least powerful role possible when executing SQL or running a workflow.
Summary
By granting roles to other roles, we can create a role hierarchy, reduce the number of privileges that need to be granted through role reuse, and allow users to take advantage of the principle of least-privilege.
