[OCI]Autonomous Database：ネットワークアクセス制限の定期的な変更をAutonomous Database自体にやらせてみた


# はじめに
「昼間は全ての場所からアクセスが可能、夜間は特定のIPアドレスからのみアクセスを可能にする」といったAutonomous DatabaseのネットワークACLの設定変更を、PL/SQL SDKを使用してAutonomous Database自体にやらせることができるか検証してみました。

# 1. 準備

こちらの検証では、リソース・プリンシパルを使用していますので、以下のドキュメントを参考にリソース・プリンシパルによるOracle Cloud Infrastructureリソースへのアクセスを有効化します。

https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/resource-principal-enable.html

今回は、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を作成します。
プログラムの説明は、コード内のコメントをご確認ください。
```sql: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の詳細画面を確認してみます。
![スクリーンショット 2022-10-21 17.01.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/9de7b627-a392-38cd-d72a-da4ee0e19b32.png)
状態が「更新中」に変わっています。

状態が「使用可能」になったら、「ネットワーク」セクションの「アクセス制御リスト」の横にある「編集」をクリックして、設定されているアクセス制御リストの内容を確認します。
![スクリーンショット 2022-10-21 17.01.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/b2cd0168-923a-47aa-3fc4-f478135d0706.png)
whitelist_ipsテーブルに格納されているIPアドレスが、ホワイトリストに設定されていることが確認できました。
![スクリーンショット 2022-10-21 17.02.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/de47886f-0cb5-a9f4-06f2-4e6823fc52f2.png)


次に、Autonomous DatabaseのネットワークACLのホワイトリストに、全ての場所からのアクセスを許可することを意味する'0.0.0.0/0'を設定するプロシージャreset_whitelist_ipsを作成します。
プログラムの説明は、コード内のコメントをご確認ください。
```sql: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の詳細画面を確認してみます。
![スクリーンショット 2022-10-21 17.13.34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/81b7ac4f-0643-9b59-94f6-c2ecbcfc2593.png)
状態が「更新中」に変わっています。

状態が「使用可能」になったら、「ネットワーク」セクションの「アクセス制御リスト」の横にある「編集」をクリックして、設定されているアクセス制御リストの内容を確認します。
![スクリーンショット 2022-10-21 17.01.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/3ee44d1e-ea33-b152-7ea9-c8af6bbee610.png)
'0.0.0.0/0'がホワイトリストに設定されていることが確認できました。
![スクリーンショット 2022-10-21 17.14.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/cf43b756-0bf2-f210-ed8a-f4da73290bbd.png)

# 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リソースへのアクセスの有効化](https://docs.oracle.com/cd//E83857_01/paas/autonomous-database/adbsa/resource-principal-enable.html)
[UPDATE_AUTONOMOUS_DATABASE Function](https://docs.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_db_database.html#ADSDK-GUID-ED5170F2-84C3-4326-9333-2244CED7D837)
[DBMS_CLOUD_OCI_DB_DATABASE_UPDATE_AUTONOMOUS_DATABASE_RESPONSE_T Type](https://docs.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_db_database_t.html#ADSDK-GUID-21034353-5AFF-4BCB-8FF6-544A896EDBEE)
[DBMS_CLOUD_OCI_DATABASE_UPDATE_AUTONOMOUS_DATABASE_DETAILS_T Type](https://docs.oracle.com/en-us/iaas/pl-sql-sdk/doc/database_t.html#ADSDK-GUID-AF1B2C2D-FA3A-4096-81CB-B23E26C90927)
[DBMS_CLOUD_OCI_DATABASE_VARCHAR2_TBL Type](https://docs.oracle.com/en-us/iaas/pl-sql-sdk/doc/database_t.html#ADSDK-GUID-76B937B9-5676-4904-AB0B-229983B92117)
[DBMS_CLOUD_OCI_DATABASE_AUTONOMOUS_DATABASE_T Type](https://docs.oracle.com/en-us/iaas/pl-sql-sdk/doc/database_t.html#ADSDK-GUID-8884E749-70DD-450B-890B-A26C881E1F4B)
[DBMS_SCHEDULER](https://docs.oracle.com/cd/F19136_01/arpls/DBMS_SCHEDULER.html)