Snowflake integration

Integrate Identity Security Posture Management (ISPM) with your Snowflake account.

Download the public key

  1. In the Identity Security Posture Management console, go to Settings Data connectors.

  2. Select Snowflake.

  3. Click Download public key.txt to get the public key.

Create a Snowflake user

  1. Sign in to Snowflake with an ACCOUNTADMIN role.

  2. Click Worksheets.

  3. Click + and select SQL Worksheet to add an SQL worksheet.

  4. Copy the following SQL commands into the worksheet:

    Copy
    -- Switch to ACCOUNTADMIN role
    use role accountadmin;
    -- create a new role for the user
    create role OKTA_ISPM_ROLE;
    -- create a user for OKTA_ISPM
    create user OKTA_ISPM_INTEGRATION RSA_PUBLIC_KEY = '<public key>';
    -- grant read-only access to the system-defined, read-only shared database
    grant imported privileges on database snowflake to OKTA_ISPM_ROLE;
    -- grant `Usage` privilege on the default warehouse and system DB
    grant usage on warehouse <warehouse> to role OKTA_ISPM_ROLE;
    -- apply the role to the OKTA_ISPM user
    grant role OKTA_ISPM_ROLE to user OKTA_ISPM_INTEGRATION;
    -- Create a task that reads security integrations
    CREATE DATABASE IF NOT EXISTS OKTA_ISPM;
    CREATE OR ALTER TASK OKTA_ISPM.PUBLIC.READ_SECURITY_INTEGRATIONS
    USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = "XSMALL"
    ALLOW_OVERLAPPING_EXECUTION = FALSE
    COMMENT = 'Okta ISPM Task for reading security integrations'
    AS
    DECLARE
    integration_name VARCHAR;
    BEGIN
    SHOW SECURITY INTEGRATIONS;
    CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.SECURITY_INTEGRATIONS AS (
    SELECT *
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    );
    CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.SECURITY_INTEGRATION_DESCRIPTIONS (
    property VARCHAR,
    property_type VARCHAR,
    property_value VARCHAR,
    property_default VARCHAR,
    integration_name VARCHAR
    );
    LET integrations_cursor CURSOR FOR SELECT * FROM OKTA_ISPM.PUBLIC.SECURITY_INTEGRATIONS;
    FOR current_integration IN integrations_cursor DO integration_name := current_integration."name";
    DESCRIBE SECURITY INTEGRATION IDENTIFIER(:integration_name);
    INSERT INTO OKTA_ISPM.PUBLIC.SECURITY_INTEGRATION_DESCRIPTIONS
    SELECT
    *,
    :integration_name
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    END FOR;
    END;

    -- Create a task that reads the authentication policies
    CREATE OR ALTER TASK OKTA_ISPM.PUBLIC.READ_AUTHENTICATION_POLICIES
    USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = "XSMALL"
    ALLOW_OVERLAPPING_EXECUTION = FALSE
    COMMENT = 'Okta ISPM Task for reading authentication policies'
    AS
    DECLARE
    policy_name VARCHAR;
    policy_db VARCHAR;
    policy_schema VARCHAR;
    policy_full_name VARCHAR;
    BEGIN
    SHOW AUTHENTICATION POLICIES;
    CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES AS (
    SELECT *
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    );
    CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES_DESCRIPTION (
    property VARCHAR,
    property_type VARCHAR,
    property_value VARCHAR,
    property_default VARCHAR,
    policy_name VARCHAR
    );
    CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES_REFERENCES (
    policy_db VARCHAR,
    policy_schema VARCHAR,
    policy_name VARCHAR,
    policy_kind VARCHAR,
    ref_database_name VARCHAR,
    ref_schema_name VARCHAR,
    ref_entity_name VARCHAR,
    ref_entity_domain VARCHAR,
    ref_column_name VARCHAR,
    ref_arg_column_names VARCHAR,
    data_database VARCHAR,
    tag_schema VARCHAR,
    tag_name VARCHAR,
    policy_status VARCHAR
    );

    LET policies_cursor CURSOR FOR SELECT * FROM OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES;
    FOR current_policy IN policies_cursor DO
    policy_db := current_policy."database_name";
    policy_schema := current_policy."schema_name";
    policy_name := current_policy."name";
    policy_full_name := :policy_db || '.' || :policy_schema || '.' || :policy_name;

    DESCRIBE AUTHENTICATION POLICY IDENTIFIER(:policy_name);
    INSERT INTO OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES_DESCRIPTION
    SELECT
    *,
    :policy_name
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

    INSERT INTO OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES_REFERENCES
    SELECT *
    FROM TABLE(
    SNOWFLAKE.INFORMATION_SCHEMA.POLICY_REFERENCES(
    POLICY_NAME => :policy_full_name
    )
    );
    END FOR;
    END
    ;
    -- Grants task related privileges
    GRANT USAGE ON DATABASE OKTA_ISPM TO ROLE OKTA_ISPM_ROLE;
    GRANT USAGE ON SCHEMA OKTA_ISPM.PUBLIC TO ROLE OKTA_ISPM_ROLE;
    GRANT SELECT ON FUTURE TABLES IN DATABASE OKTA_ISPM TO ROLE OKTA_ISPM_ROLE;
    GRANT EXECUTE TASK ON ACCOUNT TO ROLE OKTA_ISPM_ROLE;
    GRANT OPERATE ON TASK OKTA_ISPM.PUBLIC.READ_SECURITY_INTEGRATIONS TO ROLE OKTA_ISPM_ROLE;
    GRANT OPERATE ON TASK OKTA_ISPM.PUBLIC.READ_AUTHENTICATION_POLICIES TO ROLE OKTA_ISPM_ROLE;
    -- Execute tasks
    EXECUTE TASK OKTA_ISPM.PUBLIC.READ_SECURITY_INTEGRATIONS;
    EXECUTE TASK OKTA_ISPM.PUBLIC.READ_AUTHENTICATION_POLICIES;
    -- Get the account identifier
    SELECT CONCAT(
    (CURRENT_ORGANIZATION_NAME()),
    '-',
    (CURRENT_ACCOUNT_NAME())
    ) AS snowflake_account;
  5. Replace <public key> with the public key that you downloaded earlier.

  6. Replace <warehouse> with any standard extra-small warehouse.

  7. Click Run All.

  8. From the Results tab, copy the SNOWFLAKE_ACCOUNT identifier and store it safely.

Share the parameters with ISPM

  1. In the Identity Security Posture Management console, go to SettingsData connectors.

  2. Select Snowflake.

  3. Enter the following values:

    • Username: Enter the value that you used in the CREATE USER command. OKTA_ISPM_INTEGRATION is the default username.

    • Role: Enter the value that you used in the CREATE ROLE and GRANT ROLE commands. OKTA_ISPM_ROLE is the default role.

    • Account: Enter the full Snowflake account identifier that you copied earlier.

    • Warehouse: Enter the name of the warehouse that you used earlier.

  4. Click Submit.