Configuring Access Control

This topic describes the changes implemented when access control is enabled for an existing account (i.e. an account with existing users and objects).

In this Topic:

Account Administration

Designate Additional Users as Account Administrators

We recommend designating at least two users as account administrators (ACCOUNTADMIN role). You can choose to create new users or select existing users, but make sure to specify the following:

  • Assign the ACCOUNTADMIN role to each user and set it as their default role.
  • Ensure an email address is specified for each user (required for multi-factor authentication (MFA)).

For example, to specify an existing user named user2 as an account administrator:

GRANT ROLE accountadmin TO USER user2;

ALTER USER user2 SET EMAIL='user2@domain.com', DEFAULT_ROLE=ACCOUNTADMIN;

Enable MFA for All Account Administrators

To ensure the highest level of security for your Snowflake account, all account administrators (i.e. users granted the ACCOUNTADMIN role) must use MFA for login.

After you designate users as account administrators, contact Snowflake Support to request MFA to be enabled for the users, if it isn’t already enabled for them. Be prepared to provide the email addresses for each user.

For more information, see Understanding Multi-Factor Authentication.

Set Account Parameters

Account parameters can only be set at the account level by account administrators. See Parameters for descriptions of account parameters.

Account parameters are set using the ALTER ACCOUNT command.

For example, set the PERIODIC_DATA_REKEYING parameter to true:

ALTER ACCOUNT SET periodic_data_rekeying = true;

Restore the default periodic_data_rekeying value:

ALTER ACCOUNT UNSET periodic_data_rekeying;

Creating a Role Hierarchy

When creating custom roles, consider creating a role hierarchy ultimately assigned to the SYSADMIN role. The SYSADMIN role is a default system role that has privileges to create databases and warehouses 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.

You create a role hierarchy by granting a role to a second role. You could 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.

For example, you could create a custom role with all privileges on a specific schema. Any user with the role could create and use any object in the schema.

  1. 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.
  2. 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.

  3. Grant the custom role to any user who requires the specified privileges.

The following diagram illustrates the role hierarchy and the privileges granted to each role:

Role hierarchy and privileges granted to each role

Create a Role Hierarchy

This section provides the steps to create the custom role named custom in a basic role hierarchy. The SQL statements in this section would be executed by a user with the SECURITYADMIN role or higher.

  1. Create the custom role:

    CREATE ROLE custom
       COMMENT = 'This role has all privileges on schema_1';
    
  2. 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. The SYSADMIN role would inherit the combined privileges assigned to the custom 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.

  3. Grant the custom role the following object privileges:

    • USAGE on the database that contains the schema (database_a).

      Note

      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 on the tables (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;
    
  4. 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;
    
  5. Assign the custom role to a user:

    GRANT ROLE custom
       TO USER bsmith;
    
  6. 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;
    
  7. 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;
    

Show 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 grants on database_a.schema_1 created in Create a Role Hierarchy:

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 grants on role custom created in Create a Role Hierarchy:

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:

Object Privilege
Database USAGE
Schema USAGE
Table <any>

Creating a Read-Only Role

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.

  1. Create the custom read_only_rl role:

    CREATE ROLE read_only_rl
       COMMENT = 'This role is limited to querying tables in schema_1';
    
  2. 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 the read_only_rl role:

    GRANT ROLE read_only_rl
       TO ROLE sysadmin;
    
  3. 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).

      Note

      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;
    
  4. 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;
    
  5. Assign the read_only_rl role to a user:

    GRANT ROLE read_only_rl
       TO USER bsmith;
    
  6. 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;
    
  7. 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;
    

Managing Object Ownership and Privileges

To customize ownership and privileges granted on pre-existing objects, use the following high-level instructions, performed by a user with the SYSADMIN role. Note that changing access control on the objects takes immediate effect when successfully executed:

  1. If the object should be owned by a role that the SYSADMIN role dominates (lower in the hierarchy), use the GRANT OWNERSHIP command to transfer the ownership to it. Unlike other privileges, the OWNERSHIP privilege can only be granted to one role. If the target owner role is not granted to SYSADMIN, then only the holder of MANAGE GRANTS can perform the ownership transfer. For example:

    GRANT OWNERSHIP ON WAREHOUSE reports TO ROLE analyst;
    
  2. The control over the object is shared by all users who are assigned the role having the OWNERSHIP privilege on the object. In many cases, this is sufficient granularity for access control on the database objects. However, if the privileges on the object need to be controlled more explicitly, you can create explicit grants on the object using the GRANT privilege command. For example:

    GRANT OPERATE ON WAREHOUSE loader TO ROLE users;
    
  3. Verify the grants on the object using the SHOW GRANTS command. For example:

    SHOW GRANTS ON WAREHOUSE loader;