Snowflake Users, Roles, Policies ELI5?!

You don’t need to know these to write brilliant queries.

It’s akin to understanding IAM user, VPC before you start using the core services like EMR or S3 for your pipelines.

These peripheral concepts sooner or later do pop up in daily workflows..

Users, Roles, Privileges in Snowflake aren’t complicated to understand — let’s get down and GET it

Think about any person in your company. That person has two primary features, their name and their primary role.

What are roles?

What’s that person’s persona/superpower type in the company?

This role comes with privileges it’s allowed to perform.

Since there are many users with the same role probably, it is more efficinet to define privileges against roles instead of directly to users.

How many Roles can a user have?

Well, as being an Analyst role, Dave needs access to some dashboards (this action is called a privilege…each role is allowed to do )

But crucially, as also being part of Employee role, Dave also need to have privilege to his company’s leave tracker.

So dave is not just an analyst, he’s an employee too (duh). he could be a presenter, an assistant to the regional manager… as many roles as he needs!

Since all analysts in the companies are also employees, it makes to connect Dave only to the most immediate role he has been directly assigned to. While we let Employee Role be Mapped (aka “granted” ) to Analyst Roles instead.

So Employee Role <> Analyst Role & Analyst Role < > Dave. i.e Role to Role and Role to User mapping both exists.

Interesting.

How does this work in Snowflake?

Well you write

grant role analyst to user dave;
grant role employee to role analyst;

With greater powers (roles) come greater responsibilities (privileges)!

Dave has effectively been granted both analyst, employee role.

As discussed, only able to see immediate i.e explicitly defined roles of Dave

What exactly are these show grants of/to commands?

show grants of employee

“Who all (role, user) were granted from employee?” — This will show all the roles which flow down from employee for ex Analyst, User (generalist)

show grants of dave

this will error out as there’s no one derived from dave as he’s a user.

show grants to employee

This will show all the object related privileges for the role

show grants to dave

̶T̶h̶i̶s̶ ̶w̶i̶l̶l̶ ̶s̶h̶o̶w̶ ̶a̶l̶l̶ ̶t̶h̶e̶ ̶o̶b̶j̶e̶c̶t̶ ̶r̶e̶l̶a̶t̶e̶d̶ ̶p̶r̶i̶v̶i̶l̶e̶g̶e̶s̶ ̶f̶o̶r̶ ̶t̶h̶e̶ ̶r̶o̶l̶e̶

This will show all the immediate roles granted to this user….

Thus for each Role, we have information on 2 out of 3 things.

Yes -> direct privileges it contains (grants to), direct users/role it is parent to (grants of)

No -> it’s own parent roles

BONUS

Now how does Dave know what all roles he has? i.e I don’t care which immediate role i’m assigned to i want to know ALL the roles i’m assigned to eventually via traversal of my role’s parents..

Use recursion!

Explore this simple PY script to find out what superpowers you have!

PS: user can very well see the object level privileges has has via show grants..

import pandas as pd
import networkx as nx
df = pd.DataFrame(
    columns=["grant_name", "grant_to"],
    data=[("Role1", "Role2"), ("Role2", "Role3"), ("Role4", "Role3")],
)
df
# Using networkx
all_roles = {*df["grant_to"].unique(), *df["grant_name"].unique()}
all_roles
# build graph of nodes
DAG = nx.from_pandas_edgelist(
    df, source="grant_name", target="grant_to", create_using=nx.DiGraph()
)
ancestor_mapping = {n: {n} | nx.ancestors(DAG, n) for n in all_roles}
# map to df
df_role_mapping = pd.DataFrame(
    [(k, list(v)) for k, v in ancestor_mapping.items()],
    columns=["role", "role_inheritence_tree"],
)
print(df_role_mapping)
# Recursion - without networkx
immediate_parent_map = {}
for role in df["grant_to"].unique():
    immediate_parent_map[role] = df[df["grant_to"] == role]["grant_name"].unique()
immediate_parent_map
# travel from "super" role to avoid retravels
class Role:
    def __init__(self, df, Role):
        self.df = df
        self.role = Role
def find_juniors(self):
immediate_juniors = self.df[self.df["grant_name"] == self.role][
            "grant_to"
        ].unique()
global juniors_lst
        juniors_lst.extend(immediate_juniors)
for junior_role in immediate_juniors:
            Role(self.df, junior_role).find_juniors()
return juniors_lst
list_of_junior_maps = []
super_roles = set(df["grant_name"]) - set(df["grant_to"])
for role in super_roles:
    juniors_lst = []
    Role(df, role).find_juniors()
    print(f"child roles for parent {role} - {juniors_lst} ")
    list_of_junior_maps.append({role: juniors_lst})
list_of_junior_maps
# ^after traversing once and forming all links, 
# we can use this lineage to build child roles mapping for all middle nodes. 
# Role 2 -> Role3 becomes another k:v