Snowflake integration

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

Download the public key

In the Identity Security Posture Management console, go to Settings Data connectors.
Select Snowflake.
Click Download public key.txt to get the public key.

Create a Snowflake user

Sign in to Snowflake with an ACCOUNTADMIN role.
Click Worksheets.
Click + and select SQL Worksheet to add an SQL worksheet.

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 REPLACE TABLE OKTA_ISPM.PUBLIC.SECURITY_INTEGRATION_DESCRIPTIONS (

   PROPERTY VARCHAR,

   PROPERTY_TYPE VARCHAR,

   PROPERTY_VALUE VARCHAR,

   PROPERTY_DEFAULT VARCHAR,

   INTEGRATION_NAME VARCHAR

);

CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.SECURITY_INTEGRATIONS (

   NAME VARCHAR,

   TYPE VARCHAR,

   CATEGORY VARCHAR,

   ENABLED VARCHAR,

   COMMENT VARCHAR,

   CREATED_ON TIMESTAMP_LTZ,

   ISPM_LAST_UPDATED TIMESTAMP_LTZ

);

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,

   TAG_DATABASE VARCHAR,

   TAG_SCHEMA VARCHAR,

   TAG_NAME VARCHAR,

   POLICY_STATUS VARCHAR

);

CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES (

   NAME VARCHAR,

   OWNER VARCHAR,

   COMMENT VARCHAR,

   DATABASE_NAME VARCHAR,

   SCHEMA_NAME VARCHAR,

   KIND VARCHAR,

   OWNER_ROLE_TYPE VARCHAR,

   OPTIONS VARCHAR,

   CREATED_ON TIMESTAMP_LTZ,

   ISPM_LAST_UPDATED TIMESTAMP_LTZ

);

CREATE OR REPLACE TABLE OKTA_ISPM.PUBLIC.TASK_LOGS (

   MESSAGE VARCHAR,

   CREATED_ON TIMESTAMP_LTZ

);

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

       BEGIN TRANSACTION;

       DELETE FROM OKTA_ISPM.PUBLIC.SECURITY_INTEGRATIONS;

       DELETE FROM OKTA_ISPM.PUBLIC.SECURITY_INTEGRATION_DESCRIPTIONS;

       SHOW SECURITY INTEGRATIONS;

       INSERT INTO OKTA_ISPM.PUBLIC.SECURITY_INTEGRATIONS

           SELECT

               "name" AS NAME,

               "type" AS TYPE,

               "category" AS CATEGORY,

               "enabled" AS ENABLED,

               "comment" AS COMMENT,

               "created_on" AS CREATED_ON,

               current_timestamp() AS ISPM_LAST_UPDATED

           FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

       LET integrations_cursor CURSOR FOR SELECT * FROM OKTA_ISPM.PUBLIC.SECURITY_INTEGRATIONS;

       FOR current_integration IN integrations_cursor DO integration_name := current_integration.NAME;

           BEGIN

               DESCRIBE SECURITY INTEGRATION IDENTIFIER(:integration_name);

               INSERT INTO OKTA_ISPM.PUBLIC.SECURITY_INTEGRATION_DESCRIPTIONS

                   SELECT

                       *,

                       :integration_name

                   FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

           EXCEPTION when other then continue;

           END;

       END FOR;

       INSERT INTO OKTA_ISPM.PUBLIC.TASK_LOGS

           SELECT

               'SECURITY_INTEGRATIONS ended',

               current_timestamp();

       COMMIT;

   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

       BEGIN TRANSACTION;

       DELETE FROM OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES;

       DELETE FROM OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES_DESCRIPTION;

       DELETE FROM OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES_REFERENCES;

       SHOW AUTHENTICATION POLICIES;

       INSERT INTO OKTA_ISPM.PUBLIC.AUTHENTICATION_POLICIES

           SELECT

               "name" as NAME,

               "owner" as OWNER,

               "comment" as COMMENT,

               "database_name" as DATABASE_NAME,

               "schema_name" as SCHEMA_NAME,

               "kind" as KIND,

               "owner_role_type" as OWNER_ROLE_TYPE,

               "options" as OPTIONS,

               "created_on" as CREATED_ON,

               current_timestamp() AS ISPM_LAST_UPDATED

           FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

       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;

           BEGIN

               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

                       )

                   );

           EXCEPTION when other then continue;

           END;

       END FOR;

       INSERT INTO OKTA_ISPM.PUBLIC.TASK_LOGS

           SELECT

               'READ_AUTHENTICATION_POLICIES ended',

               current_timestamp();

       COMMIT;

   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 SELECT ON ALL 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;

Replace <public key> with the public key that you downloaded earlier.
Replace <warehouse> with any standard extra-small warehouse.
Click Run All.
From the Results tab, copy the SNOWFLAKE_ACCOUNT identifier and store it safely.

Allowlist ISPM IP addresses

If your Snowflake account is restricted by network policies, add the following Okta ISPM server IP addresses to your allowlist:

13.52.68.184
54.193.209.206
13.57.96.208
184.72.14.192

Share the parameters with ISPM

In the Identity Security Posture Management console, go to SettingsConnected sources.
Select Snowflake.
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.
Click Submit.