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(送信)]をクリックします。