This is a key feature of Snowflake that ensures all the access is governed within the platform. Through this feature the entire access control is governed for all users. We would first understand what each of the terminology means in this ecosystem and then further deep dive into each of those topics. There are couple of models which when combined together ensures the resilient Snowflake Role Based Access Control (RBAC) model is there.
A. Snowflake Discretionary Access Control (DAC)
Over here within Snowflake Development each object would be having an owner which in turn has an access to give grants.
B. Snowflake Role Based Access Control (RBAC)
Roles are given all the access rights, which are then given to users. This meaning we cannot assign direct privileges of the objects to the users.
It is also important to understand 4 major pillars as how Snowflake Role Based Access Control is happening within the platform.
- User: This is usually considered as an identity recognized by Snowflake which can be associated with a person or program.
- Role: This we can say is an entity to which we usually grant the access. Post getting that access these roles are then assigned to the users.
- Privilege: It is a definite set of access which we give to the object like tables, views. The privilege can be ‘select’, ‘update’, ’insert’, etc. Multiple distinct objects can be used to control the grain of the access granted.
- Object: In Snowflake all objects are secured. And these are the ones to which we usually grant the access. Example: table, views, etc.
To simplify how these Snowflake Role Based Access Control are related, the same can be pictorially depicted in the below diagram.
Hence, it is an important concept to understand that in Snowflake Role Based Access Control to securable object is given via privileges assigned to roles which are then assigned to the respective users. And each object would be having an owner who in turn has the access to give grant to other roles. This Snowflake Role Based Access Control model is different when compared with user-based access control which primarily works on the principle of giving direct privilege access to the users or user groups.
READ – Snowflake’s Evolution with Reka AI
The Snowflake RBAC model ensures there is a considerable amount of flexibility and control involved whenever we would managing them inside the platform. Hence, Snowflake Role Based Access Control framework is super powerful, agile and can scale with ease.
Securable Objects
In Snowflake each and every object resides within the logical hierarchy of container. One of the example of this can be under the schema we would have tables, views, UDFs, stored procedures, etc.
The above image shows how the objects in an Snowflake is organized with the topmost container being the Organization followed by Account. To own any Snowflake object meaning that the role would be having the ownership privilege on that object. Each secure object would only belong to one role, and that role could also be used to create the object. The users would inadvertently share control of the object when any role was granted to them.
Hence, in this Snowflake Role Based Access Control model, the owner role would be having all privileges on that object including the ability to grant and revoke privileges on an object to other roles. Privileges granted to the active role in a user session specify the capacity to conduct SQL actions on objects, these are namely:
- Create a warehouse.
- Create a database.
- Create a schema.
- Create and manage the virtual warehouse
- Add a table within a schema.
- Create tables/views/stored procedures.
- Adding the data within the table.
Users
Users are the entities to which we give the necessary access. Usually there are 2 type of users which we usually associate with Snowflake and they are:
- Standard Users.
- Application Users.
- Standard Users: These are the ones which are all human entities. Like for an example within an enterprise there can be variety of standard users namely admins, analysts, developers, testers, etc. All these users are kind of human entities and hence they are categorized as the standard users.
- Application Users: These are the users which are non-human entities and more comes from any application integration tool. Like for an example if any data integration tool like DBT, Matillion, Fivetran would interact with Snowflake then their users would fall under this category. They are also often called as system defined users.
Now, that we understand the various categories of the users which are entities within the platform so it is also understood that all these users would be given specific privileges through roles to perform different action within the Snowflake platform.
READ – Top 5 SnowFlake Development Blunders: How To Steer Clear of Common Issues
Roles
Roles are the entities to which different kind of privileges can be granted like ‘select, insert, update, delete, usage, etc.’ for the securable objects and post that the same role can be granted to the users. Once these roles are assigned to the users then only they can perform specific set of activities within the enterprise. For example, with Snowflake consulting, we were able to map role entities more effectively, improving overall system performance.
A user may also be given many roles, enabling them to swap between them during a single session. Example if an user is performing both kind of activities like the “analyst” and “tester” then in this case he can be assigned both the roles which allows the users to perform different actions.
Some other properties of the role are as follows:
- There are system-defined roles which cannot be dropped nor can the privileges granted to these roles by Snowflake be revoked.
- Custom roles can be created by the users who have been granted right roles.
- Snowflake Role Based Access Control can also be granted to other roles creating a hierarchy of roles.
In roles within Snowflake Role Based Access Control, we can primarily categorize them couple of categories namely:
- Active role.
- System defined roles.
1) Active Role
An active role can be considered as a source of authorization within a session. Now within the active role there can be many classifications like secondary role, database role, functional role, etc. which we would be covering in the following sections. A role in Snowflake Role Based Access Control becomes active only when the following things happen:
- When a session gets established and user’s default role becomes an active role.
- Once a session gets terminated then this role is no more active however there are no changes in the background for the default role for the user.
- By executing commands like “USE ROLE” or “USE SECONDARY ROLE” is going to activate the different set of permissions via roles within a session. And this can evolve over the period of time.
2) System Defined Role
System defined roles in Snowflake Role Based Access Control are the ones which comes pre-built whenever we create an account within the platform. There are different roles created to cater different needs. Below diagram depicts the kind of system defined roles which we have currently within the Snowflake Role Based Access Control platform.
All these system defined roles have got a dependency and below is how internally within the Snowflake Role Based Access Control platform they are kept as an hierarchy which is given as below:
Let us now understand each of those roles and what activities can be done by them. As mentioned above it is also important for us to understand that these roles cannot be dropped from the system.
1) ORGADMIN Role
This role would be operating at an organization level and is primarily responsible for the below mentioned activities:
- To manage & create the accounts within the organization.
- Visualize all the accounts created within the organization. Like within an account there can be a scenario where we might have a production account and a DR (Disaster Recovery) account and both of them would be tagged under same organization.
- It can also track all the organization usage data via “organization_usage” object that is given to us by Snowflake through a shared database named ‘SNOWFLAKE’.
2) ACCOUNTADMIN Role
This is a top level role within an account and should always be granted to very very limited and controlled list of users. This role is going to encapsulate 2 system defined roles within the account i.e., SYSADMIN and SECURITYADMIN which means that it is going to inherit all the privileges which these 2 roles has. Some properties are as follows:
- It has almost all access within the account and hence has to be used deligently.
- Inherits the privilege of roles SYSADMIN and SECURITYADMIN.
- This role is meant for the ADMINs within the enterprise.
- This is the most powerful role in the system.
- The roles I.e., SYSADMIN, SECURITYADMIN, USERADMIN are all the child roles to this ACCOUNTADMIN role.
- By default, when an account gets created for Snowflake ‘ACCOUNTADMIN’ role is assigned to the first user against whom this account gets created.
- ACCOUNTADMIN should never be made as a default role within the system.
- This role also should never be used for any custom scripts.
- ACCOUNTADMIN role should never be used in creating users, databases, warehouses, roles, schemas in the Snowflake Role Based Access Control model; if created it impacts heavily on the Snowflake RBAC design.
3) SECURITYADMIN Role
This role can manage the grants globally. It can create, monitor and manage the users and roles. This role can also be used to create the network and session policies within the account. Some properties of this role is as follows:
- This role would inherit all the properties of USERADMIN role.
- Can manage the grants like grant and revoke the privilege globally.
4) USERADMIN Role
This role is just meant to perform the activities with respect to the user and role management only. This meaning this role cannot create any databases, warehouse, etc. This role can also manage the roles and users created by this role and even get the ownership via the transfer of the ownership. Some properties are as follows:
- Can be used to only create and manage the users and roles.
- The privileges are inherited by the role SECURITYADMIN role.
- USERADMIN role is primarily used for ROLE control.
- USERADMIN should only ADMINISTER only users & roles in environment. They are not responsible to create databases, schemas, etc.
5) SYSADMIN Role
This is the role which has all the privileges to create the system level objects like the databases and the warehouses. Once it is created it is completely owned by them. This meaning no-one else apart from role SYSADMIN should be able to drop/modify these objects. Some properties of this role is as follows:
- To create and manage the databases and warehouses.
- All custom roles should be owned by this role.
- If any custom role created and not tagged to this role then it remains as unmanaged role and even “ACCOUNTADMIN” role cannot manage the objects created by this role.
- They are responsible to access every table in the environment.
- They also manage the warehouses.
- They should not be used to create or manage the users/roles.
6) PUBLIC Role
This is a pseudo role which is automatically granted to every user and role that are present in the account. One thing to keep in mind is that if any objects are generated by this role, they become accessible to any other users or roles who are currently logged into the account. Some more properties of this roles are:
- It’s a default role assigned to a user.
- Can be used in cases where explicit Snowflake Role Based Access Controls are not required.
- Objects owned by this role become available for everyone.
To summarize, below is the diagrammatic representation of the roles and their usage:
Newer Role Categories
Now that we have understood the most important concepts of Snowflake Role Based Access Control (RBAC). It is also worth knowing some of the newer concepts of Snowflake RBAC. There are further 2 newer categories of roles introduced by Snowflake namely:
- Secondary Roles.
- Database Roles.
Secondary Roles
This role is just meant to perform the activities with respect to the user and role management only. A classic scenario that secondary role addresses is if for one user we have assigned multiple roles, then that user has to continuously switch between roles in the same console to get access to the relevant objects and at times it becomes an overhead.
Secondary roles hence are designed that if we execute one simple command, then all those roles are assigned to that user in that session. Let us see that in action.
Set-up for this demo is given as a screenshot where we create 2 roles ‘DEV_ROLE’ & ‘TEST_ROLE’, these 2 roles are given access to the user ‘USER_01’.
DEV_ROLE à Has access to only the table TBL_QUARTERLY_SALES
TEST_ROLE à Has access to only the table DEMO_CALL_CENTER
Now, when we try logging in to Snowflake using the user ‘USER_01’, and changing the role to “DEV_ROLE’, then it would allow us to access only the table ‘TBL_QUARTERLY_SALES’ and if we try querying the table ‘DEMO_CALL_CENTER’ then we would not be able to do that.
Hence to mitigate this we have to execute a simple command and the query gets executed successfully.
To summarize, if a user has multiple roles assigned and to each role there are multiple objects associated then instead of him toggling between roles, with one simple secondary role command the user can access all the relevant objects.
READ – Advanced Snowflake Features: Streams, Tasks, Cloning and More
Database Roles
Database roles are used for controlling and governing the access only at the database roles. These are different from the account roles as mentioned in the above sections since it limits the access only till database and do not cater to cross databases OR even cater to the account level objects like ‘virtual warehouses’. One of the primary use case is this role is usually created to govern access to the SNOWFLAKE database (shared DB), that has all critical objects for the usage information, metadata information, etc.
Summary
Snowflake Role Based Access Control (RBAC) and User authorization is one of the fundamental pillars when it comes to security. It helps in strengthening the control & governance when it comes to providing security at an object level within Snowflake.
