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.

Tip 1. Start with the Principle of Least Privilege

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:

• It lowers the risk and impact of external threats: restricting user access to only what is necessary reduces the risk of accidental or malicious data breaches. And in case a breach does happen, the damage is confined to the areas of the database that the compromised account(s) can access.
• It minimises the risk of system corruption and increases stability: limiting access will help prevent changes that could affect data integrity or stability; team members with limited exposure should not have permission to drop databases! :)
• It facilitates auditing and monitoring: ensuring the minimum privileges are in place also means fewer privileges to monitor and maintain;
• It helps simplify compliance: this principle will also facilitate complying with regulatory requirements around data protection - when applicable -, such as GDPR or HIPAA, by limiting access to sensitive data to the strictly necessary.

Tip 2. Access, Functional and Service Roles

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:

• Access Roles are used to control access to your databases;
• Functional Roles are granted to Snowflake users in your company, and levels of access are controlled by granting the required Access Roles to them (according to the Principle of Least Privilege);
• Service Roles work the exact same way as Functional Roles, with the only difference that they are destined for Services and not for end (human) users.

Note that there is no technical difference between Access, Functional and Service roles. All of them are created as Account Roles.

Access 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.

Functional Roles

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).

Service Roles

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.

Tip 3. Create a DRY 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…

⛔ 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.

Tip 4. Leverage Future Grants while creating your Access Roles

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!

Tip 5. Grant Functional Roles to SYSADMIN

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.

Tip 6. Use the Snowflake UI to help

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.

Tip 7. You probably don't need ACCOUNTADMIN for that…

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:

• If you need to create users or roles, use USERADMIN!
• If you need to manage grants (e.g. grant a role to a user), use SECURITYADMIN!
• If you need to create objects such as databases, schemas, or virtual warehouses, use SYSADMIN!
• If you need to create databases or database objects such as tables, views or any other database objects, you can use SYSADMIN!
• If you want to perform ad-hoc data operations on a given table ( like a SELECT or an INSERT ), use an appropriate Functional Role!
• Finally, if you are looking to connect a 3rd party service to Snowflake, by no means use ACCOUNTADMIN! Instead, create and use an adequate Service Role :)

Applying what we learned

Context

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.

Requirements

You asked different Snowflake users in your company about their needs, and collected the following requirements:

1. 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 create reporting tables in the Reporting database.
2. Lily, a Data Engineer in your company, needs to work on loading historical data into the Finance and HR databases.
3. 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 some processed data back into the Finance database.
4. You are asked to set up a new BI and dashboard tool that will consume data from the Reporting database.

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, ...
-- ...

Steps

Define the Functional and Service Roles

With the requirements at hand, you can start my making a list to disclose what you exactly 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.

Create Access Roles

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 ;

-- Existing objects
grant usage on all schemas in database HR_DB to role HR_READ_ROLE;
grant select on all tables in database HR_DB to role HR_READ_ROLE;

-- Future objects
grant usage on future schemas in database HR_DB to role HR_READ_ROLE ;
grant select on future tables in database HR_DB to role HR_READ_ROLE ;

-- Granting Privileges to HR_READWRITE_ROLE
-- Database
grant usage on database HR_DB to role HR_READWRITE_ROLE ;

-- Existing objects
grant usage on all schemas in database HR_DB to role HR_READWRITE_ROLE;
grant select, insert, update, delete on all tables in database HR_DB to role HR_READWRITE_ROLE;

-- Future objects
grant usage on future schemas in database HR_DB to role HR_READWRITE_ROLE ;
grant select, insert, update, delete on future tables in database HR_DB to role HR_READWRITE_ROLE ;

-- (...repeat for FINANCE_DB and REPORTING_DB)

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
use role SECURITYADMIN ;
grant usage on database identifier($database_name) to role identifier($role_read_name) ;

-- (...)

Create Functional Roles

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 ;

-- Grant role to Richard
use role USERADMIN ;
grant role ANALYST_ROLE to user RICHARD ;

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 ;

-- Grant role to Lily
use role USERADMIN ;
grant role ENGINEER_ROLE to user LILY ;

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 ;

-- Grant role to service account
use role USERADMIN ;
grant role FINANCE_SAAS_ROLE to user FINANCE_SAAS_SA ;

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
use role USERADMIN ;
grant role BI_ROLE to user BI_USER ;

Wrapping it up

With all use cases covered, we have our initial role hierarchy complete! All together, this is how it looks like:

Bonus - Dealing with Changes and Adjustments

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! 🎉

Conclusion

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!

About Tasman Analytics

Tasman is a boutique analytics consultancy transforming disorganised data into meaningful business value. We think businesses deserve data platforms that actually make a difference.

Our data expertise spans analytics, business intelligence, & data science. With offices in the UK and the Netherlands, we've been serving clients across Europe since 2017.

We base our work on three essential pillars:

1. PEOPLE: We’ll teach our clients exactly what they need and how to build a high-performing data team. In the meantime, we’ll set up the right foundations for their data process, culture, and ways of working so your new team will begin with a head start.
2. TECH: We’ll build a modern data stack to give our clients a single source of truth, tailored to their needs and based on industry best practices.
3. INSIGHTS: We’ll help our clients define and interpret the metrics that matter so they can have a strategic view of their business. Once we know what works, we’ll create reliable reporting dashboards and self-service tools so they can prioritise, decide, and act faster with confidence.

Discover more about our approach and how we can accelerate your data journey via [email protected] or on our website at tasman.ai!