Role hierarchy in Snowflake - Mini Project: Build & Apply

Role hierarchy in Snowflake

📖 Scenario: You are managing access control in a Snowflake data warehouse for a small company. You want to create a clear role hierarchy to organize permissions efficiently. This will help you assign roles to users with the right access levels.

🎯 Goal: Build a simple role hierarchy in Snowflake by creating roles and granting them to each other in a parent-child relationship.

📋 What You'll Learn

Create three roles: analyst_role, manager_role, and admin_role

Grant analyst_role to manager_role

Grant manager_role to admin_role

💡 Why This Matters

🌍 Real World

Role hierarchies in Snowflake help companies organize user permissions clearly and securely, making it easier to manage who can access what data.

💼 Career

Understanding role hierarchies is essential for cloud administrators and data engineers to implement secure access control in Snowflake environments.

1

Create the initial roles

Write SQL statements to create three roles named analyst_role, manager_role, and admin_role in Snowflake.

Snowflake

-- Your code here to create roles analyst_role, manager_role, and admin_role

Need a hint?

Use the CREATE ROLE command followed by the role name.

2

Grant analyst_role to manager_role

Write a SQL statement to grant the role analyst_role to the role manager_role.

Snowflake

CREATE ROLE analyst_role;
CREATE ROLE manager_role;
CREATE ROLE admin_role;
-- Your code here to grant analyst_role to manager_role

Need a hint?

Use the GRANT ROLE command to assign one role to another.

3

Grant manager_role to admin_role

Write a SQL statement to grant the role manager_role to the role admin_role.

Snowflake

CREATE ROLE analyst_role;
CREATE ROLE manager_role;
CREATE ROLE admin_role;
GRANT ROLE analyst_role TO ROLE manager_role;
-- Your code here to grant manager_role to admin_role

Need a hint?

Use the GRANT ROLE command again to assign manager_role to admin_role.

4

Verify the role hierarchy

Write a SQL query to show the roles granted to admin_role to confirm the hierarchy.

Snowflake

CREATE ROLE analyst_role;
CREATE ROLE manager_role;
CREATE ROLE admin_role;
GRANT ROLE analyst_role TO ROLE manager_role;
GRANT ROLE manager_role TO ROLE admin_role;
-- Your code here to show roles granted to admin_role

Need a hint?

Use SHOW GRANTS TO ROLE followed by the role name to see granted roles.