Snowflakeの統合
Identity Security Posture Management(ISMP)をSnowflakeアカウントに統合します。
公開鍵をダウンロードする
-
Identity Security Posture Managementコンソールで に移動します。
-
[Snowflake]を選択します。
-
[Download public key.txt(public key.txtのダウンロード)]をクリックして公開鍵を取得します。
Snowflakeユーザーを作成する
-
ACCOUNTADMINロールでSnowflakeにサインインします。
-
[Worksheets(ワークシート)]をクリックします。
-
[+]をクリックしてから、[SQL Worksheet(SQLワークシート)]を選択してSQLワークシートを追加します。
-
以下の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; -
<public key>を、先ほどダウンロードした公開鍵に置き換えます。
-
<warehouse>を、標準タイプでサイズがXSの任意のウェアハウスに置き換えます。
-
[Run All(すべて実行)]をクリックします。
-
結果タブから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と共有する
-
Identity Security Posture Managementコンソールで に移動します。
-
[Snowflake]を選択します。
-
以下の値を入力します。
-
Username(ユーザー名):CREATE USERコマンドで使用した値を入力します。OKTA_ISPM_INTEGRATIONがデフォルトのユーザー名です。
-
Role(ロール):CREATE ROLEおよびGRANT ROLEコマンドで使用した値を入力します。OKTA_ISPM_ROLEがデフォルトのロールです。
-
Account(アカウント):先ほどコピーしたSnowflakeアカウント識別子を入力します。
-
Warehouse(ウェアハウス):先ほど使用したウェアハウスの名前を入力します。
-
-
[Submit(送信)]をクリックします。