Access Control Considerations¶
This topic provides best practices and important considerations for managing secure access to your Snowflake account and data stored within the account. In particular, it provides general guidance for configuring role-based access control, which limits access to objects based on a user’s role.
In this Topic:
- Using the ACCOUNTADMIN Role
- Aligning Object Access with Business Functions
- Managing Custom Roles
- Accessing Database Objects
- Viewing Query Results
- Understanding Cloned Objects and Granted Privileges
Using the ACCOUNTADMIN Role¶
The account administrator (ACCOUNTADMIN) role is the most powerful role in the system. This role alone is responsible for configuring parameters at the account level. Users with the ACCOUNTADMIN role can view and operate on all objects in the account, can view and manage Snowflake billing and credit data, and can stop any running SQL statements.
In the default access control hierarchy, both of the other administrator roles are owned by this role:
- The security administrator (SECURITYADMIN) role includes the privileges to create and manage users and roles.
- The system administrator (SYSADMIN) role includes the privileges to create warehouses, databases, and all database objects (schemas, tables, etc.).
By default, when your account is provisioned, the first user is assigned the ACCOUNTADMIN role. This user should then create one or more additional users who are assigned the SECURITYADMIN role. All remaining users should be created by the user(s) with the SECURITYADMIN role.
Control the Assignment of the ACCOUNTADMIN Role to Users¶
We strongly recommend the following precautions when assigning the ACCOUNTADMIN role to users:
- Assign this role only to a select/limited number of people in your organization.
- All users assigned the ACCOUNTADMIN role should also be required to use multi-factor authentication (MFA) for login (for details, see Configuring Access Control).
- Assign this role to at least two users. We follow strict security procedures for reseting a forgotten or lost password for users with the ACCOUNTADMIN role. These procedures can take up to two business days. Assigning the ACCOUNTADMIN role to more than one user avoids having to go through these procedures because the users can reset each other’s passwords.
It also helps if you associate an actual person’s email address to ACCOUNTADMIN users, so that Snowflake Support knows who to contact in an urgent situation.
Avoid Using the ACCOUNTADMIN Role to Create Objects¶
The ACCOUNTADMIN role is intended for performing initial setup tasks in the system and managing account-level objects and tasks on a day-to-day basis. As such, it should not be used to create objects in your account, unless you absolutely need these objects to have the highest level of secure access. If you create objects with the ACCOUNTADMIN role and you want users to have access to these objects, you must explicitly grant privileges on the objects to the roles for these users.
Instead, we recommend using the SYSADMIN role to create objects or creating a hierarchy of custom roles under the SYSADMIN role and using these roles to create objects.
To help prevent account administrators from inadvertently using the ACCOUNTADMIN role to create objects, assign them additional roles and designate one of these roles as their default (i.e. do not make ACCOUNTADMIN the default role for any users in the system).
This doesn’t prevent them from using the ACCOUNTADMIN role to create objects, but it forces them to explicitly change their role to ACCOUNTADMIN each time they log in. This can help make them aware of the purpose/function of roles in the system and encourage them to change to the appropriate role for performing a given task, particularly when they need to perform account administrator tasks.
Avoid Using the ACCOUNTADMIN Role for Automated Scripts¶
It’s best to use a role other than ACCOUNTADMIN for automated scripts. If, as recommended, you create a role hierarchy under the SYSADMIN role, all warehouse and database object operations can be performed using the SYSADMIN role or lower roles in the hierarchy. The only limitations you would encounter is creating or modifying users or roles. These operations must be performed by a user with the SECURITYADMIN or ACCOUNTADMIN role or another role with sufficient object privileges.
Aligning Object Access with Business Functions¶
Consider taking advantage of role hierarchy and privilege inheritance to align access to database objects with business functions in your organization. In a role hierarchy, roles are granted to other roles to form an inheritance relationship. Privileges granted to roles at a lower level are inherited by roles at a higher level.
As a simple example, suppose two databases,
d2, contain data required by business analysts in your organization. Based on their functional responsibilities, entry-level analysts
should have read-only access to
d1, but access to
d2 should be limited to advanced analysts. A recommended approach to configuring security on these databases would involve creating a
combination of object access roles and business function roles for optimal control.
There is no technical difference between an object access role and a business function role in Snowflake. The difference is in how they are used logically to assemble and assign sets of privileges to groups of users.
To configure access in this example:
As a security administrator (user with the SECURITYADMIN role) or higher, create roles
analyst_adv. These roles correspond to the business functions of your organization and serve as a catch-all for any object access roles required for these functions. Because basic analyst functions are also required by advanced analysts, grant the
analyst_basicrole to the
Following best practices for role hierarchies, grant
analyst_advto the system administrator (SYSADMIN) role. System administrators can then grant privileges on database objects to any roles in this hierarchy.
CREATE ROLE analyst_basic; CREATE ROLE analyst_adv; GRANT ROLE analyst_basic TO ROLE analyst_adv; GRANT ROLE analyst_adv TO ROLE sysadmin;
As a security administrator or higher, create object access roles
db2_read_onlyand grant these roles to the business function roles that require them. In this case, grant the
analyst_basicrole, and grant the
db2_read_onlyrole to the
CREATE ROLE db1_read_only; CREATE ROLE db2_read_only; GRANT ROLE db1_read_only TO ROLE analyst_basic; GRANT ROLE db2_read_only TO ROLE analyst_adv;
As a system administrator (user with the SYSTEMADMIN role) or higher, grant
db2_read_onlyread-only access to databases
d2, respectively. For more information, see Creating Read-Only Roles. These roles define a set of grants to access data objects.
GRANT <privileges> TO ROLE db1_read_only; GRANT <privileges> TO ROLE db2_read_only;
As a security administrator (user with the SECURITYADMIN role) or higher, grant the business function roles to the users who perform those functions:
GRANT ROLE analyst_basic TO USER user1; GRANT ROLE analyst_adv TO USER user2;
Privileges granted to the lower-level (in the role hierarchy) object access roles
db2_read_only are inherited by the higher-level business function roles
analyst_adv roles, respectively. Also, because
analyst_basic is granted to
analyst_adv, any privileges granted to
analyst_basic are inherited by
Users granted the
analyst_adv role can access both
db2; however, users granted the
analyst_basic role can only access
Managing Custom Roles¶
When a custom role is first created, it exists in isolation. The role must be assigned to any users who will use the object privileges associated with the role. The custom role must also be granted to any roles that will manage the objects created by the custom role.
By default, not even the ACCOUNTADMIN role can modify or drop objects created by a custom role. The custom role must be granted to the ACCOUNTADMIN role directly or, preferably, to another role in a hierarchy with the SYSADMIN role as the parent. The SYSADMIN role is managed by the ACCOUNTADMIN role.
For instructions to create a role hierarchy, see Creating a Role Hierarchy.
Accessing Database Objects¶
All securable database objects (such as TABLE, FUNCTION, FILE FORMAT, STAGE, SEQUENCE, etc.) are contained within a SCHEMA object within a DATABASE. The hierarchy of objects and containers is illustrated below:
To access database objects, in addition to the privileges on the specific database objects, users must be granted the USAGE privilege on the container database and schema.
For example, suppose
mytable is stored in
mydatabase.myschema. In order to query
mytable, a user must have the following privileges at a minimum:
Viewing Query Results¶
A user cannot view the result set from a query that another user executed. This behavior is intentional. For security reasons, only the user who executed a query can access the query results.
This behavior is not connected to the Snowflake access control model for objects. Even a user with the ACCOUNTADMIN role cannot view the results for a query run by another user.
Understanding Cloned Objects and Granted Privileges¶
Cloning a database, schema or table creates a copy of the source object. The cloned object includes a snapshot of data present in the source object when the clone was created.
A cloned object is considered a new object in Snowflake. Any privileges granted on the source object do not transfer to the cloned object. However, a cloned container object (a database or schema) retains any privileges granted on the objects contained in the source object. For example, a cloned schema retains any privileges granted on the tables, views, UDFs, and other objects in the source schema.