Snowflakeの統合

Identity Security Posture Management（ISMP）をSnowflakeアカウントに統合します。

公開鍵をダウンロードする

1. Identity Security Posture Managementコンソールで［Settings（設定）］［Data connectors（データコネクター）］に移動します。

2. ［Snowflake］を選択します。

3. ［Download public key.txt（public key.txtのダウンロード）］をクリックして公開鍵を取得します。

Snowflakeユーザーを作成する

1. ACCOUNTADMINロールでSnowflakeにサインインします。

2. ［Worksheets（ワークシート）］をクリックします。

3. ［+］をクリックしてから、［SQL Worksheet（SQLワークシート）］を選択してSQLワークシートを追加します。

4. 以下のSQLコマンドをコピーしてワークシートに貼り付けます。

   コピー

   -- 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;

5. <public key>を、先ほどダウンロードした公開鍵に置き換えます。

6. <warehouse>を、標準タイプでサイズがXSの任意のウェアハウスに置き換えます。

7. ［Run All（すべて実行）］をクリックします。

   

8. 結果タブからSNOWFLAKE_ACCOUNT識別子をコピーし、安全な場所に保管します。

ISPM IPアドレスを許可リストに追加する

Snowflakeアカウントがネットワークポリシーの制約を受けている場合には、 以下のOkta ISPMサーバーのIPアドレスを許可リストに追加します。

• 13.52.68.184

• 54.193.209.206

• 13.57.96.208

• 184.72.14.192

パラメーターをISPMと共有する

1. Identity Security Posture Managementコンソールで［Settings（設定）］［Connected sources（接続済みソース）］に移動します。

2. ［Snowflake］を選択します。

3. 以下の値を入力します。

   • Username（ユーザー名）：CREATE USERコマンドで使用した値を入力します。OKTA_ISPM_INTEGRATIONがデフォルトのユーザー名です。

   • Role（ロール）：CREATE ROLEおよびGRANT ROLEコマンドで使用した値を入力します。OKTA_ISPM_ROLEがデフォルトのロールです。

   • Account（アカウント）：先ほどコピーしたSnowflakeアカウント識別子を入力します。

   • Warehouse（ウェアハウス）：先ほど使用したウェアハウスの名前を入力します。

4. ［Submit（送信）］をクリックします。