Role hierarchy in Snowflake - Step-by-Step Execution

Process Flow - Role hierarchy in Snowflake

Create Roles

↓

Grant Child Role to Parent Role

↓

Grant Privileges to Child Role

↓

Parent Role Inherits Privileges

↓

Assign Roles to Users

↓

Users Access Resources Based on Role Hierarchy

This flow shows how roles are created, linked in a hierarchy, granted privileges, and assigned to users who then inherit those privileges.

Execution Sample

Snowflake

CREATE ROLE analyst;
CREATE ROLE senior_analyst;
GRANT ROLE analyst TO ROLE senior_analyst;
GRANT SELECT ON TABLE sales TO ROLE analyst;
GRANT ROLE senior_analyst TO USER alice;

This code creates two roles, links them in a hierarchy, grants table access to the base role, and assigns the senior role to a user.

Process Table

Step	Action	Role/Privilege State	User Access
1	Create role 'analyst'	Roles: analyst	No user assigned
2	Create role 'senior_analyst'	Roles: analyst, senior_analyst	No user assigned
3	Grant role 'analyst' to 'senior_analyst'	senior_analyst inherits analyst privileges	No user assigned
4	Grant SELECT on sales table to 'analyst'	analyst has SELECT on sales	No user assigned
5	Grant role 'senior_analyst' to user 'alice'	alice assigned senior_analyst role	alice can SELECT on sales (via inheritance)
6	Check alice's access	Roles: analyst < senior_analyst	alice can SELECT on sales
7	End	No changes	Execution complete

💡 All roles created, privileges granted, and user assigned; role hierarchy enables privilege inheritance.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
Roles	None	analyst	analyst, senior_analyst	senior_analyst inherits analyst	analyst has SELECT on sales	senior_analyst inherits analyst	analyst has SELECT on sales; senior_analyst inherits analyst
User alice Roles	None	None	None	None	None	senior_analyst	senior_analyst
User alice Privileges	None	None	None	None	None	SELECT on sales (via inheritance)	SELECT on sales

Key Moments - 3 Insights

Why does alice get SELECT access even though it was granted only to the 'analyst' role?

Because 'senior_analyst' role inherits the 'analyst' role (see step 3 and 5 in execution_table), alice assigned 'senior_analyst' gets all privileges of 'analyst'.

What happens if we grant privileges to 'senior_analyst' instead of 'analyst'?

Privileges granted to 'senior_analyst' apply only to that role and any roles that inherit from it (child roles), but since 'analyst' is a child, it won't inherit from 'senior_analyst'. So users with 'analyst' role won't get those privileges.

Can a user have multiple roles and how does that affect access?

Yes, a user can have multiple roles assigned. The user’s effective privileges are the union of all privileges from all assigned roles and their inherited roles.

Visual Quiz - 3 Questions

Look at the execution_table at step 5. What role is assigned to user alice?

A analyst

B senior_analyst

C admin

D sales_role

💡 Hint

Check the 'Action' and 'User Access' columns at step 5 in the execution_table.

At which step does the 'senior_analyst' role inherit privileges from 'analyst'?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look for the step where 'Grant role analyst to senior_analyst' happens in the execution_table.

If we remove the grant of 'analyst' role to 'senior_analyst', what happens to alice's access?

A alice still has SELECT on sales

B alice gains admin privileges

C alice loses SELECT on sales

D No change to alice's access

💡 Hint

Refer to the inheritance shown in variable_tracker and execution_table steps 3 and 5.

Concept Snapshot

Role hierarchy in Snowflake:
- Create roles with CREATE ROLE.
- Link roles with GRANT ROLE child TO parent.
- Grant privileges to base roles.
- Parent roles inherit privileges from child roles.
- Assign roles to users for access.
- Users get all privileges from assigned roles and inherited roles.

Full Transcript

In Snowflake, roles can be created and linked in a hierarchy. When a child role is granted to a parent role, the parent role inherits all privileges of the child role. Privileges like SELECT on tables are granted to base roles. Users assigned to higher-level roles automatically get all privileges from the roles below them. This allows easy management of permissions by grouping privileges in roles and linking them. The execution steps show creating roles, linking them, granting privileges, assigning roles to users, and how users gain access through inheritance.