Autonomous DatabaseのOracle管理パイプラインで監査ログをオブジェクト・ストレージに定期出力してみた

# はじめに
Autonomous Databaseのデータ・パイプライン機能には、ユーザ自身で管理するパイプラインとは別にOracle管理の事前定義されたADMINユーザーのパイプラインが2つ提供されています。

+ **ORA$AUDIT_EXPORT** :データベース監査ログをJSON形式で15分毎（デフォルト）にオブジェクト・ストアに出力するパイプライン

+ **ORA$APEX_ACTIVITY_LOG** :Oracle APEXワークスペース・アクティビティ・ログをJSON形式で15分毎（デフォルト）に出力するアウトライン

この記事では、ORA$AUDIT_EXPORTパイプラインを試してみました。

# 1. 現在の監査レコードを確認
現時点の監査レコードの件数をunified_audit_trailデータ・ディクショナリ・ビューで確認します。
今回監査設定はデフォルトのままです。Autonomous Databaseの監査についてはドキュメントやチュートリアルをご確認ください。

```sql 
SELECT count(*) FROM unified_audit_trail;

  COUNT(*)
----------
        55
```

# 2. パイプライン設定の編集
はじめに、ORA$AUDIT_EXPORTのデフォルト設定を確認します。user_cloud_pipeline_attributesデータ・ディクショナリ・ビューを問い合わせます。

```sql
SELECT pipeline_name, attribute_name, attribute_value FROM user_cloud_pipeline_attributes
WHERE pipeline_name ='ORA$AUDIT_EXPORT'

PIPELINE_NAME    ATTRIBUTE_NAME  ATTRIBUTE_VALUE        
---------------- --------------- ---------------------- 
ORA$AUDIT_EXPORT credential_name OCI$RESOURCE_PRINCIPAL 
ORA$AUDIT_EXPORT format          {"type":"json"}        
ORA$AUDIT_EXPORT interval        15                     
ORA$AUDIT_EXPORT priority        medium                 

4行が選択されました。
```

認証のデフォルトはOCI$RESOURCE_PRINCIPAL(OCIリソースプリンシパル）、また実行間隔は15分であることなどが確認できました。

次にSET_ATTRIBUTEプロシージャでファイルの出力場所をlocation属性で指定します。
OCI Object Storageのバケットtest_bucketのauditexportフォルダにauditbackupという接頭辞を付けて出力させるようにします。
なお、今回、他の属性はデフォルトのままですが、もし他の認証方法を利用したい場合や間隔を変更したい場合などもSET_ATTRIBUTEプロシージャで対象の属性を指定して変更することができます。


```sql
BEGIN
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE(
    pipeline_name => 'ORA$AUDIT_EXPORT',
    attribute_name => 'location',
    attribute_value => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxxxxxx/b/test_bucket/o/auditexport/auditbackup'　
  );
END;
/

PL/SQLプロシージャが正常に完了しました。

```

設定が正しく行われているか確認します。
```sql
SELECT pipeline_name, attribute_name, attribute_value FROM user_cloud_pipeline_attributes
WHERE pipeline_name ='ORA$AUDIT_EXPORT'

PIPELINE_NAME    ATTRIBUTE_NAME  ATTRIBUTE_VALUE                                                                                           
---------------- --------------- --------------------------------------------------------------------------------------------------------- 
ORA$AUDIT_EXPORT credential_name OCI$RESOURCE_PRINCIPAL                                                                                    
ORA$AUDIT_EXPORT format          {"type":"json"}                                                                                           
ORA$AUDIT_EXPORT interval        15                                                                                                        
ORA$AUDIT_EXPORT location        https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxxxxxx/b/test_bucket/o/auditexport/auditbackup 
ORA$AUDIT_EXPORT priority        medium                                                                                                    

5行が選択されました。
```
locationに設定した値が正しく入っていました。

# 3.パイプラインのテスト実行と開始
パイプラインをテストのため1回実行します。

```sql
BEGIN
    DBMS_CLOUD_PIPELINE.RUN_PIPELINE_ONCE(
        pipeline_name => 'ORA$AUDIT_EXPORT'
);
END;
/

PL/SQLプロシージャが正常に完了しました。
```
ファイルがlocationに指定したバケットに出力されていることを確認します。

```sql
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OCI$RESOURCE_PRINCIPAL', 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxxxxxx/b/test_bucket/o/auditexport/');

OBJECT_NAME                                BYTES CHECKSUM                 CREATED                  LAST_MODIFIED            
----------------------------------------- ------ ------------------------ ------------------------ ------------------------ 
auditbackup_1_20230207T034237513534Z.json 149153 U4ZBaCHZ58LNJRGqrHBdcw== 2023-02-07T03:42:37.859Z 2023-02-07T03:42:37.859Z 

1行が選択されました。
```

指定した場所に指定したauditbackupという接頭辞がついたファイルが出力されていました。
コンソールからダウンロードして内容も確認してみます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/e504b1ff-99b5-53c6-8eb4-20f54ca05f8a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/181edb98-7dc9-a468-e42b-d55899763c19.png)

JSON形式で監査レコードのデータが出力されていました。
テストが成功したので、リセットを行ってから、パイプラインを開始します。

```sql
-- リセットする
BEGIN  
   DBMS_CLOUD_PIPELINE.RESET_PIPELINE(
     pipeline_name => 'ORA$AUDIT_EXPORT',
     purge_data => TRUE
);
END;
/

PL/SQLプロシージャが正常に完了しました。

-- パイプラインを開始する
BEGIN
  DBMS_CLOUD_PIPELINE.START_PIPELINE(
      pipeline_name => 'ORA$AUDIT_EXPORT'
  );
END;
/

PL/SQLプロシージャが正常に完了しました。
```

開始されました。
この後の確認用に、15分経過する前にわざとSQL*PlusでADMINユーザでログイン失敗を起こして、監査レコードを生成しておきます。
```sql
SQL> connect admin/xxxxxxxxxxxx#@xxxxxx_low
ERROR:
ORA-01017: invalid username/password; logon denied
```

# 4.出力されたファイルの確認

指定した間隔（今回はデフォルトの15分）で監査レコードが取得されファイルが作成されるかを確認します。
user_cloud_pipeline_historyデータ・ディクショナリ・ビューでパイプラインの履歴を確認できます。

```sql
-- 履歴を確認
SELECT pipeline_id, pipeline_name, start_date,status, error_message  FROM user_cloud_pipeline_history      
WHERE pipeline_name = 'ORA$AUDIT_EXPORT';

PIPELINE_ID PIPELINE_NAME    START_DATE               STATUS    ERROR_MESSAGE 
----------- ---------------- ------------------------ --------- ------------- 
          1 ORA$AUDIT_EXPORT 2023-02-07T03:54:20.211Z SUCCEEDED               
          1 ORA$AUDIT_EXPORT 2023-02-07T04:09:20.327Z SUCCEEDED               

2行が選択されました。           

--　出力されたファイルを確認
SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OCI$RESOURCE_PRINCIPAL', 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxxxxxx/b/test_bucket/o/auditexport/');

OBJECT_NAME                                BYTES CHECKSUM                 CREATED                  LAST_MODIFIED            
----------------------------------------- ------ ------------------------ ------------------------ ------------------------ 
auditbackup_1_20230207T035420590461Z.json 149153 U4ZBaCHZ58LNJRGqrHBdcw== 2023-02-07T03:54:20.830Z 2023-02-07T03:54:20.830Z 
auditbackup_1_20230207T040920563526Z.json   3059 bBbOclPJe2DJRiLmRy0TTA== 2023-02-07T04:09:20.869Z 2023-02-07T04:09:20.869Z 

2行が選択されました。
```
2回の実行履歴とファイルが2つ出力されていることが確認できました。
2回目に出力されたファイルをダウンロードして確認してみると、1回目の後に実行したログイン失敗の監査レコードが出力されていました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/e2e8e24a-c8aa-2487-c6f7-edaf1ec4552c.png)


ファイルが複数あると確認しづらいので、JSONファイルに対して外部表を作成し、さらにビューを作成して確認してみます。

```sql
-- 外部表を作成する
BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
                              TABLE_NAME     => 'AUDITBACKUP_JSON_EXT',
                              credential_name => 'OCI$RESOURCE_PRINCIPAL',
                              FILE_URI_LIST  => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxxxxxxxxxxx/b/test_bucket/o/auditexport/auditbackup*.json',
                              COLUMN_LIST    => 'json_document blob',
                              FIELD_LIST     => 'json_document char(10000)',
                              FORMAT         => JSON_OBJECT(
                                                'rejectlimit' VALUE '999999999'
                              )
    );
END;
/

PL/SQLプロシージャが正常に完了しました。

-- 外部表に対して列を指定したビューを作成する
create or replace view v_auditbackup_json_ext as
select j.*
 from auditbackup_json_ext aj,
      json_table(aj.json_document,'$' 
	  columns (
	  　event_timestamp varchar2(30) path '$.EVENT_TIMESTAMP',
        sessionid number path '$.SESSSIONID',
		os_username varchar2(200) path '$.OS_USERNAME',
        userhost varchar2(128) path '$.USERHOST',
        dbusername varchar2(30) path '$.DBUSERNAME',
        client_program_name varchar2(48) path '$.CLIENT_PROGRAM_NAME',
        action_name varchar2(64) path '$.ACTION_NAME',
        object_name varchar2(128) path '$.OBJECT_NAME',
        sql_text CLOB path '$.SQL_TEXT') )j;

View V_AUDITBACKUP_JSON_EXTは作成されました。
```

ビューが作成できたので、元であるunified_audit_trailデータ・ディクショナリ・ビューと作成したビューの件数を確認してみます。

```sql
select count(*) from unified_audit_trail;

COUNT(*) 
-------- 
      56 

select count(*) from v_auditbackup_json_ext;
COUNT(*) 
-------- 
      48 

```

あれ、作成したビューの件数のほうが少ないですね・・・出力レコードをDBUSERNAMEごとの件数で比較してみます。
```sql
select dbusername,count(*) from unified_audit_trail group by dbusername;

DBUSERNAME COUNT(*) 
---------- -------- 
SYS               8 
ADMIN             1 
AUDSYS           47 

3行が選択されました。

select dbusername,count(*) from v_auditbackup_json_ext group by dbusername;

DBUSERNAME COUNT(*) 
---------- -------- 
ADMIN             1 
AUDSYS           47 


2行が選択されました。
```
uniied_audit_trailデータ・ディクショナリ・ビューと比較すると、作成したビューにはSYSの操作ははいっていないことがわかりました。
どうやらパイプラインでの監査ログの出力はSYSによる操作は排除されるようです。

# 5.パイプラインの停止
パイプラインを停止します。
```sql
BEGIN
DBMS_CLOUD_PIPELINE.STOP_PIPELINE('ORA$AUDIT_EXPORT');
END;
/

PL/SQLプロシージャが正常に完了しました。
```

Database Actionsでスケジューリングのジョブを見てみると、PIPELINE$1として設定されていたようです。停止のタイミングでDISABLEになっていました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/134317/5ae132c3-958a-e2b1-28d1-acf8dfe5978e.png)


# おわりに
Oracle管理の事前定義済みパイプラインORA$AUDIT_EXPORTを試してみました。
Autonomous Databaseの監査ログの保持期間は14日です。それ以上保持したい場合はOracle Data Safeの利用や、監査ログのバックアップを取得するなどの検討が必要でしたが、追加でこのパイプライン機能も利用できるのではと思いました。

# 参考資料
+ ドキュメント
    + [24 Using Data Pipelines for Continuous Load and Export](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/autonomous-pipelines.html#GUID-7A23F17E-DADD-4F49-9967-BD2FA581CED7)
    +  [43 Auditing Autonomous Database](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/adb-audit.html#GUID-76742693-33D2-46D3-8B13-CC838818B036)
+ チュートリアル
    + [504 : 監査をしてみよう](https://oracle-japan.github.io/ocitutorials/database/adb504-audit/)
    
+ その他
    + [[OCI]Autonomous Databaseのデータ・パイプライン機能（DBMS_CLOUD_PIPELINE）を試してみた（データ・ロード編）](https://qiita.com/500InternalServerError/items/daef5e28947ca75d923f)
    + [[Oracle Cloud] Autonomous Databaseのデータ・パイプライン機能（DBMS_CLOUD_PIPELINE）を試してみた（データ・エクスポート編） (2023/01/08)](https://qiita.com/kenwatan/items/88db5c1e3774efa8e0c8)