业务反馈执行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;