ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g 审计--dbms_fga_测试学习

oracle10g 审计--dbms_fga_测试学习

原创 Linux操作系统 作者:wisdomone1 时间:2009-09-05 21:15:32 0 删除 编辑
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; --以上insertdelete语句的操作未被申计记录

 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2151
  • 访问量
    11787822