Insight SQL Testing でテスト用の SQL として Oracle の監査ログ(標準監査)を使用する

こんにちは。インサイトテクノロジーの松尾です！

本投稿では、SQL テストツール Insight SQL Testing で RDS for Oracle の監査ログを使用する方法を紹介します！
本投稿自体はノウハウ的なものの紹介になりますので、Insight SQL Testing そのものが何をするツールかについては以下の投稿などを参照いただければと思います。

はじめに

Insight SQL Testing で Oracle のバージョンアップ検証や、Oracle から他の RDBMS へ移行することを検証したい場合に現行の Oracle 環境で実行されている SQL 情報を取得します。現在、Insight SQL Testing で Oracle の SQL を扱いたい場合には、監査製品 PISO を使って SQL を取り込むことになります。
PISO を使って取り込む場合には以下のような特徴があります。

• 利点
  • DB サーバーへの負荷が比較的少なく SQL を取得できる (オンプレ Oracle の場合)
  • 画面からの操作で SQL を取得できる
• 要注意点
  • DB サーバーへ Agent ソフトウェアをインストールする必要がある (オンプレ Oracle の場合)
  • Select / DML (Insert/Update/Delete) のみが取得対象になる

DB サーバーへの負荷を抑えて SQL を取得できる反面、Agent ソフトウェアをインストールする必要があります。
そこで本投稿では、Oracle の標準の監査機能を使って監査ログを出力し、その情報を Insight SQL Testing へ取り込む例を紹介します。

監査ログ(標準監査)をどのように取得するか？

標準監査の監査ログはどのように取得すればよいでしょうか？おそらく以下のパターンになると思います。

• オンプレ Oracle の場合：
  • 標準監査を有効にし指定ディレクトリへ xml ファイルを出力
  • ディレクトリからファイルを取得
• RDS for Oracleの場合：
  • 標準監査を有効にし RDS 内へ xml ファイルを出力
  • RDS API や AWS コンソールからログファイルをダウンロードするか CloudWatch Logs へ連携

※今回紹介する標準監査以外に、統合監査や Database Activity Streams (RDSの場合) による監査ログでも同様のことは可能だと思います。また別の機会に紹介したいと思います。

ちなみに、今回の投稿にあたっては、RDS for Oracle を使用し、監査ログの情報を CloudWatch Logs へ連携して確認を行いました。

Insight SQL Testing への取り込みの流れ

以下の手順で取り込みます。

• 監査ログの CloudWatch Logs への出力
• CloudWatch Logs の内容をファイル出力
• Insight SQL Testing へ取り込むための CSV ファイルへ変換
• Insight SQL Testing へ取り込み

監査ログの CloudWatch Logs への出力

RDS for Oracle で監査ログを有効化して CloudWatch Logs へ連携するには以下の設定を行います。

• audit_trail を XML,EXTENDED に設定したパラメータ⁻グループを作成
  
• RDS for Oracle 起動時にログのエクスポートを設定
  
• Oracle に SQL*Plus などで接続し AUDIT ALL STATEMENTS を実行

参考)

すると、以下のように監査ログの xml ファイルの内容が CloudWatch Logs へ出力されるようになります。

Oracle の監査ログの CloudWatch Logs への出力の特徴として、インスタンス名に相当するロググループが作成され、xml ファイルごとのログストリームが大量に作成されます。

CloudWatch Logs の内容をファイル出力

さて CloudWatch Logs へ出力されたものをファイルへ出力するには、AWS Console からエクスポートなども可能ですが、ここではスクリプトで処理することにします。

以前、私の方で作成した↑の Python スクリプトを使います。このスクリプトは指定ロググループ内の全ログストリームの全イベントを、ログストリームごとのファイルへ出力します。
※時間範囲を指定する機能は今はありませんがいずれ対応したいです。

$ python3 -m venv venv
$ source venv/bin/activate
$ pip3 install -r requirements.txt
$ python3 download_log_stream_logs.py /aws/rds/instance/<RDS-INSTANCE-NAME>

実行すると、以下のような感じでファイルが出力されます。

$ ls -l
total 3624
-rw-r--r-- 1 xxxxxx xxxxxx 2352710 Mar 30 16:31 aws_logs_SCOTT_j000_4409_20240329235637451773168121.xml.txt
-rw-r--r-- 1 xxxxxx xxxxxx  981742 Mar 30 16:31 aws_logs_SCOTT_j001_6733_20240330060009211623142906.xml.txt
-rw-r--r-- 1 xxxxxx xxxxxx  348426 Mar 30 16:31 aws_logs_SCOTT_mz01_5146_20240330055340097887199677.xml.txt
-rw-r--r-- 1 xxxxxx xxxxxx    2108 Mar 30 16:31 aws_logs_SCOTT_ora_10001_20240330001500755932712271.xml.txt
-rw-r--r-- 1 xxxxxx xxxxxx    2168 Mar 30 16:31 aws_logs_SCOTT_ora_10011_20240330062004376588084200.xml.txt
-rw-r--r-- 1 xxxxxx xxxxxx    2119 Mar 30 16:31 aws_logs_SCOTT_ora_10067_20240329143355172057421203.xml.txt
...

一つ一つのファイルは以下のような感じで、複数の AuditRecord 要素で構成されており、AuditRecord 要素が SQL 文などを含むアクティビティの単位になっているようです。

<AuditRecord><Audit_Type>8</Audit_Type><EntryId>1</EntryId><Extended_Timestamp>2024-03-30T06:20:04.376755Z</Extended_Timestamp><DB_User>/</DB_User><OS_User>rdsdb</OS_User><Userhost>ip-10-7-2-104</Userhost><OS_Process>10011</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OSPrivilege>SYSDBA</OSPrivilege><DBID>955988972</DBID><Current_User>SYS</Current_User>
<Sql_Text>CONNECT</Sql_Text>
</AuditRecord>
<AuditRecord><Audit_Type>4</Audit_Type><Session_Id>4294967295</Session_Id><StatementId>2</StatementId><EntryId>2</EntryId><Extended_Timestamp>2024-03-30T06:20:04.378720Z</Extended_Timestamp><DB_User>/</DB_User><Ext_Name>rdsdb</Ext_Name><OS_User>rdsdb</OS_User><Userhost>ip-10-7-2-104</Userhost><OS_Process>10011</OS_Process><Instance_Number>0</Instance_Number><Returncode>0</Returncode><OSPrivilege>SYSDBA</OSPrivilege><DBID>955988972</DBID><Current_User>SYS</Current_User>
<Sql_Text>COMMIT</Sql_Text>
</AuditRecord>
...

一要素だけ整形してみると以下のような感じです。

<AuditRecord>
  <Audit_Type>4</Audit_Type>
  <Session_Id>4294967295</Session_Id>
  <StatementId>2</StatementId>
  <EntryId>2</EntryId>
  <Extended_Timestamp>2024-03-30T06:20:04.378720Z</Extended_Timestamp>
  <DB_User>/</DB_User>
  <Ext_Name>rdsdb</Ext_Name>
  <OS_User>rdsdb</OS_User>
  <Userhost>ip-10-7-2-104</Userhost>
  <OS_Process>10011</OS_Process>
  <Instance_Number>0</Instance_Number>
  <Returncode>0</Returncode>
  <OSPrivilege>SYSDBA</OSPrivilege>
  <DBID>955988972</DBID>
  <Current_User>SYS</Current_User>
  <Sql_Text>COMMIT</Sql_Text>
</AuditRecord>

ここから必要な項目を取得してばよいですね。

Insight SQL Testing へ取り込むための CSV ファイルへ変換

取り込むにあたり、この xml ファイル群を処理する必要がありますが、ここでは "xml として正しい" 1ファイルへ以下の手順でまとめました。最初と最後の echo はルート要素を追加しています。

echo '<data>' > xml.txt
cat aws_logs_SCOTT* >> xml.txt
echo '</data>' > xml.txt

もちろんこの変換もスクリプトで処理します。

python3 oracle_audit_xml_to_mscsv.py xml.txt test.csv

処理結果は以下のような CSV ファイルが得られます。

"Host","Database","SID","Serial","Logged In","Logged Out","DB User","SQL Start Time","SQL Start Time(Micro Sec)","SQL Text","Bind Variables","Object","Elapsed Time","Program","Client Information - Host"
"955988972","955988972","250064","","20240329145400","20240329145410","SCOTT","20240329145400","782747","SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL","","","","","xxxxxxxxxxxxx"
"955988972","955988972","250064","","20240329145400","20240329145410","SCOTT","20240329145400","863435","BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;"," #1(8):SQL*Plus","","","","xxxxxxxxxxxxx"
...

Insight SQL Testing へ取り込み

では早速 Insight SQL Testing へ取り込んでみましょう。
以下のように「評価SQLセット」の "新規作成" にて、生成された CSV ファイルを指定します。

取り込んだ評価SQLセットを確認してみると、DDLを含むSQLが取り込めていることも確認できます。

さらに、当然ではありますが、その評価SQLセットを使ってアセスメントも可能です。
以下は試しに、取得元のDBを使ってアセスメントを実行してみた例です。同じ環境などで問題なく実行できます。

バインド変数なども期待通り取得されています。

おわりに

本投稿では、Insight SQL Testing で テスト用の SQL として Oracle の監査ログ(標準監査)を使用する方法を紹介しました。今回は RDS を使って実施していますが、オンプレの Oracle でも同様のことができると思います。ぜひトライしてみてください。
従来、Insight SQL Testing で Oracle から SQL を取り込みたい場合には PISO のセットアップを行う必要がありました。今回 Oracle の標準監査による出力を取り込む方法を紹介したことで、従来、どうしても Agent をインストールすることが不可であるなど、PISO を利用できずにあきらめざるを得なかった環境においても、SQL を取得してアセスメント対象とする選択肢ができたのではないかと思います。

更新情報

• 2024/4/1: Insight Database Testingの製品名がInsight SQL Testingへ変更になったのでそれにあわせて更新しました