Creating custom roles in Snowflake - Mechanics & Internals

Overview - Creating custom roles

What is it?

Creating custom roles in Snowflake means making your own named groups of permissions that control what users can do. Instead of using only the built-in roles, you design roles that fit your organization's needs. These roles help manage access to data and actions in a clear and organized way. Custom roles let you give just the right permissions to the right people.

Why it matters

Without custom roles, managing who can see or change data becomes messy and risky. Everyone might get too many permissions or too few, causing security problems or work delays. Custom roles solve this by letting you tailor access carefully, improving security and teamwork. This keeps your data safe and your team efficient.

Where it fits

Before learning custom roles, you should understand basic Snowflake concepts like users, roles, and privileges. After mastering custom roles, you can explore role hierarchies, role-based access control (RBAC), and automation of permissions. This topic is a key step in managing Snowflake security and governance.

Mental Model

Core Idea

A custom role is a named set of permissions that you create to control exactly what users can do in Snowflake.

Think of it like...

Creating custom roles is like making job titles in a company, where each title has specific tasks and responsibilities assigned to it.

┌───────────────┐
│   Custom Role │
│ ┌───────────┐ │
│ │ Privilege │ │
│ │ 1         │ │
│ │ Privilege │ │
│ │ 2         │ │
│ │ ...       │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│     User      │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Snowflake Roles Basics

Concept: Learn what roles are and how they control access in Snowflake.

In Snowflake, a role is like a container for permissions. These permissions let users do things like read data, write data, or manage objects. Snowflake has built-in roles like SYSADMIN and PUBLIC, but you can create your own. Roles help keep access organized and secure.

Result

You understand that roles group permissions and are assigned to users to control what they can do.

Knowing that roles are the main way Snowflake controls access helps you see why creating custom roles is powerful.

2

Foundation

Privileges and Their Purpose

Concept: Discover what privileges are and how they relate to roles.

Privileges are specific rights to perform actions, like SELECT on a table or USAGE on a warehouse. Roles hold these privileges. When a user has a role, they get all its privileges. This system lets you control access precisely by choosing which privileges go into each role.

Result

You see that privileges are the building blocks of roles and control what actions are allowed.

Understanding privileges as the smallest access units clarifies how roles control user capabilities.

3

Intermediate

Creating a Custom Role Step-by-Step

🤔Before reading on: do you think creating a role requires assigning privileges immediately or can it be done separately? Commit to your answer.

Concept: Learn how to create a custom role and assign privileges to it.

To create a custom role, use the SQL command: CREATE ROLE role_name; This makes an empty role. Then, grant privileges to it using GRANT privilege ON object TO ROLE role_name; You can add many privileges as needed. Finally, assign the role to users with GRANT ROLE role_name TO USER user_name;

Result

You can create a custom role, give it permissions, and assign it to users.

Knowing that roles can be created empty and privileges added later gives flexibility in managing access.

4

Intermediate

Role Hierarchies and Inheritance

🤔Before reading on: do you think roles can contain other roles to inherit permissions? Commit to yes or no.

Concept: Understand how roles can be organized in a hierarchy to inherit privileges.

Snowflake lets you grant one role to another role. This means the higher role inherits all privileges of the lower role. For example, if Role A has Role B granted to it, Role A gets all Role B's privileges. This helps build layered access control, like junior and senior roles.

Result

You can create role hierarchies that simplify permission management and reuse.

Understanding role inheritance helps manage complex permissions without repeating grants.

5

Intermediate

Best Practices for Naming and Organizing Roles

Concept: Learn how to name and structure roles for clarity and security.

Use clear, descriptive names for roles, like 'data_analyst' or 'finance_readonly'. Group privileges logically, avoid giving too many permissions to one role, and use role hierarchies to reduce duplication. Regularly review roles to remove unused ones and keep access tight.

Result

Your roles are easy to understand and maintain, reducing security risks.

Good naming and organization prevent confusion and accidental over-permission.

6

Advanced

Automating Role Management with SQL Scripts

🤔Before reading on: do you think role creation and privilege grants can be automated in Snowflake? Commit to yes or no.

Concept: Explore how to automate creating and updating roles using scripts.

You can write SQL scripts that create roles, grant privileges, and assign roles to users. This helps when you have many roles or need to update permissions regularly. Using scripts ensures consistency and saves time. For example, a script can create a 'marketing_readonly' role and grant SELECT on marketing tables.

Result

You can manage roles efficiently at scale with automation.

Knowing automation reduces human error and speeds up permission management in large environments.

7

Expert

Security Implications and Least Privilege Principle

🤔Before reading on: do you think granting broad privileges to custom roles is safer or riskier? Commit to your answer.

Concept: Understand how to design roles to minimize security risks by granting only needed permissions.

Granting only the minimum privileges needed for a role follows the least privilege principle. This limits damage if a user account is compromised. Avoid giving roles excessive rights like OWNERSHIP unless necessary. Regularly audit roles and privileges to detect and fix over-permission. Use role hierarchies carefully to avoid unintended privilege escalation.

Result

Your custom roles improve security by limiting access to what is strictly necessary.

Understanding least privilege helps prevent data breaches and accidental misuse.

Under the Hood

Snowflake stores roles as metadata objects that link users to privileges on database objects. When a user runs a command, Snowflake checks the user's active roles and their privileges to allow or deny the action. Role inheritance is implemented by resolving all granted roles recursively to gather all effective privileges. This happens instantly at query time, ensuring up-to-date access control.

Why designed this way?

Snowflake designed roles to be flexible and hierarchical to support complex organizations and security needs. The separation of roles and privileges allows reuse and clear management. Role inheritance reduces duplication and errors. This design balances security, usability, and performance.

┌───────────────┐       grants       ┌───────────────┐
│   User        │────────────────────▶│   Role A      │
└───────────────┘                     └───────────────┘
                                         │ grants
                                         ▼
                                   ┌───────────────┐
                                   │   Role B      │
                                   └───────────────┘
                                         │ has
                                         ▼
                                   ┌───────────────┐
                                   │ Privileges    │
                                   └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think assigning a role to a user automatically grants all privileges immediately? Commit to yes or no.

Common Belief: Assigning a role to a user instantly gives them all privileges forever.

Reality: Privileges are effective only when the user activates the role in their session; roles can be switched or multiple roles can be active.

Why it matters: Assuming privileges are always active can cause confusion and security gaps if users switch roles or sessions.

Quick: Do you think custom roles can own database objects by default? Commit to yes or no.

Common Belief: Custom roles automatically own the objects they have privileges on.

Reality: Ownership is a separate privilege and must be explicitly granted; roles with only usage or select rights do not own objects.

Why it matters: Misunderstanding ownership can lead to unexpected permission errors or inability to manage objects.

Quick: Do you think granting a role to another role copies privileges permanently? Commit to yes or no.

Common Belief: Granting a role to another role copies privileges as a snapshot at grant time.

Reality: Role inheritance is dynamic; changes to the granted role's privileges immediately affect the inheriting role.

Why it matters: Assuming static copies can cause stale permissions and security risks.

Quick: Do you think the PUBLIC role should be used for sensitive data access? Commit to yes or no.

Common Belief: The PUBLIC role is safe to grant for any data access because it is built-in.

Reality: The PUBLIC role is granted to all users by default and should not be used for sensitive permissions.

Why it matters: Using PUBLIC for sensitive access exposes data to everyone, causing major security breaches.

Expert Zone

1

Custom roles can be combined with session policies to enforce time-based or IP-based access controls, adding a security layer beyond privileges.

2

Granting OWNERSHIP privilege to a role allows it to transfer privileges and manage object lifecycle, but it also increases risk if misused.

3

Snowflake's role hierarchy is a directed acyclic graph, not just a tree, allowing complex inheritance but requiring careful design to avoid privilege loops.

When NOT to use

Avoid creating too many fine-grained custom roles that differ by only one privilege; this complicates management. Instead, use role hierarchies or attribute-based access control (ABAC) where supported. For very dynamic access needs, consider external identity providers with federated access.

Production Patterns

In production, organizations create layered roles like 'read_only', 'data_engineer', and 'admin', then assign these to users based on job function. Automation scripts manage role grants during onboarding and offboarding. Regular audits and monitoring detect privilege creep. Role hierarchies reduce duplication and simplify permission updates.

Connections

Role-Based Access Control (RBAC)

Custom roles in Snowflake implement RBAC principles by grouping permissions and assigning them to users.

Understanding RBAC helps grasp why roles are central to secure and manageable access control.

Organizational Job Titles

Custom roles mirror job titles by defining responsibilities and access levels in a company.

Seeing roles as job titles clarifies how access should align with real-world duties.

Graph Theory

Role hierarchies form a directed acyclic graph where roles inherit privileges from others.

Knowing graph theory concepts helps design role structures that avoid cycles and unintended privilege escalation.

Common Pitfalls

#1 Granting too many privileges to a single custom role.

Wrong approach:

CREATE ROLE analyst;
GRANT ALL PRIVILEGES ON DATABASE sales TO ROLE analyst;

Correct approach:

CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO ROLE analyst;

Root cause:

Misunderstanding the difference between ALL PRIVILEGES and specific privileges leads to over-permission.

#2 Assigning roles directly to users without considering role activation.

Wrong approach:

GRANT ROLE analyst TO USER alice; -- but user never activates role

Correct approach:

GRANT ROLE analyst TO USER alice;
-- User activates role with: USE ROLE analyst;

Root cause:

Not realizing users must activate roles in their session to use privileges.

#3 Creating many similar roles differing by one privilege.

Wrong approach:

CREATE ROLE analyst_read;
CREATE ROLE analyst_write;
-- Both roles differ only by one privilege

Correct approach:

CREATE ROLE analyst_read;
CREATE ROLE analyst_write;
GRANT ROLE analyst_read TO ROLE analyst_write;
-- Use role hierarchy to avoid duplication

Root cause:

Lack of understanding of role inheritance leads to role sprawl and management complexity.

Key Takeaways

Custom roles in Snowflake let you group permissions to control user access precisely and securely.

Roles hold privileges, and users gain permissions by activating assigned roles in their sessions.

Role hierarchies allow roles to inherit privileges from other roles, simplifying permission management.

Following the least privilege principle when creating roles reduces security risks and accidental data exposure.

Automating role creation and management helps maintain consistency and efficiency in large environments.