audit 审计

 一 审计功能的参数控制
audit_trail 参数的值可以设置为以下几种

1. NONE：不开启
2. DB：开启审计功能
3. OS：审计记录写入一个操作系统文件。
4. TRUE：与参数DB一样
5. FALSE：不开启审计功能。
这个参数是写道spfile里面的，需要重启数据库

二 查看是否审计功能是否启动
SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ORCL/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

三 开启审计


SQL> conn /as sysdba
SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ORCL/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

SQL> alter system set audit_sys_operations=TRUE scope=spfile;    --审计管理用户(以sysdba/sysoper角色登陆)
SQL> alter system set audit_trail=db,extended scope=spfile;

重启实例 
SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ORCL/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB, EXTENDED      
（完成）

四 关闭审计
SQL> conn /as sysdba
SQL> show parameter audit
SQL> alter system set audit_trail=none;
重启实例

五 检查各个节点的audit_trail 是否都是none

http://blog.csdn.net/mittee/archive/2009/02/09/3870109.aspx

**************************

1.

SQL> alter system set audit_trail=db_extended scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area  385875968 bytes

Fixed Size                  1219568 bytes

Variable Size             113247248 bytes

Database Buffers          268435456 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> show parameter audit;

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /u01/app/oracle/admin/gaudi/ad

                                                 ump

audit_sys_operations                 boolean     TRUE

audit_syslog_level                   string

audit_trail                          string      DB_EXTENDED

SQL> audit all on gau001.audit_test by access;

Audit succeeded.

*  BY ACCESS;BY SESSION

查看：

select username, userhost, os_username, ses_actions, obj_name, sql_text

  from DBA_AUDIT_TRAIL

SQL> /

USERNAME USERHOST             OS_USERN SES_ACTION OBJ_NAME   SQL_TEXT

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

GAU001   WORKGROUP\LUKEGUI-PC LuKegui             AUDIT_TEST select * from audit_test

2.FGA policy

SQL> begin

  2    dbms_fga.add_policy(object_schema   => 'gau001',

  3                        object_name     => 'audit_test',

  4                        policy_name     => 'high_b',

  5                        audit_condition => 'B > 10',

  6                        audit_column    => 'b',

  7                        statement_types => 'select');

  8  end;

  9  /

PL/SQL 过程已成功完成。

SQL> select os_user,db_user,sql_text from dba_fga_audit_trail;

未选定行

SQL> l

  1* select os_user,db_user,sql_text from dba_fga_audit_trail

SQL> /

OS_USER    DB_USER    SQL_TEXT

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

LuKegui    GAU001     select * from audit_test

LuKegui    GAU001     select * from audit_test where b>12

3.canceling the database auditing, dropping the FGA policy

SQL> noaudit all on gau001.audit_test;

Noaudit succeeded.

SQL> begin

  2   dbms_fga.drop_policy(object_schema=>'gau001',

  3                        object_name =>'audit_test',

  4                        policy_name =>'high_b');

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL>