Image generated using flux-softserve-anime on Replicate by Author.

How to Build Role-Based Access Audit Dashboards in Snowflake Notebooks

Security teams often find it a great challenge to maintain visibility into user roles and their privileges, with manual audits being time-consuming and error-prone. This creates potential security and compliance risks.

Here, we’ll address this by creating interactive audit dashboards that provide real-time insights into user roles and privilege assignments.

In this blog, you’ll build role-based access audit dashboards in Snowflake Notebooks. Briefly, you’ll learn how to:

• Query and analyze user role assignments
• Examine role grant distributions
• Create interactive visualizations with Streamlit and Altair

Getting Started

Before diving into building the dashboards, ensure you have access to a Snowflake account. Familiarity with SQL and Python, as well as basic data visualization concepts, will be beneficial for following along with this guide.

Firstly, we’ll download the Notebook from GitHub by clicking on Role_Based_Access_Auditing_with_Streamlit.ipynb.

It is worthy to note that Snowflake Notebooks come pre-installed with common Python libraries for data science and machine learning, including numpy, pandas, altair, and more. If additional packages are needed, you can easily add them via the Packages dropdown located on the top right of your notebook.

Building the Role-Based Access Audit Dashboards

In this section, we’ll walk through the process of building two interactive dashboards: one for user role analysis and another for role grant analysis. Each dashboard includes dynamic filtering capabilities and real-time metric updates to provide comprehensive insights.

Querying User Data

To begin, retrieve user details and role assignments using the following SQL query:

SELECT 
    u.name,
    u.disabled,
    u.last_success_login,
    u.created_on as user_created_on,
    g.role as granted_role,
    g.granted_by,
    g.created_on as grant_created_on
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.USERS u
LEFT JOIN 
    SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS g
    ON u.name = g.grantee_name
WHERE 
    g.deleted_on IS NULL
ORDER BY 
    u.name, g.role;

This query returns the following output:

Next, convert the SQL query output to a Pandas DataFrame named df_user_role:

sql_user_role.to_pandas()

We’ll now prepare the DataFrame for visualization:

# Create user activity matrix
user_activity = (
    # Group by user and role, count occurrences
    df_user_role.groupby(['NAME', 'GRANTED_ROLE']) 
    .size()
    .reset_index()
    .pivot(index='NAME', columns='GRANTED_ROLE', values=0) 
    .fillna(0)
)

# Convert to long format for heatmap
user_activity_long = user_activity.reset_index().melt(
    id_vars=['NAME'],
    var_name='ROLE',
    value_name='HAS_ROLE'
)
# Add user status information - now using the same dataframe
user_status = df_user_role[['NAME', 'DISABLED', 'LAST_SUCCESS_LOGIN']].drop_duplicates()
user_activity_long = user_activity_long.merge(
    user_status,
    on='NAME',
    how='left'
)

Creating the User Role Dashboard

Use Streamlit to create an interactive dashboard for user analysis. Incorporate the processed DataFrame user_activity_long into the app:

import pandas as pd
import altair as alt
import streamlit as st

st.title("User Analysis Dashboard")

# Streamlit filters
col1, col2 = st.columns(2)
with col1:
    selected_users = st.multiselect(
        'Select Users',
        options=sorted(user_activity_long['NAME'].unique()),
        default=sorted(user_activity_long['NAME'].unique())
    )
with col2:
    selected_roles = st.multiselect(
        'Select Roles',
        options=sorted(user_activity_long['ROLE'].unique()),
        default=sorted(user_activity_long['ROLE'].unique())
    )

# Filter data based on selections
filtered_data = user_activity_long[
    user_activity_long['NAME'].isin(selected_users) & 
    user_activity_long['ROLE'].isin(selected_roles)
]

# Display summary metrics
with st.expander("View Summary Metrics", expanded=True):
    metric_col1, metric_col2, metric_col3 = st.columns(3)
    with metric_col1:
        st.metric("Selected Users", len(selected_users))
    with metric_col2:
        st.metric("Selected Roles", len(selected_roles))
    with metric_col3:
        st.metric("Total Assignments", len(filtered_data[filtered_data['HAS_ROLE'] > 0]))

# Create styled heatmap
heatmap = alt.Chart(filtered_data).mark_rect(
    stroke='black',
    strokeWidth=1
).encode(
    x=alt.X('ROLE:N', 
            title='Roles',
            axis=alt.Axis(
                labels=True,
                tickMinStep=1,
                labelOverlap=False,
                labelPadding=10
            )),
    y=alt.Y('NAME:N', 
            title='Users',
            axis=alt.Axis(
                labels=True,
                labelLimit=200,
                tickMinStep=1,
                labelOverlap=False,
                labelPadding=10
            )),
    color=alt.Color('HAS_ROLE:Q', 
                   title='Has Role',
                   scale=alt.Scale(scheme='blues')),
    tooltip=[
        alt.Tooltip('NAME:N', title='User'),
        alt.Tooltip('ROLE:N', title='Role'),
        alt.Tooltip('HAS_ROLE:Q', title='Has Role'),
        alt.Tooltip('DISABLED:N', title='Is Disabled'),
        alt.Tooltip('LAST_SUCCESS_LOGIN:T', title='Last Login')
    ]
).properties(
    title='User Role Assignment Matrix'
).configure_view(
    stroke=None,
    continuousHeight=400
).configure_axis(
    labelFontSize=10
)

# Display the chart
st.altair_chart(heatmap, use_container_width=True)

with st.expander("View DataFrame"):
    st.dataframe(filtered_data)

The above code snippet generates the following Streamlit-powered dashboard, allowing you to filter data and visualize user role assignments dynamically:

Analyzing Role Grants

In building the second dashboard, we’ll start by crafting a SQL query to display all active privileges granted to roles, including the type of privilege, the object it was granted on, the specific object name, who granted it, and when it was created:

SELECT 
    grantee_name,
    privilege,
    granted_on,
    name as object_name,
    granted_by,
    created_on
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE deleted_on IS NULL;

This query returns the following output:

Convert the data to a Pandas DataFrame named df_role_grants:

sql_role_grants.to_pandas()

Building the Grant Analysis Dashboard

Create an interactive dashboard for role grant analysis using Streamlit:

import pandas as pd
import altair as alt

st.title("Role Grant Dashboard")

# Create selectboxes for filtering
col1, col2 = st.columns(2)
with col1:
    selected_privilege = st.multiselect(
        'Select Privileges',
        options=sorted(df_role_grants['PRIVILEGE'].unique()),
        default=sorted(df_role_grants['PRIVILEGE'].unique())[:10]
    )

with col2:
    selected_granted_on = st.multiselect(
        'Select Object Types',
        options=sorted(df_role_grants['GRANTED_ON'].unique()),
        default=sorted(df_role_grants['GRANTED_ON'].unique())
    )

# Filter data
filtered_df = df_role_grants[
    df_role_grants['PRIVILEGE'].isin(selected_privilege) &
    df_role_grants['GRANTED_ON'].isin(selected_granted_on)
]

# Show summary metrics
with st.expander("View Summary Metrics", expanded=True):
    metric_col1, metric_col2 = st.columns(2)
    
    with metric_col1:
        st.metric("Total Role Grants", len(filtered_df))
    
    with metric_col2:
        st.metric("Unique Users", filtered_df['GRANTEE_NAME'].nunique())

# Create Top N user chart
top_N_chart = alt.Chart(filtered_df).mark_bar(
    stroke='black',
    strokeWidth=1
).encode(
    x=alt.X('count():Q', 
            title='Number of Role Grants',
            axis=alt.Axis(
                labels=True,
                tickMinStep=1,
                labelOverlap=False
            )),
    y=alt.Y('GRANTEE_NAME:N', 
            title='Users',
            sort='-x',
            axis=alt.Axis(
                labels=True,
                labelLimit=200,
                tickMinStep=1,
                labelOverlap=False,
                labelPadding=10
            )),
    color=alt.Color('PRIVILEGE:N', 
                   title='Privilege Type'),
    tooltip=[
        alt.Tooltip('GRANTEE_NAME:N', title='Users'),
        alt.Tooltip('count():Q', title='Total Grants'),
        alt.Tooltip('PRIVILEGE:N', title='Privilege Type'),
        alt.Tooltip('GRANTED_ON:N', title='Granted On')
    ]
).transform_window(
    rank='rank(count())',
    sort=[alt.SortField('count()', order='descending')]
).transform_filter(
    alt.datum.rank <= 20
).properties(
    title='Top N Users by Number of Role Grants'
).configure_view(
    stroke=None,
    continuousHeight=400
).configure_axis(
    labelFontSize=10
)

# Display chart
st.altair_chart(top_N_chart, use_container_width=True)

This code will generate the following Streamlit app, enabling you to analyze role grants and identify top users based on the number of privileges assigned:

Conclusion

Congratulations! You’ve successfully built interactive role-based access audit dashboards in Snowflake Notebooks. These dashboards enable you to visually analyze user roles and privileges thereby ensuring adherence to security policies within your Snowflake environment. By implementing these tools, you can maintain robust security standards and efficiently manage user access.

Resources

Videos:

• Role-Based Access Auditing In Snowflake Notebooks With Streamlit
• YouTube playlist on Snowflake Notebooks

Articles:

• Account Usage
• Users View
• Grants to Users
• Snowflake Notebooks Guide

Documentation:

• Snowflake Documentation
• Streamlit Documentation
• Altair Documentation

Happy coding!