ORACLE审计初步入门

【转自】http://space.itpub.net/9240380/viewspace-614029

1,术语简解

dbms_fga包是oracle本身自具功能强大的pl/sql包，实现表级各种select,insert ,delete,update操作的日志记录

 

dbms_fga包适用于oracle cbo优化器模型.(注：从oracle 9i后，oracle默认就是采用cbo优化器模型，rbo模型基本已经f废弃)

 

Dbms_fga包含以下函数：

Summary of DBMS_FGA Subprograms

Table 40-1DBMS_FGA Package Subprograms

Subprogram

Description

ADD_POLICY Procedure

 

Creates an audit policy using the supplied predicate as the audit condition

DISABLE_POLICY Procedure

 

Disables an audit policy

DROP_POLICY Procedure

 

Drops an audit policy

ENABLE_POLICY Procedure

 

Enables an audit policy

 

2,实施步骤

a,添加一个审计策略

Sqlplus ‘/as sysdba’

SQL>exec dbms_fga.add_policy(object_schema=>'zxy',

object_name=>'mv',

policy_name=>'mypolicy1',

statement_types=>'select,insert,update,delete');

 

PL/SQL procedure successfully completed.

 

 测试审计功能

SQL> conn zxy/system

  Connected.

SQL> select * from mv;

  no rows selected

 

SQL> conn /as sysdba

  Connected.

 

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;

  TIMESTAMP  DB_USER      OS_USER                        SQL_TEXT

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

  03-SEP-09   ZXY                ora10g                          select * from mv

 

SQL> conn zxy/system

  Connected.

 

SQL> insert into mv values(1);

  1 row created.

 

SQL> commit;

  Commit complete.

 

SQL> conn /as sysdba

  Connected.

 

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;

  TIMESTAMP DB_USER      OS_USER                  SQL_TEXT

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

  03-SEP-09  ZXY               ora10g                     select * from mv

  03-SEP-09  ZXY               ora10g                     insert into mv values(1)

 

b,禁用申计策略

SQL>exec dbms_fga.disable_policy(object_schema=>'zxy',

                 object_name=>'mv',

                 policy_name=>'mypolicy1');

  PL/SQL procedure successfully completed.

 

测试审计功能

SQL> conn zxy/system

  Connected.

 

SQL> delete from mv;

  1 row deleted.

 

SQL> commit;

  Commit complete.

 

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail; --注：delete的操作日志未被申计记录

  TIMESTAMP DB_USER         OS_USER                   SQL_TEXT

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

  03-SEP-09  ZXY                  ora10g                      select * from mv

  03-SEP-09  ZXY                  ora10g                      insert into mv values(1)  

 

SQL> conn zxy/system

  Connected.

 

SQL> insert into mv values(1);

  1 row created.

 

SQL> insert into mv values(2);

  1 row created.

 

SQL> commit;

  Commit complete.

 

SQL>conn /as sysdba

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;

 注：以上两句insert未被申计记录

 TIMESTAMP DB_USER              OS_USER                SQL_TEXT

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

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    insert into mv values(1)

 

c,启用申计策略

SQL>exec dbms_fga.enable_policy(object_schema=>'zxy',

                 object_name=>'mv',

                 policy_name=>'mypolicy1');

  PL/SQL procedure successfully completed.

 

测试审计功能 

SQL> delete from mv where a=2; --此delete已被申计记录

  1 row deleted.

 

SQL> commit;

  Commit complete.

 

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;

 

 TIMESTAMP DB_USER              OS_USER                SQL_TEXT

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

 03-SEP-09  ZXY                       ora10g                    delete from mv where a=2

  

SQL> select * from mv;

         A

      ----------

         1

 

select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail;

03-SEP-09 ZXY

ora10g

select * from mv

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

 

 

 

 TIMESTAMP DB_USER              OS_USER                SQL_TEXT

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

 03-SEP-09  ZXY                       ora10g                    insert into mv values(1)

 03-SEP-09  ZXY                       ora10g                    delete from mv where a=2

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g

d,删除申计策略

SQL>exec dbms_fga.drop_policy(object_schema=>'zxy',

                 object_name=>'mv',

                 policy_name=>'mypolicy1');

  PL/SQL procedure successfully completed.

测试申计功能

SQL> insert into mv values(9);

  1 row created.

 

SQL> delete from mv where a=9;

  1 row deleted.

 

SQL> commit;

  Commit complete.

 

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail; --以上insert及delete语句的操作未被申计记录

 

 

 TIMESTAMP DB_USER              OS_USER                SQL_TEXT

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

 03-SEP-09  ZXY                       ora10g                    delete from mv where a=2

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    insert into mv values(1)

 

  

SQL> select * from mv;

         A

----------

        1

  

SQL> select timestamp,db_user,os_user,sql_text from dba_fga_audit_trail; --以上select的操作未被申请记录

 

 TIMESTAMP DB_USER              OS_USER                SQL_TEXT

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

 03-SEP-09  ZXY                       ora10g                    delete from mv where a=2

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    select * from mv

 03-SEP-09  ZXY                       ora10g                    insert into mv values(1)