Overview of Snowflake Role Based Access Control

Introduction to Snowflake Access Control

Role Based Access Control (RBAC) is the Snowflake Access Control Framework using which Snowflake offers granular control over object access, allowing users to specify which objects may be accessed and what operations can be carried out on them.

The main concept of Role Based Access Control (RBAC) is, access privileges on Snowflake objects are assigned to roles, which are in turn assigned to users. The role with which object is created will be the owner and has Discretionary Access Control (DAC) to grant access on that object.

Roles, Users, Objects and Privileges

Securable Object: Includes Databases, Tables, Stages, Warehouses etc. which needs to be secured by granting access. Access will be denied unless allowed by a grant.

Privileges: Privileges define the level of ability to perform the SQL actions on the object to which access is granted.

Examples include:

• Ability to update and insert data into a table.
• Ability to create new databases and warehouses in an account.

Roles: Roles are the entities to which privileges on securable objects can be granted and revoked. A role could be assigned to multiple users which grants privileges to all the users to all the objects to which role has privileges to.
There are few system-defined roles available in Snowflake account, created by default, which cannot be dropped.

Users: Users are those who need access on securable objects. Roles are assigned to users to allow them to perform required actions in their organization. A user can be assigned multiple roles. This allows users to switch roles to perform different actions using separate sets of privileges.

System Defined Roles in Snowflake

Below are the system defined roles in Snowflake

ORGADMIN

Organization Administrator role that manages operations of Organizations and accounts at the Organization level.

ACCOUNTADMIN

Account Administrator role that manages all aspects of the account. It inherits the privileges of the SYSADMIN and SECURITYADMIN system-defined roles and sits on top of role hierarchy.

SECURITYADMIN

Security Administrator role that can create, monitor, and manage users and roles as well manage any object grant globally. It inherits the privileges of the USERADMIN role via the system role hierarchy.

USERADMIN

User Administrator role that manages creation of users and roles.

SYSADMIN

System Administrator role that has privileges to create and manage warehouses and databases in an account. All Custom roles are recommended to roll up to SYSADMIN.

PUBLIC

Public role is automatically granted to all the users in the account. The objects owned by the role are available to every other user and role in your account.

Custom Roles and Role Hierarchy

Apart from the system-defined roles, Snowflake also supports creating custom roles with different privileges as per the requirements. By default, a newly created role is not assigned to any user, nor granted to any other role.

When creating custom roles, Snowflake recommends creating hierarchy of custom roles with the topmost role assigned to the SYSADMIN role.

If the custom role is not assigned to any higher administrative system role, it exists in isolation. In such cases not even the ACCOUNTADMIN role cannot modify or drop objects created by a custom role even though the grants to create objects is provided by ACCOUNTADMIN role to the custom role.

The below image illustrates the hierarchy for the system-defined roles along with the recommended structure for user defined custom roles.

Creating Custom Roles, Users and Role Hierarchy

Consider a scenario where you as a user with ACCOUNTADMIN role need to set up some custom roles needed to manage a SALES database.

Below are custom roles identified to be created

SALES_ADMIN: Users with this role has the complete access to create databases and warehouses and grant privileges on these objects to other roles.

SALES_DEV_WRITE: Users with this role has the access to connect to databases and schemas and create tables and other objects within the databases created by SALES_ADMIN role.

SALES_DEV_READ: Users with this role has read-only access on all the database objects created by SALES_ADMIN and SALES_DEV_WRITE roles.

The above discussed roles can be created as shown below. It is recommended to create roles through SECURITYADMIN system role.

-- Creating Custom Roles
------------------------------------------------------------
use role SECURITYADMIN;

create role SALES_ADMIN;
create role SALES_DEV_WRITE;
create role SALES_DEV_READ;

Three different users identified to manage and work with SALES database. The users can be created as shown below. It is recommended to create roles through USERADMIN system role.

-- Creating Users
------------------------------------------------------------
use role USERADMIN;

create user STEVE password = 'SF@123'
    COMMENT = 'Sales Administrator'
    MUST_CHANGE_PASSWORD = FALSE;

create user TONY password = 'SF@123'
    COMMENT = 'Developer'
    MUST_CHANGE_PASSWORD = FALSE;

create user BRUCE password = 'SF@123'
    COMMENT = 'Analyst'
    MUST_CHANGE_PASSWORD = FALSE;

Now that the roles and users are created, the users can be assigned with respective roles as shown below.

-- Assigning roles to users
------------------------------------------------------------
use role SECURITYADMIN;

grant role SALES_ADMIN to user STEVE;
grant role SALES_DEV_WRITE to user TONY;
grant role SALES_DEV_READ to user BRUCE;

As we discussed earlier in the section, when the roles are created, they exist in isolation not allowing the other roles (even the roles which create and grant privileges to them) to access the objects created by them. So, it is required to set up a role hierarchy for the custom roles we created.

-- Creating a Role Hierarchy for Custom Roles
----------------------------------------------------------------------
grant role SALES_ADMIN to role SYSADMIN;
grant role SALES_DEV_WRITE to role SALES_ADMIN;
grant role SALES_DEV_READ to role SALES_DEV_WRITE;

Below image shows the role hierarchy for the custom roles we created.

Granting Privileges to Roles

The custom roles when created do not have any privileges to access any database objects nor create any of them. They must be explicitly provided access.

Let us provide the access to SALES_ADMIN role to create databases and warehouses.

-- Granting privileges to roles
------------------------------------------------------------
use role ACCOUNTADMIN;

grant create database on account to role SALES_ADMIN;
grant create warehouse on account to role SALES_ADMIN;

Now that the user STEVE has got the SALES_ADMIN role and the access to create databases and warehouses, login in as user STEVE, verify the grants available to user and assign access to other users on the objects created.

Use below SQL command to see all the roles assigned to the user and who granted the role to the user. It shows the user STEVE is assigned the role SALES_ADMIN by SECURITYADMIN role.

show grants to user STEVE;

Use below SQL command to see who has the ownership on the user. It shows the role USERADMIN has ownership on the user STEVE.

show grants on user STEVE;

Using SALES_ADMIN role STEVE creates a database sales_db and a warehouse my_wh and the grants read-write access to SALES_DEV_WRITE role and read-only access to SALES_DEV_READ role.

use role SALES_ADMIN;

-- Grant read-write access on database sales_db to role SALES_DEV_WRITE
-------------------------------------------------------------------------------------------------
grant usage on database sales_db to role SALES_DEV_WRITE;
grant usage on all schemas in database sales_db to role SALES_DEV_WRITE;
grant all privileges on all tables in database sales_db to role SALES_DEV_WRITE;

-- Grant read-only access on database sales_db to role SALES_DEV_READ
-------------------------------------------------------------------------------------------------
grant usage on database sales_db to role SALES_DEV_READ;
grant usage on all schema in database sales_db to role SALES_DEV_READ;
grant select on all tables in database sales_db to role SALES_DEV_READ;

-- Grant usage on warehouse my_wh
-------------------------------------------------------------------------------------------------
grant usage on warehouse my_wh to role SALES_DEV_WRITE;
grant usage on warehouse my_wh to role SALES_DEV_READ;

Now the user TONY who was assigned SALES_DEV_WRITE role has the required privileges to create and read objects in SALES database. The user BRUCE who was assigned SALES_DEV_READ role has read-only privileges to the access the objects created by users with SALES_DEV_WRITE role and above.

The Primary and Secondary Roles

Now consider a scenario where a production SALES database has been setup and two different roles – SALES_PRD_WRITE and SALES_PRD_READ were created to grant access to the production database.

The user TONY as a dev user also needs read access to production database and hence another role SALES_PRD_READ was assigned to him. But any given time, the user TONY can use only one of the roles assigned to him and his “current role” in the session is referred to as a Primary Role. He needs to switch his roles using USE ROLE  to query between DEV and PRD environments.

The use of Secondary roles concept simplifies the role management. All roles that were granted to a user can be activated in a session.

The below image illustrates the role hierarchy where the user TONY has access to two different roles with different privileges.

In order to use the secondary roles in a session, the privileges must be provided to the user using below SQL statement.

use role SECURITYADMIN;
alter user TONY set DEFAULT_SECONDARY_ROLES=('ALL');

In order to allow all the roles that have been granted to user in addition to current active primary role, use the below SQL statement in the user session.

use secondary roles ALL;

To disable secondary roles and allow only the current primary role to perform SQL action, use the below SQL statement in the user session.

use secondary roles NONE;

Conclusion

The ACCOUNTADMIN role is most powerful role in the system which is often mismanaged. It is intended for performing initial account set up tasks and managing account level objects and should be avoided for creating users and database objects. Each system defined role has its own purpose and it needs to be used accordingly.

Hope this article helped in understanding the various system define roles in Snowflake, their purpose and how you can create custom roles and create a hierarchy of roles in Snowflake.

Subscribe to our Newsletter !!

Related Articles: