None：是默认值，不做审计；

DB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；

DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体sql语句；

OS：将audit trail 记录在操作系统文件中，文件名由audit_file_dest参数指定；

XML：10g里新增的。

注：这两个参数是static参数，需要重新启动数据库才能生效。

--设置打开审计功能（DB模式）

SQL> alter system set audit_trail=DB;

alter system set audit_trail=DB

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

因为audit_trail是静态参数，需要修改初始化参数文件才行。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--修改初始化参数文件

[oracle@gc1 audit]$ cd $ORACLE_HOME/dbs

[oracle@gc1 dbs]$ vi initPROD.ora

SQL> startup

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1219760 bytes

Variable Size 113247056 bytes

Database Buffers 301989888 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /u01/app/oracle/product/10.2.0

                                                 /db_1/rdbms/audit

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

三、审计分类

审计分类：

1）session：在同一个session，相同的语句只产生一个审计结果（默认）

2）access：在同一个session，每一个语句产生一个审计结果。

by 用户名，只审计某个特定用户的，whenever not succesful审计失败的，whenever successful审计成功的，不加就是对所有不论成功失败都审计。

3.1 基于语句

审计关键字 table

SQL> audit table  ; 

Audit succeeded.

--scott用户创建删除一个表

SQL> conn scott/tiger

Connected.

SQL> create table emp1 as select * from emp;

Table created.

SQL> drop table emp1 purge;

Table dropped.

--查看审计信息

SQL> col USERNAME for a10;

SQL> col ACTION_NAME for a20

SQL> col OBJ_NAME for a10

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME ACTION_NAME OBJ_NAME TO_CHAR(TIMESTAMP,'

---------- -------------------- ---------- -------------------

SCOTT CREATE TABLE EMP1 2014-04-21 22:53:33

SCOTT DROP TABLE EMP1 2014-04-21 22:53:40

由此可见，刚才scott用户的create table与drop table操作都被审计记下来

--删除审计信息（先查看dba_audit_trail对应的基表，然后删除基表信息即可）

SQL> delete from dba_audit_trail;

delete from dba_audit_trail

            *

ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table

SQL> set autotrace traceonly;

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

Execution Plan

----------------------------------------------------------

Plan hash value: 3419024717

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 167 | 6 (0)| 00:00:01 |

| 1 | NESTED LOOPS OUTER | | 1 | 167 | 6 (0)| 00:00:01 |

| 2 | NESTED LOOPS OUTER | | 1 | 163 | 5 (0)| 00:00:01 |

| 3 | NESTED LOOPS OUTER | | 1 | 158 | 4 (0)| 00:00:01 |

| 4 | NESTED LOOPS OUTER| | 1 | 153 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL| AUD$ | 1 | 135 | 2 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | I_AUDIT_ACTIONS | 1 | 18 | 1 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | I_SYSTEM_PRIVILEGE_MAP | 1 | 5 | 1 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | I_SYSTEM_PRIVILEGE_MAP | 1 | 5 | 1 (0)| 00:00:01 |

|* 9 | INDEX RANGE SCAN | I_STMT_AUDIT_OPTION_MAP | 1 | 4 | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("AUD"."ACTION#"="ACT"."ACTION"(+))

   7 - access("SPM"."PRIVILEGE"(+)=(-"AUD"."LOGOFF$DEAD"))

   8 - access("SPX"."PRIVILEGE"(+)=(-"AUD"."PRIV$USED"))

   9 - access("AUD"."LOGOFF$DEAD"="AOM"."OPTION#"(+))

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

          9 consistent gets

          0 physical reads

          0 redo size

        729 bytes sent via SQL*Net to client

        385 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          2 rows processed

SQL> set autotrace off;

SQL> delete from AUD$;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

no rows selected

--关闭审计

SQL> noaudit table;

Noaudit succeeded.

--审计特定用户的某些操作

SQL> audit table by scott whenever not successful;

Audit succeeded.

SQL> conn scott/tiger

Connected.

SQL> create table emp as select * from emp;

create table emp as select * from emp

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME ACTION_NAME OBJ_NAME TO_CHAR(TIMESTAMP,'

---------- -------------------- ---------- -------------------

SCOTT CREATE TABLE EMP 2014-04-21 23:23:10

3.2 基于权限

SQL> audit select any table;

Audit succeeded.

SQL> grant select any table to tom;

Grant succeeded.

--tom用户查询自身表

SQL> conn tom/tom

Connected.

SQL> select * from tab;

no rows selected

--查询审计信息（不触发审计，因为没有用到select any table权限）

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

no rows selected

--tom用户查询scott表信息

SQL> select * from scott.emp;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH CLERK 7902 17-DEC-80 800 20

      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

      7566 JONES MANAGER 7839 02-APR-81 2975 20

      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

      7782 CLARK MANAGER 7839 09-JUN-81 2450 10

      7788 SCOTT ANALYST 7566 19-APR-87 3000 20

      7839 KING PRESIDENT 17-NOV-81 5000 10

      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

      7876 ADAMS CLERK 7788 23-MAY-87 1100 20

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7900 JAMES CLERK 7698 03-DEC-81 950 30

      7902 FORD ANALYST 7566 03-DEC-81 3000 20

      7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

--再次查询审计信息（有记录，因为使用了select any table的权限）

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME ACTION_NAME OBJ_NAME TO_CHAR(TIMESTAMP,'

---------- -------------------- ---------- -------------------

TOM SESSION REC EMP 2014-04-21 23:30:56

--tom查看系统用户表

SQL> select * from dba_users;

select * from dba_users

              *

ERROR at line 1:

ORA-00942: table or view does not exist

注意：为什么有select any table权限，却不能访问dba用户表，因为o7参数设置为false，如下：

SQL> show parameter o7;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY boolean FALSE

若O7_DICTIONARY_ACCESSIBILITY为true，只要有select any table权限就可访问所有用户表，反之则不能访问系统用户外所有表。

--查看刚刚tom用户查看系统用户表是否触发了审计功能

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

no rows selected

由此可见，只有当对dba系统表查询失败后，不触发审计。

--关掉审计

SQL> noaudit select any table;

Noaudit succeeded.

SQL> delete from aud$;

0 rows deleted.

SQL> commit;

Commit complete.

--再次用tom用户使用select any table权限

SQL> select * from scott.emp;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH CLERK 7902 17-DEC-80 800 20

      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

      7566 JONES MANAGER 7839 02-APR-81 2975 20

      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

      7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

      7782 CLARK MANAGER 7839 09-JUN-81 2450 10

      7788 SCOTT ANALYST 7566 19-APR-87 3000 20

      7839 KING PRESIDENT 17-NOV-81 5000 10

      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

      7876 ADAMS CLERK 7788 23-MAY-87 1100 20

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7900 JAMES CLERK 7698 03-DEC-81 950 30

      7902 FORD ANALYST 7566 03-DEC-81 3000 20

      7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

--查看审计信息（按理关掉了，应该无相关记录）

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME ACTION_NAME OBJ_NAME TO_CHAR(TIMESTAMP,'

---------- -------------------- ---------- -------------------

TOM SESSION REC EMP 2014-04-21 23:41:20

注意：虽然关掉了审计，但再次操作时，还是会记审计信息，网上查了下原因，有人解释“audit对当前的session没有作用，只对新建session有效 ，按照这个推理，noaudit对当前的session也没有作用，等所有的session都退出重连应该就不会新增审计信息了”。

3.3 基于对象

--创建审计scott.emp对象的审计

SQL> audit all on scott.emp;

Audit succeeded.

SQL> show user

USER is "TOM"

SQL> delete from scott.emp;

delete from scott.emp

                  *

ERROR at line 1:

ORA-01031: insufficient privileges

--查看审计信息（添加ses_actions列信息）

SQL> select USERNAME,ACTION_NAME,OBJ_NAME,SES_ACTIONS,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_audit_trail;

USERNAME ACTION_NAME OBJ_NAME SES_ACTIONS TO_CHAR(TIMESTAMP,'

---------- -------------------- ---------- ------------------- -------------------

TOM SESSION REC EMP ---F------------ 2014-04-21 23:52:19

注意：ses_actions列包括16个短杠，分别代表含义如下：

1 更改，2审计，3注释，4删除，5授权，6索引，7插入，8锁定，9重命名，10选择，11更新，12引用，13执行，14未使用，15未使用，16未使用。

值S表示成功，F表示失败，B代表都有。

由此可见，上面查询结果第四个短杠F表示，删除失败，对应之前的操作。

注意，前三种审计称为一般审计，默认都看不到具体的sql语句信息（sql_text列信息为空），除非设置audit_trail参数为DB,Extended，下面讲精细审计，精细审计可看到详细的操作语句。

四、精细审计

审计的时候，加上条件过滤，使用包来做。

--查官方精细审计文档，找到Oracle Database PL/SQL Packages and Types Reference中dbms_FGA包内容，复制范例内容如下：

做相应修改后，执行即可，如：

SQL> begin

  2 DBMS_FGA.ADD_POLICY (

  3 object_schema => 'scott',

  4 object_name => 'emp',

  5 policy_name => 'mypolicy1',

  6 audit_condition => 'sal > 1000',

  7 audit_column => 'sal',

  8 handler_schema => NULL,

  9 handler_module => NULL,

 10 enable => TRUE,

 11 statement_types => 'INSERT, UPDATE,select',

 12 audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED,

 13 audit_column_opts => DBMS_FGA.ANY_COLUMNS);

 14 end;

 15 /

PL/SQL procedure successfully completed.

--查看精细审计信息

select DB_USER,SQL_TEXT,OBJECT_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_fga_audit_trail;

--scott用户执行相关操作（不触发精细审计）

SQL> conn scott/tiger

Connected.

SQL> select * from emp where sal<=1000;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH CLERK 7902 17-DEC-80 800 20

      7900 JAMES CLERK 7698 03-DEC-81 950 30

--查看审计信息（应该是无相关记录，因为查询条件得到的结果不在审计范围内）

SQL> select DB_USER,SQL_TEXT,OBJECT_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_fga_audit_trail;

no rows selected

--scott用户再次执行相关操作（触发精细审计）

SQL> select * from emp where comm is not null;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

--查看精细审计信息

SQL> col DB_USER for a10;

SQL> col SQL_TEXT for a50;

SQL> col OBJECT_NAME for a10;

SQL> select DB_USER,SQL_TEXT,OBJECT_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_fga_audit_trail;

DB_USER SQL_TEXT OBJECT_NAM TO_CHAR(TIMESTAMP,'

---------- -------------------------------------------------- ---------- -------------------

SCOTT select * from emp where comm is not null EMP 2014-04-22 00:21:44

注意：这回有精细审计内容了，虽然查询条件中不是设定的精细审计条件，但是查询结果中有sal>1000的值的记录，因此精细审计条件不是针对查询条件设定的，而是针对查询结果内容的筛选。

--删除精细审计信息（同理，也是先查找基表，然后删除基表信息即可）

SQL> set autotrace traceonly;

SQL> select DB_USER,SQL_TEXT,OBJECT_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_fga_audit_trail;

Execution Plan

----------------------------------------------------------

Plan hash value: 1613626607

------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2098 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| FGA_LOG$ | 1 | 2098 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0 recursive calls

         12 db block gets

          4 consistent gets

          0 physical reads

          0 redo size

        728 bytes sent via SQL*Net to client

        385 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          1 rows processed

SQL> set autotrace off;

SQL> delete from FGA_LOG$;

1 row deleted.

SQL> commit;

Commit complete.

--删除精细审计

修改官方案例

修改后如下：

直接执行会报错，可采用

begin

DBMS_FGA.DROP_POLICY (

object_schema => 'scott',

object_name => 'emp',

policy_name => 'mypolicy1');

end;

/

或者换行后面加上-符号，如下：

exec DBMS_FGA.DROP_POLICY (-

object_schema => 'scott',-

object_name => 'emp',-

policy_name => 'mypolicy1');

--执行删除精细审计操作

SQL> exec DBMS_FGA.DROP_POLICY (-

> object_schema => 'scott',-

> object_name => 'emp',-

> policy_name => 'mypolicy1');

PL/SQL procedure successfully completed.

--再次执行刚刚产生精细审计的语句

SQL> select * from emp where comm is not null;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

      7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

--查看精细审计信息

SQL> select DB_USER,SQL_TEXT,OBJECT_NAME,to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') from dba_fga_audit_trail;

no rows selected

由此可见，精细审计drop后，当前session不再产生审计信息，不同于一般审计，虽然删除了，但当前session还是会产生审计信息