はじめに
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の監査についてはドキュメントやチュートリアルをご確認ください。
SELECT count(*) FROM unified_audit_trail;
COUNT(*)
----------
55
2. パイプライン設定の編集
はじめに、ORA$AUDIT_EXPORTのデフォルト設定を確認します。user_cloud_pipeline_attributesデータ・ディクショナリ・ビューを問い合わせます。
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プロシージャで対象の属性を指定して変更することができます。
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プロシージャが正常に完了しました。
設定が正しく行われているか確認します。
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回実行します。
BEGIN
DBMS_CLOUD_PIPELINE.RUN_PIPELINE_ONCE(
pipeline_name => 'ORA$AUDIT_EXPORT'
);
END;
/
PL/SQLプロシージャが正常に完了しました。
ファイルがlocationに指定したバケットに出力されていることを確認します。
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という接頭辞がついたファイルが出力されていました。
コンソールからダウンロードして内容も確認してみます。
JSON形式で監査レコードのデータが出力されていました。
テストが成功したので、リセットを行ってから、パイプラインを開始します。
-- リセットする
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> connect admin/xxxxxxxxxxxx#@xxxxxx_low
ERROR:
ORA-01017: invalid username/password; logon denied
4.出力されたファイルの確認
指定した間隔（今回はデフォルトの15分）で監査レコードが取得されファイルが作成されるかを確認します。
user_cloud_pipeline_historyデータ・ディクショナリ・ビューでパイプラインの履歴を確認できます。
-- 履歴を確認
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回目の後に実行したログイン失敗の監査レコードが出力されていました。
ファイルが複数あると確認しづらいので、JSONファイルに対して外部表を作成し、さらにビューを作成して確認してみます。
-- 外部表を作成する
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データ・ディクショナリ・ビューと作成したビューの件数を確認してみます。
select count(*) from unified_audit_trail;
COUNT(*)
--------
56
select count(*) from v_auditbackup_json_ext;
COUNT(*)
--------
48
あれ、作成したビューの件数のほうが少ないですね・・・出力レコードをDBUSERNAMEごとの件数で比較してみます。
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.パイプラインの停止
パイプラインを停止します。
BEGIN
DBMS_CLOUD_PIPELINE.STOP_PIPELINE('ORA$AUDIT_EXPORT');
END;
/
PL/SQLプロシージャが正常に完了しました。
Database Actionsでスケジューリングのジョブを見てみると、PIPELINE$1として設定されていたようです。停止のタイミングでDISABLEになっていました。
おわりに
Oracle管理の事前定義済みパイプラインORA$AUDIT_EXPORTを試してみました。
Autonomous Databaseの監査ログの保持期間は14日です。それ以上保持したい場合はOracle Data Safeの利用や、監査ログのバックアップを取得するなどの検討が必要でしたが、追加でこのパイプライン機能も利用できるのではと思いました。