[OCI] Auditサービスの監査ログぜんぶAutonomous Databaseに保存する大作戦

# はじめに
前回の記事「[PL/SQL SDKを使ってAutonomous DatabaseにAuditサービスの監査ログを取得してみた](https://qiita.com/500InternalServerError/items/e656b84e27e4b14373f6)」では、OCIのAuditサービスの監査ログを取得するところまででしたが、今回は取得した監査ログの全ての項目をAutonomous Database内の表に保存してみました。

# 1. 監査ログを保存する表の作成

PL/SQL APIで取得できるAuditサービスの監査ログの項目は、以下のドキュメントで確認しました。

[DBMS_CLOUD_OCI_AD_AUDIT.LIST_EVENTSファンクション]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit.html#ADSDK-GUID-E5F76E14-9DB2-41F9-8BF5-782FE630B738)
[DBMS_CLOUD_OCI_AD_AUDIT_LIST_EVENTS_RESPONSE_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-26FBC348-84AC-4AE4-9379-79FDD26BC5C5)
[DBMS_CLOUD_OCI_AD_AUDIT_AUDIT_EVENT_TBL型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-651F19A4-1E34-47A7-944A-35398372440B)
[DBMS_CLOUD_OCI_AD_AUDIT_AUDIT_EVENT_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-30F462AA-AEF0-4738-9E7E-F073C546C835)
[DBMS_CLOUD_OCI_AD_AUDIT_DATA_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-302532A5-8275-4B7F-95F5-CDE33BDA4E39)
[DBMS_CLOUD_OCI_AD_AUDIT_IDENTITY_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-B118533C-5C85-44B5-8141-7A4603FC4F87)
[DBMS_CLOUD_OCI_AD_AUDIT_REQUEST_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-6FE336F0-4805-40CB-BBCA-91C772796C30)
[DBMS_CLOUD_OCI_AD_AUDIT_RESPONSE_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-0EF6EC68-052C-411D-AAEC-9302F63A2D7A)
[DBMS_CLOUD_OCI_AD_AUDIT_STATE_CHANGE_T型]
(https://docs.cloud.oracle.com/en-us/iaas/pl-sql-sdk/doc/dbms_cloud_oci_ad_audit_t.html#ADSDK-GUID-69822335-A205-4FFC-BA0F-7D6FB596E44D)

ざっくり言うと、
・DBMS_CLOUD_OCI_AD_AUDIT.LIST_EVENTSの戻り値は、DBMS_CLOUD_OCI_AD_AUDIT_LIST_EVENTS_RESPONSE_T型です。
・DBMS_CLOUD_OCI_AD_AUDIT_LIST_EVENTS_RESPONSE_Tのレスポンスボディは、DBMS_CLOUD_OCI_AD_AUDIT_AUDIT_EVENT_Tの集合体であるDBMS_CLOUD_OCI_AD_AUDIT_AUDIT_EVENT_TBL型です。
・DBMS_CLOUD_OCI_AD_AUDIT_AUDIT_EVENT_T型には、DBMS_CLOUD_OCI_AD_AUDIT_DATA_T型が含まれます。
・DBMS_CLOUD_OCI_AD_AUDIT_DATA_T型には、以下の4つの型が含まれます。
　- DBMS_CLOUD_OCI_AD_AUDIT_IDENTITY_T型
　- DBMS_CLOUD_OCI_AD_AUDIT_REQUEST_T型
　- DBMS_CLOUD_OCI_AD_AUDIT_RESPONSE_T型
　- DBMS_CLOUD_OCI_AD_AUDIT_STATE_CHANGE_T型

上記をベースに、Auditサービスの監査ログを保存する表（oci_audit_log）を作成します。

Pl/SQL SDK経由で取得できる監査ログの全項目を列として定義し、JSON形式のデータの列には、IS JSONのチェック制約をつけました。

DDLは以下のようになりました。

```
DROP TABLE oci_audit_log;

CREATE TABLE oci_audit_log (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
-- dbms_cloud_oci_ad_audit_audit_event_t
  event_type VARCHAR2(4000),
  cloud_events_version VARCHAR2(4000),
  event_type_version VARCHAR2(4000),
  source VARCHAR2(4000),
  event_id VARCHAR2(4000),
  event_time TIMESTAMP WITH TIME ZONE,
  content_type VARCHAR2(4000),
  -- data dbms_cloud_oci_ad_audit_data_t  
    event_grouping_id VARCHAR2(4000),
    event_name VARCHAR2(4000),
    compartment_id VARCHAR2(4000),
    compartment_name VARCHAR2(4000),
    resource_name VARCHAR2(4000),
    resource_id VARCHAR2(4000),
    availability_domain VARCHAR2(4000),
    freeform_tags VARCHAR2(4000) CONSTRAINT freeform_tags_json_chk CHECK(freeform_tags IS JSON),
    defined_tags VARCHAR2(4000) CONSTRAINT defined_tags_json_chk CHECK(defined_tags IS JSON),
    -- identity dbms_cloud_oci_ad_audit_identity_t,
    identity_principal_name VARCHAR2(4000),
    identity_principal_id VARCHAR2(4000),
    identity_auth_type VARCHAR2(4000),
    identity_caller_name VARCHAR2(4000),
    identity_caller_id VARCHAR2(4000),
    identity_tenant_id VARCHAR2(4000),
    identity_ip_address VARCHAR2(4000),
    identity_credentials VARCHAR2(4000),
    identity_user_agent VARCHAR2(4000),
    identity_console_session_id VARCHAR2(4000),
    -- request dbms_cloud_oci_ad_audit_request_t,
    request_id VARCHAR2(4000),
    request_path VARCHAR2(4000),
    request_action VARCHAR2(4000),
    request_parameters VARCHAR2(4000) CONSTRAINT request_parameters_json_chk CHECK(request_parameters IS JSON),
    request_headers VARCHAR2(10000) CONSTRAINT request_headers_json_chk CHECK(request_headers IS JSON),
    -- response dbms_cloud_oci_ad_audit_response_t,
    response_status VARCHAR2(4000),
    response_time TIMESTAMP WITH TIME ZONE,
    response_headers VARCHAR2(4000) CONSTRAINT response_headers_json_chk CHECK(response_headers IS JSON),
    response_payload VARCHAR2(4000) CONSTRAINT response_payload_json_chk CHECK(response_payload IS JSON),
    response_message VARCHAR2(4000),
    -- state_change dbms_cloud_oci_ad_audit_state_change_t,
    state_change_previous VARCHAR2(4000) CONSTRAINT state_change_previous_json_chk CHECK(state_change_previous IS JSON),
    state_change_l_current VARCHAR2(4000) CONSTRAINT state_change_l_current_json_chk CHECK(state_change_l_current IS JSON),
    -- additional_details json_element_t,
    additional_details VARCHAR2(4000) CONSTRAINT additional_details_json_chk CHECK(additional_details IS JSON)
);
```
# 2.PL/SQLプロシージャの作成
次に、PL/SQL SDKを使用して指定した日付（日本時間）の監査ログを取得し、oci_audit_log表に保存するPL/SQLプロシージャ「get_audit_log」を作成します。

```
CREATE OR REPLACE PROCEDURE get_audit_log (get_date IN varchar2)
IS
  compartment_id VARCHAR2(100) := 'ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXX';
  region VARCHAR2(20) := 'ap-tokyo-1';
  credential_name VARCHAR2(30) := 'MY_SDK_CRED';

  loop_flg    NUMBER := 1;
  page        VARCHAR2(1000) :='';
  
  start_time  TIMESTAMP WITH TIME ZONE;
  end_time    TIMESTAMP WITH TIME ZONE;
  response          dbms_cloud_oci_ad_audit_list_events_response_t;
  response_body     dbms_cloud_oci_ad_audit_audit_event_tbl;
  json_obj          json_object_t;
  l_keys            json_key_list;
  
  audit_event dbms_cloud_oci_ad_audit_audit_event_t;
  audit_data dbms_cloud_oci_ad_audit_data_t;
  audit_identity dbms_cloud_oci_ad_audit_identity_t;
  audit_request dbms_cloud_oci_ad_audit_request_t;
  audit_response dbms_cloud_oci_ad_audit_response_t;
  audit_state_change dbms_cloud_oci_ad_audit_state_change_t;
  audit_additional_details json_element_t;
  
  freeform_tags VARCHAR2(4000) := NULL;
  defined_tags VARCHAR2(4000) := NULL;
  request_parameters VARCHAR2(4000) := NULL;
  request_headers VARCHAR2(10000) := NULL;	  
  response_headers VARCHAR2(4000) := NULL;
  payload VARCHAR2(4000) := NULL;
  previous VARCHAR2(4000) := NULL;
  l_current VARCHAR2(4000) := NULL;
  additional_details VARCHAR2(4000) := NULL;
  
BEGIN
  -- セッションのNLS_TIMESTAMP_TZ_FORMATをRFC 3339に合わせて設定
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT=''YYYY-MM-DD"T"HH24:MI:SS"Z"''';
  -- 監査ログ取得対象期間の開始時間/終了時間をUTCで設定
  SELECT TO_TIMESTAMP_TZ(get_date||' 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF') -9/24 INTO start_time FROM dual;
  SELECT TO_TIMESTAMP_TZ(get_date||' 23:59:59.99','YYYY-MM-DD HH24:MI:SS.FF') -9/24 INTO end_time FROM dual;
  
  -- 複数ページがある場合のループ処理（デフォルトでは1ページあたり100レコード）
  WHILE loop_flg = 1 LOOP
    loop_flg := 0;
	
    -- 指定した日の監査ログを取得
    response := DBMS_CLOUD_OCI_AD_AUDIT.list_events (
      compartment_id => compartment_id,
      start_time => start_time,
      end_time => end_time,
      page => page,
      region => region,
      credential_name => credential_name
    );
	
    -- responseからresponse_bodyを取得
    response_body := response.response_body;
    
    -- レスポンスヘッダ内にopc-next-pageがあるか確認
    -- もしある場合は次のページがあるのでpageをセットして再度list_eventsを実行
    json_obj := response.headers;
    l_keys := json_obj.get_keys;
    FOR i IN 1..l_keys.count LOOP 
      IF l_keys(i) = 'opc-next-page' THEN 
        loop_flg := 1;
        page := RTRIM(SUBSTR(json_obj.get(l_keys(i)).to_string,3),'"');
      END IF;
    END LOOP;

    -- 取得した監査ログから個々の監査レコードの内容を取得
    FOR i IN 1 .. response_body.count LOOP
      audit_event := response_body(i);
      audit_data := audit_event.data;
      audit_identity := audit_data.identity;
      audit_request := audit_data.request;
      audit_response := audit_data.response;
      audit_state_change := audit_data.state_change;
      audit_additional_details := audit_data.additional_details;

     -- JSONオブジェクトを文字列に変換/JSONオブジェクトが空もしくは'null'の場合を考慮
      IF audit_data.freeform_tags IS NOT NULL THEN
        freeform_tags := audit_data.freeform_tags.to_string();
        IF freeform_tags = 'null' THEN freeform_tags := NULL; END IF;
      END IF;
      IF audit_data.defined_tags IS NOT NULL THEN
        defined_tags := audit_data.defined_tags.to_string();
        IF defined_tags = 'null' THEN defined_tags := NULL; END IF;
      END IF;
      request_parameters := audit_request.parameters.to_string();
      request_headers := audit_request.headers.to_string();
      response_headers := audit_response.headers.to_string();
      IF audit_response.payload IS NOT NULL THEN
        payload := audit_response.payload.to_string();
        IF payload = 'null' THEN payload := NULL; END IF;
      END IF;
      IF audit_state_change.previous IS NOT NULL THEN
	    previous := audit_state_change.previous.to_string();
        IF previous = 'null' THEN previous := NULL; END IF;
      END IF;
      IF audit_state_change.l_current IS NOT NULL THEN
	    l_current := audit_state_change.l_current.to_string();
        IF l_current = 'null' THEN l_current := NULL; END IF;
      END IF;      
      IF audit_data.additional_details IS NOT NULL THEN
        additional_details := audit_data.additional_details.to_string();
        IF additional_details = 'null' THEN additional_details := NULL; END IF;
      END IF;

      -- 取得した監査レコードをoci_audit_log表にINSERT
      INSERT INTO oci_audit_log (
--      dbms_cloud_oci_ad_audit_audit_event_t
        event_type ,
        cloud_events_version ,
        event_type_version ,
        source ,
        event_id ,
        event_time ,
        content_type ,
--      data dbms_cloud_oci_ad_audit_data_t  
          event_grouping_id ,
          event_name ,
          compartment_id ,
          compartment_name ,
          resource_name ,
          resource_id ,
          availability_domain ,
          freeform_tags ,
          defined_tags ,
--        identity dbms_cloud_oci_ad_audit_identity_t,
            identity_principal_name ,
            identity_principal_id ,
            identity_auth_type ,
            identity_caller_name ,
            identity_caller_id ,
            identity_tenant_id ,
            identity_ip_address ,
            identity_credentials ,
            identity_user_agent ,
            identity_console_session_id ,
--        request dbms_cloud_oci_ad_audit_request_t,
            request_id ,
            request_path ,
            request_action ,
            request_parameters ,
            request_headers ,
--        response dbms_cloud_oci_ad_audit_response_t,
            response_status ,
            response_time ,
            response_headers ,
            response_payload ,
            response_message ,
--        state_change dbms_cloud_oci_ad_audit_state_change_t,
            state_change_previous ,
            state_change_l_current ,
--        additional_details json_element_t,
            additional_details 
      ) VALUES (
--      dbms_cloud_oci_ad_audit_audit_event_t
        audit_event.event_type ,
        audit_event.cloud_events_version ,
        audit_event.event_type_version ,
        audit_event.source ,
        audit_event.event_id ,
        audit_event.event_time ,
        audit_event.content_type ,
--      data dbms_cloud_oci_ad_audit_data_t  
          audit_data.event_grouping_id ,
          audit_data.event_name ,
          audit_data.compartment_id ,
          audit_data.compartment_name ,
          audit_data.resource_name ,
          audit_data.resource_id ,
          audit_data.availability_domain ,
          freeform_tags ,
          defined_tags ,
--        identity dbms_cloud_oci_ad_audit_identity_t,
            audit_identity.principal_name ,
            audit_identity.principal_id ,
            audit_identity.auth_type ,
            audit_identity.caller_name ,
            audit_identity.caller_id ,
            audit_identity.tenant_id ,
            audit_identity.ip_address ,
            audit_identity.credentials ,
            audit_identity.user_agent ,
            audit_identity.console_session_id ,
--        request dbms_cloud_oci_ad_audit_request_t,
            audit_request.id ,
            audit_request.path ,
            audit_request.action ,
            request_parameters ,
            request_headers,
--        response dbms_cloud_oci_ad_audit_response_t,
            audit_response.status ,
            audit_response.response_time ,
            response_headers ,
            payload ,
            audit_response.message ,
--        state_change dbms_cloud_oci_ad_audit_state_change_t,
            previous ,
            l_current , 
--        additional_details json_element_t,
            additional_details 
      );
    END LOOP;
  END LOOP;
  COMMIT;
END;
/
```
# 3.実行結果の確認
oci_audit_log表が空であることを確認します。

```
SQL> SELECT COUNT(*) FROM oci_audit_log;

  COUNT(*)
----------
	 0
```
日付を指定して、get_audit_logを実行します。

```
exec get_audit_log('2020-11-01')

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.439
```
oci_audit_log表のデータ件数を確認します。

```
SQL> SELECT COUNT(*) FROM oci_audit_log;

  COUNT(*)
----------
       866
```
おぉ、無事に監査ログがoci_audit_log表に保存されたようです。

列名の表示をオフにし、oci_audit_log表に保存された最後の監査ログを確認してみます。

```
SQL> set heading off
SQL> SELECT * FROM oci_audit_log
  2  WHERE id = (SELECT MAX(id) FROM oci_audit_log);

       866
com.oraclecloud.Compartments.ListRegionSubscriptions
0.1
2.0
Compartments
256f870f-d4a7-4ac2-b027-f3b2fb741a4e
2020-11-01T14:54:01Z
application/json

ListRegionSubscriptions
ocid1.tenancy.oc1..aXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
zeon


AD1


oci-optimizer
oci-optimizer/65:50:3E:B7:E0:08:56:DD:27:B7:5E:1F:0D:45:78:23:AD:B0:88:BF



ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
10.0.2.9,252.11.66.55
ST$eyJraWQiOiJhc3dfb2MxX2RlMWIxZmM3IiwiYWxnIjoiUlMyNTYifQ.eyJzdWIiOiJvY2ktb3B0aW
1pemVyIiwic3ZjIjoib2NpLW9wdGltaXplciIsImlzX3N2YyI6InRydWUiLCJvcGMtY2VydHR5cGUiOi
JpbnN0YW5jZSIsImlzcyI6ImF1dGhTZXJ2aWNlLm9yYWNsZS5jb20iLCJmcHJpbnQiOiI2NTo1MDozRT
pCNzpFMDowODo1NjpERDoyNzpCNzo1RToxRjowRDo0NTo3ODoyMzpBRDpCMDo4ODpCRiIsInB0eXBlIj
oic2VydmljZSIsInN2Y0hvc3RpbmdUZW5hbnRJZCI6Im9jaWQxLnRlbmFuY3kub2MxLi5hYWFhYWFhYT
Y0ZDRyb2QyY2dycWxleXNrZ2FvNHdrYmVvc3lscWVsbG1lcWJ2eHhreWJob25scmJna2EiLCJhdWQiOi
JvY2kiLCJ0dHlwZSI6Ing1MDkiLCJvcGMtaW5zdGFuY2UiOiJvY2lkMS5pbnN0YW5jZS5vYzEuYXAtdG
9reW8tMS5hbnhoaWxqcnFjaWljbHFjdGJzbnc3bDIyYTZ4b2Y3ZXl1amFoZDZrNWdjZjN4cnVmNW1iYz
J3dGE2c3EiLCJleHAiOjE2MDQyNDMzMzYsIm9wYy1jb21wYXJ0bWVudCI6Im9jaWQxLmNvbXBhcnRtZW
50Lm9jMS4uYWFhYWFhYWF2djNpZ2NoN3FoaHBnZHZkNWxkdmxmbjVyaDZ0Z2FteHZmZm10aW5lZGc0aH
NwYmxxb2NxIiwiaWF0IjoxNjA0MjQyMTM2LCJqdGkiOiI4MGJiZGVlZC00OWU2LTQ2MTUtOTc0MC01Nj
A5YzkxNjJmY2QiLCJ0ZW5hbnQiOiJvY2lkMS50ZW5hbmN5Lm9jMS4uYWFhYWFhYWE2NGQ0cm9kMmNncn
FsZXlza2dhbzR3a2Jlb3N5bHFlbGxtZXFidnh4a3liaG9ubHJiZ2thIiwiandrIjoie1wia2lkXCI6XC
I2NTo1MDozRTpCNzpFMDowODo1NjpERDoyNzpCNzo1RToxRjowRDo0NTo3ODoyMzpBRDpCMDo4ODpCRl
wiLFwiblwiOlwidGlSWjNNWGJRR29jQXRQOGliTEQ0cVZsdENENERVOUJpLWN0bEw1VnBrYl9NX0RJOX
hHd2d3ZUlLY2c2N1RSZHJTZk92RTBoZzdySnpQUURnWjZFLXBGVFlwX1hqVjllZTVFdDJzTk1lLVRNdE
tLendyTzdUWW90ZlJHenV3dHZRREhuSEY4SDBjeXpfVWw4bXZtUndBU2hCTFVaS1VDN3lNcWRDbFVTek
c3Tk9Kb1dJRTNnX0JnRmJtcVhxcG1TdjJPT0tjMTBxbjRHdnRXRXZmdlowWHhvRW14R3NoYTJYSDRaeV
hIOHN3eUhZaUx2OHlhd2hPT0taY0tlNGhFQklFSEtjLW53T2E1dHkxZVNQVlhVRGZYcmZHTXhBdWw3Qy
1xVnR5NjcwbHBuWjV3TTcyeEdNS3JxOUFCdUdScGZKV2t3d0lVaHhVeEZsTjlaYV9UdXB3XCIsXCJlXC
I6XCJBUUFCXCIsXCJrdHlcIjpcIlJTQVwiLFwiYWxnXCI6XCJSUzI1NlwiLFwidXNlXCI6XCJzaWdcIn
0iLCJvcGMtdGVuYW50Ijoib2NpZDEudGVuYW5jeS5vYzEuLmFhYWFhYWFhNjRkNHJvZDJjZ3JxbGV5c2
tnYW80d2tiZW9zeWxxZWxsbWVxYnZ4eGt5YmhvbmxyYmdrYSJ9.hDf9tIUQ4arjSAbuDj7-a3AZystox
knOuMpN97B-EyPzPII2pl59Ey_uRG0mATqu9EEILYV3AkypM-s5usAK5hcgDFqE1kqPb87el2W5OZi3q
1R6gRszI701SfdK3b_MBx3gl8d8KyhympN1HNR3gY7X28SiSuPedvopYQv9GnFrhNp6XjAM3SbIMdvLJ
IW2cgBfFcfBqraDR27oX9BjcLH2VXOVFaxl9m4ySYV3h5MtO14kJWyVyKMEJdSOrpCXm9tLuk0eqZjSg
m7KU0u3nVpmDj277Qf6s_EAhTiVsBfe3HMePTPOZ3w4FBUzmXFq52lcl3uhjKEaj7iYpMSpQw
Oracle-JavaSDK/1.25.0 (Linux/4.14.35-2025.400.9.1.el7uek.x86_64; Java/1.8.0_251;
 Java HotSpot(TM) 64-Bit GraalVM EE 19.3.2/25.251-b08-jvmci-20.1-b02-dev)

25FD581987E544009BC34577E7B15E73/392C6A4B998A9AF38FF4AFA4DBD2C937/D5EDD19CC0FC99
5A23795DCD6B2463DD
/20160918/tenancies/ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/regionSubscriptions
GET
{"limit":["99"]}
{"Accept":["application/json"],"Connection":["keep-alive"],"Date":["Sun, 01 Nov
2020 14:54:01 GMT"],"User-Agent":["Oracle-JavaSDK/1.25.0 (Linux/4.14.35-2025.400
.9.1.el7uek.x86_64; Java/1.8.0_251; Java HotSpot(TM) 64-Bit GraalVM EE 19.3.2/25
.251-b08-jvmci-20.1-b02-dev)"],"X-Forwarded-For":["10.0.2.9,252.11.66.55"],"X-OC
I-LB-NetworkMetadata":["{\"originalConnection\":{\"sourceIp\":\"10.0.2.9\",\"sou
rcePort\":53398,\"destinationIp\":\"140.204.8.221\",\"destinationPort\":443,\"pr
otocol\":\"https\"},\"paResourceConnection\":{\"sourceIp\":\"252.11.66.55\",\"so
urcePort\":53398,\"destinationIp\":\"172.24.80.24\",\"destinationPort\":443},\"p
aResource\":{\"ocid\":\"\",\"vcnOcid\":\"ocid1.vcn.oc1.ap-tokyo-1.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\"}}"],"X-OCI-LB-PrivateAccessMeta
data":["eyJvcmlnaW5hbENvbm5lY3Rpb24iOnsic291cmNlSXAiOiIxMC4wLjIuOSIsInNvdXJjZVBv
cnQiOjUzMzk4LCJkZXN0aW5hdGlvbklwIjoiMTQwLjIwNC44LjIyMSIsImRlc3RpbmF0aW9uUG9ydCI6
NDQzLCJwcm90b2NvbCI6Imh0dHBzIn0sInBhUmVzb3VyY2VDb25uZWN0aW9uIjp7InNvdXJjZUlwIjoi
MjUyLjExLjY2LjU1Iiwic291cmNlUG9ydCI6NTMzOTgsImRlc3RpbmF0aW9uSXAiOiIxNzIuMjQuODAu
MjQiLCJkZXN0aW5hdGlvblBvcnQiOjQ0M30sInBhUmVzb3VyY2UiOnsib2NpZCI6IiIsInZjbk9jaWQi
OiJvY2lkMS52Y24ub2MxLmFwLXRva3lvLTEuYW1hYWFhYWFxY2lpY2xxYXFtaTZscnBheXdxZHJtcTI0
eXhlZndqNGM0cTMzZWtpaHBsMnl0dWtvNjVhIn19"],"X-Real-IP":["10.0.2.9"],"X-Real-Port
":["53398"],"oci-original-url":["https://identity.ap-tokyo-1.oraclecloud.com/201
60918/tenancies/ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
xeoulgpb2kp7haa/regionSubscriptions"],"opc-client-info":["Oracle-JavaSDK/1.25.0"
],"opc-principal":["{\"tenantId\":\"ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXX\",\"subjectId\":\"oci-optimizer/65:50:3E:B7:
E0:08:56:DD:27:B7:5E:1F:0D:45:78:23:AD:B0:88:BF\",\"claims\":[{\"key\":\"h_(requ
est-target)\",\"value\":\"get /20160918/tenancies/ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXX/regionSubscriptions\",\"issuer
\":\"h\"},{\"key\":\"svcHostingTenantId\",\"value\":\"ocid1.tenancy.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\",\"issuer\":\"authService
.oracle.com\"},{\"key\":\"opc-instance\",\"value\":\"ocid1.instance.oc1.ap-tokyo
-1.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXxx\",\"issuer\":\"a
uthService.oracle.com\"},{\"key\":\"svc\",\"value\":\"oci-optimizer\",\"issuer\"
:\"authService.oracle.com\"},{\"key\":\"opc-tenant\",\"value\":\"ocid1.tenancy.o
c1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\",\"issuer\":\"
authService.oracle.com\"},{\"key\":\"opc-compartment\",\"value\":\"ocid1.compart
ment.oc1..XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX\",\"issue
r\":\"authService.oracle.com\"},{\"key\":\"ttype\",\"value\":\"x509\",\"issuer\"
:\"authService.oracle.com\"},{\"key\":\"ptype\",\"value\":\"service\",\"issuer\"
:\"authService.oracle.com\"},{\"key\":\"h_date\",\"value\":\"Sun, 01 Nov 2020 14
:54:01 GMT\",\"issuer\":\"h\"},{\"key\":\"h_host\",\"value\":\"identity.ap-tokyo
-1.oraclecloud.com\",\"issuer\":\"h\"},{\"key\":\"is_svc\",\"value\":\"true\",\"
issuer\":\"authService.oracle.com\"},{\"key\":\"fprint\",\"value\":\"65:50:3E:B7
:E0:08:56:DD:27:B7:5E:1F:0D:45:78:23:AD:B0:88:BF\",\"issuer\":\"authService.orac
le.com\"},{\"key\":\"authorization\",\"value\":\"Signature headers=\\\"date (req
uest-target) host\\\",keyId=\\\"ST$eyJraWQiOiJhc3dfb2MxX2RlMWIxZmM3IiwiYWxnIjoiU
lMyNTYifQ.eyJzdWIiOiJvY2ktb3B0aW1pemVyIiwic3ZjIjoib2NpLW9wdGltaXplciIsImlzX3N2Yy
I6InRydWUiLCJvcGMtY2VydHR5cGUiOiJpbnN0YW5jZSIsImlzcyI6ImF1dGhTZXJ2aWNlLm9yYWNsZS
5jb20iLCJmcHJpbnQiOiI2NTo1MDozRTpCNzpFMDowODo1NjpERDoyNzpCNzo1RToxRjowRDo0NTo3OD
oyMzpBRDpCMDo4ODpCRiIsInB0eXBlIjoic2VydmljZSIsInN2Y0hvc3RpbmdUZW5hbnRJZCI6Im9jaW
QxLnRlbmFuY3kub2MxLi5hYWFhYWFhYTY0ZDRyb2QyY2dycWxleXNrZ2FvNHdrYmVvc3lscWVsbG1lcW
J2eHhreWJob25scmJna2EiLCJhdWQiOiJvY2kiLCJ0dHlwZSI6Ing1MDkiLCJvcGMtaW5zdGFuY2UiOi
JvY2lkMS5pbnN0YW5jZS5vYzEuYXAtdG9reW8tMS5hbnhoaWxqcnFjaWljbHFjdGJzbnc3bDIyYTZ4b2
Y3ZXl1amFoZDZrNWdjZjN4cnVmNW1iYzJ3dGE2c3EiLCJleHAiOjE2MDQyNDMzMzYsIm9wYy1jb21wYX
J0bWVudCI6Im9jaWQxLmNvbXBhcnRtZW50Lm9jMS4uYWFhYWFhYWF2djNpZ2NoN3FoaHBnZHZkNWxkdm
xmbjVyaDZ0Z2FteHZmZm10aW5lZGc0aHNwYmxxb2NxIiwiaWF0IjoxNjA0MjQyMTM2LCJqdGkiOiI4MG
JiZGVlZC00OWU2LTQ2MTUtOTc0MC01NjA5YzkxNjJmY2QiLCJ0ZW5hbnQiOiJvY2lkMS50ZW5hbmN5Lm
9jMS4uYWFhYWFhYWE2NGQ0cm9kMmNncnFsZXlza2dhbzR3a2Jlb3N5bHFlbGxtZXFidnh4a3liaG9ubH
JiZ2thIiwiandrIjoie1wia2lkXCI6XCI2NTo1MDozRTpCNzpFMDowODo1NjpERDoyNzpCNzo1RToxRj
owRDo0NTo3ODoyMzpBRDpCMDo4ODpCRlwiLFwiblwiOlwidGlSWjNNWGJRR29jQXRQOGliTEQ0cVZsdE
NENERVOUJpLWN0bEw1VnBrYl9NX0RJOXhHd2d3ZUlLY2c2N1RSZHJTZk92RTBoZzdySnpQUURnWjZFLX
BGVFlwX1hqVjllZTVFdDJzTk1lLVRNdEtLendyTzdUWW90ZlJHenV3dHZRREhuSEY4SDBjeXpfVWw4bX
ZtUndBU2hCTFVaS1VDN3lNcWRDbFVTekc3Tk9Kb1dJRTNnX0JnRmJtcVhxcG1TdjJPT0tjMTBxbjRHdn
RXRXZmdlowWHhvRW14R3NoYTJYSDRaeVhIOHN3eUhZaUx2OHlhd2hPT0taY0tlNGhFQklFSEtjLW53T2
E1dHkxZVNQVlhVRGZYcmZHTXhBdWw3Qy1xVnR5NjcwbHBuWjV3TTcyeEdNS3JxOUFCdUdScGZKV2t3d0
lVaHhVeEZsTjlaYV9UdXB3XCIsXCJlXCI6XCJBUUFCXCIsXCJrdHlcIjpcIlJTQVwiLFwiYWxnXCI6XC
JSUzI1NlwiLFwidXNlXCI6XCJzaWdcIn0iLCJvcGMtdGVuYW50Ijoib2NpZDEudGVuYW5jeS5vYzEuLm
FhYWFhYWFhNjRkNHJvZDJjZ3JxbGV5c2tnYW80d2tiZW9zeWxxZWxsbWVxYnZ4eGt5YmhvbmxyYmdrYS
J9.hDf9tIUQ4arjSAbuDj7-a3AZystoxknOuMpN97B-EyPzPII2pl59Ey_uRG0mATqu9EEILYV3AkypM
-s5usAK5hcgDFqE1kqPb87el2W5OZi3q1R6gRszI701SfdK3b_MBx3gl8d8KyhympN1HNR3gY7X28SiS
uPedvopYQv9GnFrhNp6XjAM3SbIMdvLJIW2cgBfFcfBqraDR27oX9BjcLH2VXOVFaxl9m4ySYV3h5MtO
14kJWyVyKMEJdSOrpCXm9tLuk0eqZjSgm7KU0u3nVpmDj277Qf6s_EAhTiVsBfe3HMePTPOZ3w4FBUzm
XFq52lcl3uhjKEaj7iYpMSpQw\\\",algorithm=\\\"rsa-sha256\\\",signature=\\\"*****\\
\",version=\\\"1\\\"\",\"issuer\":\"h\"},{\"key\":\"opc-certtype\",\"value\":\"i
nstance\",\"issuer\":\"authService.oracle.com\"}]}"],"opc-request-id":["25FD5819
87E544009BC34577E7B15E73/392C6A4B998A9AF38FF4AFA4DBD2C937"]}
200
2020-11-01T14:54:02Z
{"Cache-Control":["no-cache, no-store, must-revalidate"],"Content-Length":["107"
],"Content-Type":["application/json"],"Date":["Sun, 01 Nov 2020 14:54:01 GMT"],"
Pragma":["no-cache"],"opc-request-id":["25FD581987E544009BC34577E7B15E73/392C6A4
B998A9AF38FF4AFA4DBD2C937/D5EDD19CC0FC995A23795DCD6B2463DD"]}

ListRegionSubscriptions succeeded





```
監査ログがoci_audit_log表に保存されていることが確認できました。

# まとめ
こちらのPL/SQLプロシージャを前日の日付を引数にして毎日実行することで、前日分の監査ログをAutonomous Database内に保存することができますので、Auditサービスでの監査ログの最大保持期間である365日を超えた場合でもSQLを使用して監査ログを参照することが可能になりますね。

めでたし、めでたし。