Role hierarchy in Snowflake - Deep Dive

Overview - Role hierarchy in Snowflake

What is it?

Role hierarchy in Snowflake is a way to organize and manage user permissions by creating roles that can inherit privileges from other roles. This means a role can have its own permissions and also gain permissions from roles above it in the hierarchy. It helps control who can do what in the Snowflake data platform in a clear and manageable way. This system makes it easier to assign and update access rights for many users.

Why it matters

Without role hierarchy, managing permissions for many users would be chaotic and error-prone. Each user would need individual permissions, making it hard to keep track and secure. Role hierarchy solves this by grouping permissions and allowing inheritance, so changes can be made once and affect many users. This reduces mistakes, saves time, and keeps data safe.

Where it fits

Before learning role hierarchy, you should understand basic Snowflake concepts like users, roles, and privileges. After mastering role hierarchy, you can learn about advanced access control features like masking policies, resource monitors, and multi-factor authentication to further secure your Snowflake environment.

Mental Model

Core Idea

Role hierarchy in Snowflake is like a family tree where child roles inherit permissions from their parent roles, making permission management simple and scalable.

Think of it like...

Imagine a company where managers have certain keys to rooms, and their assistants inherit copies of those keys plus some extra keys for their own rooms. Instead of giving keys to every employee individually, you give them to managers and assistants based on their position, and the assistants automatically get the keys their managers have.

Role Hierarchy Structure:

  ┌─────────────┐
  │ ACCOUNTADMIN │
  └──────┬──────┘
         │ inherits
  ┌──────▼──────┐
  │  SYSADMIN   │
  └──────┬──────┘
         │ inherits
  ┌──────▼──────┐
  │ SECURITYADMIN │
  └──────┬──────┘
         │ inherits
  ┌──────▼──────┐
  │  USERADMIN  │
  └──────┬──────┘
         │ inherits
  ┌──────▼──────┐
  │   ANALYST   │
  └─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Roles and Privileges

Concept: Roles are containers for permissions called privileges that allow users to perform actions in Snowflake.

In Snowflake, a role is like a job title that defines what actions a user can do. Privileges are specific permissions like reading data or creating tables. Users are assigned roles, and through these roles, they get the privileges needed to work.

Result

Users gain access to Snowflake features based on the roles assigned to them.

Understanding that roles group privileges helps simplify managing user permissions instead of assigning permissions individually.

2

Foundation

What is Role Hierarchy?

Concept: Role hierarchy allows roles to inherit privileges from other roles, creating a chain of permissions.

Instead of assigning all privileges directly to one role, Snowflake lets you create a hierarchy where a role can inherit privileges from a parent role. This means if Role B inherits from Role A, Role B automatically has all privileges of Role A plus any additional ones it has.

Result

Privileges are shared down the hierarchy, reducing duplication and easing permission management.

Knowing that roles can inherit permissions prevents repetitive work and helps maintain consistent access control.

3

Intermediate

Creating and Granting Roles

🤔Before reading on: do you think granting a role to another role copies privileges or links them dynamically? Commit to your answer.

Concept: Granting a role to another role links them dynamically, so changes in the parent role affect the child role automatically.

When you grant Role A to Role B, Role B inherits Role A's privileges dynamically. If you add or remove privileges from Role A later, Role B's permissions update automatically. This is done using the SQL command: GRANT ROLE role_a TO ROLE role_b;

Result

Role B always has up-to-date privileges from Role A without manual updates.

Understanding dynamic linking avoids confusion about stale permissions and ensures security policies stay current.

4

Intermediate

Assigning Roles to Users

🤔Before reading on: does a user get privileges only from their assigned role or also from roles inherited by that role? Commit to your answer.

Concept: Users get privileges from their assigned roles and all roles those roles inherit from, forming a full permission set.

When you assign a role to a user, the user gains all privileges of that role plus any inherited roles. For example, if USERADMIN inherits SYSADMIN, and a user has USERADMIN, they get SYSADMIN privileges too. This is done with: GRANT ROLE useradmin TO USER alice;

Result

Users have comprehensive access based on the full role hierarchy under their assigned roles.

Knowing that inheritance flows through assigned roles helps predict user permissions accurately.

5

Intermediate

Default Roles and Role Switching

Concept: Users can have a default role but can switch to other roles they have been granted during a session.

Each user has a default role that applies when they log in. However, users can switch roles to gain different privileges temporarily using the command: USE ROLE role_name; This allows flexible access control without changing user assignments.

Result

Users can perform tasks requiring different privileges by switching roles as needed.

Understanding role switching empowers users to work securely with least privilege and only elevate access when necessary.

6

Advanced

Managing Complex Role Hierarchies

🤔Before reading on: do you think circular role grants (A grants B, B grants A) are allowed or prevented? Commit to your answer.

Concept: Snowflake prevents circular role grants to avoid infinite loops and confusion in permission inheritance.

When building role hierarchies, Snowflake checks to ensure no cycles exist. Circular grants would cause roles to inherit from each other endlessly, breaking permission logic. Administrators must design hierarchies carefully to maintain a clear, acyclic structure.

Result

Role hierarchies remain stable, predictable, and secure without loops.

Knowing that circular grants are blocked helps design clean and maintainable permission structures.

7

Expert

Role Hierarchy Impact on Security and Auditing

🤔Before reading on: does revoking a privilege from a parent role immediately affect all child roles? Commit to your answer.

Concept: Revoking privileges from a parent role immediately removes those privileges from all child roles due to inheritance.

Because child roles inherit privileges dynamically, any change in a parent role's privileges instantly affects all descendants. This means security changes propagate quickly, but also requires careful planning to avoid unintended access loss. Auditing tools track role grants and privilege changes to maintain compliance.

Result

Security policies can be enforced consistently and changes audited effectively across the hierarchy.

Understanding dynamic privilege propagation is critical to avoid accidental permission gaps or overexposure in production.

Under the Hood

Snowflake stores roles and their grants in system tables. When a role is granted to another role, it creates a link rather than copying privileges. At runtime, Snowflake resolves a user's effective privileges by traversing the role hierarchy graph, collecting all privileges from assigned and inherited roles. This dynamic resolution ensures up-to-date permissions without duplication.

Why designed this way?

This design avoids redundancy and manual synchronization of permissions. It allows administrators to manage permissions centrally and propagate changes automatically. Alternatives like copying privileges would cause inconsistencies and increase management overhead. The graph-based model also supports complex organizational structures.

Role Hierarchy Internal Flow:

  [Role A]───┐
     │       │
     ▼       ▼
  [Role B]   [Role C]
     │         │
     └───┬─────┘
         ▼
      [User]

At runtime, Snowflake collects privileges from Role B and Role C, including those inherited from Role A, to determine User's effective permissions.

Myth Busters - 4 Common Misconceptions

Quick: Does granting a role to another role copy privileges or link them dynamically? Commit to your answer.

Common Belief: Granting a role to another role copies all privileges at that moment, so later changes don't affect the child role.

Reality: Granting a role to another role creates a dynamic link, so any privilege changes in the parent role immediately affect the child role.

Why it matters: Believing privileges are copied can lead to stale permissions and security holes when changes are made but not reflected.

Quick: If a user has multiple roles, do privileges combine or does only one role apply? Commit to your answer.

Common Belief: A user can only use one role's privileges at a time, so multiple roles don't combine.

Reality: A user's effective privileges are the union of all privileges from all roles assigned and inherited, even if only one role is active at a time.

Why it matters: Misunderstanding this can cause unexpected access or denial of service when switching roles.

Quick: Can role hierarchies contain loops where roles grant each other? Commit to your answer.

Common Belief: Role hierarchies can have circular grants without issues.

Reality: Snowflake prevents circular role grants to avoid infinite inheritance loops.

Why it matters: Allowing loops would break permission resolution and cause system errors.

Quick: Does revoking a privilege from a child role affect the parent role? Commit to your answer.

Common Belief: Revoking a privilege from a child role also removes it from the parent role.

Reality: Privileges flow downward; revoking from a child role only affects that child, not the parent role.

Why it matters: Confusing this can lead to incorrect permission assumptions and security risks.

Expert Zone

1

Role hierarchy inheritance is transitive and dynamic, meaning changes at any level propagate instantly through all descendants.

2

Snowflake's role hierarchy supports fine-grained access control by combining multiple roles assigned to a user, enabling least privilege principles.

3

Managing role grants carefully avoids privilege explosion, where users gain more access than intended through multiple inherited roles.

When NOT to use

Role hierarchy is not suitable when you need completely isolated permissions without inheritance. In such cases, assign privileges directly to roles without granting other roles. For very simple setups, flat role assignments without hierarchy may be easier to manage.

Production Patterns

In production, organizations use a layered role hierarchy with top-level administrative roles (e.g., ACCOUNTADMIN), mid-level operational roles (e.g., SYSADMIN), and bottom-level user roles (e.g., ANALYST). Roles are granted to users based on job function, and role switching is used for temporary privilege elevation. Auditing tracks role grants and privilege changes to ensure compliance.

Connections

Unix File System Permissions

Similar pattern of hierarchical permission inheritance and group membership

Understanding Unix groups and permission inheritance helps grasp how Snowflake roles inherit privileges through a hierarchy.

Object-Oriented Programming Inheritance

Role hierarchy mirrors class inheritance where child classes inherit properties and methods from parent classes

Knowing OOP inheritance clarifies how roles inherit privileges and how changes in parent roles affect child roles.

Organizational Management Structures

Role hierarchy reflects real-world organizational charts where managers delegate responsibilities to subordinates

Seeing role hierarchy as an organizational chart helps understand delegation and inheritance of permissions in Snowflake.

Common Pitfalls

#1 Creating circular role grants causing infinite inheritance loops

Wrong approach:

GRANT ROLE role_a TO ROLE role_b;
GRANT ROLE role_b TO ROLE role_a;

Correct approach:

Avoid granting roles to each other in a circular manner; design a clear acyclic hierarchy instead.

Root cause:

Misunderstanding that roles can be granted mutually without causing logical errors.

#2 Assigning too many privileges to a single role causing privilege explosion

Wrong approach:

GRANT ALL PRIVILEGES ON DATABASE mydb TO ROLE analyst;

Correct approach:

Grant only necessary privileges to roles and use multiple roles to combine permissions as needed.

Root cause:

Lack of principle of least privilege and misunderstanding of role combination.

#3 Assuming revoking a privilege from a child role removes it from parent roles

Wrong approach:

REVOKE SELECT ON TABLE sales FROM ROLE analyst;
-- expecting parent roles to lose SELECT privilege too

Correct approach:

Revoke privileges only from the roles that should lose them; parent roles remain unaffected.

Root cause:

Confusing direction of privilege inheritance in role hierarchy.

Key Takeaways

Role hierarchy in Snowflake organizes permissions so child roles inherit privileges from parent roles dynamically.

This inheritance simplifies permission management and ensures consistent access control across users.

Users gain effective privileges from all assigned roles and their inherited roles, enabling flexible access.

Snowflake prevents circular role grants to maintain a stable and secure permission structure.

Understanding role hierarchy is essential for designing secure, scalable, and maintainable Snowflake environments.