Oracle利用dbms_fga对表进行监控

 

Oracle利用dbms_fga对表进行监控

前言

这几天生产业务出现一些问题，某张表的某个字段总是会被置空，怀疑是并发引起的问题。

由于业务复杂无法比较难确定是哪个程序引起的问题。

一开始配合业务人员，根据提供的时间对表的数据进行追溯，使用闪回查询，查出每秒目标字段的值变化情况，

但是由于数据库服务器时间和实际create_time时间存在几秒区别，结果仅供参考。

缺点是语句多并且有可能报ORA-01555错误而查不出结果。

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进行监控，

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操作被记录，

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.