Oracle 11g系统级别触发器来跟踪监控drop误操作

前言：
db中有一张表的数据老是紊乱，猜猜是经历过drop、create的数据同步操作，但是现在谁也不知道在哪里操作的，所以准备做一个触发器去记录下是哪个应用服务器那个db账号操作的。

3，系统级别触发器

3.1 触发事件

包括各种DDL操作以及各种数据库事件，ddl包括create、alter、drop、rename、grant、revoke、audit、noaudit、commit、truncate、analyze、associate statistics、disassociate statistis。触发时间可以before或者after

3.2 建立触发器记录的表

-- Create table
create table Z_TRIG_SYS
(
  lt                    DATE,
  sid                   NUMBER,
  serial#               NUMBER,
  username              VARCHAR2(30),
  oSUSEr                VARCHAR2(64),
  machine               VARCHAR2(32),
  terminal              VARCHAR2(16),
  object_name           VARCHAR2(200),
  ora_sysevent          VARCHAR2(200),
  program               VARCHAR2(64),
  sqltext               VARCHAR2(4000),
  status                VARCHAR2(30),
  client_ip             VARCHAR2(60),
  ora_dbname            VARCHAR2(60),
  ora_client_ip_address VARCHAR2(60)
);

-- Add comments to the columns 
comment on column Z_TRIG_SYS.lt
  is '录入时间';
comment on column Z_TRIG_SYS.sid
  is '当前session的id';
comment on column Z_TRIG_SYS.serial#
  is 'sid的序列号，顺序自增';
comment on column Z_TRIG_SYS.username
  is '登录的用户名';
comment on column Z_TRIG_SYS.osuser
  is '操作者的os系统';
comment on column Z_TRIG_SYS.machine
  is '操作者的机器名称';
comment on column Z_TRIG_SYS.object_name
  is '操作对象名称';
comment on column Z_TRIG_SYS.ora_sysevent
  is '操作事件';
comment on column Z_TRIG_SYS.sqltext
  is '执行的sql片段';
comment on column Z_TRIG_SYS.client_ip
  is '客户端ip';
comment on column Z_TRIG_SYS.ora_dbname
  is '执行的数据库';
comment on column Z_TRIG_SYS.ora_client_ip_address
  is '客户端ip地址';

3.3 建立system级别触发器

create or replace trigger trig_system
after drop on database
begin
  if ora_login_user!='system' then
     insert into z_trig_sys(
         lt                    ,
        sid                   ,
        serial#               ,
        username              ,
        osuser                ,
        machine               ,
        terminal              ,
        object_name           ,
        ora_sysevent          ,
        program               ,
        sqltext               ,
        status                ,
        client_ip             ,
        ora_dbname            ,
        ora_client_ip_address )

        select sysdate,
               s.SID,
                  s.SERIAL#,
                  s.USERNAME,
                  s.OSUSER,
               s.MACHINE,
               s.TERMINAL,
               s.PROGRAM,
               ora_dict_obj_name,
               ora_sysevent,
               'drop object on database',
               '',
              sys_context('userenv','ip_address'),
              ora_database_name,
              ora_client_ip_address
          from v$sql q, v$session s
         where s.audsid=(select userenv('SESSIONID') from dual)
           and s.prev_sql_addr=q.address
           AND s.PREV_HASH_VALUE = q.hash_value;
           -- commit;
           -- AND sys_context('userenv','ip_address') !='192.168.180.106';

   end if;
end trig_system;

3.4，调试报错

ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger

去掉触发器中的commit;

4，查看监控结果，如下所示：

SQL> create table zz_back(id number);
Table created
SQL> insert into zz_back values(1);
1 row inserted
SQL> commit;
Commit complete
SQL> drop table zz_back;
Table dropped
SQL> select * from z_trig_sys;
LT                 SID    SERIAL# USERNAME                       OSUSER                                                           MACHINE                          TERMINAL         OBJECT_NAME                                                                      ORA_SYSEVENT                                                                     PROGRAM                                                          SQLTEXT                                                                          STATUS                         CLIENT_IP                                                    ORA_DBNAME                                                   ORA_CLIENT_IP_ADDRESS
----------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- -------------------------------- ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
2015/11/4 1        787      63023 POWERDESK                      Administrator                                                    WORKGROUP\WIN-TIMMAN             WIN-TIMMAN       plsqldev.exe                                                                     ZZ_BACK                                                                          DROP                                                             drop object on database                                                                                         192.168.120.181                                              POWERDES                                                     

SQL>

看到有记录了，但是有一些没有取到值，比如ora_client_ip_address等，有待继续完善