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,不知是否有其他问题,请建议!
建议继续学习:
- 利用MySQL触发器高性能造数据 (阅读:4912)
- MySQL 的触发器添加出现Not allowed to return a result set from a trigger (阅读:2477)
- 使用审计功能记录错误密码登陆信息 (阅读:1299)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:eygle@eygle.com(eygle) 来源: Oracle Life
- 标签: 审计 触发器
- 发布时间:2011-01-11 22:38:12
- [67] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [65] Go Reflect 性能
- [59] 图书馆的世界纪录
- [59] android 开发入门
- [59] 【社会化设计】自我(self)部分――欢迎区
- [58] IOS安全–浅谈关于IOS加固的几种方法
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 读书笔记-壹百度:百度十年千倍的29条法则
- [46] 界面设计速成