ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 细粒度审计测试FGA

细粒度审计测试FGA

原创 Linux操作系统 作者:redhouser 时间:2012-05-18 09:55:33 0 删除 编辑

--1,清理审计记录
truncate table sys.fga_log$;

select * from sys.fga_log$;

select * from dba_tables where table_name like '%AUDIT%';

--创建测试表
drop table hr.c;

create table hr.c
as
select * from hr.countries;

--2,增加审计
BEGIN
  dbms_fga.add_policy(object_schema     => 'hr',
                      object_name       => 'c',
                      policy_name       => 'mypolicy1',
                      audit_condition   => 'region_id=2',
                      audit_column      => NULL,
                      handler_schema    => NULL,
                      handler_module    => NULL,
                      enable            => TRUE,
                      statement_types   => 'INSERT, UPDATE ,select',
                      audit_trail       => dbms_fga.db + dbms_fga.extended,
                      audit_column_opts => dbms_fga.any_columns);
END;
/

--查询审计策略
SELECT object_schema,
       object_name,
       policy_name,
       policy_text,
       enabled,
       sel,
       ins,
       upd,
       del,
       audit_trail,
       policy_column_options
  FROM dba_audit_policies
  where policy_name='MYPOLICY1';
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT ENABLED SEL INS UPD DEL AUDIT_TRAIL  POLICY_COLUMN_OPTIONS
------------- ----------- ----------- ----------- ------- --- --- --- --- ------------ ---------------------
HR            C           MYPOLICY1   region_id=2 YES     YES YES YES NO  DB+EXTENDED  ANY_COLUMNS

--3,测试
--结合审计记录,分析审计条件:
--需同时满足statement_types和audit_condition条件,其中audit_condition指受影响记录是否满足该条件;audit_column,audit_column_opts可以进一步限制是否显式、全部/部分访问指定列。

select * from hr.c;
-->audit

insert into hr.c values('a','a',1);
commit;
-->no audit

insert into hr.c values('a','a',2);
commit;
-->audit

insert into hr.c values('b','b',2);
rollback;
-->audit

update hr.c set country_id='a' where region_id=2 and country_name='Canada';;
commit;
-->ORA-00911: invalid character,no audit

update hr.c set country_id='a' where region_id=2 and 1=2;
commit;
-->no audit

update hr.c set country_id='a' where region_id=1 and country_name='Belgium';
commit;
-->no audit

delete from hr.c where region_id=2 and country_name='Argentina';
commit;
-->no audit

select count(*) from hr.c;
-->audit

select count(*) from hr.c where region_id=2;
-->audit

select count(*) from hr.c where region_id=1;
-->no audit

update hr.c set country_id='b' where region_id=2 and country_name='Canada';
commit;
-->audit


--审计结果:
SELECT session_id,
       db_user,
       object_schema,
       object_name,
       policy_name,
       statement_type,
       sql_text
  FROM dba_common_audit_trail t
  order by t.entryid;
SESSION_ID DB_USER OBJECT_SCHEMA OBJECT_NAME POLICY_NAME STATEMENT_TYPE SQL_TEXT
---------- ------- ------------- ----------- ----------- -------------- --------------------------------------------------------------------------------
   1884664 BOCNET  HR            C           MYPOLICY1   SELECT         select * from hr.c
   1884685 BOCNET  HR            C           MYPOLICY1   INSERT         insert into hr.c values('a','a',2)
   1884685 BOCNET  HR            C           MYPOLICY1   INSERT         insert into hr.c values('b','b',2)
   1884685 BOCNET  HR            C           MYPOLICY1   SELECT         select count(*) from hr.c
   1884685 BOCNET  HR            C           MYPOLICY1   SELECT         select count(*) from hr.c where region_id=2
   1884685 BOCNET  HR            C           MYPOLICY1   UPDATE         update hr.c set country_id='b' where region_id=2 and country_name='Canada'
                                                                                                                                                                                                                     
6 rows selected
SQL>


SQL> show parameter audit
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/product/10.2.0/db_1/admin/bocnet/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string     
audit_trail                          string      NONE

==>FGA与常规审计无直接关系。

begin
  dbms_fga.drop_policy(object_schema => 'hr',object_name => 'c',policy_name => 'mypolicy1');
end;

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

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    787255