删除oracle 审计数据,Oracle中审计删除（DELETE）操作的触发器

Oracle中审计删除(DELETE)操作的触发器

SQL> connect / as sysdba

Connected.

SQL> grant select on v_$sql to eygle;

Grant succeeded.

SQL> grant select on v_$session to eygle;

Grant succeeded.创建测试表及审计表：

SQL> connect eygle/eygle

Connected.

SQL> create table eygle as select user_id,username from dba_users;

Table created.

SQL> desc eygle

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

USER_ID                                   NOT NULL NUMBER

USERNAME                                  NOT NULL VARCHAR2(30)

SQL> create table eygle_delete_audit

2  (user_id number,username varchar2(30),deleteby varchar2(30),deldate date,ipaddress varchar2(50),sqltext clob);

Table created.创建如下触发器：

CREATE OR REPLACE TRIGGER eygle_after_delete

AFTER DELETE

ON eygle

FOR EACH ROW

BEGIN

INSERT INTO eygle_delete_audit

SELECT :old.user_id,:old.username,user,sysdate,SYS_CONTEXT ('userenv', 'ip_address')

,q.sql_text

FROM v$sql q, v$session s

WHERE s.audsid = (SELECT USERENV ('SESSIONID') FROM DUAL)

AND s.sql_id = q.sql_id;

END;

/测试删除操作：

SQL> connect eygle/eygle@rac1

Connected.

SQL> delete from eygle where user_id=46;

1 row deleted.

SQL> commit;

Commit complete.

SQL> truncate table eygle_delete_audit;

Table truncated.

SQL> delete from eygle where user_id=0;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from eygle_delete_audit;

USER_ID USERNAME                       DELETEBY

---------- ------------------------------ ------------------------------

DELDATE      IPADDRESS

------------ --------------------------------------------------

SQLTEXT

--------------------------------------------------------------------------------

0 SYS                            EYGLE

12-JAN-11    172.16.3.248

delete from eygle where user_id=0

能够成功记录删除信息及SQL，不知是否有其他问题，请建议！

By eygle on 2011-01-12 10:53 |

Comments (6) |

SQL.PLSQL | 2707 |

6 Comments

OLD是Delete之前的值啊；绑定变量可以从视图里边查找，一般没有那么高的精度要求了。

OLD是Delete之前的值啊；绑定变量可以从视图里边查找，一般没有那么高的精度要求了。

In RAC environment, is it better to use gv$session?