Enabled through the AUDIT_TRAIL parameter
* NONE: Disables collection of audit records
* DB: Enables auditing with records stored in the database
* OS: Enables auditing with records stored in the operating system audit trail
Can audit:
* Login events
* Exercise of system privileges
* Exercise of object privileges
* Use of SQL statements
Standard Database Auditing
To use database auditing you must first set the non-dynamic parameterAUDIT_TRAIL to
point to a storage location for audit records. The normal setting for
this parameter is DB, which causes audit records to be stored in the DBA_AUDIT_TRAIL table.
Database auditing can capture information
about login events, the exercise of system privileges, and the exercise
of object privileges. Audit information can be focused by the user
generating the audit event or by the status of the event (successful or
not). The following audit command that generates information is
probably not required because it is not well focused. This option
captures any operation that affects any table:
SQL> AUDIT TABLE;
Audit succeeded.
A better example of an audit command (because it is more narrowly focused) is:
SQL> AUDIT DELETE ON hr.employees WHENEVER SUCCESSFUL;
Audit succeeded.
Specifying Audited Options
* System privilege auditing (nonfocused and focused)
AUDIT select any table, create any trigger;
AUDIT select any table BY hr BY SESSION;
*
Object privilege auditing (nonfocused and focused)
AUDIT ALL on hr.employees;
AUDIT UPDATE,DELETE on hr.employees BY ACCESS;
*
Session auditing
AUDIT session whenever not successful;
SQL statement auditing: The statement shown
in the slide will audit any DDL statement that affects a table
including CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and so on. SQL
Statement auditing can be focused by username or by success/failure.
SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;
SQL statement auditing: The statement shown
in the slide will audit any DDL statement that affects a table
including CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and so on. SQL
Statement auditing can be focused by username or by success/failure.
SQL> AUDIT TABLE BY hr WHENEVER NOT SUCCESSFUL;
System privilege auditing can be used to
audit the exercise of any system privilege (such as DROP ANY TABLE). It
can be focused by username or success/failure. By default, each time an
audited system privilege is exercised an audit record is generated. You
can choose to group those records so that only one record is generated
per session (that way if a user updates 100,000 records in a table
belonging to another user, you only gather one audit record). If the BY
SESSION clause is not specified, the default is BY ACCESS. Consider
using the BY SESSION clause to limit the performance and storage impact
of system privilege auditing.
Object privilege auditing can be used to
audit actions on tables, views, procedures, sequences, directories and
user-defined data types. This type of auditing can be focused by
success/failure and grouped by session or access. Unlike
system privilege auditing, the default grouping is by session so you
must implicitly specify BY ACCESS if you want a separate audit trail
record generated for each action.
（因为测试的时候没有加
by access,
使得
select
后的
insert
没有被
audit
，在这里迷惑了很久，重新仔细的看文档，才发现这段，
E
文还是不行啊）
The AUDIT SESSION
option audits the creation of user sessions. It can be focused by
username or by success/failure. This option is unique because it
generates a single audit record for each session created by connections
to an instance. An audit record is inserted into the audit trail at
connect time and updated at disconnect time. Cumulative information
about a session such as connection time, disconnection time, logical
and physical I/Os processed, and more is stored in a single audit
record that corresponds to the session. In many databases it is common
to use the AUDIT SESSION (nonfocused) command. In almost all databases you should AUDIT SESSION WHENEVER NOT SUCCESSFUL because this allows you to detect attempts to break into your database
Note: Often your audit options start as
nonfocused because you are not sure what type of activity you are
looking for. The AUDIT ALL option is a convenient shortcut to audit a
broad range of activity.
Viewing Auditing Options
To see which audit options have been selected, use the views listed above.
DBA_STMT_AUDIT_OPTSand
DBA_PRIV_AUDIT_OPTScontain only records of statement or privilege audit
options that have been specified. DBA_OBJ_AUDIT_OPTS contains one
record per auditable object regardless of what object audit options
have been specified. The view shows a column for each auditable option.
For example, INSERTaudit options are shown in the INScolumn. Audit
options are displayed as SUCCESSFUL/NOT SUCCESSFULwith three possible
values for each status:
? - Not audited
? SCollect audit records by session
? ACollect audit records by access
SQL> SELECT object_name, object_type, ins, upd
FROM dba_obj_audit_opts WHERE object_name = 'EMPLOYEES'
OBJECT_NAMEOBJECT_TY INS UPD
------------ --------- --- ---
EMPLOYEES TABLE A/S -/-
简言之：查询
select * from dba_obj_audit_opts --object的audit
Viewing Auditing Results
Access to audit records should be tightly
controlled because they may contain sensitive information. Usually the
task of managing the audit trail is handled by the administrator but if
it needs to be delegated the DELETE_CATALOG_ROLE grants permission to
delete from the audit trail.
Value-Based Auditing
Database auditing records that inserts,
updates, and deletes have occurred on audited objects, but does not
capture the actual values that were changed. Value-based auditing
extends database auditing, capturing the actual values that have been
changed. Value-based auditing leverages database triggers (event-driven
PL/SQL constructs).
网转中文：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
1、审计初始化参数：在init文件中添加 AUDIT_TRAIL = DB
2、设置审计
对象审计：AUDIT DELETE,INSERT,UPDATE ON TABLE (BY USER);
SESSION审计：
AUDIT SESSION;
-- 指定用户
AUDIT SESSION BY (USER1,USER2)
权限审计：
AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOTSUCCESSFUL;
AUDIT DELETE ANY TABLE;
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS WHENEVER NOT SUCCESSFUL;
3、关闭审计NOAUDIT
4、清除审计
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name = 。。。
5、相关视图查询,从中可以查询到相关的数据
STMT_AUDIT_OPTION_MAP-审计选项类型代码
AUDIT_ACTIONS-action代码
ALL_DEF_AUDIT_OPTS-对象创建时默认的对象审计选项
DBA_STMT_AUDIT_OPTS-当前数据库系统审计选项
DBA_PRIV_AUDIT_OPTS-权限审计选项
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS-对象审计选项
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL-审计记录
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT-审计对象列表
DBA_AUDIT_SESSION
USER_AUDIT_SESSION-session审计
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT-语句审计
DBA_AUDIT_EXISTS-使用BY AUDIT NOT EXISTS选项的审计
DBA_AUDIT_POLICIES-审计POLICIES
DBA_COMMON_AUDIT_TRAIL-标准审计+精细审计
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
审计是对选定的用户动作的监控和记录，通常用于：
u
审查可疑的活动。例如：数据被非授权用户所删除，此时安全管理员可决定对该 数据库的所有连接进行审计，以及对数据库的所有表的成功地或不成功地删除进行审计。
u
监视和收集关于指定数据库活动的数据。例如：DBA可收集哪些被修改、执行了多少次逻辑的I/O等统计数据。
ORACLE
支持三种审计类型：
u
语句审计，对某种类型的SQL语句审计，不指定结构或对象。
u
特权审计，对执行相应动作的系统特权的使用审计。
u
对象审计，对一特殊模式对象上的指定语句的审计。
ORACLE
所允许的审计选择限于下列方面：
u
审计语句的成功执行、不成功执行，或者其两者。
u
对每一用户会话审计语句执行一次或者对语句每次执行审计一次。
u
对全部用户或指定用户的活动的审计。
当数据库的审计是使能的，在语句执行阶段产生审计记录。审计记录包含有审计的操作、用户执行的操作、操作的日期和时间等信息。审计记录可存在数据字典表（称为审计记录）或操作系统审计记录中。数据库审计记录是在SYS模式的AUD$表中。
设置
ORACLE
审计
下列步骤可以设置
ORACLE
的审计功能：
1.
修改参数文件（
init<sid>.ora
，如果使用服务器参数文件使用
alter system set
<parameter>=<value> scope=spfile|both
，详情参照
1.1
节中关于参数文件的介绍），设置 AUDIT_TRAIL参数，并且重启数据库。
AUDIT_TRAIL
的取值如下：
l
DB/TRUE
：启动审计功能，并且把审计结果存放在数据库的 SYS.AUD$ 表中
l
OS
：启动审计功能，并把审计结果存放在操作系统的审计信息中
l
DB_EXTENDED
：具有DB/TRUE的功能，另外填写
AUD$
的
SQLBIND
和
SQLTEXT
字段
l
NONE/FALSE
：关闭审计功能
2.
如果设置
AUDIT_TRAIL = OS
,
还需要修改参数
AUDIT_FILE_DEST
。
如果操作系统支持设置
AUDIT_TRAIL=OS
，文件会自动存放在
AUDIT_FILE
_DEST
所指定的目录下，并且文件名包含进程的
PID
。
比如：
AUDIT_FILE_DEST = $ORACLE_HOME/rdbms/audit
$ ls -l $ORACLE_HOME/rdbms/audit
-rw-rw---- 1 ora92 dba 881 Mar 17 09:57 ora_13264.aud
$ ps -ef|grep 13264
ora92 13264 13235 0 09:56:43 ? 0:00 oracleV92 (DESCRIPTION=(LOCAL=Y)
SQL> select spid, program, username from v$process;
SPID PROGRAM USERNAME
------ -------------------------------------------- -------------
...
13264 oracle@frhp11 (TNS V1-V3) ora92
注意：
WINDOWS NT
不使用
AUDIT_FILE_DEST
参数，如果使用
OS
设置，那么审计信息会存放在
WINDOWS NT
的事件日志里。下面的章节会有专门的介绍。
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
表空间又足够的空间存放审计信息。
4.
关闭并重启数据库
5.
设置所需要的审计信息
下面是一个例子
SQL> connect system/manager
SQL> grant audit system to scott;
SQL> connect scott/tiger
SQL> audit session;
停止审计：
SQL> noaudit session;
将审计相关的表移动到其他表空间
由于
AUD$
表等审计相关的表存放在
SYSTEM
表空间，因此为了不影响系统的性能，保护
SYSTEM
表空间，最好把
AUD$
移动到其他的表空间上。可以使用下面的语句来进行移动：
sql>connect / as sysdba;
sql>alter table aud$ move tablespace <new tablespace>;
sql>alter index I_aud1 rebuild online tablespace <new tablespace>;
SQL> alter table audit$ move tablespace <new tablespace>;
SQL> alter index i_audit rebuild online tablespace <new tablespace>;
SQL> alter table audit_actions move tablespace <new tablespace>;
SQL> alter index i_audit_actions rebuild online tablespace <new tablespace>;
From http://hi.baidu.com/dbaeyes/blog/item/ad1da21b9f2616d6ad6e75bd.html