oracle10g 审计--dbms_fga_测试学习

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-1 DBMS_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>

 

 

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>

 

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

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

 

03-SEP-09 ZXY

ora10g

insert into mv values(1)

 

 

 

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

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

 

03-SEP-09 ZXY

ora10g

select * from mv

 

03-SEP-09 ZXY

ora10g

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

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

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

 

03-SEP-09 ZXY

ora10g

select * from mv

 

03-SEP-09 ZXY

ora10g

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

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

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

 

03-SEP-09 ZXY

ora10g

select * from mv

 

03-SEP-09 ZXY

ora10g

 

TIMESTAMP DB_USER

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

OS_USER

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

SQL_TEXT

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

select * from mv

 

03-SEP-09 ZXY

ora10g

insert into mv values(1)

 

3,oracle申计相关数据字典及视图

以上具体字典列定义，请参见oracle官方文档

 

 

 

 

4,后期学习测试目标

1,dbms_fga包是否可以审计除table对象以外的对象，比如function,trigger,index,view及其它

  ,与审计表操作有何区别

2,测试dbms_fga包与oracle 初始化参数audit_trail的相关性

3,dbms_fga包相关subfunction的选择的细节用法及测试

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-614029/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-614029/