.webp)
Managing Role-Based Access Control in Snowflake with Terraform
Data engineers should be weeping with happiness: role-based access control in the powerful Snowflake cloud data warehouse can become a tedious chore. The Terraform module developed by taod enables the 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 conventional security concepts to their limits. Role-based access control (RBAC) is therefore becoming increasingly popular as a robust and scalable system. Users and rights to database objects (DBOs) are grouped into roles. The aim is simplified administration and clearly defined security structures that can still be adapted flexibly and efficiently. Users are only ever assigned user roles (functional roles). Resource roles (access roles), on the other hand, define specific privileges to database objects. For example, an access role can combine READ, WRITE and UPDATE rights to a database. Resource roles are then assigned to the user roles.
In this way, you bundle several privileges on a DBO in a resource role, several resource roles in a user role and several users in the same user role. These levels of abstraction not only make it easier to manage authorizations, they are also less error-prone 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 conventional security concepts.
Limits of RBAC
But even RBAC, with its scalability, reaches its limits as soon as you start working with a modern data stack. Ingestion tools connect hundreds of sources and ELT tools like dbt easily build a data warehouse with several hundred tables in a handful of schemas. Manually managing multiple resource roles for each database and schema alone becomes an error-prone chore in such scenarios.
By far the most tedious part of implementing RBAC in a cloud data warehouse as powerful 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 typically used bundled on a resource type and create a corresponding Access Role for each grouping and instance of that type. For example, we can define the MANAGE Access Role on a database as a grouping of the MODIFY, USAGE and MONITOR privileges. We would then create an Access Role called AR_DATENBANKNAME_MANAGE for each database in order to be able to assign precisely these three privileges to this database in a bundle. Similarly, access roles can be defined for schemas, data warehouses, resource monitors and storage integrations.
Infrastructure as Code with Terraform
This is where Terraform comes into play. This Infrastructure as Code (IaC) tool enables the automated and consistent provisioning and management of cloud infrastructure. The declarative syntax allows you to define the desired resources in Snowflake without having to worry about the details of provisioning. Of course, reusable modules can also be described in Terraform, which can be used to automatically create similar, formulaic resources such as Access Roles.
The Snowflake RBAC Terraform module developed by taod specifically for this purpose takes care of all the work involved in creating resources. As a kind of blueprint for the Snowflake account, only the desired structure needs to be recorded hierarchically in a Yaml file. Relevant for this are the functional roles including the Snowflake users to be assigned and all database objects to which Access Roles are to be assigned. In addition, the Access Role blueprints specified by the module can be assigned to these objects (here create_schema and manage Access Roles are at database level).
The specific rights that an Access Role has are defined in the module for each type of DBO: The module also handles all the logic surrounding the naming conventions of the 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 database db_dev, whereby the user role dbt_developer is assigned both Access Roles and data_loader only the latter.
Due to the hierarchical inheritance of roles in Snowflake, the user "alice" with the data_ loader role now has the access role create_schema with the USAGE, MONITOR, and CREATE SCHEMA privileges on the db_dev database. The module pulls this schema from resources and translates it into the corresponding commands in Snowflake to implement the schema as described. Not only the roles and rights are managed automatically. All database objects are also subject to the watchful eye of Terraform after the module has been deployed. As a result, Snowflake users have a robust security concept and benefit from all the advantages that Terraform brings as an infrastructure-as-code tool.
This article first appeared 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
