Overview of Access Control¶
Access control privileges determine who can access and perform operations on specific objects in Snowflake.
In this Topic:
Access Control Framework¶
Snowflake’s approach to access control combines aspects from both of the following models:
Discretionary Access Control (DAC): Each object has an owner, who can in turn grant access to that object.
Role-based Access Control (RBAC): 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.
Every securable object resides within a logical container in a hierarchy of containers. The top-most container is the customer account. 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 are the entities to which privileges on securable objects can be granted and revoked. Roles are assigned to users to allow them to perform actions required for business functions in their organization. A user can be assigned multiple roles. This allows users to switch roles (i.e. choose which role is active in the current Snowflake session) to perform different actions using separate sets of privileges.
Roles can be also granted to other roles, creating a hierarchy of roles. Users with appropriate access can create custom 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. System-defined roles cannot be dropped. In addition, the privileges granted to these roles by Snowflake cannot be revoked.
Additional privileges can be granted to the system-defined roles, but is not recommended. System-defined roles are created with privileges related to account-management. As a best practice, it is not recommended to mix account-management privileges and entity-specific privileges in the same role. If additional privileges are needed, we recommend granting the additional privileges to a custom role and assigning the custom role to the system-defined role.
(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.
(aka Security Administrator)
Role that can manage any object grant globally, as well as create, monitor, and manage users and roles. More specifically, this role:
Is granted the MANAGE GRANTS security privilege to be able to modify any grant, including revoking it.
Inherits the privileges of the USERADMIN role via the system role hierarchy (e.g. USERADMIN role is granted to SECURITYADMIN).
(aka User and Role Administrator)
Role that is dedicated to user and role management only. More specifically, this role:
Is granted the CREATE USER and CREATE ROLE security privileges.
Can create users and roles in the account.
This role can also manage users and roles that it owns. Only the role with the OWNERSHIP privilege on an object (i.e. user or role), or a higher role, can modify the object properties. In addition, the role must have the global CREATE USER or CREATE ROLE privilege, respectively, to modify users or roles it owns.
(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.
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 (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. This role structure allows system administrators to manage all objects in the account, such as warehouses and database objects, while restricting management of users and roles to 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.
For each securable object, there is a set of privileges that can be granted on it. For existing objects, privileges must be granted on individual objects, e.g. the SELECT privilege on the
mytable table. To simplify grant management, future grants allow defining an initial set of privileges on objects created in a schema; i.e. the SELECT privilege on all new tables created in the
Privileges are managed using the GRANT and REVOKE commands.
In regular (i.e. non-managed) schemas, use of these commands is restricted to the role that owns an object (i.e. has the OWNERSHIP privilege on the object) or roles that have the MANAGE GRANTS global privilege for the object (typically the SECURITYADMIN role).
In managed access schemas, object owners lose the ability to make grant decisions. Only the schema owner or a role with the MANAGE GRANTS privilege can grant privileges on objects in the schema, including future grants, centralizing privilege management.
For more details, see Access Control Privileges.
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: The Primary Role and Secondary Roles¶
Every active user session has a “current role,” also referred to as a primary 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 criteria:
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.
In addition, a set of secondary roles can be activated in a user session. A user can perform SQL actions on objects in a session using the aggregate privileges granted to the primary and secondary roles. The roles must be granted to the user before they can be activated in a session. Note that while a session must have exactly one active primary role at a time, one can activate any number of secondary roles at the same time.
Authorization to execute CREATE <object> statements comes from the primary role only. When an object is created, its ownership is set to the currently active primary role. However, for any other SQL action, any permission granted to any active primary or secondary role can be used to authorize the action. For example, if any role in a secondary role hierarchy owns an object (i.e. has the OWNERSHIP privilege on the object), the secondary roles would authorize performing any DDL actions on the object. Both the primary role as well as all secondary roles inherit privileges from any roles lower in their role hierarchies.
For organizations whose security model includes a large number of roles, each with a fine granularity of authorization via permissions, the use of secondary roles simplifies role management. All roles that were granted to a user can be activated in a session. Secondary roles are particularly useful for SQL operations such as cross-database joins that would otherwise require creating a parent role of the roles that have permissions to access the objects in each database.
When a user attempts to create an object, Snowflake compares the privileges available to the current role in the user’s session against the privileges required to create the object. For any other SQL actions attempted by the user, Snowflake compares the privileges available to the current primary and secondary roles against the privileges required to execute the action on the target objects. If the session has the required privileges on the objects, the action is allowed.
There is no concept of a “super-user” or “super-role” in Snowflake that can bypass authorization checks. All access requires appropriate access privileges.