Access control considerations¶

Using the ACCOUNTADMIN Role¶

The account administrator (i.e users with the ACCOUNTADMIN system role) 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 manage Snowflake billing and credit data, and can stop any running SQL statements.

Note that ACCOUNTADMIN is not a superuser role. This role only allows viewing and managing objects in the account if this role, or a role lower in a role hierarchy, has sufficient privileges on the objects.

In the system role hierarchy, the other administrator roles are children of this role:

• The user administrator (USERADMIN) role includes the privileges to create and manage users and roles (assuming ownership of those roles or users has not been transferred to another role).

• The security administrator (i.e users with the SECURITYADMIN system role) role includes the global MANAGE GRANTS privilege to grant or revoke privileges on objects in the account. The USERADMIN role is a child of this role in the default access control hierarchy. For more information about the children system-defined roles, see System-defined roles.

• The system administrator (SYSADMIN) role includes the privileges to create warehouses, databases, and all database objects (schemas, tables, etc.).

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. As a result, 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 created in mydb.myschema. In order to query mytable, a user must have the following privileges at a minimum:

Database:

USAGE on mydb

Schema:

USAGE on myschema

Table:

SELECT on mytable

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.

For instructions to create a role hierarchy, see Creating a role hierarchy.

Aligning object access with business functions¶

Consider taking advantage of role hierarchies 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. Permissions granted to roles at a lower level are inherited by roles at a higher level.

For optimal flexibility in controlling access to database objects, create a combination of object access roles with different permissions on objects and assign them as appropriate to functional roles:

• Grant permissions on database objects or account objects (such as warehouses) to access roles.

• Grant access roles to functional roles to create a role hierarchy. These roles correspond to the business functions of your organization and serve as a catch-all for any access roles required for these functions.

  When appropriate, grant lower-level functional roles to higher-level functional roles in a parent-child relationship where the parent roles map to business functions that should subsume the permissions of the child roles.

  Following best practices for role hierarchies, grant the highest-level functional roles in a role hierarchy to the system administrator (SYSADMIN) role. System administrators can then grant privileges on database objects to any roles in this hierarchy:

Example¶

As a simple example, suppose two databases in an account, fin and hr, contain payroll and employee data, respectively. Accountants and analysts in your organization require different permissions on the objects in these databases to perform their business functions. Accountants should have read-write access to fin but might only require read-only access to hr because human resources personnel maintain the data in this database. Analysts could require read-only access to both databases.

Permissions on existing database objects are granted via the following hierarchy of access roles and functional roles:

Note

When new objects are added in each database, consider automatically granting privileges on the objects to roles based on object type (e.g. schemas, tables, or views). For information, see Simplifying Grant Management Using Future Grants (in this topic).

Custom Role	Description	Privileges
db_hr_r	Access role that permits read-only access to tables in the hr database.	USAGE on database hr. USAGE on all schemas in database hr. SELECT on all tables in database hr.
db_fin_r	Access role that permits read-only access to tables in the fin database.	USAGE on database fin. USAGE on all schemas in database fin. SELECT on all tables in database fin.
db_fin_rw	Access role that permits read-write access to tables in the fin database.	USAGE on database fin. USAGE on all schemas in database fin. SELECT, INSERT, UPDATE, DELETE on all tables in database fin.
accountant	Functional role for accountants in your organization.	N/A
analyst	Functional role for analysts in your organization.	N/A

The following diagram shows the role hierarchy for this example:

To configure access control for this example:

1. As a user administrator (user with the USERADMIN role) or another role with the CREATE ROLE privilege on the account, create the access roles and functional roles in this example:

   CREATE ROLE db_hr_r;
   CREATE ROLE db_fin_r;
   CREATE ROLE db_fin_rw;
   CREATE ROLE accountant;
   CREATE ROLE analyst;
   

   Copy

2. As a security administrator (user with the SECURITYADMIN role) or another role with the MANAGE GRANTS privilege on the account, grant the required minimum permissions to each of the access roles:

   -- Grant read-only permissions on database HR to db_hr_r role.
   GRANT USAGE ON DATABASE hr TO ROLE db_hr_r;
   GRANT USAGE ON ALL SCHEMAS IN DATABASE hr TO ROLE db_hr_r;
   GRANT SELECT ON ALL TABLES IN DATABASE hr TO ROLE db_hr_r;
   
   -- Grant read-only permissions on database FIN to db_fin_r role.
   GRANT USAGE ON DATABASE fin TO ROLE db_fin_r;
   GRANT USAGE ON ALL SCHEMAS IN DATABASE fin TO ROLE db_fin_r;
   GRANT SELECT ON ALL TABLES IN DATABASE fin TO ROLE db_fin_r;
   
   -- Grant read-write permissions on database FIN to db_fin_rw role.
   GRANT USAGE ON DATABASE fin TO ROLE db_fin_rw;
   GRANT USAGE ON ALL SCHEMAS IN DATABASE fin TO ROLE db_fin_rw;
   GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN DATABASE fin TO ROLE db_fin_rw;
   

   Copy

3. As a security administrator (user with the SECURITYADMIN role) or another role with the MANAGE GRANTS privilege on the account, grant the db_fin_rw access role to the accountant functional role, and grant the db_hr_r db_fin_r access roles to the analyst functional role:

   GRANT ROLE db_fin_rw TO ROLE accountant;
   GRANT ROLE db_hr_r TO ROLE analyst;
   GRANT ROLE db_fin_r TO ROLE analyst;
   

   Copy

4. As a security administrator (user with the SECURITYADMIN role) or another role with the MANAGE GRANTS privilege on the account, grant both the analyst and accountant roles to the system administrator (SYSADMIN) role:

   GRANT ROLE accountant,analyst TO ROLE sysadmin;
   

   Copy

5. As a security administrator (user with the SECURITYADMIN role) or another role with the MANAGE GRANTS privilege on the account, grant the business functional roles to the users who perform those business functions in your organization. In this example, the analyst functional role is granted to user user1, and the accountant functional role is granted to user user2.

   GRANT ROLE accountant TO USER user1;
   GRANT ROLE analyst TO USER user2;
   

   Copy

Managing database object access using database roles¶

Database roles are essentially the same as traditional roles created at the account level (i.e. custom account roles) except for their scope: To permit SQL actions on objects within a database, privileges can be granted to a database role in the same database.

Database roles are intended to satisfy the following use cases:

Ease of management:

Database owners can independently manage access to securable objects within their own databases. Database owners can perform the following actions:

• Create and manage database roles.

• Grant privileges to database roles.

  Privileges on objects granted to the database roles must be scoped to objects contained in the database where the role exists. Privileges on objects in one database (e.g. tables or views) cannot be granted to database roles in another database.

  Any privilege, including OWNERSHIP, can be granted to database roles on objects in a database. Note that only an account role can hold the OWNERSHIP privilege on the database itself.

• Create or extend role hierarchies. Grant database roles to other database roles within the same database, and then grant the highest-level database roles in a database to account roles. For more information, see Role hierarchy and privilege inheritance.

  Note that granting a database role to an account role implicitly grants the USAGE privilege on the database that contains the database role to that account role. Granting the USAGE privilege on the database explicitly is not required.

Data Sharing:

Data providers in Snowflake’s Secure Data Sharing can segment the securable objects in a share by creating multiple database roles in a database to share and granting privileges on a subset of the objects in the database to each database role. After creating a database from a share that includes database roles, data consumers grant each shared database role to one or more account-level roles in their own account.

Without database roles, account administrators in data consumer accounts grant a single privilege, IMPORTED PRIVILEGES, to roles to allow their users to access all databases and database objects (tables, secure views, etc.) in a share. There is no option to allow different groups of users in a data consumer account to access a subset of the shared objects. This all or nothing approach requires data providers to create multiple shares to grant access to different objects in the same databases.

Note that database roles cannot be activated directly in a session. Grant database roles to account roles, which can be activated in a session.

Centralizing Grant management using managed access schemas¶

With regular (i.e. non-managed) schemas in a database, object owners (i.e. roles with the OWNERSHIP privilege on one or more objects) can grant access on those objects to other roles, with the option to further grant those roles the ability to manage object grants.

To further lock down object security, consider using managed access schemas. 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, 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 information on managed access schemas, see Creating managed access schemas.

Simplifying grant management using future grants¶

Future grants allow defining an initial set of privileges on objects of a certain type (e.g. tables or views) in a specified schema. As new objects are created, the defined privileges are automatically granted to a role, simplifying grant management.

Consider the following scenario, in which a particular role is granted the SELECT privilege on all new tables created in schema. At a later date, the decision is made to revoke the privilege from this role and instead grant it to a different role. Using the ON FUTURE keywords for new tables and the ALL keyword for existing tables, few SQL statements are required to grant and revoke privileges on new and existing tables. For example:

-- Grant the SELECT privilege on all new (i.e. future) tables in a schema to role R1
GRANT SELECT ON FUTURE TABLES IN SCHEMA s1 TO ROLE r1;

-- / Create tables in the schema /

-- Grant the SELECT privilege on all new tables in a schema to role R2
GRANT SELECT ON FUTURE TABLES IN SCHEMA s1 TO ROLE r2;

-- Grant the SELECT privilege on all existing tables in a schema to role R2
GRANT SELECT ON ALL TABLES IN SCHEMA s1 TO ROLE r2;

-- Revoke the SELECT privilege on all new tables in a schema (i.e. future grant) from role R1
REVOKE SELECT ON FUTURE TABLES IN SCHEMA s1 FROM ROLE r1;

-- Revoke the SELECT privilege on all existing tables in a schema from role R1
REVOKE SELECT ON ALL TABLES IN SCHEMA s1 FROM ROLE r1;

For more information on future grants, see Assigning future grants on objects.

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.

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.

For more details about cloning, see Cloning considerations and CREATE <object> … CLONE.