Oracle中审计删除（DELETE）操作的触发器

最新推荐文章于 2022-10-24 23:07:08 发布

cuilian2013

最新推荐文章于 2022-10-24 23:07:08 发布

阅读量596

点赞数

文章标签：数据库

本文介绍了一个简单的Oracle触发器实现方案，用于审计表中记录的删除操作。通过该触发器可以记录删除操作的时间、执行者、IP地址等信息，并将这些信息存储到专门的审计表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle中审计删除（DELETE）操作的触发器

转载链接：http://www.eygle.com/archives/2011/01/oracle_after_delete_trigger.html

帮朋友写的一个简单审计删除操作的触发器，供参考。
首先是授权：

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-740740/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17252115/viewspace-740740/