【Oracle12Cお勉強】統合監査を触ってみる

統合監査とは？

• 12Cからの新機能。
• 各監査を一つにまとめられる。
• SYS.UNIFIED_AUDIT_TRAILに情報が集約される。
• デフォルトでは無効。有効化する必要がある。有効化には、インスタンスの停止が必要。

統合監査の有効化

▼現状確認

$ sqlplus / as sysdba
SQL> SELECT * FROM V$OPTION WHERE PARAMETER='Unified Auditing';
PARAMETER                             VALUE  CON_ID
------------------------------------- ----- -------
Unified Auditing                      FALSE       0

SQL> exit

▼データベースの停止

SQL> shutdown immediate
SQL> exit

▼有効化

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

▼データベースの起動

$ sqlplus / as sysdba
SQL> startup

▼確認

SQL> SELECT * FROM V$OPTION WHERE PARAMETER='Unified Auditing';
PARAMETER        VALUE  CON_ID
---------------- ----- -------
Unified Auditing TRUE        0

UNIFIED_AUDIT_TRAILの内容確認

▼shutdown,startupの履歴を確認

SQL> select EVENT_TIMESTAMP,DBUSERNAME,ACTION_NAME,SQL_TEXT 
  2  from unified_audit_trail 
  3  where action_name in('SHUTDOWN','STARTUP') order by 1;

EVENT_TIMESTAMP          DBUSERNAME ACTION_NAME SQL_TEXT
------------------------ ---------- ----------- ------------------
16-09-06 09:20:30.816834 SYS        STARTUP     STARTUP
16-09-06 09:23:39.365349 SYS        SHUTDOWN    SHUTDOWN IMMEDIATE
16-09-06 09:23:57.500256 SYS        STARTUP     STARTUP

▼ALTER PLUGGABLE DATABASEの履歴を確認

SQL> select EVENT_TIMESTAMP,DBUSERNAME,SQL_TEXT 
  2  from unified_audit_trail 
  3  where action_name = 'ALTER PLUGGABLE DATABASE' order by 1;

EVENT_TIMESTAMP          DBUSERNAME SQL_TEXT
------------------------ ---------- ----------------------------------------------------------------
15-12-29 07:40:42.218214 SYS        alter pluggable database pdb$seed close
15-12-29 07:40:43.549021 SYS        alter pluggable database pdb$seed open
15-12-29 07:41:46.154337 SYS        alter pluggable database pdb$seed close immediate instances=all
15-12-29 07:41:47.960807 SYS        alter pluggable database pdb$seed OPEN READ WRITE
15-12-29 07:44:07.357800 SYS        alter pluggable database pdb1 open
15-12-29 07:44:46.727027 SYS        alter pluggable database pdb2 open
16-02-14 20:52:02.808739 SYS        alter pluggable database pdb1 open

▼ALTER DATABASEの履歴を確認

SQL> select EVENT_TIMESTAMP,DBUSERNAME,SQL_TEXT 
  2  from unified_audit_trail 
  3  where action_name = 'ALTER DATABASE' order by 1;

EVENT_TIMESTAMP          DBUSERNAME SQL_TEXT
------------------------ ---------- ------------------------
16-09-06 09:20:43.441958 SYS        ALTER DATABASE   MOUNT
16-09-06 09:21:04.468813 SYS        ALTER DATABASE OPEN
16-09-06 09:23:39.355402 SYS        ALTER DATABASE DISMOUNT
16-09-06 09:24:01.995592 SYS        ALTER DATABASE   MOUNT
16-09-06 09:24:03.054083 SYS        ALTER DATABASE OPEN

以上