Oracle 11g系統層級觸發器來跟蹤監控drop誤操作

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 tablecreate 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_systemafter drop on databasebegin  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 createdSQL> insert into zz_back values(1);1 row insertedSQL> commit;Commit completeSQL> drop table zz_back;Table droppedSQL> 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等，有待繼續完善