Automated cloud infrastructure with Terraform

Managing Role-Based Access Control in Snowflake with Terraform

Data engineers should cry with happiness: The role-based access control in the powerful cloud data warehouse Snowflake can degenerate into annoying hard work. The Terraform module developed by taod enables automated and secure management of cloud infrastructure. A technical insight.

Increasing compliance guidelines and data protection regulations as well as constantly growing data warehouses with numerous resources are pushing traditional security concepts more and more to their limits. Role-Based Access Control (RBAC) is therefore becoming increasingly popular as a robust and scalable system. Here, users and rights to database objects (DBOs) are grouped into roles. The aim is simplified administration and clearly defined security structures, which can still be flexibly and efficiently adapted. Users are only ever assigned user roles (functional roles). Resource roles (access roles), on the other hand, define specific privileges on database objects. For example, an access role can combine READ, WRITE and UPDATE rights to a database. Resource roles are then assigned to user roles.

In this way, you bundle multiple privileges to a DBO in a resource role, multiple resource roles in a user role, and multiple users in the same user role. These levels of abstraction not only make it easier to manage permissions, they are also less prone to errors than manually assigning all direct user rights to DBOs. In addition, RBAC is scalable, meaning that significantly less effort is required for new users or resources compared to traditional security concepts.

The challenge: scaling limits of manual administration

But even RBAC, with its scalability, reaches its limits as soon as with a Modern Data Stack work is being done. Ingestion tools connect hundreds of sources and ELT tools such as dbt Build a data warehouse with several hundred tables in a handful of schemas with ease. Manually managing multiple resource roles for each database and schema alone becomes error-prone hard work in such scenarios.

By far the most annoying effort involved in implementing RBAC in such a powerful cloud data warehouse as Snowflake is the creation and rights management of the numerous access roles. Fortunately, this is always extremely formulaic. Engineers only need to define once which groups of rights are usually bundled together on a resource type and create a corresponding access role for each group and instance of that type. For example, we can define the MANAGE Access Role on a database as a grouping of MODIFY, USAGE, and MONITOR privileges. For each database, we would then create an access role called AR_DATABASENNAME_MANAGE in order to be able to assign exactly these three privileges in a bundle to that database. Similarly, access roles can be defined on schemas, data warehouses, resource monitors, and storage integrations.

The taod Terraform module: Infrastructure as Code for Snowflake

That's where Terraform comes in. This infrastructure as code (IaC) tool enables automated and consistent deployment and management of cloud infrastructure. The declarative syntax allows you to define the desired resources in Snowflake without worrying about deployment details. Of course, Terraform can also describe reusable modules that can automatically create similar, formulaic resources such as Access Roles.

The Snowflake RBAC Terraform module developed by taod specifically for this purpose does the entire work of creating resources. As a kind of blueprint for the Snowflake account, all you need to do is record the desired structure hierarchically in a yaml file. The functional roles, including the assignable Snowflake users and all database objects to which access roles are to be assigned, are relevant for this. In addition, the access role blueprints specified by the module can be assigned to these same objects (here are create_schema and manage access roles at database level).

The specific rights of an access role are defined in the module for each type of DBO: The module also adopts all the logic surrounding naming conventions of access roles and functional roles. In the example above, the access roles AR_CREATE_SCHEMA__DB_DEV and AR_MANAGE__ DB_DEV would be created for the db_dev database, with the dbt_developer user roles being assigned both access roles and data_loader only the latter.

As a result of the hierarchical inheritance of roles in Snowflake, the user “alice” with the data_ loader role now has the create_schema access role with USAGE, MONITOR and CREATE SCHEMA privileges on the db_dev database. The module draws this blueprint from resources and translates it into the appropriate commands in Snowflake to implement the blueprint as described. It is not only roles and rights that are automatically managed. After using the module, all database objects are also the watchful eye of Terraform. As a result, Snowflake users have a robust security concept and benefit from all the benefits of Terraform as an infrastructure-as-code tool.

This article was first published in a modified form in issue 02/23 of our magazine data! You can find all issues and articles here:

Data! Magazine: Cloud Services, Data Analytics & AI | taod

‍