一. 官网说明
1.1 Oracle 11gR2 concepts
From：
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/ds_concepts003.htm#ADMIN12108
1.1.1 Database Auditing
Databaseauditing is the monitoring and recording of selected user database actions. You can use standard auditing to audit SQL statements,privileges, schemas, objects, and network and multitier activity.Alternatively, you can use fine-grained auditingto
monitor specific database activities, such as actions on a database table ortimes that activities occur. For example, you can audit a table accessed after9:00 p.m.
Reasons for using auditing include:
（1）Enabling future accountability for current actions
（2）Deterring users (or others, such as intruders) from inappropriateactions based on their accountability
（3）Investigating, monitoring, and recording suspicious activity
（4）Addressing auditing requirements for compliance
1.1.2 Oracle Audit Vault
OracleAudit Vault enables you to consolidate, report, and configure alerts foraudited data. You can consolidate audit data generated by Oracle Database andother relational databases. You can also use Oracle Audit Vault to monitoraudit settings on target
databases.
1.2 Oracle 10gR2 concept
From：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#i12374
1.2.1 Overview of Database Auditing
Auditingis the monitoring and recording of selected user database actions. It can bebased on individual actions, such as the type of SQL statement run, or oncombinations of factors that can include name, application, time, and so on.Security policies
can cause auditing when specified elements in an Oracledatabase are accessed or altered, including content.
Auditing is generally used to:
（1）Enable future accountability for current actions taken in aparticular schema, table, or row, or affecting specific content
（2）Investigate suspicious activity. For example, if an unauthorizeduser is deleting data from tables, then the security administrator could auditall connections to the database and all successful and unsuccessful deletionsof rows from all tables in the
database.
（3）Monitor and gather data about specific database activities. Forexample, the database administrator can gather statistics about which tablesare being updated, how many logical I/Os are performed, or how many concurrentusers connect at peak times.
Youcan use Enterprise Manager to view and configure audit-related initializationparameters and administer audited objects for statement auditing and schemaobject auditing. For example, Enterprise Manager shows the properties forcurrent audited statements,
privileges, and objects. You can view theproperties of each object, and you can search audited objects by theirproperties. You can also turn on and turn off auditing on objects, statements,and privileges.
1.2.2 Types and Records of Auditing
Oracle allows audit options to be focused or broad. You can audit:
（1）Successful statement executions, unsuccessful statement executions,or both
（2）Statement executions once in each user session or once every timethe statement is run
（3）Activities of all users or of a specific user
Oracle auditing enablesthe use of several different mechanisms, with the following features:
Table 20-1 Types of Auditing
|
Type of Auditing
|
Meaning/Description
|
|
Statement auditing
|
Audits SQL statements by type of statement, not by the specific schema objects on which they operate. Typically broad, statement auditing audits the use of several types of related actions for each option. For example, AUDIT TABLE tracks several DDL statements
|
|
Privilege auditing
|
Audits the use of powerful system privileges enabling corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing because it audits only the use of the target privilege. You can set privilege auditing to
|
|
Schema object auditing
|
Audits specific statements on a particular schema object, such as AUDIT SELECT ON employees. Schema object auditing is very focused, auditing only a specific statement on a specific schema object. Schema object auditing always applies to all users of the
|
|
Fine-grained auditing
|
Audits data access and actions based on content. Using DBMS_FGA, the security administrator creates an audit policy on the target table. If any rows returned from a DML statement block match the audit condition, then an audit event entry is inserted into
|
1.2.3Audit Records and the Audit Trails
Audit records includeinformation such as the operation that was audited, the user performing theoperation, and the date and time of the operation. Audit records can be storedin either a data dictionary table, called the databaseaudit trail, or in operating
system files, calledan operating system audit trail.
1.2.3.1 DatabaseAudit Trail
The database audit trail is a single table named SYS.AUD$ in the SYS schema ofeach Oracle database's data dictionary. Several predefined views are providedto help you use the information in this table.
Audit trail records can contain different types ofinformation, depending on the events audited and the auditing options set.The following information is always included in each audit trail record, if theinformation is meaningful to the particular audit
action:
（1）User name
（2）Instance number
（3）Process identifier
（4）Session identifier
（5）Terminal identifier
（6）Name of the schema object accessed
（7）Operation performed or attempted
（8）Completion code of the operation
（9）Date and time stamp
（10）System privileges used
1.2.3.2 Auditing in a DistributedDatabase
Auditing is siteautonomous. An instance audits only the statements issued by directly connectedusers. A local Oracle node cannot audit actions that take place in a remotedatabase. Because remote connections are established through the user accountof
a database link, statements issued through the database link's connectionare audited by the remote Oracle node.
1.2.3.3 OperatingSystem Audit Trail
Oracleallows audit trail records to be directed to an operating system audit trail ifthe operating system makes such an audit trail available to Oracle. If not, then audit records are written to a file outside thedatabase, with a format similar to other
Oracle trace files.
Oracleallows certain actions that are always audited to continue, even when theoperating system audit trail (or the operating system file containing auditrecords) is unable to record the audit record. The usual cause of this is thatthe operating system
audit trail or the file system is full and unable toaccept new records.
Systemadministrators configuring operating system auditing should ensure that theaudit trail or the file system does not fill completely. Most operating systemsprovide administrators with sufficient information and warning to ensure thisdoes not occur.
Note, however, that configuring auditing to use the databaseaudit trail removes this vulnerability, because the Oracle database serverprevents audited events from occurring if the audit trail is unable to acceptthe database audit record for the statement.
1.2.3.4 Operating System AuditRecords
Theoperating system audit trail is encoded, but it is decoded in data dictionaryfiles and error messages.
（1）Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table describes thesecodes.
（2）Privileges used describes any system privileges used to perform theoperation. The SYSTEM_PRIVILEGE_MAP table describesall of these codes.
（3）Completion code describes the result of the attempted operation.Successful operations return a value of zero, and unsuccessful operationsreturn the Oracle error code describing why the operation was unsuccessful.
1.2.3.5 RecordsAlways in the Operating System Audit Trail
Somedatabase-related actions are always recorded into the operating system audittrail regardless of whether database auditing isenabled:
（1）At instance startup, anaudit record is generated that details the operating system user starting theinstance, the user's terminal identifier, the date and time stamp, and whetherdatabase auditing was enabled or disabled. Thisinformation is recorded
into the operating system audit trail, becausethe database audit trail is not available until after startup has successfullycompleted. Recording the state of database auditing at startup also acts as anauditing flag, inhibiting an administrator from performing
unaudited actions byrestarting a database with database auditing disabled.
（2）At instance shutdown, anaudit record is generated that details the operating system user shutting downthe instance, the user's terminal identifier, the date and time stamp.
（3）During connections with administrator privileges,an audit record is generated that details the operating system user connectingto Oracle with administrator privileges. This recordprovides accountability regarding users connected with administratorprivileges.
Onoperating systems that do not make an audit trail accessible to Oracle, theseaudit trail records are placed in an Oracle audit trail file in the samedirectory as background process trace files.
1.2.3.6 When Are Audit RecordsCreated?
Anyauthorized database user can set his own audit options at any time, but the recording of audit information is enabled or disabled bythe security administrator.
When auditing is enabled in the database, an audit record isgenerated during the execute phase of statement execution.
SQL statements inside PL/SQL programunits are individually audited, as necessary, when the program unit is run.
The generation and insertion of an audittrail record is independent of a user's transaction being committed. That is, even if a user's transaction is rolled back, theaudit trail record remains committed.
Statement and privilegeaudit options in effect at the time a database user connects to the databaseremain in effect for the duration of the session. Setting or changing statementor privilege audit options in a session does not cause effects in that session.The
modified statement or privilege audit options take effect only when thecurrent session is ended and a new session is created. In contrast, changes toschema object audit options become effective for current sessions immediately.
Operations by the SYS user and by users connected through SYSDBAor SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initializationparameter. Successful SQL statements from SYS are auditedindiscriminately. The audit records for sessions established
by the user SYS orconnections with administrative privileges are sent to an operating systemlocation. Sending them to a location separate from the usual database audittrail in the SYS schema provides for greater auditing security.
二. Audit说明
2.1 审计
审计（Audit)用于监视用户所执行的数据库操作，审计记录可存在数据字典表（称为审计记录:存储在system表空间中的 SYS.AUD$表中，可通过视图dba_audit_trail查看）或操作系统审计记录中(默认位置为$ORACLE_BASE/admin/$ORACLE_SID/adump/).。默认情况下审计是没有开启的。
当数据库的审计是使能的，在语句执行阶段产生审计记录。审计记录包含有审计的操作、用户执行的操作、操作的日期和时间等信息。
不管你是否打开数据库的审计功能，以下这些操作系统会强制记录：用管理员权限连接Instance；启动数据库；关闭数据库。
2.1.1 Oracle审计功能
审计是对选定的用户动作的监控和记录，通常用于：
审查可疑的活动。例如：数据被非授权用户所删除，此时安全管理员可决定对该数据库的所有连接进行审计，以及对数据库的所有表的成功地或不成功地删除进行审计。
监视和收集关于指定数据库活动的数据。例如：DBA可收集哪些被修改、执行了多少次逻辑的I/O等统计数据。
2.1.2 ORACLE所允许的审计选择限于下列方面：
审计语句的成功执行、不成功执行，或者其两者。
对每一用户会话审计语句执行一次或者对语句每次执行审计一次。
对全部用户或指定用户的活动的审计。
2.1.3 审计相关的表安装
SQLPLUS> connect / AS SYSDBA
SQLPLUS> select * from sys.aud$; --没有记录返回
SQLPLUS> select * from dba_audit_trail; - 没有记录返回
如果做上述查询的时候发现表不存在，说明审计相关的表还没有安装，需要安装。
SQLPLUS> connect / as sysdba
SQLPLUS> @$ORACLE_HOME/rdbms/admin/cataudit.sql
审计表安装在SYSTEM表空间。所以要确保SYSTEM表空间又足够的空间存放审计信息。
安装后要重启数据库
2.1.4 将审计相关的表移动到其他表空间
由于AUD$表等审计相关的表存放在SYSTEM表空间，因此为了不影响系统的性能，保护SYSTEM表空间，最好把AUD$移动到其他的表空间上。可以使用下面的语句来进行移动：
sql>connect / as sysdba;
sql>alter table aud$ move tablespace<new tablespace>;
sql>alter index I_aud1 rebuild onlinetablespace <new tablespace>;
SQL> alter table audit$ move tablespace<new tablespace>;
SQL> alter index i_audit rebuild onlinetablespace <new tablespace>;
SQL> alter table audit_actions movetablespace <new tablespace>;
SQL> alter index i_audit_actions rebuildonline tablespace <new tablespace>;
2.1.5 truncate 或者 delete sys.aud$ 表
在delete 之前，可以先把aud$表exp备份一下，注意，不要直接exp，先创建一张临时表，然后将临时表exp。
sql>createtable audit_record tablespace users as select * from sys.aud$;
然后exp：
exptables=AUDIT_RECORD file=audit_record.dmp
最后delete 数据：
sql>delete from sys.aud$;
或者删除指定表的审计：
sql>delete from sys.aud$ whereobj$name='&table_nmae';
注意，delete 不会释放system表空间。 可以使用truncate table：
sql>truncate table sys.aud$
2.2 和审计相关的两个主要参数
2.2.1 Audit_sys_operations
AUDIT_SYS_OPERATIONSenables or disables the auditing of top-level operations, which are SQL statementsdirectly issued by users when connecting with SYSDBA or SYSOPER privileges.(SQL statements run from within PL/SQL procedures or functions are notconsidered
top-level.) The audit records are written to the operating system'saudit trail. The audit records will be written in XML format if the AUDIT_TRAILinitialization parameter is set to xml or xml, extended.
OnUNIX platforms, if the AUDIT_SYSLOG_LEVEL parameter has also been set, then itoverrides the AUDIT_TRAIL parameter and SYS audit records are written to thesystem audit log using the SYSLOG utility.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams015.htm#REFRN10005
默认为false，当设置为true时，所有sys用户（包括以sysdba, sysoper身份登录的用户）的操作都会被记录，audit trail不会写在aud$表中，这个很好理解，如果数据库还未启动aud$不可用，那么像conn /as sysdba这样的连接信息，只能记录在其它地方。如果是windows平台，audti trail会记录在windows的事件管理中，如果是linux/unix平台则会记录在audit_file_dest参数指定的文件中。
SQL> show parameteraudit_file_dest
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/dave2/adump
2.2.2 Audit_trail
AUDIT_TRAIL enables or disables databaseauditing.
Values:
（1）none：Disables standard auditing. This value is thedefault if the AUDIT_TRAIL parameter was not set in the initializationparameter file or if you created the database using a method other thanDatabase Configuration Assistant. If you created the database using
DatabaseConfiguration Assistant, then the default is db.
（2）os：Directs all audit records to an operating system file. Oraclerecommends that you use the os setting, particularly if you are using anultra-secure database configuration.
（3）db：Directs audit records to the database audit trail (the SYS.AUD$table), except for records that are always written to the operating systemaudit trail. Use this setting for a general database for manageability.
If the database was started in read-onlymode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAILto os. Check the alert log for details.
（4）db, extended：Performs all actions of AUDIT_TRAIL=db,and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$table, when available. These two columns are populated only when this parameteris specified.
If the database was started in read-onlymode with AUDIT_TRAIL set to db, extended, then Oracle Database internally setsAUDIT_TRAIL to os. Check the alert log for details.
（5）xml：Writes to the operating system audit record file in XML format.Records all elements of the AuditRecord node except Sql_Text and Sql_Bind tothe operating system XML audit file.
（6）xml, extended：Performs all actions of AUDIT_TRAIL=xml,and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$table, wherever possible. These columns are populated only when this parameteris specified.
Youcan use the SQL AUDIT statement to set auditing options regardless of thesetting of this parameter.