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 ROLEto 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
| Command | Purpose |
|---|---|
| CREATE ROLE | Create a new role. |
| GRANT ROLE | Make parent role inherit child role privileges. |
| GRANT | 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.
