Why Role hierarchy in Snowflake? - Purpose & Use Cases

The Big Idea

What if you could control who sees what in your data with just a few role assignments instead of endless permission lists?

The Scenario

Imagine you manage access for a team by writing down who can do what on paper or in a simple list. Every time someone new joins or changes roles, you have to update each list manually for every system resource.

The Problem

This manual way is slow and confusing. You might forget to update a list, give someone too many permissions, or waste hours fixing mistakes. It's like juggling many keys for many doors instead of having a master key.

The Solution

Role hierarchy in Snowflake lets you organize permissions in layers. You create roles that inherit permissions from other roles, so managing access becomes simple and less error-prone. Change one role, and all related roles update automatically.

Before vs After

✗ Before

GRANT SELECT ON TABLE sales TO USER jane;
GRANT INSERT ON TABLE sales TO USER john;

✓ After

CREATE ROLE analyst;
GRANT SELECT ON TABLE sales TO ROLE analyst;
GRANT ROLE analyst TO USER jane;

What It Enables

It enables easy, secure, and scalable access control that grows with your team and data.

Real Life Example

A company uses role hierarchy to give all analysts read access to sales data, while managers get extra rights to update reports, all managed by assigning roles instead of individual permissions.

Key Takeaways

Manual permission management is slow and risky.

Role hierarchy organizes access in layers for easier control.

Changes in roles automatically update permissions for all users assigned.