Streamlit: Application of Snowflake Row Access Policies

In my last post, I showed how to set up hierarchical roles in Snowflake and then apply a row access policy based on those roles. In this article, I’ll show an application of those policies using a Streamlit app. An important note is that this is not a native Streamlit app in Snowflake. As of the writing of this article, native Streamlit apps do not support changing the session role when querying Snowflake.

Assumptions

For this article, I assume that readers already have a Python IDE installed ( I use VS Code) and have installed the Streamlit, Snowpark, Altair, and Pandas libraries using pip.

Streamlit App Setup

First, I’ll download the latest version of “My_First_Streamlit.py” from my recent stacked bar chart demo from my Snowflake stage and open it in VS Code. Since I need to build a connection to Snowflake directly and not use the built-in native app session, I’ll need to import the Snowpark library.

#IMPORT STREAMLIT LIBRARY
import streamlit as st
#IMPORT SNOWPARK
import snowflake.snowpark as sp
#IMPORT SNOWPARK SESSION
from snowflake.snowpark.context import get_active_session
#IMPORT PANDAS
import pandas as pd
#IMPORT ALTAIR CHARTS
import altair as alt

Create Session

So as not to rebuild the entire app, I’ll add a new function definition that will attempt to use get_active_session() from the native app, but if an error occurs, the function will build a session manually. I’ll use this function to set the session variable so that the rest of the app will execute as expected without alteration.

##CREATE NEW FUNCTION TO TRY GET ACTIVE SESSION FROM SNOWPARK
##OTHERWISE BUILD CONNECTION
def open_session():
    snow_session = None

    try:
      snow_session = get_active_session()
    except:
      #READ CREDS INTO DICTIONARY
        creds = {
            "account":"YOUR_ACCOUNT_LOCATOR",
            "user":"USERNAME",
            "password":"PASSWORD",    
            "database":"STREAMLIT_DB",
            "schema":"STREAMLIT_DATA",
            "role":"SALES_MGR",
            "warehouse":"STREAMLIT_XS_WH"
        }        
        #BUILD SESSION
        snow_session = sp.Session.builder.configs(creds).create()

    return snow_session

Role Selectbox

To simulate different users with different roles logging into the app, I’ll use the CURRENT_AVAILABLE_ROLES() Snowflake function to obtain the names of the roles available to my account, and then filter it to only those “Sales Roles” needed for this sales app. I’ll also add a new Python function, get_roles(), to the app that retrieves the role names from Snowflake, displays them in a Streamlit select box, and then sets the session role based on the chosen value.

def get_roles():
    role_sql ="""
    SELECT REPLACE(VALUE,'"','') AS ROLE_NAME
    FROM TABLE(FLATTEN(input => PARSE_JSON(CURRENT_AVAILABLE_ROLES())))
    WHERE VALUE LIKE '%SALES%' AND VALUE != 'SNOW_SALES'
    ORDER BY VALUE
    """
    role_df = session.sql(role_sql).collect()
    current_role = st.selectbox("Choose a role:",options=role_df)
    session.use_role(current_role)

Connect and Add Role Chooser to the App

Using the new Python functions, I’ll make a couple of small updates to the app to set the connection variable and display the role chooser in a sidebar on the app.

#CREATE A SESSION VARIABLE
session = open_session()


#ADD ROLE CHOOSER TO SIDEBAR
with st.sidebar:
    get_roles()

Run Streamlit Locally

Since I’m using VS Code, running the streamlit app locally is quite easy. From a new terminal window in VS Code, use the cd command to change the local directory to the home of the .py file for this app. Then execute the command streamlit run filename.py

The Streamlit library will handle the rest and open the app in your default browser in a local session.

Role Selection and Data Access

Now that the app is running, by choosing different roles in the role chooser Selectbox, the app queries the Snowflake data using that role. By doing so, the row access policy on the VW_SALES_DATA view is enforced. Notice that the SALES_MGR role can see all regions in the region selectbox, but AM_EU_SALES is limited to only America and Europe. Similarly, NON_AM_EU_SALES is limited to regions that are NOT America or Europe. The list of regions for each role matches those shown when initially setting the row access policy in my last post.

Conclusion

Exposing data in an app or reporting tool using row access policies is one of the next logical steps to “activate” your data. The example shown here could be updated to require a Snowflake login to remove the “role chooser” and apply the row access policy.

Unfortunately, the methods used in this demo are not currently supported in Native Streamlit apps. Hopefully, as new features are added, there will be additional support added to allow native apps to execute SQL commands as other roles based upon the user running the app.