Overview

What You'll Learn

What You'll Build

What You'll Need

Prerequisites

Setup

Open Snowflake Notebook

aside positive NOTE: On the above mentioned GitHub page, please click on the download icon (upon hover it should display "Download raw file").

Creating the User Dataset

Approach 1: Creation via SQL Query

USE ROLE ACCOUNTADMIN; -- Sets current role to ACCOUNTADMIN
CREATE OR REPLACE WAREHOUSE MFA_DEMO_WH; -- By default, this creates an XS Standard Warehouse
CREATE OR REPLACE DATABASE MFA_DEMO_DB;
CREATE OR REPLACE SCHEMA MFA_DEMO_SCHEMA;
CREATE OR REPLACE STAGE MFA_DEMO_ASSETS; -- Store data files

-- create csv format
CREATE FILE FORMAT IF NOT EXISTS MFA_DEMO_DB.MFA_DEMO_SCHEMA.CSVFORMAT 
    SKIP_HEADER = 1 
    TYPE = 'CSV';

-- Create stage and load external demo data from S3
CREATE STAGE IF NOT EXISTS MFA_DEMO_DB.MFA_DEMO_SCHEMA.MFA_DEMO_DATA 
    FILE_FORMAT = MFA_DEMO_DB.MFA_DEMO_SCHEMA.CSVFORMAT 
    URL = 's3://sfquickstarts/sfguide_building_mfa_audit_system_with_streamlit_in_snowflake_notebooks/demo_data.csv';
    -- https://sfquickstarts.s3.us-west-1.amazonaws.com/sfguide_building_mfa_audit_system_with_streamlit_in_snowflake_notebooks/demo_data.csv

LS @MFA_DEMO_DATA; -- List contents of the stage we just created

-- Create a new data table called MFA_DEMO
CREATE OR REPLACE TABLE MFA_DEMO_DB.MFA_DEMO_SCHEMA.MFA_DATA (
    USER_ID NUMBER,
    NAME VARCHAR(100),
    CREATED_ON TIMESTAMP,
    DELETED_ON TIMESTAMP,
    LOGIN_NAME VARCHAR(100),
    DISPLAY_NAME VARCHAR(100),
    FIRST_NAME VARCHAR(50),
    LAST_NAME VARCHAR(50),
    EMAIL VARCHAR(255),
    MUST_CHANGE_PASSWORD BOOLEAN,
    HAS_PASSWORD BOOLEAN,
    COMMENT VARCHAR(255),
    DISABLED BOOLEAN,
    SNOWFLAKE_LOCK BOOLEAN,
    DEFAULT_WAREHOUSE VARCHAR(100),
    DEFAULT_NAMESPACE VARCHAR(100),
    DEFAULT_ROLE VARCHAR(100),
    EXT_AUTHN_DUO BOOLEAN,
    EXT_AUTHN_UID VARCHAR(100),
    HAS_MFA BOOLEAN,
    BYPASS_MFA_UNTIL TIMESTAMP,
    LAST_SUCCESS_LOGIN TIMESTAMP,
    EXPIRES_AT TIMESTAMP,
    LOCKED_UNTIL_TIME TIMESTAMP,
    HAS_RSA_PUBLIC_KEY BOOLEAN,
    PASSWORD_LAST_SET_TIME TIMESTAMP,
    OWNER VARCHAR(100),
    DEFAULT_SECONDARY_ROLE VARCHAR(100),
    TYPE VARCHAR(50)
);

-- Copy the data from your stage to this newly created table
COPY INTO MFA_DEMO_DB.MFA_DEMO_SCHEMA.MFA_DATA
    FROM @MFA_DEMO_DB.MFA_DEMO_SCHEMA.MFA_DEMO_DATA

Approach 2: Creation via GUI

Setting up Email Notifications

CREATE OR REPLACE NOTIFICATION INTEGRATION my_email_int
  TYPE=EMAIL
  ENABLED=TRUE;

aside positive IMPORTANT: This setup ensures you have the necessary privileges to create notification integrations

Creating the MFA Status Query

Writing the Query

SELECT USER_ID, LOGIN_NAME, EMAIL, HAS_MFA
FROM CHANINN_DEMO_DATA.PUBLIC.MFA_DATA
WHERE HAS_MFA = 'FALSE'

Testing Email Notifications

CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    '[email protected]',
    'Email subject goes here',
    'Hello world! This is a test message!'
);

aside positive IMPORTANT: Replace [email protected] with the email address tied to your user account.

Building the Streamlit Interface

Creating the Interactive Dashboard

from snowflake.snowpark.context import get_active_session
import streamlit as st

session = get_active_session()

# DataFrame of users and their MFA status
st.header('MFA activation status')

mfa_selection = st.selectbox('Select an MFA status:', ('All', 'MFA Activated', 'MFA Not Activated'))
if mfa_selection == 'All':
    df = session.sql(
              """SELECT USER_ID, LOGIN_NAME, EMAIL, HAS_MFA 
                    FROM CHANINN_DEMO_DATA.PUBLIC.MFA_DATA"""
            ).to_pandas()
    paragraph = "<p>Here's the Multi-Factor Authentication status of all users. Please refer users to the <a href='https://docs.snowflake.com/en/user-guide/security-mfa'>Docs page on MFA</a> to activate MFA.</p>"
if mfa_selection == 'MFA Activated':
    df = session.sql(
              "SELECT USER_ID, LOGIN_NAME, EMAIL, HAS_MFA FROM CHANINN_DEMO_DATA.PUBLIC.MFA_DATA WHERE HAS_MFA = 'TRUE'"
            ).to_pandas()
    paragraph = "<p>Congratulations, these users have activated their Multi-Factor Authentication!</p>"
if mfa_selection == 'MFA Not Activated':
    df = session.sql(
              "SELECT USER_ID, LOGIN_NAME, EMAIL, HAS_MFA FROM CHANINN_DEMO_DATA.PUBLIC.MFA_DATA WHERE HAS_MFA = 'FALSE'"
            ).to_pandas()
    paragraph = "<p>It appears that the following users have not activated Multi-Factor Authentication. Please refer users to the <a href='https://docs.snowflake.com/en/user-guide/security-mfa'>Docs page on MFA</a> to activate MFA.</p>"
st.dataframe(df)

# Send Email
if st.button('Send Report'):
    email= '[email protected]'
    email_subject = "Important: Activate Multi-Factor Authentication for User's Account"
    header = '<p>Dear System Administrator,</p>'
    body = header + '\n' + paragraph + '\n' + df.to_html(index=False, justify='left')

    session.call('SYSTEM$SEND_EMAIL',
                             'my_email_int',
                              email,
                              email_subject,
                              body,
                             'text/html')
    st.success('Report sent!', icon='✅')

Conclusion

What You Learned

Related Resources