某客户新上线业务系统,采用Oracle linux + Oracle 19c发布业务,在正式上线前对开发环境进行测试,发现某几张表总会莫名奇妙的丢失几行数据导致部分用户的权限丢失,为了捕获导致问题发生的原因,客户询问是否有办法抓取指定表的增删改查操作,想到可以开启数据库的audit_trail,但是这个参数是全局级的,而且修改需要重启数据库,不太适合此场景,后想到采用FGA(Fine-Grained Audit)精细化审计策略来实现客户的需求。

1、使用sys用户添加审计策略

例如需要审计的表都属于用户XIAOZC,需要审计的表共三张,分别为EC_GR,EC_TE,EC_IN,精细审计策略添加如下:

sqlplus / as sysdba

SQL> begin

dbms_fga.add_policy(object_schema => 'XIAOZC',object_name => 'EC_GR',policy_name => 'PC_SG',statement_types => 'SELECT,INSERT,UPDATE,DELETE');

end;

/

SQL> begin

dbms_fga.add_policy(object_schema => 'XIAOZC',object_name => 'EC_TE',policy_name => 'PC_ST',statement_types => 'SELECT,INSERT,UPDATE,DELETE');

end;

/

SQL> begin

dbms_fga.add_policy(object_schema => 'XIAOZC',object_name => 'EC_IN',policy_name => 'PC_SI',statement_types => 'SELECT,INSERT,UPDATE,DELETE');

end;

查看有哪些审计策略

select * from dba_audit_policies;

至此,需要审计的表的审计策略添加完成。


2、审计结果查询

待审计策略执行后,测试运行一段时间对审计结果进行查询。

su - oracle

conn / as sysdba

SQL> set linesize 200

SQL> set pagesize 1000

SQL> col TIMESTAMP for a20

SQL> col db_user for a10

SQL> col OS_USER for a15

SQL> col POLICY_NAME for a15

SQL> col OBJECT_NAME for a20

SQL> col userhost for a35

--查询审计到了多少条记录,发现已经审计到了数据

select count(*) from dba_fga_audit_trail;

--查询所有操作信息

select TIMESTAMP,DB_USER,OS_USER,USERHOST,OBJECT_NAME,POLICY_NAME,SQL_TEXT from dba_fga_audit_trail;

--查询审计到的delete操作

select TIMESTAMP,DB_USER,OS_USER,USERHOST,OBJECT_NAME,POLICY_NAME,SQL_TEXT from dba_fga_audit_trail where sql_text like '%DELETE%';

--查询审计到的update操作

select TIMESTAMP,DB_USER,OS_USER,USERHOST,OBJECT_NAME,POLICY_NAME,SQL_TEXT from dba_fga_audit_trail where sql_text like '%UPDATE%';

--查询审计到的insert操作

select TIMESTAMP,DB_USER,OS_USER,USERHOST,OBJECT_NAME,POLICY_NAME,SQL_TEXT from dba_fga_audit_trail where sql_text like '%INSERT%';

根据审计结果,进行问题定位,找到影响权限丢失的原因。


3、删除审计策略

由于审计信息默认记录到system表空间,长时间审计会导致system表空间骤增,待定位问题后需要将审计策略删除。删除审计策略如下:

begin

dbms_fga.drop_policy(object_schema => 'XIAOZC',object_name => 'EC_GR',policy_name => 'PC_SG');

end;

/

begin

dbms_fga.drop_policy(object_schema => 'XIAOZC',object_name => 'EC_TE',policy_name => 'PC_ST');

end;

/

begin

dbms_fga.drop_policy(object_schema => 'XIAOZC',object_name => 'EC_IN',policy_name => 'PC_SI');

end;

/

至此,排查问题增加的审计策略删除完成。


4、清空审计数据

释放审计数据占用的system表空间,将审计数据进行截断清除。

conn / as sysdba

truncate table dba_fga_audit_trail;