Row access policies in Streamlit in Snowflake

This topic describes using context functions and row access policies in Streamlit in Snowflake.

Context functions and row access policies in Streamlit in Snowflake

To use context functions such as CURRENT_USER and data from tables with row access policies in a Streamlit in Snowflake app, a user with the ACCOUNTADMIN role must grant the global READ SESSION privilege to the Streamlit app owner role, as shown in the following example:

USE ROLE ACCOUNTADMIN;
GRANT READ SESSION ON ACCOUNT TO ROLE streamlit_owner_role;
Copy

Note

In a Streamlit in Snowflake app, you can’t use row access policies that use CURRENT_ROLE. Streamlit in Snowflake apps run with owner’s rights, so using CURRENT_ROLE inside a Streamlit app always returns the app owner role. For more information, see Understanding owner’s rights and Streamlit in Snowflake apps.

Example: Access data in a table with row access policy using CURRENT_USER

You can use a Streamlit in Snowflake app to govern access to rows in a table protected by a row access policy. Specify the CURRENT_USER function in the body of the row access policy and add the row access policy to the table.

The following example demonstrates how to govern access to a table that is protected by a row access policy in a Streamlit in Snowflake app.

  1. Create a table and insert data:

    CREATE TABLE row_access_policy_test_table (
    id INT,
    some_data VARCHAR(100),
    the_owner VARCHAR(50)
);

INSERT INTO row_access_policy_test_table (id, some_data, the_owner)
VALUES
    (4, 'Some information 4', 'ALICE'),
    (5, 'Some information 5', 'FRANK'),
    (6, 'Some information 6', 'ALICE');
    Copy

  2. Create a row access policy:

    CREATE OR REPLACE ROW ACCESS POLICY st_schema.row_access_policy
AS (the_owner VARCHAR) RETURNS BOOLEAN ->
    the_owner = CURRENT_USER();
    Copy

  3. Add the row access policy to the table:

    ALTER TABLE row_access_policy_test_table ADD ROW ACCESS POLICY st_schema.row_access_policy ON (the_owner);
    Copy

  4. Create a Streamlit app.

  5. Grant the global READ SESSION privilege to the Streamlit app owner role:

    GRANT READ SESSION ON ACCOUNT TO ROLE streamlit_owner_role;
    Copy

  6. Add the following code to your Streamlit app:

    # Import Python packages
import streamlit as st
from snowflake.snowpark.context import get_active_session

st.title("CURRENT_USER() + Row Access Policy in SiS Demo :balloon:")
st.write(
        """You can access `CURRENT_USER()` and data from tables with row access policies
        in Streamlit in Snowflake apps
        """)

# Get the current credentials
session = get_active_session()

st.header('Demo')

st.subheader('Credentials')
sql = "SELECT CURRENT_USER();"
df = session.sql(sql).collect()
st.write(df)

st.subheader('Row Access on a Table')
sql = "SELECT * FROM st_db.st_schema.row_access_policy_test_table;"
df = session.sql(sql).collect()

st.write(df)
    Copy
Language: English