eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« HP-UX平台的 EXPDP 转义符定义 FLASHBACK_TIME | Blog首页 | [招聘信息]-飞信招聘高级MySQL dba »

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,不知是否有其他问题,请建议!




历史上的今天...
    >> 2016-01-12文章:
    >> 2012-01-12文章:
    >> 2009-01-12文章:
    >> 2008-01-12文章:
    >> 2006-01-12文章:
           EMC 错了么 之 终结篇
           言论2005
    >> 2005-01-12文章:
           dml lock allocation latch

无觅

By eygle on 2011-01-12 10:53 | Comments (6) | SQL.PLSQL | 2707 |

6 Comments

如何他使用绑定变量,不就不能知道他删除哪条记录?


如何他使用绑定变量,不就不能知道他删除哪条记录?

:old 是哪里来的?

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


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com