Privilege inheritance in Snowflake’s role hierarchy is a powerful feature that simplifies access management and reduces administrative overhead.

In Snowflake, roles can be granted to other roles, creating a hierarchical structure. Privileges associated with a role are inherited by any roles above it in the hierarchy. This means that when you grant a privilege to a lower-level role, all roles above it in the hierarchy automatically receive that privilege as well.

Let me illustrate this with an example:

Let’s consider a simple role hierarchy:

1. ANALYST_ROLE (lowest level)
2. DATA_ENGINEER_ROLE (middle level)
3. SYSADMIN (highest level)

We can set up this hierarchy using the following commands:

USE ROLE SECURITYADMIN;

GRANT ROLE ANALYST_ROLE TO ROLE DATA_ENGINEER_ROLE;

GRANT ROLE DATA_ENGINEER_ROLE TO ROLE SYSADMIN;

Now, let’s grant some privileges:

GRANT USAGE ON DATABASE SOURCE_DB TO ROLE ANALYST_ROLE;

GRANT SELECT ON ALL TABLES IN SCHEMA SOURCE_DB.PUBLIC TO ROLE ANALYST_ROLE;

GRANT SELECT ON FUTURE TABLES IN SCHEMA SOURCE_DB.PUBLIC TO ROLE ANALYST_ROLE;

With this setup:

1. ANALYST_ROLE has USAGE privilege on SOURCE_DB and SELECT privilege on all tables in SOURCE_DB.PUBLIC schema.
2. DATA_ENGINEER_ROLE automatically inherits these privileges from ANALYST_ROLE.
3. SYSADMIN also inherits these privileges through DATA_ENGINEER_ROLE.

This inheritance simplifies management because:

1. You only need to grant privileges once at the lowest appropriate level.
2. When you add new objects (e.g. tables) to SOURCE_DB.PUBLIC, all roles in the hierarchy automatically get access without additional grants.
3. If you need to modify access, you can do it at one level, and the changes propagate up the hierarchy.