技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> Oracle中审计删除(DELETE)操作的触发器

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

浏览:1983次  出处信息
    帮朋友写的一个简单审计删除操作的触发器,供参考。

    首先是授权:

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

建议继续学习:

  1. 利用MySQL触发器高性能造数据    (阅读:4922)
  2. MySQL 的触发器添加出现Not allowed to return a result set from a trigger    (阅读:2480)
  3. 使用审计功能记录错误密码登陆信息    (阅读:1334)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1