Role hierarchy in Snowflake - Commands & Configuration

Introduction

Managing access in Snowflake can be complex. Role hierarchy helps by organizing roles so that permissions flow from higher roles to lower roles automatically.

When you want to give a group of users access to multiple resources without assigning permissions individually.

When you need to create a clear structure of who can do what in your Snowflake account.

When you want to simplify permission management by grouping related permissions under roles.

When you want to delegate administration tasks by assigning roles with specific privileges.

When you want to ensure security by controlling access levels through role inheritance.

Commands

Create a new role named 'analyst' to group permissions for data analysis tasks.

Terminal

CREATE ROLE analyst;

Expected Output

Statement executed successfully.

Create a higher-level role 'senior_analyst' that will inherit permissions from 'analyst'.

Terminal

CREATE ROLE senior_analyst;

Expected Output

Statement executed successfully.

Make 'analyst' a child role of 'senior_analyst' so 'senior_analyst' inherits all 'analyst' permissions.

Terminal

GRANT ROLE analyst TO ROLE senior_analyst;

Expected Output

Grant succeeded.

Give 'analyst' role permission to read data from the 'sales_db' database.

Terminal

GRANT SELECT ON DATABASE sales_db TO ROLE analyst;

Expected Output

Grant succeeded.

Check all permissions and inherited roles assigned to 'senior_analyst'.

Terminal

SHOW GRANTS TO ROLE senior_analyst;

Expected Output

grantee_name | granted_role | privilege | granted_on | name

senior_analyst | analyst | ROLE | ROLE | analyst

senior_analyst | SELECT | DATABASE | sales_db

Key Concept

If you remember nothing else, remember: roles can inherit permissions from other roles, making permission management simpler and more organized.

Common Mistakes

Granting privileges directly to users instead of roles.

This makes permission management complex and error-prone as you must update each user individually.

Always grant privileges to roles and assign roles to users.

Not granting child roles to parent roles to create the hierarchy.

Without this, roles do not inherit permissions, defeating the purpose of role hierarchy.

Use 'GRANT ROLE child_role TO ROLE parent_role' to establish inheritance.

Assuming role inheritance is bidirectional.

Inheritance flows only from child to parent; parent role gets child's permissions, not the other way.

Grant child roles to parent roles explicitly to set correct inheritance direction.

Summary

Create roles to group permissions logically.

Use 'GRANT ROLE child_role TO ROLE parent_role' to build role hierarchy.

Assign privileges to child roles so parent roles inherit them automatically.