【转自】http://space.itpub.net/9240380/viewspace-614029
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
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)
