Oracle Audit

某天突然发现一个Oracle数据库中system表空间大小有点异常，经过统计，原来是启用了Oracle的Audit功能，并将审计的信息存放在系统表空间。

数据文件大小

[oracle@dev DEV]$ ll -h
total 11G
-rw-r-----. 1 oracle oinstall  9.8M Mar 28 09:56 control01.ctl
-rw-r-----. 1 oracle oinstall  101M Mar 28 09:03 example01.dbf
-rw-r-----. 1 oracle oinstall  2.0G Mar 28 09:56 oa_data01.dbf
-rw-r-----. 1 oracle oinstall  2.0G Mar 28 09:55 oa_index01.dbf
-rw-r-----. 1 oracle oinstall  2.0G Mar 28 09:21 oa_temp01.dbf
-rw-r-----. 1 oracle oinstall   51M Mar 28 08:19 redo01.log
-rw-r-----. 1 oracle oinstall   51M Mar 28 08:58 redo02.log
-rw-r-----. 1 oracle oinstall   51M Mar 28 09:56 redo03.log
-rw-r-----. 1 oracle oinstall   51M Mar 28 08:19 redo11.log
-rw-r-----. 1 oracle oinstall   51M Mar 28 08:58 redo12.log
-rw-r-----. 1 oracle oinstall   51M Mar 28 09:56 redo13.log
-rw-r-----. 1 oracle oinstall 1001M Mar 28 09:55 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  5.2G Mar 28 09:56 system01.dbf
-rw-r-----. 1 oracle oinstall   30M Mar 28 05:56 temp01.dbf
-rw-r-----. 1 oracle oinstall  106M Mar 28 09:55 undotbs01.dbf
-rw-r-----. 1 oracle oinstall  5.1M Mar 28 09:03 users01.dbf

从以上输出可用看出，system01.dbf大小为5.2G，相比其他数据文件，有点不正常。

表空间使用统计

SELECT DS.OWNER,
       DS.SEGMENT_NAME,
       DS.SEGMENT_TYPE,
       (SUM(BYTES) / 1024 / 1024) "SEGMENT_SIZE(MB)",
       MIN(UPPER('&tablespace')) "TABLESPACE"
  FROM DBA_SEGMENTS DS
 WHERE DS.TABLESPACE_NAME = UPPER('&tablespace')
 GROUP BY DS.OWNER, DS.SEGMENT_NAME, DS.SEGMENT_TYPE
 ORDER BY SUM(BYTES) DESC;

   	OWNER	SEGMENT_NAME	SEGMENT_TYPE	SEGMENT_SIZE(MB) TABLESPACE
1	SYS		AUD$			TABLE			4549			 SYSTEM
2	SYS		IDL_UB1$		TABLE			240				 SYSTEM
3	SYS		SOURCE$			TABLE			64				 SYSTEM
4	SYS		IDL_UB2$		TABLE			30				 SYSTEM
5	SYS		C_TOID_VERSION#	CLUSTER			23				 SYSTEM
6	SYS		C_OBJ#_INTCOL#	CLUSTER			19				 SYSTEM
7	SYS		C_OBJ#			CLUSTER			12				 SYSTEM
8	SYS		I_SOURCE1		INDEX			12				 SYSTEM
9	SYS		ARGUMENT$		TABLE			11				 SYSTEM
10	SYS		JAVA$MC$		TABLE			11				 SYSTEM

其中SYS.AUD$表占用4549MB，大约是整个SYSTEM表空间的90%。AUD$是Oracle数据库的审计跟踪表，专门用于存储审计跟踪信息。

异常处理

审计设置

SQL> show parameter audit

NAME				    TYPE	   VALUE
---------------------- ----------- ------------------------------
audit_file_dest 	   string	   /db/oracle/admin/DEV/adump
audit_sys_operations   boolean	   FALSE
audit_syslog_level	   string
audit_trail			   string	   DB

数据库审计是否启用通过参数AUDIT_TRAIL来设置，该参数为静态参数，要使更改生效，必须重启数据库。

在本数据库中，启用了审计，且审计的数据存储在DB，即表SYS.AUD$中。

清除所有审计数据

SQL> conn /as sysdba
Connected.
SQL> truncate table aud$;

Table truncated.

SQL> alter table aud$ shrink;
alter table aud$ shrink
                      *
ERROR at line 1:
ORA-10630: Illegal syntax specified with SHRINK clause

The SYSTEM tablespace is created with manual segment allocation and as such it is not possible to run the SHRINK command for the objects that are located within. However, if the AUD$ table is moved to another tablespace (locally managed with automatic segment space management) then it can be shrunk.

It is recommended to use shrink on the AUD$ only during a downtime window, since part of the shrink operation will use incompatible locks.

SQL> begin
  2  dbms_audit_mgmt.set_audit_trail_location(
  3  audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
  4  audit_trail_location_value => 'USERS'
  5  );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> alter table aud$ enable row movement;

Table altered.

SQL> alter table sys.aud$ shrink space cascade;

Table altered.


SQL> begin
  2  dbms_audit_mgmt.set_audit_trail_location(
  3  audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
  4  audit_trail_location_value => 'SYSTEM'
  5  );
  6  end;
  7  /

PL/SQL procedure successfully completed.

查看AUD$大小

 SELECT DS.OWNER,
        DS.SEGMENT_NAME,
        DS.SEGMENT_TYPE,
        (SUM(BYTES) / 1024 / 1024) "SEGMENT_SIZE(MB)"
   FROM DBA_SEGMENTS DS
  WHERE DS.SEGMENT_NAME = UPPER('&segment')
  GROUP BY DS.OWNER, DS.SEGMENT_NAME, DS.SEGMENT_TYPE;

   	OWNER	SEGMENT_NAME	SEGMENT_TYPE	SEGMENT_SIZE(MB)
1	SYS		AUD$			TABLE					  0.125

除了使用TRUNCATE，Oracle也提供了DBMS_AUDIT_MGMT程序包，可用结合DBMS_SCHEDULE定期清理过期的审计信息。具体用法参见Auditing Enhancements in Oracle Database 11gR2。

关于审计

Concepts and Overview

Database auditing is the process of recording, monitoring and reporting of the actions performed on a database. It allows the security auditors to observe whether the database users are using the database according to the established policies and that there are no policy violations. Database Auditing facilitates the analysis of the database activity patterns/trends and it can help in the process of gathering the historical data about a particular database user or activity.

One can use standard auditing to audit SQL statements, privileges, schemas, objects, and network and multitier activity. Alternatively, one can use Fine Grained Auditing (available only in Enterprise Edition) to monitor specific database activities based on factors such as actions on a database table or times when those activities occur. FGA is very flexible since the audit_condition can reference a PL/SQL function that will control whether the audit record is produced or not.

Reasons for using auditing include:

Enabling future accountability for current actions
Deterring users (or others, such as intruders) from inappropriate actions based on their accountability
Investigating, monitoring, and recording suspicious activity
Addressing auditing requirements for compliance

Auditing Installation

The database standard auditing is a feature available by default in all the database editions.

Configuration and Administration

To use auditing one must first enable it and then define exactly what must be audited. The audited actions are recorded either in the SYS.AUD$ table or in operating system files.

Enabling Auditing

The auditing is enabled by setting the AUDIT_TRAIL parameter to a value different than NONE followed by a restart of the database. The following table presents all the possible legal values for the AUDIT_TRAIL parameter:

NONE：Auditing is disabled
DB：The auditing is enabled and the audit data is written to the SYS.AUD$ table
DB_EXTENDED：Behaves as DB but also populates the SQL_TEXT and SQL_BIND columns
OS：The auditing is enabled. On Unix the audit data is written to text files which are located in the directory specified via AUDIT_FILE_DEST. On Windows the audit data will be sent to the Event Viewer.
XML：The auditing is enabled and the audit data is written to XML files which are located in the directory/folder specified via AUDIT_FILE_DEST. This is the case for Windows as well.
XML_EXTENDED：Behaves as XML but also populates the SQL_TEXT and SQL_BIND tags

As seen in the above table the location where the audit data is sent to is controlled by the AUDIT_TRAIL parameter. When this parameter is set to OS, XML or XML_EXTENDED the exact location of the audit data is controlled by AUDIT_FILE_DEST and AUDIT_SYSLOG_LEVEL. Other factors that influence the exact location for the audit data are :