Oracle監査証跡テーブルのメンテナンススクリプト

最近Oracleの監査を実施してくれって要件が多くて、その都度監査テーブルのメンテナンスを考えないで方式設計をする人が多くて困ってます。

今回はWindows用にスクリプト（バッチファイル）を書きましたが、sql は他プラットフォームでも応用が効くはずです。

ファイルを格納するのが一番いいんですが、内容の説明を少ししたいので以下に全て貼り付けることにします。

まずバッチファイルです。

■　バッチファイル( oraCollect_forWin.bat )


@echo off
REM ##########################################################################
REM # 機能          : Oracle監査テーブルバックアップ_forWin                  
REM # 作成者        : Yasuaki Omokawa                                        
REM # 作成日        : 2014/1/28                                              
REM ##########################################################################
REM *　引数：　なし

REM #------------------------------------------------------------------------#
REM -- 初期定義
REM #------------------------------------------------------------------------#

REM ステータス初期化
set STATUS=0

REM 実行日取得
set DAY=%date:~-10,4%%date:~-5,2%%date:~-2,2%

REM ログファイル定義
set LOGFILE=<ログ格納パス>oraCollect_forWin_%DAY%.log


REM #------------------------------------------------------------------------#
REM -- 開始処理
REM #------------------------------------------------------------------------#

echo 処理を開始します %date% %time% >> %LOGFILE%

REM #------------------------------------------------------------------------#
REM -- メイン処理
REM #------------------------------------------------------------------------#

set DAY=%dt:~-10,4%%dt:~-5,2%%dt:~-2,2%

set yy=%dt:~-10,4%
set mm=%dt:~-5,2%
set dd=%dt:~-2,2%


REM 監査ログ出力ディレクトリ定義
set OUTDIR="<ログ格納パス>"

echo ## ファイル取得用ディレクトリオブジェクト作成 %date% %time% >> %LOGFILE%
echo sqlplus -S system/system@<データベース名> @create_dir.sql >> %LOGFILE%
sqlplus -S system/system@<データベース名> @create_dir.sql >> %LOGFILE%
set STATUS=%ERRORLEVEL%
if not %STATUS% == 0 (
 goto ERROR
 )

echo ## 監査ログSPOOL %date% %time% >> %LOGFILE%
echo sqlplus -S system/system@<データベース名> @get_simpleAud.sql >> %LOGFILE%
sqlplus -S system/system@<データベース名> @get_simpleAud.sql >> %LOGFILE%
set STATUS=%ERRORLEVEL%
if not %STATUS% == 0 (
 goto ERROR
 )

echo ## 監査ログ移動 %date% %time% >> %LOGFILE%
echo move <監査ログ格納場所>\<データベース名>*.csv %OUTDIR% >> %LOGFILE%
move <監査ログ格納場所>\<データベース名>*.csv %OUTDIR% >> %LOGFILE%
set STATUS=%ERRORLEVEL%
if not %STATUS% == 0 (
 goto ERROR
 )

echo ## 監査テーブルのトランケート %date% %time% >> %LOGFILE%
echo sqlplus -S sys/sys@<データベース名> as sysdba @aft_expaud.sql >> %LOGFILE%
sqlplus -S sys/sys@<データベース名> as sysdba @aft_expaud.sql >> %LOGFILE%
set STATUS=%ERRORLEVEL%
if not %STATUS% == 0 (
 goto ERROR
 )

echo ## ディレクトリオブジェクト削除 %date% %time% >> %LOGFILE%
echo sqlplus -S system/system@<データベース名> @drop_dir.sql >> %LOGFILE%
sqlplus -S system/system@<データベース名> @drop_dir.sql >> %LOGFILE%
set STATUS=%ERRORLEVEL%
if not %STATUS% == 0 (
 goto ERROR
 )

REM #------------------------------------------------------------------------#
REM -- 正常終了
REM #------------------------------------------------------------------------#

echo 正常終了しました %date% %time% >> %LOGFILE%
exit 0

REM #------------------------------------------------------------------------#
REM -- 異常終了
REM #------------------------------------------------------------------------#
:ERROR
echo 異常終了しました %date% %time% >> %LOGFILE%
echo 直前のコマンドを確認してください >> %LOGFILE%
echo ステータスコード：%STATUS% >> %LOGFILE%
exit %STATUS%


これが主ファイルで、ここから以下のsql文を読み込ませています。

ディレクトリオブジェクト作成SQL↓　
■　create_dir.sql

create or replace directory DIR_BACKUP_AS as '<監査ログを出力するOSディレクトリ>';
exit


ディレクトリパスは各プラットフォーム用に書き直してください。

監査ログテーブルのスプールsql↓
■  get_simpleAud.sql

declare

  --保存フォルダの定義 ディレクトリオブジェクトを事前に作成しておくこと
  dnm     varchar2(200) := 'DIR_BACKUP_AS';
  fnm     varchar2(200) := '';
  mydate  date := SYSDATE;
  vSID    varchar2(32);

  --処理日時を含むファイル名を取得するカーソルの定義
  cursor c1 is select
       '_simpleAudit_' || to_char(sysdate, 'yyyymmddhh24miss') || '.csv' filename 
       from dual;

  --監査用テーブルを取得するカーソルの定義
  cursor c2 is select
  A.OS_USERNAME,
  A.USERNAME,
  A.TERMINAL,
  A.USERHOST,
  A.OWNER,
  A.OBJ_NAME,
  A.SESSIONID,
  A.ACTION_NAME,
  A.TIMESTAMP
 from
  dba_audit_object A
 order by A.TIMESTAMP
 ;

  fno    utl_file.file_type;
  rec    varchar2(1000);
  svtbl  varchar2(200) := '';

begin

  --ファイル名セット
  SELECT instance_name INTO vSID FROM v$instance;
  for rec in c1 loop
    fnm := vSID || rec.filename;
  end loop;

  --CSVファイルオープン
  fno := utl_file.fopen(dnm, fnm, 'W');

  --タイトル行の出力
    utl_file.put_line (fno, 
      '"TIMESTAMP' -- いつ
      || '","OS_USERNAME' -- 誰が1
      || '","USERNAME' -- 誰が2
      || '","TERMINAL' -- どこで1
      || '","USERHOST' -- どこで2
      || '","SESSIONID' -- どこで3
      || '","OBJECT_NAME' -- 何を1 
      || '","ACTION_NAME' -- 何を2
      || '"'
    );

  --データの出力
  for rec in c2 loop
    utl_file.put_line (fno, 
      '"'      || to_char(rec.TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
      || '","' || rec.OS_USERNAME
      || '","' || rec.USERNAME
      || '","' || rec.TERMINAL
      || '","' || rec.USERHOST
      || '","' || rec.SESSIONID
      || '","' || rec.OWNER||'.'||rec.OBJ_NAME
      || '","' || rec.ACTION_NAME
      || '"'
    );
  end loop;

  --CSVファイルクローズ
  utl_file.fclose(fno);

commit;

  --例外処理
 exception
   when OTHERS then
    utl_file.put_line (fno, '** ERROR **');
    rollback;
end;
/
exit


このスクリプトで監査テーブルから項目を抽出し、csvファイルへ出力しています。項目は要件に合わせて編集します。

監査ログテーブルを切り捨てるsql↓
■　aft_expaud.sql
-- 監査ログテーブルの切り捨て
truncate table sys.aud$;
exit

項目抽出が完了した監査ログテーブルをトランケートします。


ディレクトリオブジェクトの削除sql↓
■　drop_dir.sql
drop directory DIR_BACKUP_AS;
exit 

作業時に作成したディレクトリオブジェクトを削除します。





以上です。
監査ログテーブルは、デフォルトでSYSTEM表領域に出来てしまうのでメンテしていないと領域溢れでデータベースがハングする可能性が大きいです。充分注意しないとお客さんの業務損失になって大変迷惑を掛けるので、自戒も込めて書いてみました。