August 27, 2021

How Do I Grant Privileges to a Role in Snowflake? Pt. 3

By Tony Foerster

Thanks for tuning back into our series on access controls in the Snowflake Data Cloud. If you missed our previous blog on how to grant a role to another role in Snowflake, be sure to give it a read. In this blog, we’ll explore how to grant privileges to a role in Snowflake. 

Snowflake has a fine-grained access control model where different levels of privileges can be granted to roles. Privileges are always granted to roles (never directly to users). The following statement grants the USAGE privilege on the database rocketship to the role engineer:

					GRANT USAGE ON DATABASE rocketship TO ROLE engineer;
A simple, two-part diagram with a circle and a square.

Image created by Traverse

Privilege grants in Snowflake always follow the same form:

					GRANT <privileges> ON <object type> <object name> TO ROLE <role_name>;

Multiple privileges can be granted in the same statement, they must be separated by commas:


Now engineer has both USAGE and MONITOR privileges on the database rocketship:

A simple 3-part diagram

Image created by Traverse

Each different object type has its own special set of privileges, for example, the database object type has the privileges MODIFY, MONITOR, USAGE, CREATE SCHEMA, and IMPORTED PRIVILEGES. Privileges for each specific object type can be found in the Snowflake documentation for grants.

To grant every privilege instead of listing them out, the privilege list can be replaced by the special keyword ALL can be used:

					GRANT ALL ON DATABASE rocketship TO ROLE engineer;

ALL isn’t a real privilege, but instead is expanded by Snowflake into all available privileges for that object type (in this case all database privileges).

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