Understanding Access Control in Snowflake¶
Snowflake provides granular control over access to objects — who can access what objects, what operations can be performed on those objects, and who can create or alter access control policies. Note that authentication is a separate topic covered elsewhere in the Snowflake documentation.
In this Topic:
Overview and Key Concepts¶
Snowflake’s approach to access control combines aspects of the following two models:
- Discretionary Access Control (DAC), in which each object has an owner, who can in turn grant access to that object.
- Role-based Access Control (RBAC), in which access privileges are assigned to roles, which are in turn assigned to users.
The key concepts to understanding access control in Snowflake are:
- Securable object: an entity to which access can be granted. Unless allowed by a grant, access will be denied.
- Role: 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.
- Privilege: a defined level of access to an object. Multiple distinct privileges may be used to control the granularity of access granted.
- User: a user identity recognized by Snowflake, whether associated with a person or program.
In the Snowflake model, access to securable objects is allowed via privileges assigned to roles, which are in turn assigned to other roles or users. In addition, each securable object has an owner that can grant access to other roles. This model is different from a user-based access control model, in which rights and privileges are assigned to each user or group of users. The Snowflake model is designed to provide a significant amount of both control and flexibility.
Securable Objects¶
Every securable object resides within a logical container in a hierarchy of containers. The top-most container is the customer account, within which reside USER, ROLE, WAREHOUSE, and DATABASE objects. All other securable objects (such as TABLE, FUNCTION, FILE FORMAT, STAGE, SEQUENCE, etc.) are contained within a SCHEMA object within a DATABASE. This hierarchy of objects and containers is illustrated below:
Every securable object is owned by a single role, which is typically the role used to create the object. When this role is assigned to users, they effectively have shared control over the securable object. The owning role has all privileges on the object by default, including the ability to grant or revoke privileges on the object to other roles. In addition, ownership can be transferred from one role to another.
Access to objects is defined by privileges granted to roles. The following are examples of privileges on various objects in Snowflake:
- Ability to create a warehouse.
- Ability to list tables contained in a schema.
- Ability to add data to a table.
Roles¶
Roles are the entities to which privileges can be granted and revoked. Roles are assigned to users to specify which privileges are available to which users. A user can be assigned multiple roles, in which case the user’s privileges are the combination of the privileges granted to all of the roles that have been assigned to the user.
Roles can be also granted to other roles, creating a hierarchy of roles. The privileges associated with a role are inherited by any roles above that role in the hierarchy.
There are a small number of system-defined roles in a Snowflake account. Users with appropriate access can alter the system-defined roles and can also create custom roles.
System-Defined Roles¶
The following roles are provided by default:
| ACCOUNTADMIN: | AKA Account Administrator Role that encapsulates the SYSADMIN and SECURITYADMIN system-defined roles. It is the top-level role in the system and should be granted only to a limited/controlled number of users in your account. |
|---|---|
| SECURITYADMIN: | AKA Security Administrator Role that can create, monitor, and manage users and roles. More specifically, this role is used to:
|
| SYSADMIN: | AKA System Administrator Role that has privileges to create warehouses and databases (and other objects) in an account. If, as recommended, you create a role hierarchy that ultimately assigns all custom roles to the SYSADMIN role, this role also has the ability to grant privileges on warehouses, databases, and other objects to other roles. |
| PUBLIC: | Pseudo-role that is automatically granted to every user and every role in your account. The PUBLIC role can own securable objects, just like any other role; however, the objects owned by the role are, by definition, available to every other user and role in your account. This role is typically used in cases where explicit access control is not needed and all users are viewed as equal with regard to their access rights. |
Custom Roles¶
Custom roles (i.e. any roles other than the system-defined roles) can be created by the SECURITYADMIN roles as well as by any role to which the CREATE ROLE privilege has been granted. By default, the newly-created role is not assigned to any user, nor granted to any other role.
When creating roles that will serve as the owners of objects in the system, we recommend creating a hierarchy of custom roles, with the top-most custom role assigned to the system role SYSADMIN. That way, the system administrators will be able to manage all warehouses and all databases, while maintaining management of user and roles restricted to users granted the SECURITYADMIN or ACCOUNTADMIN roles. Conversely, if a custom role is not assigned to SYSADMIN through a role hierarchy, the system administrators will not be able to manage the objects owned by the role. Only those roles granted the MANAGE GRANTS privilege (typically only the SECURITYADMIN role) will see the objects and be able to modify their access grants.
Privileges¶
For each securable object, there is a set of privileges that can be granted on it. Privileges are always defined as a privilege on a specified, existing object, e.g. CREATE TABLE privilege on SCHEMA myschema object.
Privileges are managed using the GRANT and REVOKE commands. Usage of these commands is restricted to roles that own an object or role that have the MANAGE GRANTS global privilege for the object (typically the SECURITYADMIN role).
Role Hierarchy and Privilege Inheritance¶
The following diagram illustrates the hierarchy for the system-defined roles along with the recommended structure for additional, user-defined custom roles:
For a more specific example of role hierarchy and privilege inheritance, consider the following scenario:
- Role 3 has been granted to Role 2.
- Role 2 has been granted to Role 1.
- Role 1 has been granted to User 1.
In this scenario:
- Role 2 inherits Privilege C.
- Role 1 inherits Privileges B and C.
- User 1 has all three privileges.
Enforcement Model¶
Every active user session has a “current role”. When a session is initiated (e.g. a user connects via JDBC/ODBC or logs in to the Snowflake web interface), the current role is determined based on the following:
- If a role was specified as part of the connection and that role is a role that has already been granted to the connecting user, the specified role becomes the current role.
- If no role was specified and a default role has been set for the connecting user, that role becomes the current role.
- If no role was specified and a default role has not been set for the connecting user, the system role PUBLIC is used.
During the course of a session, the user can use the USE ROLE command to change the current role. If the role is granted other roles, the user’s session has privileges equal to the sum of the privileges of the current role and all the privileges of the roles granted to the current role within the role hierarchy.
When a user attempts to execute an action on an object, Snowflake compares the privileges available in the user’s session against the privileges required on the object for that action. If the session has the required privileges on the object, the action is allowed.
Note
There is no concept of a “super-user” or “super-role” in Snowflake that can bypass authorization checks. All access requires appropriate access privileges.
Privileges Reference¶
The following privileges are available in the Snowflake model. The meaning of each privilege varies depending on the object type to which it is applied, and not all objects support all privileges:
| OWNERSHIP: | Ability to delete, alter, and grant or revoke access to an object. Required to rename an object. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege). |
|---|---|
| USAGE: | Ability to execute a USE command on an object or showing the objects within a container such as DATABASE or SCHEMA; however, a contained object is only listed if the executing role has at least one privilege on the object. |
| MANAGE GRANTS: | Ability to grant or revoke privileges on any object as if the invoking role were the owner of the object. |
| MONITOR: | Ability to see details within an object (e.g. queries and usage within a warehouse). |
| OPERATE: | Limited to virtual warehouses, provides the ability to start, stop, hibernate, or resume a virtual warehouse. |
| CREATE {x}: | Ability to create a new object of type {x} (e.g. a new table within a schema). |
| MODIFY: | Ability to change the settings or properties of an object (e.g. on a virtual warehouse, provides the ability to change the size of a virtual warehouse). |
| INSERT: | Ability to execute an INSERT command on a table. |
| UPDATE: | Ability to execute an UPDATE command on a table. |
| DELETE: | Ability to execute a DELETE command on a table. |
| TRUNCATE: | Ability to execute a TRUNCATE command on a table. |
| SELECT: | Ability to execute a SELECT statement on a table or view. |
The following sections describe the specific privileges available for each type of object and their usage.
Global Privileges¶
| Privilege | Usage |
|---|---|
| CREATE USER | Enables creating a new user. |
| CREATE ROLE | Enables creating a new role. |
| MANAGE GRANTS | Enables modifying grants on objects for which the role is not the owner. |
| CREATE WAREHOUSE | Enables creating a new virtual warehouse. |
| CREATE DATABASE | Enables creating a new database or create a clone of an existing database. |
| ALL [ PRIVILEGES ] | Grants all global privileges. |
User and Role Privileges¶
| Privilege | Usage |
|---|---|
| OWNERSHIP | Grant full control over a user/role. |
Virtual Warehouse Privileges¶
| Privilege | Usage |
|---|---|
| MODIFY | Enables altering any properties of a warehouse, including changing its size. |
| MONITOR | Enables viewing current and past queries executed on a warehouse as well as usage statistics on that warehouse. |
| OPERATE | Enables changing the state of a warehouse (stop, start, hibernate, resume) as well as abort any executing queries. |
| USAGE | Enables using a virtual warehouse and, as a result, execute queries on the warehouse. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on the warehouse. |
| OWNERSHIP | Transfers ownership of a warehouse, which gives full control over the warehouse. |
Database Privileges¶
| Privilege | Usage |
|---|---|
| MODIFY | Enables altering any settings of a database. |
| MONITOR | Enables viewing details for a database. |
| USAGE | Enables using a database, including showing objects (i.e. schemas) in the database. |
| CREATE SCHEMA | Enables creating a new schema in a database, including cloning a schema. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on a database. |
| OWNERSHIP | Transfers ownership of a database, which grants full control over the database. |
Note that changing the properties of a database, including comments, requires the OWNERSHIP privilege for the database.
Schema Privileges¶
| Privilege | Usage |
|---|---|
| MODIFY | Enables altering any settings of a schema. |
| MONITOR | Enables viewing details for a schema. |
| USAGE | Enables using a schema, including showing objects (e.g. TABLES, VIEWS, FUNCTIONS, SEQUENCES, STAGES, or FILE FORMATS) in the schema. |
| CREATE TABLE | Enables creating a new table in a schema, including cloning a table. |
| CREATE VIEW | Enables creating a new view in a schema. |
| CREATE STAGE | Enables creating a new stage in a schema, including cloning a stage. |
| CREATE FILE FORMAT | Enables creating a new file format in a schema, including cloning a file format. |
| CREATE SEQUENCE | Enables creating a new sequence in a schema, including cloning a sequence. |
| CREATE FUNCTION | Enables creating a new UDF in a schema. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on a schema. |
| OWNERSHIP | Transfers ownership of a schema, which grants full control over the schema. |
Note
- Changing the properties of a schema, including comments, requires the OWNERSHIP privilege for the database.
- Operating on a schema also requires the USAGE privilege on the parent database.
Table Privileges¶
| Privilege | Usage |
|---|---|
| SELECT | Enables executing a SELECT statement on a table. |
| INSERT | Enables executing an INSERT command on a table. |
| UPDATE | Enables executing an UPDATE command on a table. |
| TRUNCATE | Enables executing a TRUNCATE command on a table. |
| DELETE | Enables executing a DELETE command on a table. |
| REFERENCES | Enables referencing a table as the unique/primary key table for a foreign key constraint. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on a table. |
| OWNERSHIP | Transfers ownership of a table, which grants full control over the table. Required to alter a table. |
Note
Operating on a table also requires the USAGE privilege on the parent database and schema.
View Privileges¶
| Privilege | Usage |
|---|---|
| SELECT | Enables executing a SELECT statement on a view. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on a view. |
| OWNERSHIP | Transfers ownership of a view, which grants full control over the view. Required to alter a view. |
Note
Operating on a view also requires the USAGE privilege on the parent database and schema.
Stage Privileges¶
| Privilege | Usage |
|---|---|
| USAGE | Enables using an external stage object in a SQL statement; not applicable to internal stages. |
| READ | Enables performing any operations that require reading from an internal stage (GET, LIST, COPY INTO table, etc.); not applicable to external stages. |
| WRITE | Enables performing any operations that require writing to an internal stage (PUT, REMOVE, COPY INTO location, etc.); not applicable for external stages. |
| ALL [ PRIVILEGES ] | Grants all applicable privileges, except OWNERSHIP, to a stage (internal or external). |
| OWNERSHIP | Transfers ownership of a stage, which grants full control over the stage. |
Note
- When granting or revoking both the READ and WRITE privileges for an internal stage:
- The READ privilege must be granted before the WRITE privilege or at the same time.
- The WRITE privilege must be revoked before the READ privilege or at the same time.
- Operating on a stage also requires the USAGE privilege on the parent database and schema.
File Format Privileges¶
| Privilege | Usage |
|---|---|
| USAGE | Enables utilizing a file format in a SQL statement. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on the file format. |
| OWNERSHIP | Transfers ownership of a file format, which grants full control over the file format. Required to alter a file format. |
Note
Operating on file formats also requires the USAGE privilege on the parent database and schema.
Other Database Object Privileges (Sequences, UDFs)¶
| Privilege | Usage |
|---|---|
| USAGE | Enables utilizing the specified object in a SQL statement. |
| ALL [ PRIVILEGES ] | Grants all privileges, except OWNERSHIP, on the specified object. |
| OWNERSHIP | Transfers ownership of the specified object, which grants full control over the object. Required to alter the object. |
Note
Operating on these other database objects also requires the USAGE privilege on the parent database and schema.