Configuring Access Control¶
This topic describes how to configure object-level security using the system-defined roles (provided by Snowflake) and custom roles (optional).
In this Topic:
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 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’s 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).
For example, you can create a custom role with all privileges on a specific schema:
Grant this role the following privileges:
USAGE on the database that contains the schema
ALL on the schema that contains the tables to query
USAGE on a warehouse used to execute queries on the tables in the schema.
Create the hierarchy of roles. Grant the custom role to the SYSADMIN role. The parent roles inherit the object privileges associated with each child role.
Grant the custom role to any user who requires the specified privileges.
Any user with the role can create and use any object in the schema. The following diagram shows an example role hierarchy and the privileges granted to each role:

Note
The following sections provide step-by-step instructions for creating a custom role named custom
in a basic role hierarchy. This custom
role allows users to create objects in a schema and to manage those objects (as the object owner). The role does not have permissions on existing objects in the schema, although those could be given through additional privilege grants at either the schema or object level.
Execute the SQL statements in this section as a user with the SECURITYADMIN role (or higher).
Create a Custom Role¶
Create the
custom
role:CREATE ROLE custom COMMENT = 'This role has all privileges on schema_1';
Grant the
custom
role the following object privileges:USAGE on the database that contains the schema (
database_a
). To use any objects in a schema, a role must also have the USAGE privilege on the container database.ALL [ PRIVILEGES ] on the schema (
schema_1
).USAGE on the warehouse used to execute queries (
warehouse_1
). Users with this role can execute queries using this warehouse.
GRANT USAGE ON DATABASE database_a TO ROLE custom; GRANT ALL ON SCHEMA database_a.schema_1 TO ROLE custom; GRANT USAGE ON WAREHOUSE warehouse_1 TO ROLE custom;
Grant the Role to Another Role¶
Assign the role to a higher-level role in a role hierarchy. In this example, we are assigning the custom
role to the SYSADMIN role. The SYSADMIN role inherits any object privileges granted
to the custom
role:
GRANT ROLE custom 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.
Grant the Role to a User¶
Use the ALTER USER to disable the user you want to modify. This will forcefully close all existing sessions for the user while you are making the changes to that user. For example, the following command disables user Bonnie Smith (bsmith):
ALTER USER bsmith SET DISABLED=TRUE;
Assign the
custom
role to a user:GRANT ROLE custom TO USER bsmith;
Set the default role for the user. The following command defines the default role for user Bonnie Smith:
ALTER USER bsmith SET DEFAULT_ROLE = custom;
Enable the user using the ALTER USER command, so the user can log in again, now with the new default role. For example:
ALTER USER bsmith SET DISABLED=false;
Viewing Granted Privileges¶
To view the current set of privileges granted on an object, you can execute the SHOW GRANTS command. 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 Create a Custom Role:
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 | |-------------------------------+--------------------+------------+---------------------+------------+--------------+--------------+--------------| | 2016-08-24 12:35:08.000 -0700 | OWNERSHIP | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | SYSADMIN | true | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE FILE FORMAT | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE FUNCTION | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE SEQUENCE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE STAGE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE TABLE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE VIEW | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | MODIFY | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | MONITOR | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | USAGE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | +-------------------------------+--------------------+------------+---------------------+------------+--------------+--------------+--------------+
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 custom
created in Create a Custom Role:
SHOW GRANTS TO ROLE custom;
Snowflake returns the following results:
+-------------------------------+--------------------+------------+---------------------+------------+--------------+--------------+--------------+ | created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by | |-------------------------------+--------------------+------------+---------------------+------------+--------------+--------------+--------------| | 2016-11-22 12:34:29.000 -0800 | USAGE | DATABASE | DATABASE_A | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE FILE FORMAT | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE FUNCTION | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE SEQUENCE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE STAGE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE TABLE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | CREATE VIEW | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | MODIFY | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | MONITOR | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | USAGE | SCHEMA | DATABASE_A.SCHEMA_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | | 2016-11-22 12:34:30.000 -0800 | USAGE | WAREHOUSE | WAREHOUSE_1 | ROLE | CUSTOM | false | ACCOUNTADMIN | +-------------------------------+--------------------+------------+---------------------+------------+--------------+--------------+--------------+
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
Creating Read-Only Roles¶
Suppose you needed a role that is limited to querying the tables in a specific schema (e.g. database_a.schema_1
). Users who execute commands using this role cannot update the table data,
create additional database objects, or drop tables.
In this scenario, you could create a custom role with limited access to the schema and its tables. You would then grant the read-only role to the users who require read-only access to the schema and tables. These users can work in the limited default role without concern about accidentally modifying or dropping schema objects.
Note
Execute the SQL statements in this section as a user with the SECURITYADMIN role (or higher).
Create the custom
read_only_rl
role:CREATE ROLE read_only_rl COMMENT = 'This role is limited to querying tables in schema_1';
Assuming you have implemented a role hierarchy (recommended), assign the role to a higher-level role in a role hierarchy. In this example, we are assigning the
read_only_rl
role to the SYSADMIN role. The SYSADMIN role inherits any object privileges granted to theread_only_rl
role:GRANT ROLE read_only_rl TO ROLE sysadmin;
Grant the
read_only_rl
role the following object privileges:USAGE on the database that contains the schema (
database_a
).USAGE on the schema that contains the tables to query (
schema_1
). To use any objects in a schema, a role must also have the USAGE privilege on the database and schema:SELECT on all existing tables.
USAGE on the warehouse used to execute queries on the tables (
warehouse_1
). Users with this role can execute queries using this warehouse.
GRANT USAGE ON DATABASE database_a TO ROLE read_only_rl; GRANT USAGE ON SCHEMA database_a.schema_1 TO ROLE read_only_rl; GRANT SELECT ON ALL TABLES IN SCHEMA database_a.schema_1 TO ROLE read_only_rl; GRANT USAGE ON WAREHOUSE warehouse_1 TO ROLE read_only_rl;
Note
The
GRANT SELECT ON ALL TABLES IN SCHEMA <schema>
statement only applies to existing tables. The read-only role must be granted the SELECT privilege on any tables created in the schema thereafter. For example:GRANT SELECT ON TABLE database_a.schema_1.table_new TO ROLE read_only_rl;
Use the ALTER USER to disable the user you want to modify. This will forcefully close all existing sessions for the user while you are making the changes to that user. For example, the following command disables user Bonnie Smith (bsmith):
ALTER USER bsmith SET DISABLED=TRUE;
Assign the
read_only_rl
role to a user:GRANT ROLE read_only_rl TO USER bsmith;
Set the default role for the user. The following command defines the default role for user Bonnie Smith:
ALTER USER bsmith SET DEFAULT_ROLE = read_only_rl;
Enable the user using the ALTER USER command, so the user can log in again, now with the new default role. For example:
ALTER USER bsmith SET DISABLED=false;
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, 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¶
Future grants defined for an object at the database level apply to all objects of that type created in future. For more information, see Defining Future Grants on Database or Schema Objects (in this topic).
You must define future grants on each object type (schemas, tables, views, streams, etc.) individually. For more information, see Defining Future Grants on Existing Database or Schema Objects (in this topic).
The privileges defined using future grants are automatically granted at object creation time.
When future grants are defined at both the database and schema level, the schema level grants take precedence over the database level grants, and the database level grants are ignored.
Database level future grants apply to both regular and managed access schemas. For more information, see Security Privileges Required to Manage Future Grants (in this topic).
Security Privileges Required to Manage Future Grants¶
The following permissions are required to grant or revoke privileges on future objects:
- Database level
The global MANAGE GRANTS privilege is required to grant or revoke privileges on future objects in a database. Only the SECURITYADMIN and ACCOUNTADMIN system roles have the MANAGE GRANTS privilege; however, the privilege can be granted to custom roles.
- Schema level
In managed access schemas (i.e. schemas created using the CREATE SCHEMA … WITH MANAGED ACCESS syntax), only the schema owner (i.e. the role with the OWNERSHIP privilege on the schema) can grant or revoke privileges on future objects in the schema.
In standard schemas, the global MANAGE GRANTS privilege is required to grant or revoke privileges on future objects in the schema.
Defining Future Grants on Database or Schema Objects¶
You can assign future grants on database or schema objects using the GRANT <privileges> … TO ROLE command with the ON FUTURE keywords.
Code example for setting future grants at the database level:
use role accountadmin;
-- Grant the USAGE privilege on all future schemas in a database to role R1
grant usage on future schemas in database DB1 to role R1;
Code example for setting future grants at the schema level:
use role accountadmin;
-- Grant the SELECT privilege on all future tables in a schema to role R1
GRANT SELECT ON FUTURE TABLES IN SCHEMA DB1.schema1 TO ROLE R1;
-- Grants the SELECT and INSERT privileges on all future tables in a schema to R1
grant select,insert on future tables in schema DB1.schema1 to role R1;
Defining Future Grants on Existing Database or Schema Objects¶
Future grants only pertain to new objects. You must explicitly grant the desired privileges to a role on existing objects using the GRANT <privileges> … TO ROLE command.
Code Example:
use role accountadmin;
-- Grant the USAGE privilege on all existing schemas in a database to role R1
grant usage on all schemas in database DB1 to role R1;
-- Grant the SELECT privilege on all existing tables in a schema to role R1
grant select on all tables in schema DB1.schema1 to role R1
Revoking Future Grants on Database or Schema Objects¶
You can revoking future grants on database objects using the REVOKE <privileges> … FROM ROLE command with the ON FUTURE keywords.
Note
Revoking future grants on database objects, only removes privileges granted on future objects of a specified type rather than existing objects. Any privileges granted on existing objects are retained.
Code Example:
use role accountadmin;
-- Revoke the USAGE privilege on all existing schemas in a database from role R1
revoke usage on all schemas in database DB1 from role R1;
-- Revoke the SELECT and INSERT privileges on tables in a schema from the role R1
revoke select,insert on future tables in schema DB1.schema1 from role R1;
Managing Future Grants Using the Web Interface¶
You can also define future grants using the web interface:
- 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.
Restrictions and Limitations¶
The following restrictions and limitations apply to future grants at database or schema level:
Future grants are not supported for:
Data sharing
Data replication
Future grants are supported on named stages with the following restrictions:
The WRITE privilege cannot be specified without the READ privilege.
The READ privilege cannot be revoked if the WRITE privilege is present.
For internal stages, only future grants with the READ or WRITE privilege are materialized.
For external stages, only future grants with the USAGE privileges are materialized.
Future grants are not applied when renaming or swapping a table.
No more than one future grant of the OWNERSHIP privilege is allowed on each securable object type.
Database level future grants of OWNERSHIP privilege on the objects of managed access schemas in the database are not affected.
When a database is cloned, the schemas in the cloned database copy the future privileges from the source schemas. This maintains consistency with the regular object grants, in which the grants of the source object (i.e. database) are not copied to the clone, but the grants on all the children objects (i.e. schemas in the database) are copied to the clones.
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:
- Web Interface
- 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:
- Web Interface
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¶
Snowflake provides extensive account usage and billing information about data storage/transfer and warehouse usage/load:
- Web interface
- SQL
Query any of the following:
Table functions (in the Information Schema):
Views (in Account Usage):
However, by default, this information can be accessed/viewed only by account administrators. To enable users who are not account administrators to access/view this information, Snowflake provides the global MONITOR USAGE privilege. Granting the MONITOR USAGE privilege to a role allows all users who are granted the role to access this historical/usage information.
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.
For example, to grant this privilege to the custom
role:
GRANT MONITOR USAGE ON ACCOUNT TO ROLE custom;