Oracle FGA审计的使用

0、参考资料

Oracle® Database Security Guide 11g Release 2 (11.2) E16543-059 Verifying Security Access with Auditin

Oracle Audit Vault Administrator’s Guid （about Oracle Audit Vault, which provides advanced auditing features）

Oracle Database Auditing (Doc ID 1681398.2)

1、FGA审计

1.1、简单使用步骤：

精细审计（Fine-Grained Audit）

使用FGA策略实现精细审计的时候，不需要激活数据库审计，并且oracle会自动将审计结果放到数据字典表FGA_LOG$中。

–创建审计策略

exec dbms_fga.add_policy(object_schema=>'som', object_name=>'n_agent', policy_name=>'check_n_agent',audit_condition=>'agent_id=270',audit_column=>'balance',statement_types => 'UPDATE');

–执行dml语句

update n_agent set balance=99 where agent_id=270;

–查询审计结果

select * from dba_fga_audit_trail;

–停用审计策略

exec dbms_fga.disable_policy(object_schema=>'som',object_name=>'n_agent',policy_name=>'check_n_agent');

–启用审计策略

exec dbms_fga.enable_policy(object_schema=>'som',object_name=>'n_agent',policy_name=>'check_n_agent');

–删除审计策略

exec dbms_fga.drop_policy(object_schema=>'som',object_name=>'n_agent',policy_name=>'check_n_agent');

–删除精细审计结果

delete from sys.fga_log$

参数说明：

object_schema: 指定方案名
object_name: 指定数据库对象名
audit_condition：指定审计条件
audit_column:指定审计列
statement_types:指定需要审计的sql语句

1.2、使用FGA制作蜜罐

• 使用存储过程创建蜜罐表以及审计策略

存储过程,实施每个open的用户下创建表，创建审计

declare               
  cursor cur_usr is 
 select username from dba_users where account_status='OPEN' and username not in (select username from V_$PWFILE_USERS)
 and username not in (select distinct owner from dba_tables where table_name ='T_VIP_USER_INFO');
   v_user varchar2(200);
begin
  open cur_usr;
  loop
    fetch cur_usr into v_user;
    exit when cur_usr%notfound;
	--execute immediate 'drop table '||v_user||'.T_VIP_USER_INFO purge';
    execute immediate 'create table '||v_user||'.T_VIP_USER_INFO(id int,customer_name varchar(100),pass varchar(100))';
	execute immediate 'insert into '||v_user||q'[.T_VIP_USER_INFO values (13028194391,'zhaojianguo','zhao_13028194391')]';
    execute immediate 'insert into '||v_user||q'[.T_VIP_USER_INFO values (18636100393,'chenming','chenming123')]';
    execute immediate 'insert into '||v_user||q'[.T_VIP_USER_INFO values (18828194341,'wangshuo','ws18828194391')]';
    execute immediate 'insert into '||v_user||q'[.T_VIP_USER_INFO values (18973189395,'liuhai','hai2010520')]';
    execute immediate 'insert into '||v_user||q'[.T_VIP_USER_INFO values (15528194397,'liupengfei','12345678')]';
    execute immediate 'insert into '||v_user||q'[.T_VIP_USER_INFO values (15010152391,'chenmomo','chen666momo')]';
	commit;
    --dbms_fga.drop_policy(object_schema=>v_user,object_name=>'T_VIP_USER_INFO',policy_name=>'P_FGA_'||v_user);
	dbms_fga.add_policy(object_schema=>v_user,object_name=>'T_VIP_USER_INFO',policy_name=>'P_FGA_'||v_user,statement_types=>'SELECT,INSERT,UPDATE,DELETE');
    end loop;
   close cur_usr;
end;
/

• 删除审计策略

declare               
  cursor cur_usr is 
select OBJECT_SCHEMA from DBA_AUDIT_POLICIES where OBJECT_NAME='T_VIP_USER_INFO';
   v_user varchar2(200);
begin
  open cur_usr;
  loop
    fetch cur_usr into v_user;
    exit when cur_usr%notfound;
    dbms_fga.drop_policy(object_schema=>v_user,object_name=>'T_VIP_USER_INFO',policy_name=>'P_FGA_'||v_user);
    end loop;
   close cur_usr;
end;
/

• 检查审计策略和审计日志

--检查审计日志
col TIMESTAMP for a30
col DB_USER for a12
col OBJECT_NAME for a20
col SQL_TEXT for a50
col POLICY_NAME for a20
select to_char(TIMESTAMP,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,POLICY_NAME,DB_USER,OBJECT_NAME,SQL_TEXT from dba_fga_audit_trail;

--检查审计策略
set linesize 200
col OBJECT_SCHEMA for a16
col OBJECT_NAME for a20
col POLICY_NAME for a14
col POLICY_TEXT for a50
col POLICY_COLUMN for a10
col ENABLED for a8
select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,
POLICY_COLUMN, ENABLED, SEL, INS, UPD, DEL
from DBA_AUDIT_POLICIES ;