Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use Role Hierarchy in Snowflake for Access Control

In Snowflake, you use role hierarchy by creating roles and granting them to other roles, forming a chain of permissions. This lets a higher-level role inherit all privileges of the roles granted to it, simplifying access management.
📐

Syntax

To build a role hierarchy in Snowflake, you use the GRANT ROLE command to assign one role to another. The syntax is:

  • GRANT ROLE <child_role> TO ROLE <parent_role>; - Grants the child role to the parent role, so the parent inherits the child's privileges.
  • USE ROLE <role_name>; - Switches the current session to the specified role.
sql
GRANT ROLE child_role TO ROLE parent_role;
USE ROLE parent_role;
💻

Example

This example shows how to create a role hierarchy where analyst_role inherits privileges from read_only_role. The analyst_role can then access all objects that read_only_role can.

sql
CREATE ROLE read_only_role;
CREATE ROLE analyst_role;

-- Grant privileges to read_only_role
GRANT SELECT ON DATABASE my_database TO ROLE read_only_role;

-- Grant read_only_role to analyst_role to inherit privileges
GRANT ROLE read_only_role TO ROLE analyst_role;

-- Switch to analyst_role to use inherited privileges
USE ROLE analyst_role;
Output
Role 'read_only_role' created. Role 'analyst_role' created. Privileges granted to role 'read_only_role'. Role 'read_only_role' granted to role 'analyst_role'. Current role set to 'analyst_role'.
⚠️

Common Pitfalls

Common mistakes when using role hierarchy in Snowflake include:

  • Granting roles in the wrong direction (parent to child instead of child to parent).
  • Not switching to the correct role with USE ROLE to test permissions.
  • Forgetting to grant necessary privileges to the base roles before creating hierarchy.
  • Assuming role hierarchy automatically grants ownership privileges, which it does not.
sql
/* Wrong way: granting parent role to child (does not create inheritance) */
GRANT ROLE parent_role TO ROLE child_role;

/* Correct way: granting child role to parent (parent inherits child privileges) */
GRANT ROLE child_role TO ROLE parent_role;
📊

Quick Reference

CommandPurpose
CREATE ROLE ;Create a new role.
GRANT ROLE TO ROLE ;Make parent role inherit child role privileges.
GRANT ON TO ROLE ;Assign privileges to a role.
USE ROLE ;Switch current session to a role.
✅

Key Takeaways

Grant child roles to parent roles to build role hierarchy in Snowflake.
Use USE ROLE to switch and test permissions of a specific role.
Role hierarchy helps simplify permission management by inheritance.
Always grant privileges to base roles before creating hierarchy.
Role hierarchy does not grant ownership; ownership must be assigned explicitly.