Oracle利用dbms_fga对表进行监控
Oracle利用dbms_fga对表进行监控
前言
这几天生产业务出现一些问题,某张表的某个字段总是会被置空,怀疑是并发引起的问题。
由于业务复杂无法比较难确定是哪个程序引起的问题。
一开始配合业务人员,根据提供的时间对表的数据进行追溯,使用闪回查询,查出每秒目标字段的值变化情况,
但是由于数据库服务器时间和实际create_time时间存在几秒区别,结果仅供参考。
缺点是语句多并且有可能报ORA-01555错误而查不出结果。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT id FROM zkm.test as of timestamp cast(to_timestamp('2020-08-06 08:43:46', 'syyyy-mm-dd hh24:mi:ss') as date) WHERE id=1;
由于该问题出现比较频繁,因此最后想到对有问题的表进行监控以查看引起数据变化的SQL语句,会话信息等。
环境模拟
SYS@zkm> create table zkm.test as select level id from dual connect by level<=100000; Table created.
现在制定策略对表zkm.test进行监控,
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
begin dbms_fga.add_policy( object_schema => 'ZKM', object_name => 'TEST', policy_name => 'MONITOR_TEST', statement_types => 'select,update,delete,insert', enable=>TRUE); end; /
SYS@zkm> begin 2 dbms_fga.add_policy( object_schema => 'ZKM', 3 object_name => 'TEST', 4 policy_name => 'MONITOR_TEST', 5 statement_types => 'select,update,delete,insert', 6 enable=>TRUE); 7 end; 8 / PL/SQL procedure successfully completed.
查询已存在的监控策略,
select * from dba_audit_policies;
开启另外一个会话,对表进行select操作,
[oracle@oracle ~]$ sqlplus zkm/oracle@oracle/zkm SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 2 07:31:24 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ZKM@oracle/zkm> select count(*) from test; COUNT(*) ---------- 100000 ZKM@oracle/zkm>
查询是否该select操作被记录,
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
col timestamp for a15 col userhost for a15 col OBJECT_SCHEMA for a15 col os_user for a15 col OBJECT_NAME for a15 col SQL_TEXT for a50 col POLICY_NAME for a15 select session_id, os_process, timestamp, userhost, os_user, db_user, object_schema, object_name, statement_type, sql_text, policy_name from dba_fga_audit_trail where policy_name = 'MONITOR_TEST' order by timestamp;
SYS@zkm> col timestamp for a15 SYS@zkm> col userhost for a15 SYS@zkm> col OBJECT_SCHEMA for a15 SYS@zkm> col os_user for a15 SYS@zkm> col OBJECT_NAME for a15 SYS@zkm> col SQL_TEXT for a50 SYS@zkm> col POLICY_NAME for a15 SYS@zkm> select session_id, 2 os_process, 3 timestamp, 4 userhost, 5 os_user, 6 db_user, 7 object_schema, 8 object_name, 9 statement_type, 10 sql_text, 11 policy_name 12 from dba_fga_audit_trail 13 where policy_name = 'MONITOR_TEST' 14 order by timestamp; SESSION_ID OS_PROCESS TIMESTAMP USERHOST OS_USER DB_USER OBJECT_SCHEMA OBJECT_NAME STATEME SQL_TEXT POLICY_NAME ---------- ---------------- --------------- --------------- --------------- ------------------------------ --------------- --------------- ------- -------------------------------------------------- --------------- 770006 2763 02-JUN-20 oracle oracle ZKM ZKM TEST SELECT select count(*) from test MONITOR_TEST
其他操作呢?
session 1: ZKM@oracle/zkm> update test set id=99999 where id=1; 1 row updated. session 2: SYS@zkm> col timestamp for a15 SYS@zkm> col userhost for a15 SYS@zkm> col OBJECT_SCHEMA for a15 SYS@zkm> col os_user for a15 SYS@zkm> col OBJECT_NAME for a15 SYS@zkm> col SQL_TEXT for a50 SYS@zkm> col POLICY_NAME for a15 SYS@zkm> select session_id, 2 os_process, 3 timestamp, 4 userhost, 5 os_user, 6 db_user, 7 object_schema, 8 object_name, 9 statement_type, 10 sql_text, 11 policy_name 12 from dba_fga_audit_trail 13 where policy_name = 'MONITOR_TEST' 14 order by timestamp; SESSION_ID OS_PROCESS TIMESTAMP USERHOST OS_USER DB_USER OBJECT_SCHEMA OBJECT_NAME STATEME SQL_TEXT POLICY_NAME ---------- ---------------- --------------- --------------- --------------- ------------------------------ --------------- --------------- ------- -------------------------------------------------- --------------- 770006 2763 02-JUN-20 oracle oracle ZKM ZKM TEST SELECT select count(*) from test MONITOR_TEST 770007 2819 02-JUN-20 oracle oracle ZKM ZKM TEST UPDATE update test set id=99999 where id=1 MONITOR_TEST session 1: ZKM@oracle/zkm> rollback; Rollback complete.
结果说明只要执行过dml操作,无论是否提交或者回滚均会被审计。
注意的地方
值得注意的是,session_id并不是指的操作会话的sid,而是v$session视图中的audsid,
如果想要查询会话信息要注意这点。
在根据sid=40,可以查看会话的spid,
SYS@zkm> select a.sid,a.serial#,b.pid,b.spid from v$session a ,v$process b where a.sid=&sid and b.addr=a.paddr; Enter value for sid: 40 old 1: select a.sid,a.serial#,b.pid,b.spid from v$session a ,v$process b where a.sid=&sid and b.addr=a.paddr new 1: select a.sid,a.serial#,b.pid,b.spid from v$session a ,v$process b where a.sid=40 and b.addr=a.paddr SID SERIAL# PID SPID ---------- ---------- ---------- ------------------------ 40 47 19 2819
可以看出,dba_fga_audit_trail中的os_process如同字段意义一样,就是spid。
删除策略
问题解决之后记录删除策略,毕竟开启审计是需要消耗额外的资源的。
SYS@zkm> exec dbms_fga.drop_policy(object_schema=>'ZKM', object_name => 'TEST',policy_name => 'MONITOR_TEST'); PL/SQL procedure successfully completed.