Edited at

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

More than 1 year has passed since last update.


統合監査とは?


  • 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

以上