Explaining Permissions in Snowflake

The aim of this page📝 is to describe Snowflake authentication model. What was confusing me the most — having worked with Microsoft Active Directory in the past — is that permissions are only assigned to roles and that a user must assume a role. There is no way of having bunch of users and each of them being a special unique little flower. The only way are roles, i.e. templates for permissions.

3 min readAug 15, 2023

Snowflake’s approach to access control combines aspects from
Discretionary Access Control (DAC)
Role-based Access Control (RBAC)
In DAC, each object has an owner, who can in turn grant access to that object
In RBAC, access privileges are assigned to roles, which are in turn assigned to users.
The 4 key concepts to understanding access control in Snowflake are these: Securable object + Role + Privilege +User
A securable object is an entity to which access can be granted. Unless allowed by a grant, access is denied.
A role is an entity to which privileges can be granted. Roles are in turn assigned to users. Note that roles can also be assigned to other roles, creating a role hierarchy.
A privilege is a defined level of access to an object. Multiple distinct privileges may be used to control the granularity of access granted.
A user is a user identity recognized by Snowflake, whether associated with a person or program.

In Snowflake, permissions are not assigned directly to users. Instead, they are assigned to roles, which are then granted to users.
This means that every user must have at least one role in order to have any permissions.
Roles are used to control access to objects in the system, such as databases and tables.
Roles are granted access privileges for these objects, and then the roles are granted to users to enable them to create, modify, and use the objects for which the roles have privileges.
If you want to assign specific permissions to a user, you would need to create a custom role with the desired permissions and then grant that role to the user.
You can create as many custom roles as you need in order to achieve the desired combination of permissions for your users.

The first command SHOW GRANTS TO USER <user>; shows the roles granted to the user, i.e. what is user allowed to do
The second command SHOW GRANTS ON USER <user>; shows the privileges granted on the user, i.e. what role is allowed to do something to the user

In Snowflake, OWNERSHIP is a special type of privilege that grants full control over an object and can only be granted from one role to another role. Only users with the OWNERSHIP role can grant Snowflake database roles.
USAGE enables viewing a Snowflake Marketplace or Data Exchange listing.
The SHOW GRANTS statement can be used to see all access control privileges that have been explicitly granted to roles, users, and shares in Snowflake.
To view all privileges granted to a role, the SHOW GRANTS TO ROLE <role_name> statement can be used.
To see privileges granted on a specific warehouse, database or schema, the SHOW GRANTS ON <object_type> <object_name> statement can be used where <object_type> is either WAREHOUSE, DATABASE or SCHEMA and <object_name> is the name of the warehouse, database or schema.

Snowflake Grant Report is a tool developed by Snowflake Labs that extracts Roles and Grants data from Snowflake and provides tabular and visual reports on the Role hierarchy and Grant assignments.
It also provides the ability to compare privilege configurations between two different reports, enabling analysis of privilege changes over time in the same or even different accounts.