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 is 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 organization. Securable objects such as tables, views, functions, and stages are contained in a schema object, which are in turn contained in a database. All databases for your Snowflake account are contained in the account object. This hierarchy of objects and containers is illustrated below:
To own an object means that a role has the OWNERSHIP privilege on the object. Each securable object is owned by a single role, which by default is the role used to create the object. When this role is assigned to users, they effectively have shared control over the object. In a regular schema, the owner 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. However, in a managed access schema, object owners lose the ability to make grant decisions. Only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) or a role with the MANAGE GRANTS privilege can grant privileges on objects in the schema.
The ability to perform SQL actions on objects is defined by the privileges granted to the active role in a user session. The following are examples of SQL actions available 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.
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.
Users who have been granted a role with the necessary privileges can create custom roles to meet specific business and security needs.
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. For more information about role hierarchies and privilege inheritance, see Role Hierarchy and Privilege Inheritance (in this topic).
A role owner (i.e. the role that has the OWNERSHIP privilege on the role) does not inherit the privileges of the owned role. Privilege inheritance is only possible within a role hierarchy.
Although additional privileges can be granted to the system-defined roles, it 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, Snowflake recommends granting the additional privileges to a custom role and assigning the custom role to the system-defined role.
Types of Roles¶
The following role types are essentially the same, except for their scope. Both types enable administrators to authorize and restrict access to objects in your account.
Except where noted in the product documentation, the term role refers to either type.
- Account roles
To permit SQL actions on any object in your account, grant privileges on the object to an account role.
- Database Roles
To limit SQL actions to a single database, as well as any object in the database, grant privileges on the object to a database role in the same database.
Note that database roles cannot be activated directly in a session. Grant database roles to account roles, which can be activated in a session.
For more information about database roles, refer to:
Role Hierarchy and Privilege Inheritance (in this topic)
Database Roles and Role Hierarchies (in this topic)
Database roles in the shared SNOWFLAKE database.
Active roles serve as the source of authorization for any action taken by a user in a session. Both the primary role and any secondary roles can be activated in a user session.
A role becomes an active role in either of the following ways:
When a session is first established, the user’s default role and default secondary roles are activated as the session primary and secondary roles, respectively.
Note that client connection properties used to establish the session could explicitly override the primary role or secondary roles to use.
Executing a USE ROLE or USE SECONDARY ROLES statement activates a different primary role or secondary roles, respectively. These roles can change over the course of a session if either command is executed again.
(aka Organization Administrator)
Role that manages operations at the organization level. More specifically, this 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 (i.e. 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.
(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 account roles can be created using the USERADMIN role (or a higher role) as well as by any role to which the CREATE ROLE privilege has been granted.
Custom database roles can be created by the database owner (i.e. the role that has the OWNERSHIP privilege on the database).
By default, a 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 securable objects in the system, Snowflake recommends 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 USERADMIN role.
Conversely, if a custom role is not assigned to SYSADMIN through a role hierarchy, the system administrators cannot manage the objects owned by the role. Only those roles granted the MANAGE GRANTS privilege (only the SECURITYADMIN role by default) can view the objects and modify their access grants.
For instructions to create custom roles, see Creating Custom Roles.
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. grant the SELECT privilege on all new tables created in the
myschema schema to a specified role).
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 any roles that have the MANAGE GRANTS global privilege for the object (only the SECURITYADMIN role by default).
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.
Note that a role that holds the global MANAGE GRANTS privilege can grant additional privileges to the current (grantor) role.
For more details, see Access Control Privileges.
Role Hierarchy and Privilege Inheritance¶
The following diagram illustrates the hierarchy for the system-defined roles, as well as the recommended structure for additional, user-defined account roles and database roles. The highest-level database role in the example hierarchy is granted to a custom (i.e. user-defined) account role. In turn, this role is granted to another custom role in a recommended structure that allows the system-defined SYSADMIN role to inherit the privileges of custom account roles and database roles:
ORGADMIN is a separate system role that manages operations at the organization level. This role is not included in the hierarchy of system 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.
Database Roles and Role Hierarchies¶
The following limitations currently apply to database roles:
If a database role is granted to a share, then no other database roles can be granted to that database role. For example, if database role
d1.r1is granted to a share, then attempting to grant database role
In addition, if a database role is granted to another database role, the grantee database role cannot be granted to a share.
Database roles that are granted to a share can be granted to other database roles, as well as account roles.
Account roles cannot be granted to database roles in a role hierarchy.
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.
A database role can neither be a primary nor a secondary role. To assume the privileges granted to a database role, grant the database role to an account role. Only account roles can be activated in a session.
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.
During the course of a session, the user can use the USE ROLE or USE SECONDARY ROLES command to change the current primary or secondary roles, respectively. The user can use the CURRENT_SECONDARY_ROLES function to show all active secondary roles for the current session.
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.