Snowflake Access Control — Part-2

In Part 1 I covered the fundamental aspects of Snowflake Access Control.

In this guide, we will walk through the step-by-step process of creating an optimal Snowflake Access Control system.

Before diving deep into Access Control, it’s important to understand the various features Snowflake offers across its different editions. Each edition provides different capabilities, and selecting the right one for your organization will impact the level of security, governance, and performance available. Understanding these features is crucial for designing an effective access control system tailored to your business needs.

Snowflake Edition — Enterprise Edition

Below are some key features available in the Enterprise Edition that are not included in the Standard Edition:

Extended Time Travel: With the Enterprise Edition (and above), you can extend your data retention period from the default of 1 day to up to 90 days, enhancing your data management and recovery options.[Reference]

Enhanced Data Protection: Benefit from periodic rekeying of encrypted data. Snowflake automatically rotates Snowflake-managed keys every 30 days. Retired keys are securely destroyed once they are no longer needed, ensuring continuous data protection.[Reference]

User Access Auditing: Access detailed auditing capabilities through the Account Usage view, which tracks user access history to Snowflake objects (e.g., tables, views, columns) for up to 365 days, offering valuable insights into data access patterns.[Reference]

Multi-Cluster Warehouses: Efficiently manage compute resources with multi-cluster warehouses that automatically scale to handle varying user and query concurrency during peak and off-peak hours.[Reference]

Early Access for Testing: Enjoy 12-hour early access to weekly releases, allowing you to test and validate changes before they are deployed to your production accounts.[Reference]

Snowflake Edition — Business Critical Edition

If a customer opts for Customer Managed Keys (CMK), the Snowflake Business Critical Edition is required.

Customer Managed Keys (CMK): In the Business Critical Edition, customers can combine their own customer-managed key with a Snowflake-managed key to create a composite master key, known as Tri-Secret Secure, offering an additional layer of security.[Reference]

Account

In many enterprise applications, customers are spread across various geographical locations. To ensure compliance with local regulations, accounts should be created based on the customer’s location. This approach not only helps in adhering to data residency laws, which increasingly mandate that customer data stays within specific regions, but also reduces latency, leading to a seamless business experience.

By organizing accounts geographically, companies can better manage customer data, improve performance, and remain compliant with regional data protection laws.

Account creation can be streamlined during the bootstrapping phase by having prior knowledge of customer locations and regions where the company plans to sell its product. This proactive approach ensures that the necessary accounts are set up in advance, aligned with customer geographies, and prepared to meet local regulatory requirements. It also allows for smoother onboarding and more efficient expansion into new markets.

Product Admin Role with User

To ensure security and adhere to the least privilege principle, we must create custom roles that allow an admin user to perform only the necessary operations specific to the product. We should avoid granting broad roles like ACCOUNTADMIN ,SYSADMIN, SECURITYADMIN or USERADMINas they provide excessive permissions that may not be needed and could impact other products sharing the same Snowflake account.

Key Considerations for Role Creation:

• Granularity: Each role is tailored to a specific function, limiting permissions to only what is required.
• Modularity: Roles can be easily managed and extended without affecting other parts of the system.
• Security: By sticking to the least privilege principle, we minimize the risk of unnecessary access or misuse.

Role Creation

We are granting all necessary permissions to the role “sf_product_admin_role,” which will be used to create resources and manage permissions without relying on any of the default system roles provided by Snowflake.

Below, we are creating roles for warehouse management and database creation.

-- Use a higher-level role to create and manage roles (but not assigned to users)
USE ROLE ACCOUNTADMIN;

-- Create a role for managing all product-specific permissions
CREATE ROLE sf_product_admin_role;

-- Create a role for managing warehouses
CREATE ROLE sf_product_wh_manage_role;

-- Create a role for creating and managing databases
CREATE ROLE sf_product_db_create_role;

Granting permissions to Roles

We are granting permissions for creating databases and warehouses, as well as managing users and roles. Once these roles are established, we will assign them to the sf_product_admin_role.

It’s important to note that with the sf_product_admin_role, we can only grant the permissions specifically assigned to that role. This is a significant security measure, as it prevents users from accessing anything outside their designated permissions. Additionally, we can create new roles with specific permissions and assign them to any new users as needed.

-- Grant necessary privileges to each role following the least privilege principle
GRANT CREATE DATABASE ON ACCOUNT TO ROLE sf_product_db_create_role;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE sf_product_wh_manage_role;
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE sf_product_wh_manage_role;

-- Grant user management and role creation privileges to the admin role
GRANT CREATE USER ON ACCOUNT TO ROLE sf_product_admin_role;
GRANT CREATE ROLE ON ACCOUNT TO ROLE sf_product_admin_role;

-- Grant the warehouse and database roles to the product admin role
GRANT ROLE sf_product_wh_manage_role TO ROLE sf_product_admin_role;
GRANT ROLE sf_product_db_create_role TO ROLE sf_product_admin_role;

User Creation and Role Assignment

-- Optionally, create a user for the product admin and assign the custom role
CREATE USER sf_product_admin_user PASSWORD = '<password>';
GRANT ROLE sf_product_admin_role TO USER sf_product_admin_user;

This setup ensures that admin users have sufficient permissions to manage the product but do not inadvertently affect other applications or resources sharing the same Snowflake account.

In the overview above, I demonstrated how to create roles using the least privilege principle. Permissions are granted to roles, not directly to users.

To grant users the necessary access, roles are assigned to them, allowing users to inherit the permissions associated with those roles. This approach promotes a more secure and manageable method for controlling access while minimizing unnecessary permissions.