Snowflake Access Control — Part-1

Snowflake is a cloud-native data platform that offers a scalable, high-performance solution for data warehousing. It runs on major cloud providers like AWS, Azure, and Google Cloud, enabling flexibility and avoiding vendor lock-in. Snowflake is widely used for analytics, data sharing, and building modern data applications with ease.

Recently, I received a requirement to provision and ingest data into Snowflake for customers with varying packages or licenses. The challenge was that these customers had different usage patterns and requirements, making it inefficient to share the same Snowflake cluster across all of them. Each customer needed a tailored solution based on their workload, data size, and processing needs.

One key aspect was dynamic warehouse management. Since not all customers required the same level of compute resources, we needed to allocate and scale warehouses flexibly. This ensured that larger customers received the necessary resources to handle their workloads, while smaller customers used minimal resources, optimizing both performance and cost.

Additionally, for development and staging environments, we couldn’t afford to use the same high-cost infrastructure as production. These instances required cost-effective, lower-tier resources since they were primarily used for testing and non-critical tasks. Balancing resource allocation between production, development, and staging environments while minimizing costs was essential for an efficient and scalable solution.

For this use case, access control is crucial to ensure data security, privacy, and resource management.

Requirements

Dynamic User and Role Creation: Ability to easily add new users and roles as needed, allowing for flexibility in user management.

Principle of Least Privilege: Roles should be created to provide only the necessary permissions for users to perform their tasks. This minimizes security risks by limiting access to sensitive data.

Warehouse Management : This refers to the ability to adjust the size of compute resources (warehouses) based on customer needs, which can include scaling up (increasing resources) or down (decreasing resources). This allows warehouses to be assigned either to multiple users or to a single user, facilitating tailored access to compute resources.

Defined Roles for Databases, Schemas, and Tables: Specific roles should be established for different levels of data organization within Snowflake, ensuring that users have appropriate access based on their roles.

Scripts or DDLs for Multiple Customers: This requirement emphasizes the need for automation and efficiency, with scripts or Data Definition Language (DDL) commands designed to work across different customer environments.

User Management: Establish separate users for distinct use cases, such as ingestion, querying, DDL execution, and monitoring.

Role-Based Access Control

In Snowflake, implementing role-based access control (RBAC) involves defining different roles to manage access for administrators, developers, and end-users. Roles related toWAREHOUSE, DATABASE, SCHEMA, TABLES are created to control access to specific Snowflake objects such as databases, schemas, tables, warehouses, or other features. This ensures that sensitive data and resources are protected from unauthorized access. Additionally, only users with the appropriate roles can perform administrative actions, such as creating or managing warehouses and customer-specific data, reducing the risk of accidental or inappropriate changes.

Customer-Specific Access

To enhance data protection and ensure compliance with industry regulations like GDPR, it is essential to create isolated access environments for each customer based on their package or license. This can be achieved by utilizing separate Snowflake accounts or databases, allowing for strict database-level segregation. This setup prevents data overlap and accidental exposure, accommodating the varying access levels and data security requirements of different customers while minimizing the risk of unauthorized access or data breaches.

Dynamic Warehouse Access

To optimize resource utilization and minimize costs, it is important to grant warehouse access dynamically based on customer size and usage. This approach involves controlling who can scale, start, or stop warehouses for specific customers, ensuring that compute power is allocated efficiently according to actual needs. By implementing this strategy, organizations can prevent resource misuse and unnecessary expenses while effectively managing their computing resources.

By setting up robust access control, we ensure that the system is secure, compliant with regulations, and optimized for both resource management and cost efficiency.

Common Terms in Snowflake

A securable object refers to an entity that can have access permissions granted to it; access is denied unless a grant explicitly allows it.

A role is an entity to which privileges can be assigned, and these roles can be allocated to users. Additionally, roles can be assigned to other roles, forming a hierarchical structure.

A privilege represents a specific level of access to an object, allowing for various distinct privileges to be utilized to fine-tune the granularity of access granted.

A user is an identity recognized by Snowflake, which can be associated with either an individual or a program.

Snowflake Securable Objects

Every securable object resides within a logical container in a hierarchy of containers. The top-most container is the customer organization. Securable objects such as tables, views, functions, and stages are contained in a schema object, which are in turn contained in a database.

Below is the Object Hierarchy in Snowflake.

Default Snowflake Account Roles

In Snowflake, Roles are structured in a hierarchical manner, allowing for different levels of access and permissions.

ACCOUNTADMIN

The ACCOUNTADMIN role has the highest level of privileges in Snowflake and can perform any task.

Permissions
— All permissions of USERADMIN, SECURITYADMIN, and SYSADMIN.
— Manage billing, account settings, and overall account configuration.
— Create and manage other roles and users.

SYSADMIN

The SYSADMIN role is primarily responsible for managing Snowflake objects, such as databases, schemas, and warehouses.

Permissions
— Create, modify, and drop databases, schemas, and warehouses.
— Grant privileges on those objects to other roles.
— Typically has full access to a specific database.

SECURITYADMIN

The SECURITYADMIN role manages security-related tasks, including permissions and access control.It also inherits privileges from the USERADMIN role.

Permissions
— Grant and revoke access privileges on objects.
— Manage network policies, resource monitors, and object ownership.
— Oversee role hierarchy and access levels.

USERADMIN

The USERADMIN role is responsible for managing users and roles within Snowflake.

Permissions
— Create, modify, and drop users and roles.
— Grant roles to users.
— Manage user access to databases and warehouses.

PUBLIC

This is the default role assigned to all users. It includes basic access to Snowflake objects, and any object that is shared publicly can be accessed by all users.This role is typically used when explicit access control is unnecessary, treating all users equally concerning their access rights.

Permissions
— Limited; typically read access to publicly shared objects.

Role Hierarchy

ACCOUNTADMIN
├── SECURITYADMIN
│ ├── USERADMIN
│ └── PUBLIC
└── SYSADMIN

Key Points

• Inheritance: Roles inherit permissions from their parent roles, which means that users with a higher role (e.g., ACCOUNTADMIN) have all the permissions of the roles below it in the hierarchy.
• Role Assignment: Users can be assigned multiple roles, allowing for flexible permission management.
• Security Best Practices: It is a best practice to grant users the least privilege necessary for their tasks to enhance security.

In the next section, we will utilize the information above to create various roles and users that meet the specified requirements.