はじめに
「昼間は全ての場所からアクセスが可能、夜間は特定のIPアドレスからのみアクセスを可能にする」といったAutonomous DatabaseのネットワークACLの設定変更を、PL/SQL SDKを使用してAutonomous Database自体にやらせることができるか検証してみました。
1. 準備
こちらの検証では、リソース・プリンシパルを使用していますので、以下のドキュメントを参考にリソース・プリンシパルによるOracle Cloud Infrastructureリソースへのアクセスを有効化します。
今回は、Autonomous Database内のテーブルに格納されているIPアドレスを、Autonomous DatabaseのネットワークACLのホワイトリストに設定する形で実装します。
ホワイトリストに設定するIPアドレスを格納するwhitelist_ipsテーブルを作成します。
SQL> CREATE TABLE whitelist_ips (
2 whitelist_ip VARCHAR2(100),
3 added_on DATE
4 );
表が作成されました。
whitelist_ipsテーブルにホワイトリストに設定するIPアドレスを追加します。
SQL> INSERT INTO whitelist_ips VALUES ('153.240.146.136', sysdate);
1行が作成されました。
SQL> INSERT INTO whitelist_ips VALUES ('202.45.129.182', sysdate);
1行が作成されました。
SQL> COMMIT;
コミットが完了しました。
whitelist_ipsテーブル内のデータを確認します。
SQL> col whitelist_ip for a20
SQL> SELECT * FROM whitelist_ips;
WHITELIST_IP ADDED_ON
-------------------- --------
153.240.146.136 22-10-21
202.45.129.182 22-10-21
SQL>
2. ストアド・プロシージャの作成
Autonomous DatabaseのネットワークACLのホワイトリストにwhitelist_ipsテーブルに格納されているにIPアドレスを設定するプロシージャset_whitelist_ipsを作成します。
プログラムの説明は、コード内のコメントをご確認ください。
CREATE OR REPLACE PROCEDURE set_whitelist_ips
IS
-- ターゲットとなるADBのOCIDを指定
target_adb_ocid VARCHAR2(200) := 'ocid1.autonomousdatabase.oc1.iad.xxxxxxxxxx....';
-- ターゲットとなるADBがあるリージョンを指定
region_id VARCHAR2(30) := 'us-ashburn-1';
-- DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASEの実行に必要な変数の定義
autonomous_database_details dbms_cloud_oci_database_update_autonomous_database_details_t := dbms_cloud_oci_database_update_autonomous_database_details_t();
response dbms_cloud_oci_db_database_update_autonomous_database_response_t;
response_body dbms_cloud_oci_database_autonomous_database_t;
-- カウンタの定義
i NUMBER := 0;
-- whitelist_ips表からwhitelist_ipを取得するカーソルの定義
CURSOR whitelist_ip_cur IS SELECT whitelist_ip FROM whitelist_ips;
BEGIN
-- autonomous_database_details.whitelisted_ipsの初期化
autonomous_database_details.whitelisted_ips := dbms_cloud_oci_database_varchar2_tbl();
-- カーソルをオープン
OPEN whitelist_ip_cur;
-- カーソルがNOTFOUNDになるまで、フェッチしたデータをautonomous_database_details.whitelisted_ipsに追加
LOOP
i := i + 1;
autonomous_database_details.whitelisted_ips.extend(1);
FETCH whitelist_ip_cur INTO autonomous_database_details.whitelisted_ips(i);
EXIT WHEN whitelist_ip_cur%NOTFOUND;
END LOOP;
-- カーソルをクローズ
CLOSE whitelist_ip_cur;
-- DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASEを実行して、ホワイトリストIPを変更
response := DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASE (
autonomous_database_id => target_adb_ocid,
update_autonomous_database_details => autonomous_database_details,
region => region_id,
credential_name => 'OCI$RESOURCE_PRINCIPAL'
);
END;
/
上記を実行してみます。
SQL> CREATE OR REPLACE PROCEDURE set_whitelist_ips
2 IS
3 target_adb_ocid VARCHAR2(200) := 'ocid1.autonomousdatabase.xxxxxxxxxx....';
4 region_id VARCHAR2(30) := 'us-ashburn-1';
5 autonomous_database_details dbms_cloud_oci_database_update_autonomous_database_details_t := dbms_cloud_oci_database_update_autonomous_database_details_t();
6 response dbms_cloud_oci_db_database_update_autonomous_database_response_t;
7 response_body dbms_cloud_oci_database_autonomous_database_t;
8 i NUMBER := 0;
9 CURSOR whitelist_ip_cur IS SELECT whitelist_ip FROM whitelist_ips;
10
11 BEGIN autonomous_database_details.whitelisted_ips := dbms_cloud_oci_database_varchar2_tbl();
12 OPEN whitelist_ip_cur;
13 LOOP
14 i := i + 1;
15 autonomous_database_details.whitelisted_ips.extend(1);
16 FETCH whitelist_ip_cur INTO autonomous_database_details.whitelisted_ips(i);
17 EXIT WHEN whitelist_ip_cur%NOTFOUND;
18 END LOOP;
19 CLOSE whitelist_ip_cur;
20 response := DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASE (
21 autonomous_database_id => target_adb_ocid,
22 update_autonomous_database_details => autonomous_database_details,
23 region => region_id,
24 credential_name => 'OCI$RESOURCE_PRINCIPAL'
25 );
26 END;
27 /
プロシージャが作成されました。
SQL>
Autonomous DatabaseのネットワークACLのホワイトリストにwhitelist_ipsテーブルに格納されているにIPアドレスを設定するプロシージャset_whitelist_ipsが作成できました。
作成したプロシージャset_whitelist_ipsを実行してみます。
SQL> exec set_whitelist_ips
PL/SQLプロシージャが正常に完了しました。
SQL>
問題なくプロシージャが実行できました。
OCIコンソールでAutonomous Databaseの詳細画面を確認してみます。
状態が「更新中」に変わっています。
状態が「使用可能」になったら、「ネットワーク」セクションの「アクセス制御リスト」の横にある「編集」をクリックして、設定されているアクセス制御リストの内容を確認します。
whitelist_ipsテーブルに格納されているIPアドレスが、ホワイトリストに設定されていることが確認できました。
次に、Autonomous DatabaseのネットワークACLのホワイトリストに、全ての場所からのアクセスを許可することを意味する'0.0.0.0/0'を設定するプロシージャreset_whitelist_ipsを作成します。
プログラムの説明は、コード内のコメントをご確認ください。
CREATE OR REPLACE PROCEDURE reset_whitelist_ips
IS
-- ターゲットとなるADBのOCIDを指定
target_adb_ocid VARCHAR2(200) := 'ocid1.autonomousdatabase.oc1.iad.xxxxxxxxxx....';
-- ターゲットとなるADBがあるリージョンを指定
region_id VARCHAR2(30) := 'us-ashburn-1';
-- DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASEの実行に必要な変数の定義
autonomous_database_details dbms_cloud_oci_database_update_autonomous_database_details_t := dbms_cloud_oci_database_update_autonomous_database_details_t();
response dbms_cloud_oci_db_database_update_autonomous_database_response_t;
response_body dbms_cloud_oci_database_autonomous_database_t;
BEGIN
-- autonomous_database_details.whitelisted_ipsに'0.0.0.0/0'を設定
autonomous_database_details.whitelisted_ips := dbms_cloud_oci_database_varchar2_tbl('0.0.0.0/0');
-- DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASEを実行して、ホワイトリストIPを変更
response := DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASE (
autonomous_database_id => target_adb_ocid,
update_autonomous_database_details => autonomous_database_details,
region => region_id,
credential_name => 'OCI$RESOURCE_PRINCIPAL'
);
END;
/
上記を実行します。
SQL> CREATE OR REPLACE PROCEDURE reset_whitelist_ips
2 IS
3 target_adb_ocid VARCHAR2(200) := 'ocid1.autonomousdatabase.oc1.iad.xxxxxxxxxx....';
4 region_id VARCHAR2(30) := 'us-ashburn-1';
5 autonomous_database_details dbms_cloud_oci_database_update_autonomous_database_details_t := dbms_cloud_oci_database_update_autonomous_database_details_t();
6 response dbms_cloud_oci_db_database_update_autonomous_database_response_t;
7 response_body dbms_cloud_oci_database_autonomous_database_t;
8
9 BEGIN
10 autonomous_database_details.whitelisted_ips := dbms_cloud_oci_database_varchar2_tbl('0.0.0.0/0');
11 response := DBMS_CLOUD_OCI_DB_DATABASE.UPDATE_AUTONOMOUS_DATABASE (
12 autonomous_database_id => target_adb_ocid,
13 update_autonomous_database_details => autonomous_database_details,
14 region => region_id,
15 credential_name => 'OCI$RESOURCE_PRINCIPAL'
16 );
17 END;
18 /
プロシージャが作成されました。
SQL>
Autonomous DatabaseのネットワークACLのホワイトリストに'0.0.0.0/0'を設定するプロシージャreset_whitelist_ipsが作成できました。
作成したプロシージャreset_whitelist_ipsを実行してみます。
SQL> exec reset_whitelist_ips
PL/SQLプロシージャが正常に完了しました。
SQL>
問題なくプロシージャが実行できました。
OCIコンソールでAutonomous Databaseの詳細画面を確認してみます。
状態が「更新中」に変わっています。
状態が「使用可能」になったら、「ネットワーク」セクションの「アクセス制御リスト」の横にある「編集」をクリックして、設定されているアクセス制御リストの内容を確認します。
'0.0.0.0/0'がホワイトリストに設定されていることが確認できました。
3. プロシージャの定期実行の設定
日本時間の毎日午後10時にSET_WHITELIST_IPプロシージャを実行するジョブ「SET_WHITELIST_IP_JOB」を作成します。
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'SET_WHITELIST_IP_JOB',
4 job_type => 'STORED_PROCEDURE',
5 job_action => 'ADMIN.SET_WHITELIST_IP',
6 start_date => TO_TIMESTAMP_TZ('2022-10-22 22:00:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
7 repeat_interval => 'FREQ=DAILY;INTERVAL=1',
8 auto_drop => FALSE,
9 enabled => TRUE
10 );
11 END;
12 /
PL/SQLプロシージャが正常に完了しました。
SQL>
ジョブ「SET_WHITELIST_IP_JOB」が作成できました。
次に、日本時間の毎日午前8時にRESET_WHITELIST_IPプロシージャを実行するジョブ「RESET_WHITELIST_IP_JOB」を作成します。
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'RESET_WHITELIST_IP_JOB',
4 job_type => 'STORED_PROCEDURE',
5 job_action => 'ADMIN.RESET_WHITELIST_IP',
6 start_date => TO_TIMESTAMP_TZ('2022-10-22 08:00:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
7 repeat_interval => 'FREQ=DAILY;INTERVAL=1',
8 auto_drop => FALSE,
9 enabled => TRUE
10 );
11 END;
12 /
PL/SQLプロシージャが正常に完了しました。
SQL>
ジョブ「RESET_WHITELIST_IP_JOB」が作成できました。
ジョブが登録されているかをUSER_SCHEDULER_JOBSで確認してみます。
SQL> SELECT
2 job_name, start_date, repeat_interval, enabled
3 FROM user_scheduler_jobs
4 WHERE job_name lIKE '%_WHITELIST_IP_JOB';
JOB_NAME START_DATE REPEAT_INTERVAL ENABLE
---------------------- ------------------------------- --------------------- ------
RESET_WHITELIST_IP_JOB 22-10-22 08:00:00.000000 +09:00 FREQ=DAILY;INTERVAL=1 TRUE
SET_WHITELIST_IP_JOB 22-10-22 22:00:00.000000 +09:00 FREQ=DAILY;INTERVAL=1 TRUE
SQL>
ジョブが登録され、正しくスケジュールされていることが確認できました。
これで、「朝8時から夜10時までは全ての場所からアクセスが可能、夜10時から翌朝8時までは特定のIPアドレスからのみアクセスを可能にする」といったことが、Autonomous Database単体で実現できることがわかりました。
参考資料
リソース・プリンシパルによるOracle Cloud Infrastructureリソースへのアクセスの有効化
UPDATE_AUTONOMOUS_DATABASE Function
DBMS_CLOUD_OCI_DB_DATABASE_UPDATE_AUTONOMOUS_DATABASE_RESPONSE_T Type
DBMS_CLOUD_OCI_DATABASE_UPDATE_AUTONOMOUS_DATABASE_DETAILS_T Type
DBMS_CLOUD_OCI_DATABASE_VARCHAR2_TBL Type
DBMS_CLOUD_OCI_DATABASE_AUTONOMOUS_DATABASE_T Type
DBMS_SCHEDULER