Oracle 审计commit 与 rollbacked 操作

业务反馈执行update后表里数据实际并没有更新，打开增删改操作细粒度审计发现只有一条update，没有别的操作。由于细粒度审计无论DML语句是提交还是回滚都会记录，我们没有办法直接判断这个update是不是真的提交了。下面有两种方法可以用来区分DML是提交了还是回滚了。

 

一、 利用FGA与trigger的区别

FGA无论DML语句是提交还是回滚都会记录，但是trigger如果DML语句回滚了是不会记入trigger中设置的日志表的。

模拟案例如下：

create table tmp1126_update(a int);
create table tmp1126(a int);
insert into tmp1126 values(123);
commit;

-- 建触发器
CREATE TRIGGER TMP1126_UPDATE_TRI
after update
on TMP1126
for each row
declare
begin
insert into TMP1126_UPDATE values(:old.A);
insert into TMP1126_UPDATE values(:new.A);
end;
/
ALTER TRIGGER TMP1126_UPDATE_TRI ENABLE;

-- 设置FGA
BEGIN
DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA  => 'MYUSER',
           OBJECT_NAME   => 'TMP1126',
           POLICY_NAME   => 'TMP1126_UPDATE',
           ENABLE     => TRUE,
           STATEMENT_TYPES => 'UPDATE');
END;
/

更新一行数据并提交

update tmp1126 set a=5;
commit;

查询FGA与trigger记录，发现均有记到值

更新一行数据并回滚

update tmp1126 set a=8;
commit;

查询发现FGA有记到值，trigger没有，说明该语句实际上回滚了。

 

二、 利用audit all statements

业务方要求审计的时候需要把DML操作是提交了还是回滚了也记录下来，测试开DML的标准审计并不能做到。SR的回复是应该开到 audit all statements 级别，当然如果要审计的用户执行语句非常多，这种审计对性能应该可能较大并且会产生大量审计日志。

与业务方沟通他们要审计的用户不是业务用户，只会有时执行一些修数据的操作，量很小，可以开启。

审计开法应该是：

SQL> audit all statements by hr by access; 
Audit succeeded.

SQL> audit update table, insert table, delete table by hr by access; 

Audit succeeded.

SQL>connect hr/hr;

-- performed insert,update,commit,rollback on hr.employees table.

SQL> select action,SQL_TEXT,ACTION_NAME,OBJ_NAME from dba_audit_trail;

ACTION SQL_TEXT ACTION_NAME OBJ_NAME
---------- -------------------------------------------------- ---------------------------- --------------------
3 SELECT DECODE('A','A','1','2') FROM DUAL SELECT DUAL
44 COMMIT
44 COMMIT
2 insert into hr.employees values('p',30) INSERT EMPLOYEES
44 commit COMMIT
6 update hr.employees set country='g' where age=30 UPDATE EMPLOYEES
45 rollback ROLLBACK
3 select count(*) from sys.aud$ SELECT AUD$

补充：查询历史回滚量最多的会话

select * from (select distinct ses.USERNAME ,n.NAME,ses.sid,s.VALUE value , ses.MACHINE, ses.TERMINAL , ses.PROGRAM, ses.MODULE
From v$sesstat s, v$statname n, v$session ses
where s.STATISTIC# = n.STATISTIC#
and ses.SID = s.SID
and n.NAME like '%user rollbacks%'
and ses.username is not null
and s.value > 0
order by value desc ) x
where rownum <5000;

 

参考

https://blog.csdn.net/Hehuyi_In/article/details/90044964