Configuring Access Control¶
This topic describes how to configure access control security for securable objects in your account.
Account Administration¶
Designating Additional Users as Account Administrators¶
By default, each account has one user who has been designated as an account administrator (i.e. user granted the system-defined ACCOUNTADMIN role). We recommend designating at least one other user as an account administrator. This helps ensure that your account always has at least one user who can perform account-level tasks, particularly if one of your account administrators is unable to log in.
For these additional account administrators, you can choose to create new users or designate existing users, but make sure to specify the following:
Grant the ACCOUNTADMIN role to the user(s), but do not set this role as their default. Instead, designate a lower-level administrative role (e.g. SYSADMIN) or custom role as their default. This helps prevent account administrators from inadvertently using the ACCOUNTADMIN role to create objects.
Ensure an email address is specified for each user (required for multi-factor authentication).
For example, grant the ACCOUNTADMIN and SYSADMIN roles to an existing user named user2
and specify SYSADMIN as the default role:
GRANT ROLE ACCOUNTADMIN, SYSADMIN TO USER user2; ALTER USER user2 SET EMAIL='user2@domain.com', DEFAULT_ROLE=SYSADMIN;
Enabling MFA for Each Account Administrator¶
To ensure the highest level of security for your Snowflake account, we strongly recommend that any user who can modify or view sensitive data be required to use multi-factor authentication (MFA) for login.
This recommendation applies particularly to users with the ACCOUNTADMIN role, but can also be expanded to include users with the SECURITYADMIN and SYSADMIN roles.
For more details, see Access Control Considerations and Multi-factor authentication (MFA).
Creating Custom Roles¶
To follow the general principle of “least privilege”, we recommend creating custom roles that align with the business functions in your organization to permit SQL actions on a narrow set of securable objects.
The workflow is as follows:
Create a custom role.
Grant a set of privileges to the role.
Grant the role to one or more users who require the privileges granted to the role to perform SQL actions for their business needs.
Grant the role to another role to create or add to a role hierarchy. While not required, this step is highly recommended. For more information, see Creating a Role Hierarchy (in this topic).
This section provides instructions for creating a role named r1
and granting the following privileges to the role. The privileges allow
a user who activates the role in a session to query a single table, d1.s1.t1
:
Privilege |
Object |
Notes |
---|---|---|
USAGE |
Warehouse Database Schema |
To query an object (e.g. a table or view), a role must have the USAGE privilege on a warehouse. The warehouse provides the compute resources to execute the query. To operate on any object in a schema, a role must have the USAGE privilege on the container database and schema. |
SELECT |
Table |
After a role is created, additional privileges can be granted to it to allow users with the role to perform additional SQL actions on the same or additional objects.
Create a Role¶
Create the
r1
role, using CREATE ROLE.Only user administrators (i.e. users with the USERADMIN system role or higher), or another role with the CREATE ROLE privilege on the account, can create roles.
CREATE ROLE r1 COMMENT = 'This role has all privileges on schema_1';
Grant Privileges to the Role¶
Grant the
r1
role the privileges defined in the table earlier in this section.The SECURITYADMIN system role can be used to grant privileges on objects to roles. For additional options, see GRANT <privileges>.
GRANT USAGE ON WAREHOUSE w1 TO ROLE r1; GRANT USAGE ON DATABASE d1 TO ROLE r1; GRANT USAGE ON SCHEMA d1.s1 TO ROLE r1; GRANT SELECT ON TABLE d1.s1.t1 TO ROLE r1;
Grant the Role to Users¶
Assign role
r1
role to usersmith
.The SECURITYADMIN role can be used to grant roles to users. For additional options, see GRANT ROLE.
GRANT ROLE r1 TO USER smith;
Optionally set the new custom role as the default role for the user. The next time the user logs into Snowflake, the default role is automatically active in the session.
Only the role with the OWNERSHIP privilege on the user, or a higher role, can execute this command.
The following command sets the default role for user
smith
:ALTER USER smith SET DEFAULT_ROLE = r1;
Creating Custom Read-Only Roles¶
Suppose you need a role that is limited to querying all tables in a specific schema (e.g. d1.s1
). Users who execute
commands using this role cannot update the table data, create additional database objects, or drop tables. The role is limited to querying
table data.
To create a read-only role, complete the basic steps described in Creating Custom Roles (in this topic). In the
Grant Privileges to the Role section, grant the read-only role (named read_only
in these instructions) the following object
privileges:
Privilege |
Object |
Notes |
---|---|---|
USAGE |
Warehouse |
To query an object (e.g. a table or view), a role must have the USAGE privilege on a warehouse. The warehouse provides the compute resources to execute the query. |
SELECT |
Table |
To operate on any object in a schema, a role must have the USAGE privilege on the container database and schema. |
The GRANT <privilege> statements are as follows:
GRANT USAGE
ON DATABASE d1
TO ROLE read_only;
GRANT USAGE
ON SCHEMA d1.s1
TO ROLE read_only;
GRANT SELECT
ON ALL TABLES IN SCHEMA d1.s1
TO ROLE read_only;
GRANT USAGE
ON WAREHOUSE w1
TO ROLE read_only;
Note
The GRANT SELECT ON ALL TABLES IN SCHEMA <schema>
statement grants the SELECT privilege on all existing tables only. To
grant the SELECT privilege on all future tables to the role, execute the following
statement:
GRANT SELECT ON FUTURE TABLES IN SCHEMA d1.s1 TO ROLE read_only;
Creating a Role Hierarchy¶
When creating custom roles, consider creating a role hierarchy ultimately assigned to a high-level administrator role. In general, the SYSADMIN role works well as the role all other roles are assigned to in a hierarchy, although it is important to note that any role with sufficient privileges could serve this function. The SYSADMIN role is a system-defined role that has privileges to create warehouses, databases, and database objects in an account and grant those privileges to other roles. In the default system hierarchy, the top-level ACCOUNTADMIN role manages the system administrator role.
Create a role hierarchy by granting a role to a second role. You can then grant that second role to a third role. The privileges associated with a role are inherited by any roles above that role in the hierarchy (i.e. the parent role).
The following diagram shows an example role hierarchy and the privileges granted to each role:

Grant a Role to Another Role¶
Assign the role to a higher-level role in a role hierarchy. In this example, we are assigning the r1
role created in
Creating Custom Roles (in this topic) to the SYSADMIN role. The SYSADMIN role inherits any object privileges granted to the
r1
role:
GRANT ROLE r1 TO ROLE sysadmin;
Note
In a more complex example, you could assign the custom
role to another child role of SYSADMIN (or another administrator role,
such as a custom role with sufficient privileges to create databases). The SYSADMIN role would inherit the combined privileges assigned
to the custom
role and its parent role. If the role above custom
in the hierarchy owned any objects, then the role hierarchy
would ensure that members of the SYSADMIN role also owned those objects (indirectly) and could manage them as expected.
Viewing Granted Privileges¶
To view the current set of privileges granted on an object, you can execute the SHOW GRANTS command.
Note
Executing the SHOW GRANTS command on a specific object requires the same object privileges as running the SHOW command for that object type.
For example, running the SHOW GRANTS command on a table requires the following privileges on the table and the container database and schema:
- Database
USAGE
- Schema
USAGE
- Table
any privilege
For example, to view the current permissions on a schema, execute the following command:
SHOW GRANTS ON SCHEMA <database_name>.<schema_name>;
For example, execute the following command to view the privileges on database_a.schema_1
that were granted in
Creating Custom Roles (in this topic):
SHOW GRANTS ON SCHEMA database_a.schema_1;
Snowflake returns the following results:
+-------------------------------+-----------------------+------------+----------------------+------------+--------------------------+--------------+---------------+ | created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by | |-------------------------------+-----------------------+------------+----------------------+------------+--------------------------+--------------+---------------| | 2022-03-07 09:04:23.635 -0800 | USAGE | SCHEMA | D1.S1 | ROLE | R1 | false | SECURITYADMIN | +-------------------------------+-----------------------+------------+----------------------+------------+--------------------------+--------------+---------------+
You can also run the SHOW GRANTS command to view the current set of privileges granted to a role, or the current set of roles granted to a user:
SHOW GRANTS TO ROLE <role_name>; SHOW GRANTS TO USER <user_name>;
For example, execute the following command to view the privileges granted on role r1
created in Creating Custom Roles
(in this topic):
SHOW GRANTS TO ROLE r1;
Snowflake returns the following results:
+-------------------------------+-----------+------------+----------------------+------------+--------------+--------------+---------------+ | created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by | |-------------------------------+-----------+------------+----------------------+------------+--------------+--------------+---------------| | 2022-03-07 09:08:43.773 -0800 | USAGE | DATABASE | D1 | ROLE | R1 | false | SECURITYADMIN | | 2022-03-07 09:08:55.253 -0800 | USAGE | SCHEMA | D1.S1 | ROLE | R1 | false | SECURITYADMIN | | 2022-03-07 09:09:07.206 -0800 | SELECT | TABLE | D1.S1.T1 | ROLE | R1 | false | SECURITYADMIN | | 2022-03-07 09:08:34.838 -0800 | USAGE | WAREHOUSE | W1 | ROLE | R1 | false | SECURITYADMIN | +-------------------------------+-----------+------------+----------------------+------------+--------------+--------------+---------------+
Assigning Future Grants on Objects¶
To simplify grant management, future grants allow defining an initial set of privileges to grant on new (i.e. future) objects of a certain type in a database or a schema. As new objects are created in the database or schema, the defined privileges are automatically granted to a specified role.
Future grants only define the initial set of privileges granted on new objects of a specified type. After an individual object is created, administrators can explicitly grant additional privileges or revoke privileges on the object. This allows fine-grained access control over all objects in the schema or database.
Considerations¶
When future grants are defined on the same object type for a database and a schema in the same database, the schema-level grants take precedence over the database level grants, and the database level grants are ignored. This behavior applies to privileges on future objects granted to one role or different roles.
For example, the following statements grant different privileges on objects of the same type at the database and schema levels.
Grant the SELECT privilege on all future tables in database
d1
to roler1
:GRANT SELECT ON FUTURE TABLES IN DATABASE d1 TO ROLE r1;
Grant the INSERT and DELETE privileges on all future tables in schema
d1.s1
to roler2
.GRANT INSERT,DELETE ON FUTURE TABLES IN SCHEMA d1.s1 TO ROLE r2;
The future grants assigned to the
r1
role on object types in schemad1.s1
are ignored completely. When new tables are created in schemad1.s1
, only the future privileges defined on tables for ther2
role are granted.Database level future grants apply to both regular and managed access schemas.
Defining Future Grants on Database or Schema Objects¶
Grant privileges on future objects of a specified type using the GRANT <privileges> command with the ON FUTURE keywords.
Revoking Future Grants on Database or Schema Objects¶
Revoke grants on future objects using the REVOKE <privileges> command with the ON FUTURE keywords.
Object Cloning and Future Grants¶
When a database or schema is cloned, future grants are copied to its clone. This behavior maintains consistency with the regular object grants; that is, grants of privileges on a source object (i.e. database) are not copied to its clones, but privilege grants on all child objects (i.e. tables in the database) are copied to the clones.
When an object in a schema is cloned, any future grants defined for this object type in the schema are applied to the cloned object unless the COPY GRANTS option is specified in the CREATE <object> statement for the clone operation. In that case, the new object retains the access permissions of the original object and does not inherit any future grants for objects of that type.
Managing Future Grants Using the Web Interface¶
You can also define future grants using the Classic Console:
- Grants on future database objects
Click on the row for a specific database. The security panel opens.
Click the Grant Privileges button. The Grant Privileges dialog opens.
From the Grant privileges on dropdown list, select future
object_type
to define future grants on new objects of a specific object type.From the remaining dropdown lists, select the privileges you are granting on new objects of the type you specified, as well as the role that the privileges will be granted to.
Click the Grant Privileges button.
- Grants on future schema objects
Click on the row for a specific schema. The security panel opens.
Click the Grant Privileges button. The Grant Privileges dialog opens.
From the Grant privileges on dropdown list, select future
object_type
to define future grants on new objects of a specific object type.From the remaining dropdown lists, select the privileges you are granting on new objects of the type you specified, as well as the role that the privileges will be granted to.
Click the Grant Privileges button.
Creating Managed Access Schemas¶
Managed access schemas improve security by locking down privilege management on objects.
In regular (i.e. non-managed) schemas, object owners (i.e. a role with the OWNERSHIP privilege on an object) can grant access on their objects to other roles, with the option to further grant those roles the ability to manage object grants.
With managed access schemas, 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.
You can create a managed access schema using either the web interface or SQL:
- Classic Console
- SQL
Execute a CREATE SCHEMA statement with the WITH MANAGED ACCESS keywords.
You can change a regular schema to a managed access schema (or vice-versa) using either the web interface or SQL:
- Classic Console
Click on Databases
» <db_name> » Schemas » <schema_name> » Alter a schema.
- SQL
Execute an ALTER SCHEMA statement with the ENABLE | DISABLE MANAGED ACCESS keywords.
The following table indicates which roles can manage object privileges in a regular or managed access schema:
Role |
Can grant object privileges in a regular schema |
Can grant object privileges in a managed access schema |
---|---|---|
SYSADMIN |
No |
No |
SECURITYADMIN or higher |
Yes |
Yes |
Database owner |
No |
No |
Schema owner |
No |
Yes |
Object owner |
Yes |
No |
Any role with the MANAGE GRANTS privilege |
Yes |
Yes |
Enabling Non-Account Administrators to Monitor Usage and Billing History in the Classic Console¶
Snowflake provides extensive account usage and billing information about data storage/transfer and warehouse usage/load:
- Snowsight
Select Admin » Usage.
- Classic Console
- SQL
Query any of the following:
Table functions (in the Snowflake Information Schema):
Views (in Account Usage):
By default, this information can be accessed/viewed only by account administrators.
Note
Currently, Snowsight only displays usage and billing information to account administrators. It is not possible to grant other roles the ability to view this information.
To enable users who are not account administrators to access/view this information, grant the following privileges to a system-defined or custom role. Granting the privileges to a role allows all users who are granted the role to access this historical/usage information:
Privilege
Object
Description
MONITOR USAGE
Account (i.e. global privilege)
Allows users who have been granted the role to view usage and billing information in the web interface and query the corresponding table functions in the Information Schema.
In addition, with this privilege, the SHOW DATABASES and SHOW WAREHOUSES commands return the lists of all databases and warehouses in the account, respectively, regardless of other privilege grants.
IMPORTED PRIVILEGES
snowflake
databaseAllows users who have been granted the role to query all of the ACCOUNT USAGE views, including the views containing usage and billing information.
For more information, see Enabling the SNOWFLAKE Database Usage for Other Roles.
For example, to grant these permissions to the custom
role:
GRANT MONITOR USAGE ON ACCOUNT TO ROLE custom;
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE custom;