使用logminer进行审计 Audit by using logminer

ALLSTARS_ORACLE

使用logminer进行审计
Audit by using logminer
本文作者: 林少杰(kevinlin.ora@gmail.com )
环境：aix ,oracle 10g
关键词：logminer,redo,audit
使用logminer进行审计本文简述logminer工具的介绍，日志挖掘的配置、限制，以及日志挖掘的过程。

在使用Logminer前必须设置最小附加日志，没有设置的情况下，在挖掘时会出现信息遗漏的情况。

当登录信息、事务开始到结束的信息，不在同一个重做日志文件内，那么需要尝试同时挖掘目标操作的重做日志文件与之前邻近的几个重做日志文件，直到得到足够的信息。

另外，logminer对一些特殊对象类型和数据类型并不支持。

将logminer工具作为审计工具在一定条件下是可以成立的，但其工作量较大，不推荐作为需要频繁使用的审计场合。
具体内容：

ORACLE的logminer工具提供一个读取重做日志文件的途径，用于查询重做日志信息，这个过程通常称之为日志挖掘。重做日志(redo log)信息包含了数据库的活动历史，比如insert,update,delete,create,drop等操作的信息，当有误操作的时候就可能根据这些信息来确定误操作的来源，达到审计的目的。

重做日志文件(redo log file)包括：在线重做日志文件(online redo log file)、归档日志文件(archive log file)。其中在线重做日志文件进行归档后就称为归档日志文件。以上两种日志文件包含在rman备份中，其保留的时间由恢复策略决定。在决定使用日志挖掘前，必须确定需要涉及到哪些重做日志文件。

Logminer可以在生产库上使用，也可以在测试库上使用。但测试数据库的某些配置需要与生产库一致，如db_block_size、字符集等，我们建议挖掘环境的基本配置、平台应与生产库一致。

Logminer工具的使用需要一些条件，在使用前进行配置，而且在不同的版本中也有不同的局限性。logminer工具主要通过数据库中的dbms_logmnr包来进行日志挖掘，挖掘信息体现在视图V$logmnr_contents中，可使用指定条件的SQL对其进行查询，通过设定各种条件来定位目标事务，从而获取目标事务的开始时间、具体行为、会话号、客户端等信息。

默认情况下，logminer按照scn的顺序来返回事务信息。

我们可以使用COMMITTED_DATA_ONLY选项(这个选项有内存耗尽的bug)来达到只显示提交的事务信息。

1.            logminer配置

Logminer需要使用一种数据字典库对重做日志进行解析，还原成可读的信息，同时为了保证结果的正确，必须在源数据库中配置最小附加日志(minimal supplemental logging)。

如果没有设置最小附加日志，挖掘信息会有遗漏，详见ORACLEsupport文档：Effect ofSupplemental Logging on LogMiner with Example [ID 750198.1]，介绍了最小附加日志对日志挖掘结果的影响。
数据库字典库有三种：
Online catalog，即当前数据库的数据字典；
在重做日志文件中创建数据字典信息；
数据字典文件，在数据库外创建的文件。

1.1    ORACLE推荐在数据库外部创建数据库字典文件，因此需要设置utl_file_dir参数，例如：

SQL>Alter system set ult_file_dir=’/pkgusgdb12/oradmp/ORACLE/USGDB1/scripts’ scope=spfile sid=’USGDB12’;
此参数在数据库重启后生效。建议定期生成数据字典文件。

1.2    设置数据库为归档模式

设置归档目录后，在mount状态下：
Alter databasearchivelog;

1.3    最小附加日志的配置，需要在没有事务的情况下进行：

Alter database add supplemental log data;

可以通过查询V$database来确认生效：

SQL> select SUPPLEMENTAL_LOG_DATA_MINfrom V$database;

SUPPLEMENTAL_LOG_DATA_MIN
--------
YES

Logminer相关的一些视图：
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS

2.      oracle 10g 中logminer的限制

oracle10g中的logminer在绝大多数情况下，可以从重做日志文件中得到信息，但也有一些特殊情况不能支持。

logminer不支持以下数据类型或表的类型：
- BFILE数据类型
- Simple andnested abstract 数据类型 (ADTs)
- 嵌套表 和 VARRAY类型
- Object refs
- XMLTYPE类型
- 有LOB列的索引组织表IOT
- 使用压缩的表

3.      Logminer步骤

日志挖掘的一般流程如下：

1)     确定所要挖掘信息的时间范围

2)     根据时间确定重做日志文件的范围

3)     选择合适的数据字典文件。

4)     在生产库上进行挖掘，或者搭建日志挖掘的环境。为了不影响生产库，建议使用后者。

5)     确定挖掘的条件（根据表名、SQL操作命令等条件确定查询V$logmnr_contents的谓词条件）

6)     进行挖掘与查询结果。

7)     结束挖掘会话。

具体步骤为：

1)   创建数据字典文件：

SQL>EXECUTE DBMS_LOGMNR_D.BUILD ('dictionary.ora', 'directory of UTL_FILE_DIR',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

2)   加入需要挖掘的重做日志文件：

首先使用NEW选项来选择一个重做日志文件作为第一个文件：

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '/oracle/logs/log1.f',OPTIONS => DBMS_LOGMNR.NEW);

如果需要同时挖掘2个及以上的重做日志文件，则使用ADDFILE选项：

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '/oracle/logs/log2.f',OPTIONS => DBMS_LOGMNR.ADDFILE);

或者忽略OPTIONS

SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '/oracle/logs/log2.f');

如果需要将已加入的重做日志文件取消，则使用DBMS_LOGMNR.REMOVE_LOGFILE：

SQL>EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE (LOGFILENAME => '/oracle/logs/log2.f');

3)   开始日志挖掘

SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR (DICTFILENAME =>'/directory of ult_file_dir/dictionary.ora');

4)   根据条件查询V$LOGMNR_CONTENTS视图来获取挖掘结果。

SQL> SELECT* FROM V$LOGMNR_CONTENTS WHERE …

5)  结束挖掘。

SQL>EXECUTE DBMS_LOGMNR.END_LOGMNR

在挖掘结果中，我们所关心的审计用的内容为：会话号、帐号或者客户端信息，对数据的具体操作以及对应的时间。根据这些信息来确定谁在什么时候做了什么操作。

但由于用户帐号和会话信息在重做日志文件中的记录方式，会在实际挖掘过程中遇到一些复杂情况，并不能一次获得所有想要的信息。

我们先来看一下会话信息记录方式：

1.        当会话登录时，在当前的在线重做日志文件中记录会话信息（数据库帐号、客户端程序、客户端机器、客户端进程等）。

2.        在事务开始时，在当前的重做日志文件中记录会话号(session#, serial#, audit_sessionid)等。

由于有些会话并不是登录后马上进行事务操作，也有可能要进行多个事务操作，且时间间隔很长，因此对我们有用的审计信息并不总是和挖掘到的事务操作在一个重做日志文件里，这就使第一次的挖掘结果并不能尽如人意，需要进行第二次或多次挖掘来得到更有用的信息。

也就是说在重做日志文件范围较大，无法一次性进行挖掘的情况下，如果会话登录、事务开始、事务中间过程、事务结束所相关的重做日志文件不同，那么会带来确定帐号的复杂度。

我们先来看看最复杂的情况，如下：

操作	重做日志文件	包含信息	对不同文件进行挖掘的情况
会话登录logon	重做日志文件1	帐号信息	只对这个文件进行挖掘时，有帐号，但无法找到目标操作		同时对这4个文件进行挖掘可以确定目标操作与帐号、客户端信息。
事务开始	重做日志文件2	只含有会话号	只对这个文件进行挖掘时，有会话号，但无法找到目标操作	同时对这三个文件进行挖掘，可以确定目标操作与会话号。   可以结合logon触发器的日志确定帐号。
事务中	重做日志文件3	只有具体操作信息	只对这个文件进行挖掘时，无法确定会话号和帐号
事务结束	重做日志文件4	只有具体操作信息	只对这个文件进行挖掘时，无法确定会话号和帐号

因此当挖掘到目标操作后，如果会话信息还无法确定，那么需要尝试同时挖掘目标操作的重做日志文件与之前邻近的几个重做日志文件，直到得到足够的信息。

复杂情况下的日志挖掘方案：

1)     根据条件找到目标操作的重做日志文件；如果不能获取会话号和帐号信息，进入第二步；

2)     根据第一步的重做日志文件，向以前挖掘，获取会话号(由于一个事务的开始与结束通常不会跨越很多个重做日志文件，因此我们总是可以获取会话号)。如果这时还是没有帐号信息，进入第三步。

3)     这时有两个选择：A.继续向以前挖掘，直到找到用户帐号及客户端信息；B.根据会话号和logon触发器记录的登录日志来确定用户帐号。相对来说B的代价较小。

以下是模拟出的复杂情况的结果示例：

1.      未包含会话号的挖掘结果

可以看到，这时只有事务操作信息、表名信息。用户帐号为unknown，而会话号均为0。继续往以前进行挖掘。

SCN	COMMIT_TIMESTAMP	SEG_OWNER	TABLE_NAME	SESSION#	SERIAL#	USERNAME	OPERATION	AUDIT_SESSIONID
1.24E+13		WEIHULIN	T_MLD	0	0	UNKNOWN	UPDATE	0
1.24E+13	2011/9/21 14:12			0	0	UNKNOWN	COMMIT	0

2.      包含会话号，但不包含帐号信息的挖掘结果

可以看到，我们挖掘到了事务的开始(OPERATION为START)，这时就有了事务操作信息、表名信息，也有了会话号。这时可以根据LOGON触发器所记录的登录日志来确定用户帐号。
但用户帐号仍为unknown，因为会话登录的那个时间点在这几个重做日志文件的范围外。仍需对以前的重做日志文件进行挖掘。

SCN	COMMIT_TIMESTAMP	SEG_OWNER	TABLE_NAME	SESSION#	SERIAL#	USERNAME	OPERATION	AUDIT_SESSIONID
1.24E+13				1301	18812	UNKNOWN	START	43036444
1.24E+13		WEIHULIN	T_MLD	1301	18812	UNKNOWN	UPDATE	43036444
1.24E+13		WEIHULIN	T_MLD	1301	18812	UNKNOWN	UPDATE	43036444
1.24E+13	2011/9/21 14:12			1301	18812	UNKNOWN	COMMIT	43036444

3.      包含帐号信息的挖掘结果

当挖掘的重做日志包含了会话登录的那个时间点，那么就可以得到以下完整的信息，这也是我们所需要的审计信息了。

SCN	COMMIT_TIMESTAMP	SEG_OWNER	TABLE_NAME	SESSION#	SERIAL#	USERNAME	OPERATION	AUDIT_SESSIONID
1.24E+13				1301	18812	WEIHULIN	START	43036444
1.24E+13		WEIHULIN	T_MLD	1301	18812	WEIHULIN	UPDATE	43036444
1.24E+13		WEIHULIN	T_MLD	1301	18812	WEIHULIN	UPDATE	43036444
1.24E+13	2011/9/21 14:12			1301	18812	WEIHULIN	COMMIT	43036444