Snowflake Role Hierarchy

Snowflake Role Hierarchy

One of the most common security-related questions a client asks is, how to retrieve a list of users and roles from Snowflake? Considering there is no direct view provided by Snowflake either in the database or the UI, this can be an unnecessary, albeit minor, challenge for their teams.

Today, we’re going to try and build the role hierarchy from the ground up. We’re going to leverage the built-in Snowflake provide inbound share, ‘SNOWFLAKE’. The caveat here is this is not a real-time data feed. Depending on the views you’re trying to retrieve the data from, it can take between an hour to three hours to refresh.

The most important thing to remember is, we need to use the appropriate role, i.e., ‘AccountAdmin’ in this case. Also, we need an active warehouse as this is an inbound share we are retrieving the data from.

USE ROLE AccountAdmin;
USE WAREHOUSE Compute_WH;

Let’s start with a simple query to list all of the users we have in our database.

SHOW users;

As you’ll note, we have a simple list of all the users that exist within our Snowflake account. It includes users who have been created using external Active Directory profiles as well.

And, we’ll follow that with a list of all the roles we have.

SHOW roles;

This is a particularly important table in that it shows all of the roles available to us. Furthermore, it shows all those roles which have been assigned or granted to users or roles. It is also important to note who the owner is, and might I add, using AccountAdmin to create custom roles is not the best practice, as has been done in this case. Please note, as per Snowflake, it is best to use the SecurityAdmin role for role creation.

As per Snowflake RBAC or Role Based Access Control, we cannot grant permissions/privileges to users directly. We must grant permissions/privileges to roles, which are then granted to users or other roles. Considering this, we need to look into two secure views within the Account_Usage schema under the Snowflake Inbound Shared database. Please note, as these views are secure shared views, their data retrieval and refresh performance is average. For instance, it took me 35+ seconds to retrieve 300+ rows.

SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.Grants_To_Roles;

This is an interesting view, it shows us all of the privileges that have already been granted and to which roles. It also adds information on who granted these roles, was it with the grant option or when it was deleted. Definitely worth exploring in detail for our use case.

SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.Grants_To_Users;

This is a similar view to the previous one, in that it shows us the roles which have been granted to users. But, the key thing to note here is that it only shows the top role given to a user, not all the roles in the hierarchy that are granted to a user. For that, we’re going to have to combine the results of the above two views, which is precisely what is coming up next.

From Grants_To_Roles, let’s only consider the columns that we are interested in, i.e. ‘The Grantee_Name’ and ‘Name’, and discard the rest:

SELECT Grantee_Name, "NAME"
FROM SNOWFLAKE.ACCOUNT_USAGE.Grants_To_Roles
WHERE 1=1
AND Granted_To = 'ROLE'
AND Granted_On = 'ROLE'
AND Deleted_On IS NULL;

And finally, from Grants_to_Users, let’s take ‘Role’, ‘Grantee_Name’, and ‘Granted_By’ and discard the rest:

SELECT Role,
Grantee_Name,
Granted_By
FROM SNOWFLAKE.ACCOUNT_USAGE.Grants_To_Users
WHERE 1=1
AND Deleted_On IS NULL;

Bringing it all together:

--Snowflake Role Hierarchy
WITH


--Get role to role hierarchy
Role_To_Role_Hierarchy(Role_Granted_To, Role_Assigned_Through_Role)
as
(
SELECT Grantee_Name, "NAME"
FROM SNOWFLAKE.ACCOUNT_USAGE.Grants_To_Roles
WHERE 1=1
AND Granted_To = 'ROLE'
AND Granted_On = 'ROLE'
AND Deleted_On IS NULL
),

--Get role to user hierarchy
Role_To_User_Hierarchy(Role_Granted_To_User, User_Granted_To, Granted_By)
as
(
SELECT Role,
Grantee_Name,
Granted_By
FROM SNOWFLAKE.ACCOUNT_USAGE.Grants_To_Users
WHERE 1=1
AND Deleted_On IS NULL
)

--Bringing it together
SELECT R2UH.User_Granted_To,
CASE 
    WHEN R2RH.Role_Assigned_Through_Role IS NULL THEN R2UH.Role_Granted_To_User
    ELSE R2RH.Role_Assigned_Through_Role
END as Assigned_Role,
R2UH.Granted_By,
R2RH.Role_Granted_To,
R2UH.Role_Granted_To_User,
R2RH.Role_Assigned_Through_Role
FROM Role_To_User_Hierarchy R2UH LEFT JOIN Role_To_Role_Hierarchy R2RH
ON R2UH.Role_Granted_To_User = R2RH.Role_Granted_To
ORDER BY R2UH.User_Granted_To;

The following case statement helps determine whether a role was granted to a user directly or was granted to a role.

CASE
    WHEN R2RH.Role_Assigned_Through_Role IS NULL THEN R2UH.Role_Granted_To_User
    ELSE R2RH.Role_Assigned_Through_Role
END as Assigned_Role

The following image is the final output of our CTE query. To understand the above case statement, take a look at line 6, where the Role_Granted_Through_Role is NULL, meaning there is no hierarchy here, and this role was granted directly to the user and not through any role.

Also, and most importantly, you can see rows 1 through 5 with the same User_Granted_To, indicating a one: many relationships between user and roles, signifying the complete list of users and their respective assigned roles.

That brings us to the end of our discussion and the Snowflake role hierarchy.

Up Next:

Read Snowflake Time Travel