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.

Practice

1. What does role hierarchy in Snowflake allow you to do?

easy

A. Store data in different regions automatically

B. Create multiple users with the same password

C. Let roles inherit permissions from other roles

D. Schedule queries to run at specific times

Solution

1. Step 1: Understand role hierarchy concept

   Role hierarchy means roles can inherit permissions from other roles, simplifying access management.

2. Step 2: Identify correct description

   Only Let roles inherit permissions from other roles describes this inheritance feature correctly.

3. Final Answer:

   Let roles inherit permissions from other roles -> Option C

4. Quick Check:

   Role hierarchy = permission inheritance [OK]

Hint: Role hierarchy means roles inherit permissions [OK]

Common Mistakes:

• Confusing role hierarchy with user password management
• Thinking role hierarchy controls data storage
• Mixing role hierarchy with query scheduling

2. Which of the following is the correct syntax to grant a role to another role in Snowflake?

easy

A. GRANT ROLE parent_role TO ROLE child_role;

B. GRANT ROLE TO ROLE parent_role child_role;

C. GRANT ROLE child_role TO ROLE parent_role;

D. GRANT ROLE parent_role child_role TO ROLE;

Solution

1. Step 1: Recall Snowflake grant syntax

   To make one role inherit another, you grant the parent role to the child role using: GRANT ROLE parent_role TO ROLE child_role;

2. Step 2: Match syntax to options

   GRANT ROLE parent_role TO ROLE child_role; matches the correct syntax exactly.

3. Final Answer:

   GRANT ROLE parent_role TO ROLE child_role; -> Option A

4. Quick Check:

   Grant parent to child = correct syntax [OK]

Hint: Grant parent role TO child role syntax [OK]

Common Mistakes:

• Reversing parent and child roles in the grant statement
• Incorrect order of keywords in the command
• Missing 'TO ROLE' keyword

3. Given the following role hierarchy:
GRANT ROLE analyst TO ROLE senior_analyst;
GRANT ROLE senior_analyst TO ROLE manager;
If a user is assigned the role manager, which roles' permissions does the user have?

medium

A. Manager and senior_analyst only

B. Manager, senior_analyst, and analyst

C. Only manager

D. Only analyst

Solution

1. Step 1: Trace role inheritance

   Manager role inherits senior_analyst, which inherits analyst. So manager includes all permissions from senior_analyst and analyst.

2. Step 2: Determine user permissions

   User with manager role has permissions from manager, senior_analyst, and analyst roles.

3. Final Answer:

   Manager, senior_analyst, and analyst -> Option B

4. Quick Check:

   Role inheritance includes all ancestor roles [OK]

Hint: User gets all inherited roles' permissions [OK]

Common Mistakes:

• Assuming user only has direct role permissions
• Ignoring transitive inheritance in role hierarchy
• Confusing child and parent roles

4. You run this command:
GRANT ROLE analyst TO senior_analyst;
What is wrong with this command?

medium

A. Cannot grant a role to another role

B. Role names must be in quotes

C. The order of roles is reversed

D. Missing 'ROLE' keyword before 'senior_analyst'

Solution

1. Step 1: Check syntax for granting roles

   The correct syntax requires 'TO ROLE' before the role receiving the grant.

2. Step 2: Identify missing keyword

   The command misses 'ROLE' keyword before 'senior_analyst'. It should be: GRANT ROLE analyst TO ROLE senior_analyst;

3. Final Answer:

   Missing 'ROLE' keyword before 'senior_analyst' -> Option D

4. Quick Check:

   Grant syntax requires 'TO ROLE' [OK]

Hint: Always include 'TO ROLE' keyword in grant statements [OK]

Common Mistakes:

• Omitting 'ROLE' keyword after TO
• Thinking roles cannot be granted to roles
• Using quotes unnecessarily around role names

5. You want to create a role hierarchy where intern inherits from analyst, and analyst inherits from manager. Which sequence of commands correctly sets this up?

hard

A. GRANT ROLE manager TO ROLE analyst; GRANT ROLE analyst TO ROLE intern;

B. GRANT ROLE analyst TO ROLE intern; GRANT ROLE manager TO ROLE analyst;

C. GRANT ROLE manager TO ROLE analyst; GRANT ROLE intern TO ROLE analyst;

D. GRANT ROLE intern TO ROLE analyst; GRANT ROLE analyst TO ROLE manager;

Solution

1. Step 1: Understand inheritance direction

   Child role inherits from parent role by granting parent role TO child role.

2. Step 2: Apply to given roles

   Intern inherits analyst: GRANT ROLE analyst TO ROLE intern;
   Analyst inherits manager: GRANT ROLE manager TO ROLE analyst;

3. Step 3: Match sequence to options

   GRANT ROLE manager TO ROLE analyst; GRANT ROLE analyst TO ROLE intern; matches the correct order and syntax.

4. Final Answer:

   GRANT ROLE manager TO ROLE analyst; GRANT ROLE analyst TO ROLE intern; -> Option A

5. Quick Check:

   Grant parent TO child for inheritance [OK]

Hint: Grant parent role TO child role to inherit [OK]

Common Mistakes:

• Reversing parent and child roles in grants
• Granting child TO parent instead of parent TO child
• Mixing order of commands