.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
Doch selbst RBAC mit seiner Skalierfähigkeit kommt an seine Grenzen, sobald mit einem <a href="https://www.taod.de/services/data-engineering-consulting“ data-webtrackingID="blog_content_link" > Modern Data Stack </a> gearbeitet wird. Durch Ingestion Tools werden hunderte Quellen angebunden und ELT-Tools wie <a href="https://www.taod.de/tech-beratung/dbt-labs“ data-webtrackingID="blog_content_link" > dbt </a bauen mit Leichtigkeit ein Data Warehouse mit mehreren hundert Tabellen in einer Handvoll Schemas. Das manuelle Verwalten mehrerer Ressourcenrollen für allein jede Datenbank und jedes Schema wird in solchen Szenarien zu einer fehleranfälligen Fleißarbeit.
Der mit Abstand lästigste Aufwand bei der Umsetzung von RBAC in einem so mächtigen Cloud Data Warehouse wie <a href="https://www.taod.de/tech-beratung/snowflake“ data-webtrackingID="blog_content_link" > Snowflake </a> ist die Erstellung und Rechteverwaltung der zahlreichen Access Roles. Zum Glück ist diese immer extrem formelhaft. Engineers müssen nur einmal definieren, welche Gruppen von Rechten üblicherweise auf einen Ressourcentyp gebündelt genutzt werden, und erstellen eine entsprechende Access Role für jede Gruppierung und Instanz dieses Typs. So können wir beispielsweise die MANAGE Access Role auf einer Datenbank definieren, als Gruppierung der Privilegien MODIFY, USAGE und MONITOR. Für jede Datenbank würden wir dann eine Access Role namens AR_DATENBANKNAME_MANAGE erstellen, um genau diese drei Privilegien auf dieser Datenbank gebündelt zuweisen zu können. Analog lassen sich Access Roles auf Schemas, Data Warehouses, Resource Monitors und Storage Integrations definieren.
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.
Das <a href="https://www.taod.de/services“ data-webtrackingID="blog_content_link" > von taod eigens hierzu entwickelte </a> Snowflake RBAC Terraform-Modul übernimmt die komplette Arbeit der Ressourcen-Erstellung. Als eine Art Blaupause für den Snowflake Account muss lediglich die gewünschte Struktur hierarchisch in einer Yaml-Datei aufgezeichnet werden. Relevant hierfür sind die Functional Roles inklusive der zuzuordnenden Snowflake-Nutzer und alle Datenbankobjekte, auf die Access Roles vergeben werden sollen. Außerdem können zu ebendiesen Objekten die vom Modul vorgegebenen Access Role Blaupausen zugeordnet werden (hier sind create_schema und manage Access Roles auf Datenbankebene).
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
