In our last article on Snowflake, we provided a comprehensive guide on how Snowflake roles and privileges work, as well as how Role-based Access can help you create a simple, scalable access control hierarchy.
In this post, we are continuing our deep-dive on access control, by sharing some tips and guidelines on how to kickstart and manage your control access, and a real-life example to follow along.
The lessons in this post are based on our real experience at Tasman Analytics, where we’ve designed and implemented RBAC hierarchies in Snowflake for over 20 different clients.
While creating and working on your access model and handing out permissions to different users in your company, you should aim at creating a structure where users can have the exact permissions they need (no more, no less) to carry out their job. Why would the new Junior Finance Analyst have permission to drop tables when he only needs to query a balance sheet table?
This is also known as the Principle of Least Privilege, and when applied to your Snowflake access model, it includes many different benefits:
The Principle of Least Privilege is a good way to get things started, as it presents a good compromise between good security practices and maintenance. Some companies might want to adopt more simplified security models, at the cost of increased security risks, while others might consider stricter security practices. Consider researching about the Zero-Trust security model and how the Principle of Least Privilege relates to it.
In our last article, we mentioned different role types: account roles; database roles; and even instance roles. These have fundamental and technical differences from each other. To keep things simple, in this article, we are sticking to Account Roles.
We are now going to explore a different approach to help us define roles: based on their intent. There are 3 types of roles that we want to present here:
Note that there is no technical difference between Access, Functional and Service roles. All of them are created as Account Roles.
Access Roles are created to ensure different levels of access to databases and database objects. You can see them as the low-level building blocks for your hierarchy.
A simple way to create Access Roles is to use READ
and READWRITE
roles for each existing database:
READ
: grants SELECT
privileges in all tables/views in the database.READWRITE
: grants SELECT
, INSERT
, UPDATE
and DELETE
operations.It can be a good starting point, but depending on the needs of your company, you can create more fine-grained Access Roles (e.g. to access specific schemas within a database).
Functional Roles are designed to be aligned with business functions, and will have a blend of Access Roles granted to them. For example, a DATA_ANALYST
role could inherit the privileges from the READ
* *Access Roles created for some databases. On the other hand, a DATA_ENGINEER
role could inherit the READWRITE
ones for the same databases.
These are the roles that will be granted to end users. You can build them according to the actual needs, using the Access Roles as building blocks (and always keeping the Principle of Least Privilege in mind).
This kind of role is essentially the same as a Functional one, but instead of being granted to users in your company, it is granted to Service Accounts. These can be used by third-party tools, like BI and dashboard platforms, or any other SaaS that requires READ
or READWRITE
access to database objects in Snowflake (i.e. Airbyte, Rudderstack, Mode).
In the same fashion as Functional Roles, you can build Service Roles according to the actual needs, using the Access Roles as building blocks and always keeping the Principle of Least Privilege in mind.
With these 3 types of roles, you have the building blocks you need to create a simple and effective access role hierarchy.
One of the key principles of software development is to avoid code repetition, where the acronym D.R.Y. stands for “Don't Repeat Yourself”. With Access, Functional and Service roles, you can start sketching what your role hierarchy will look like. You can use the following 4 steps to iterate.
1. Define the Functional and Service Roles needed in your company
Gather the requirements from the different Snowflake users within your company, and make a list of Functional Roles you need, along with the access levels required. This list will then allow you to sketch the Access Roles you will need.
2. Create Access Roles
Once your requirements list is complete, you will be able to visualise the Access Roles needed. You can now create these roles and grant the appropriate privileges on the respective database objects (i.e. FINANCE_DB_READ_ROLE
).
3. Create Functional and Service Roles (and grant Access Roles to them)
With Access Roles now in place, you have everything you need to build the Functional Roles for the colleagues in your company, as well as the Service Roles for the services to use. So, to fulfil the requirements initially defined, grant Access Roles to the Functional/Service Roles you just created (i.e. DATA_ANALYST
).
4. Grant Functional Roles to Users and Service Roles to Service Accounts
Your role hierarchy is now complete! The final step is to grant the Functional Roles to the Snowflake users and Service Roles to Service Accounts. If adjustments are needed, simply repeat steps 1-4.
⛔ Do not grant Access Roles directly to End Users/Service Accounts
If you skip the Functional Roles layer and grant a set of Access Roles directly to a user, you will end up having to do it every time a new user with similar responsibilities joins the team. Also, every time an adjustment is needed, you will have to do it manually for multiple users.
Having a Functional Role means there will be only a single place to make adjustments. Those changes will be applied to all users granted that specific Functional Role. The Access Roles layer exists to standardise access to database objects throughout your account; not to be used directly!
⛔ Do not grant Functional Roles to other Functional Roles
Keep it simple! Each Functional Role should be a composition of Access Roles. Introducing dependencies might complicate things. Especially as your company grows and job positions evolve with time.
So you created Access Roles for a database, but new schemas and tables keep being added and you find yourself constantly updating its privileges? Then this tip is for you! You can grant privileges on future schemas in a database, as well as on future tables, views or other objects. So every time a new object is created, the grant is automatically added.
The usage of future grants in the creation of Access Roles will help minimise maintenance chores, by ensuring your roles have proper privileges when new database objects are created. No need to run new grant
statements every time your database gets new objects!
When a custom role is first created, it exists in isolation, and the same applies to any objects created by that role. By default, not even the ACCOUNTADMIN
role can modify or drop objects created by a custom role. So when a role has no parent, you risk creating objects in your account that will be accessible only by that role.
When granting a custom role to SYSADMIN
, you are ensuring that at least SYSADMIN
(and ACCOUNTADMIN
) roles will be able to manage objects created by that same role.
So to finalise, adopt the good practice of granting your Functional Roles to SYSADMIN
by default.
Snowsight has a great feature you can use to display the existing role hierarchy in the Snowflake account. You can use it to visualise the roles you create and how they connect.
Look for the Graph option under Users & Roles. Then you can use the Focus option to observe a specific role and its parent/child roles.
Or in other words, use appropriate roles for all administrative operations you perform. Remember the Principle of Least Privilege!
The ACCOUNTADMIN
role by default has way more privileges than the ones needed for your daily operations/chores, so you should avoid using it unless there is a good reason. Here is what you can use instead for common operations:
USERADMIN
!SECURITYADMIN
!SYSADMIN
!SYSADMIN
!SELECT
or an INSERT
), use an appropriate Functional Role!ACCOUNTADMIN
! Instead, create and use an adequate Service Role :)For example, the SELECTdbt-snowflake-monitoring package requires a special privilege IMPORTED PRIVILEGES
on the SNOWFLAKE
database, which can only be granted by the ACCOUNTADMIN
role. In this case, use USERADMIN
to create an Access Role for that single privilege, grant the privilege using ACCOUNTADMIN
, and use SECURITYADMIN
to grant the Access Role to a Service Role for dbt.
You are a Data Engineer working in a Data Team, and you are starting to work on implementing Snowflake in your organization. You are asked to manage 3 databases:
This is a brief description of the 3 databases:
HR_DB
contains data about employees and headcount;FINANCE_DB
contains data financial data like balance sheets and financial statements;REPORTING_DB
contains data for analytics and reporting purposes.Your task now is to ensure everyone in the company has the right access to these databases.
You asked different Snowflake users in your company about their needs, and collected the following requirements:
Use the following code to create the databases in Snowflake:
use role SYSADMIN ;
create database FINANCE_DB ;
create database HR_DB ;
create database REPORTING_DB ;
-- Create tables, views, ...
-- ...
With the requirements at hand, you can start by making a list to disclose exactly what you need.
For Functional Roles:
For Service Roles:
⚠️ Providing full READWRITE
permissions on all schemas of a database to a 3rd party tool like we are doing here (Finance SaaS) might not be the best idea in real life. In a more realistic scenario, the tool would only have access to a specific set of schemas where it needs to read from/write to. Remember this is a simplified model.
Based on the tables above, you can now see the Access Roles you need:
HR_READ_ROLE
HR_READWRITE_ROLE
FINANCE_READ_ROLE
FINANCE_READWRITE_ROLE
REPORTING_READ_ROLE
REPORTING_READWRITE_ROLE
As previously mentioned, READ
and READWRITE
roles will allow performing SELECT
or SELECT
, INSERT
, UPDATE
and DELETE
statements, respectively. This is what it looks like:
And these are the statements to execute in Snowflake:
-- Create Access Roles for HR_DB
use role USERADMIN ;
create role HR_READ_ROLE
comment = 'Access Role READ for HR_DB' ;
create role HR_READWRITE_ROLE
comment = 'Access Role READWRITE for HR_DB' ;
-- Grant Privileges to Access Roles for HR_DB
use role SECURITYADMIN ;
-- Granting Privileges to HR_READ_ROLE
-- Database
grant usage on database HR_DB to role HR_READ_ROLE ;
Note that you can leverage fixed naming conventions for Databases and Access Roles. You can create a single script and use templating to reuse and easily deploy multiple databases along with their respective Access Roles. Here is an example:
-- Assign values to the following variables accordingly
set database_name = 'HR_DB';
set role_read_name = 'HR_READ_ROLE';
set role_readwrite_name = 'HR_READWRITE_ROLE';
-- Create database
use role SYSADMIN ;
create database identifier($database_name) ;
-- Create Access Roles for the database
use role USERADMIN ;
create role identifier($role_read_name) ;
create role identifier($role_readwrite_name) ;
-- Grant Privileges to READ role
With Access Roles in place, you can easily create Functional Roles that represent the needs of the users. This is where we start looking at the requirements.
Picking up the ask of Richard:
🎯 Richard, a Data Analyst working with the Finance and HR teams, asks to be able to read all data from Finance and HR for his analyses. He also wants to be able to be able to write tables in the Reporting database.
This is what it looks like:
This is a simple implementation of Access and Functional Roles to address Richard’s needs. The role ANALYST_ROLE
is the Functional Role, and is the parent of HR_READ_ROLE
, FINANCE_READ_ROLE
, and REPORTING_READWRITE_ROLE
(all Access Roles).
Richard is then granted ANALYST_ROLE
. He can now query tables in HR_DB
and FINANCE_DB
, and can modify data in REPORTING_DB
. And the role can also be granted to anyone else in Richard’s team with similar responsibilities. 🎉
In Snowflake, things looks like this:
-- Create Functional Role ANALYST_ROLE
use role USERADMIN ;
create role ANALYST_ROLE
comment = 'Functional Role for Data Analysts' ;
-- Grants to role ANALYST_ROLE
use role SECURITYADMIN ;
grant role HR_READ_ROLE to role ANALYST_ROLE ;
grant role FINANCE_READ_ROLE to role ANALYST_ROLE ;
grant role REPORTING_READWRITE_ROLE to role ANALYST_ROLE ;
-- Grant Functional Role to SYSADMIN (good practice mentioned before)
grant role ANALYST_ROLE to role SYSADMIN ;
Now, applying the same principles for Lily:
🎯 Lily, a Data Engineer in your company, needs to work on loading historic data into the Finance and HR databases.
A similar example for a Data Engineering Functional role:
The setup is similar, but in this case, the role ENGINEER_ROLE
becomes the parent of HR_READWRITE_ROLE
and FINANCE_READWRITE_ROLE
, and is then granted to Lily.
The SQL looks like this:
-- Create Functional Role ENGINEER_ROLE
use role USERADMIN ;
create role ENGINEER_ROLE
comment = 'Functional Role for Data Engineers' ;
-- Grants to role ANALYST_ROLE
use role SECURITYADMIN ;
grant role HR_READWRITE_ROLE to role ENGINEER_ROLE ;
grant role FINANCE_READWRITE_ROLE to role ENGINEER_ROLE ;
-- Grant Functional Role to SYSADMIN (good practice mentioned before)
grant role ENGINEER_ROLE to role SYSADMIN ;
Now, working on the requirements for the new Finance SaaS:
🎯 The Finance Team is using a new SaaS tool, which needs to read data from both Finance and HR databases, and as a bonus, will write back some processed data back into the Finance database.
This is easy, right? Here is the diagram:
In this case, a new Service Role is created, called FINANCE_SAAS_ROLE
. It acts essentially as a Functional Role, with the only difference being that it will be used programmatically and not by a human being. The role inherits the privileges from FINANCE_READWRITE_ROLE
and HR_READ_ROLE
, and is granted to a Snowflake Service Account to be used by the service.
In code:
-- Create Service Role FINANCE_SAAS_ROLE
use role USERADMIN ;
create role FINANCE_SAAS_ROLE
comment = 'Service Role for Finance SaaS' ;
-- Grants to role FINANCE_SAAS_ROLE
use role SECURITYADMIN ;
grant role FINANCE_READWRITE_ROLE to role FINANCE_SAAS_ROLE ;
grant role HR_READ_ROLE to role FINANCE_SAAS_ROLE ;
-- Grant Service Role to SYSADMIN (good practice mentioned before)
grant role FINANCE_SAAS_ROLE to role SYSADMIN ;
Picking up the BI tool use case:
🎯 You are asked to set up a new BI and dashboards tool that will consume data from the Reporting database.
Business as usual?
In this case, the only Access Role that BI_ROLE
needs is REPORTING_READ_ROLE
. The BI tool will now be able to perform SELECT
queries to populate its dashboards! 🎉
In code:
-- Create Service Role BI_ROLE
use role USERADMIN ;
create role BI_ROLE
comment = 'Service Role for BI and Insights Tool' ;
-- Grants to role BI_ROLE
use role SECURITYADMIN ;
grant role REPORTING_READ_ROLE to role BI_ROLE ;
-- Grant Service Role to SYSADMIN (good practice mentioned before)
grant role BI_ROLE to role SYSADMIN ;
-- Grant role to service account
With all use cases covered, we have our initial role hierarchy complete! All together, this is how it looks like:
One of the benefits of having a DRY hierarchy is that it can be easily adjusted if new requirements show up. For example, imagine the Data Engineer Lily forgot to ask for READWRITE
access to REPORTING_DB
. This request can be answered quite easily:
-- Adjust DATA_ENGINEER functional role
use role SECURITYADMIN ;
grant role REPORTING_READWRITE_ROLE to role DATA_ENGINEER ;
You are now managing a hierarchy that can easily adapt to new business requirements! 🎉
In this article we provided some tips and tricks on how to create a Snowflake RBAC hierarchy, and included a real-life example, to make your Snowflake Admin life easier. Do you know about a good tip that is missing here? Let us know so we can update the article!
Miguel is a Data Engineer at Tasman Analytics, where he helps fast-growing companies enhance their data capabilities. With a background in BI and Data Analysis, he has gained valuable experience working in consultancy and product companies. More recently, driven by curiosity and a desire to gain more exposure to the technical aspects of the data lifecycle, he transitioned to Data Engineering. Miguel works daily with tools like Snowflake to help organizations build and optimize their data infrastructure.
Get up and running with SELECT in 15 minutes.
Gain visibility into Snowflake usage, optimize performance and automate savings with the click of a button.