Welcome to Tech Station, SDG Group’s hub for uncovering the latest innovations in data and analytics! In this article, we explore a powerful combination: Permifrost and Snowflake. We will demonstrate how to use Permifrost, an open-source tool, to manage Snowflake permissions declaratively through YAML files. This "Permissions as Code" approach not only automates governance but also eliminates unwanted grants, strengthens security, and makes the entire data environment easier to maintain and audit.
Looking for something else? Check all of our content here.
In the ever-evolving world of data engineering, maintaining data governance, security, and efficiency is paramount.
As data teams grow and the volume of data increases, it becomes increasingly challenging to manage permissions and maintain a clean, secure data environment.
Often, companies migrating to platforms like Snowflake face issues such as:
Inconsistent Permissions: Different teams with varying levels of access often lead to overly permissive grants.
Manual Management: Manually assigning permissions is slow and prone to human error.
Audit Difficulties: Tracking and auditing permissions across the environment becomes a complex task, increasing security risks.
This article explores a powerful solution to these challenges by combining Permifrost and Snowflake to enhance maintainability and security.
Integrating Permifrost with Snowflake transforms permission management into a version-controlled, automated process.
This article will provide a practical guide focusing on implementing points 1 and 4 of the described solution.
Let's see how to do it. Here are the prerequisites:
Access to a Snowflake account with the SECURITYADMIN role.
Permifrost installed (pip install permifrost).
Git installed and a GitHub (or other Git repository) account.
First, let's create the necessary objects in Snowflake.
|
Create a Database and a Scheme: -- example:
USE ROLE securityadmin; CREATE DATABASE refactoring; USE DATABASE refactoring; CREATE SCHEMA sbellesi; Create Roles and Users: Define the roles and users that will require permissions. --example:
|
|
Initialize Permifrost Repository: Create a new Git repository to manage your Snowflake permissions. mkdir permifrost_repo
Define Permissions in YAML: Create a # example
# Databases databases: - refactoring: shared: false # put false if your db isn't a shared object! # Roles roles: - analyst_role: warehouses: - compute_wh privileges: databases: read: - refactoring schemas: read: - refactoring.* tables: read: - refactoring.sbellesi.* - engineer_role: warehouses: - compute_wh privileges: databases: read: - refactoring write: - refactoring schemas: read: - refactoring.* write: - refactoring.* tables: read: - refactoring.sbellesi.* write: - refactoring.sbellesi.* # Users # can_login is required the rest of the parameters are optional # None of the values are validated in Snowflake, hence default_warehouse, default_namespace and default_role # can contain invalid values users: - analyst_user: can_login: true member_of: - analyst_role has_password: true - engineer_user: can_login: true member_of: - engineer_role has_password: true # Warehouses # Warehouse sizes are informative and not altered by Permifrost to align with the spec file warehouses: - compute_wh: size: x-small meta: some_key: some_value: can_login: true member_of: - engineer_role has_password: true # Warehouses # Warehouse sizes are informative and not altered by Permifrost to align with the spec file warehouses: - compute_wh: size: x-small meta: some_key: some_value |
Connecting Permifrost to Snowflake: We will use the connection via Username and Password by setting environment variables.
| PERMISSION_BOT_USER='SNOWFLAKE_USER_NAME' PERMISSION_BOT_ACCOUNT='SNOWFLAKE_ACCOUNT' PERMISSION_BOT_WAREHOUSE='SNOWFLAKE_WAREHOUSE' PERMISSION_BOT_PASSWORD='SNOWFLAKE_PASSWORD' PERMISSION_BOT_DATABASE='SNOWFLAKE_DATABASE' PERMISSION_BOT_ROLE='SECURITYADMIN' |
SECURITYADMIN. If you attempt to run it with a different role, you will encounter an error indicating insufficient privileges.
The error you will get if you try to run Permifrost with another role
Apply Permissions: We suggest running the command with the --dry option first to check the configuration and preview the queries.
--general command
Confirming spec loads successfully |
To appreciate the change, you can run the following commands in Snowflake before and after applying Permifrost:
|
show grants to role engineer_role; Now, to demonstrate how Permifrost handles removing permissions, let's modify the # permifrost.yml (versione modificata) |
Running the dry run again will now show the REVOKE commands needed to align the environment, demonstrating that Permifrost has identified and is preparing to remove the excess privileges.
| Confirming spec loads successfully Loading spec file Checking spec file for errors ... SQL Commands generated for given spec file: [PENDING] REVOKE monitor, create schema ON database refactoring FROM ROLE engineer_role; [PENDING] REVOKE monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON FUTURE schemas IN database refactoring FROM ROLE engineer_role; [PENDING] REVOKE monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON schema refactoring.public FROM ROLE engineer_role; [PENDING] REVOKE monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON schema refactoring.sbellesi FROM ROLE engineer_role; [PENDING] REVOKE insert, update, delete, truncate, references ON FUTURE tables IN schema refactoring.sbellesi FROM ROLE engineer_role; ... |
By following this tutorial, you have successfully integrated Permifrost and Snowflake to manage permissions as code, ensuring a secure and maintainable data environment.
This setup simplifies permission management and enhances security by removing unwanted grants and ensuring consistent access controls.
For data teams facing similar challenges, adopting this approach can significantly improve their data governance and security posture, paving the way for more efficient and secure data operations.
Want to simplify permission management and strengthen the security of your Snowflake environment? Contact us for a personalized consultation and discover how the "Permissions as Code" approach with Permifrost can automate your data governance, ensuring consistent, auditable, and error-proof access control.