细粒度审计测试FGA

--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;
--&gtaudit

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

insert into hr.c values('a','a',2);
commit;
--&gtaudit

insert into hr.c values('b','b',2);
rollback;
--&gtaudit

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

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

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

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

select count(*) from hr.c;
--&gtaudit

select count(*) from hr.c where region_id=2;
--&gtaudit

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

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

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

转载于:http://blog.itpub.net/18922393/viewspace-730105/