ORACLE 11g 實現偽審計使用者<登陸退出>的詳細記錄

標籤：資訊   date   資料   last   action   問題   session   資源   class   

需求就是需要記錄使用者登陸登出的記錄，包括時間以及串連ip地址以及串連方式等等，這樣有助於跟蹤分析問題，特別是一些人為無意識的dml操作導致資料丟失、資料混亂的問題追蹤，還是非常有效。
大家知道Oracle庫開啟了審計audit後，肯定有辦法查到問題，但是審計比較消耗資源，所以可以通過觸發器之類的來間接實現這個功能。

1、建記錄表

-- Create table  create table UC_LOGON_OFF  (    user_id         VARCHAR2(30),    session_id      NUMBER(8),    host            VARCHAR2(30),    last_program    VARCHAR2(48),    last_action     VARCHAR2(32),    last_module     VARCHAR2(32),    logon_day       DATE,    logon_time      VARCHAR2(10),    logoff_day      DATE,    logoff_time     VARCHAR2(10),    elapsed_minutes NUMBER(8),    sid             NUMBER(8),    serial          NUMBER(8)  )  tablespace USERS      pctfree   10       //--Pctfree：塊中保留用於UPDATE操作的空間百分比，當資料佔用的空間達到此上限時，新的資料將不能插入此塊中  initrans  1        //1--並行事務控制，1個代表最多一個並行事務  maxtrans  255;    //--maxtrains定義了資料區塊中資料槽的最大值。事務表會根據需要動態擴充，最大達到MAXTRANS 個條目（假設塊上有足夠的自由空間）。所分配的每個事務條目需要佔用塊首部中的23～24 位元組的儲存空間。

 

2、建立登陸觸發器

CREATE OR REPLACE TRIGGER trig_logon_audit    AFTER LOGON     ON DATABASE  BEGIN     INSERT INTO timdba.uc_logon_off          select  USER,                  SYS_CONTEXT (‘USERENV‘, ‘SESSIONID‘),                  SYS_CONTEXT (‘USERENV‘, ‘HOST‘),                  NULL,                  NULL,                  NULL,                  SYSDATE,                  TO_CHAR (SYSDATE, ‘yyyy-mm-dd hh24:mi:ss‘),                  NULL,                  NULL,                  NULL,                  NULL, nullfrom dual;  END;  

 

3，建立登出的觸發器
登出這裡也採用了insert語句，是考慮到因為使用者如果用了update的話，在登陸記錄裡面做update操作，如果登陸使用者惡意的話，它是可以修改記錄的。它下次登陸後，直接修改上次登陸的記錄的ip地址等等資訊，這樣就會給我們誤導了，當然我們也可以根據歸檔日誌來分析，但是會比較麻煩一些，那麼同理這張記錄表uc_logon_off表就失去記錄的功效了，因為每次我們都會懷疑這裡的記錄是否被認為修過過了，而不是真實的原始記錄。
那現在改成了insert，再加上後面禁止delete、update操作，那麼就確保uc_logon_off表的資料的真實性，雖然有可能被人惡意insert新記錄的可能，但是每次它串連後最後一條記錄都是真實的，這樣我們就只根據uc_logon_off表進行分析就可以得出使用者的操作行為記錄。

CREATE OR REPLACE TRIGGER trig_logoff_audit   BEFORE LOGOFF     ON DATABASE  DECLARE      v_date date;  BEGIN      SELECt t.logon_day INTO v_date from (select logon_day FROM timdba.uc_logon_off where session_id= SYS_CONTEXT (‘USERENV‘, ‘SESSIONID‘) AND logon_day IS NOT NULL order by logon_day asc) t where rownum<2;      INSERT INTO timdba.uc_logon_off          select  USER,                  SYS_CONTEXT (‘userenv‘, ‘SESSIONID‘),                  SYS_CONTEXT (‘userenv‘, ‘host‘),                 (SELECT action FROM v$session WHERE SYS_CONTEXT (‘USERENV‘, ‘SESSIONID‘) = audsid),                 (SELECT program FROM v$session WHERE SYS_CONTEXT (‘USERENV‘, ‘SESSIONID‘) = audsid),                 (SELECT module  FROM v$session WHERE SYS_CONTEXT (‘USERENV‘, ‘SESSIONID‘) = audsid),                  v_date,                  TO_CHAR(v_date, ‘hh24:mi:ss‘),                  sysdate,                  TO_CHAR(sysdate, ‘hh24:mi:ss‘),                  TRUNC(TO_NUMBER(sysdate - v_date) * 1440,2),                  ROUND(TO_NUMBER(sysdate - v_date) * 24 * 60*60 ),                  NULL,                  SYS_CONTEXT (‘userenv‘, ‘ip_address‘) from dual;                                   commit;      COMMIT;  END;

 

4，禁止刪改登陸登出操作記錄

create or replace trigger timdba.trig_uc_logonoff    before update or delete on timdba.uc_logon_off  DECLARE     PRAGMA AUTONOMOUS_TRANSACTION;  BEGIN    IF deleting THEN      RAISE_APPLICATION_ERROR(-20001, ‘can not delete ‘);   ELSIF updating then      RAISE_APPLICATION_ERROR(-20001, ‘can not update ‘);    END IF;  END;  

 

   
5，給所有的使用者授權記錄操作
因為觸發器裡面需要查詢v$sql以及v$session，還要對timdba.uc_logon_off表進行操作，所以需要賦予這些操作許可權，那麼如果要記錄所有使用者的，就需要把所有使用者都賦予這樣的操作許可權。

--（1）統計需要授權的語句  grant create session,connect to dw;  grant select on v_$sql to dw;  grant select on v_$session to dw;  grant select,insert on timdba.uc_logon_off to dw;  --（2）建立暫存資料表，記錄所有使用者  create table timdba.Z_USERS as select distinct owner from all_objects;  --（3）產生授權的sql語句  select ‘grant create session,connect to ‘ || owner ||‘; ‘ from timdba.Z_USERS t where t.owner not in(‘TIMDBA‘,‘SYS‘,‘SYSTEM‘)  union all  select  ‘grant select on v_$sql to  ‘ || owner ||‘; ‘ from timdba.Z_USERS t where t.owner not in(‘TIMDBA‘,‘SYS‘,‘SYSTEM‘)  union all  select ‘grant select on v_$session to  ‘ || owner ||‘; ‘   from timdba.Z_USERS t where t.owner not in(‘TIMDBA‘,‘SYS‘,‘SYSTEM‘)  union all  select ‘grant select,insert on timdba.uc_logon_off to ‘ || owner ||‘; ‘ from timdba.Z_USERS t where t.owner not in(‘TIMDBA‘,‘SYS‘,‘SYSTEM‘);  --（4）將sql結果記錄copy到一個檔案grant_logonoff.sql，放到/home/oracle目錄，然後sqlplus登陸執行sql檔案授權。  SQL> @/home/oracle/grant_logonoff.sql;  Grant succeeded.  Grant succeeded.  Grant succeeded.  ……s     

 

6，查看部分記錄
查詢sql：

select  t.elapsed_minutes,t.elapsed_seconds,t.ip,t.*  from  uc_logon_off t
where t.user_id in(‘DW‘,‘LOGMINER‘,‘YS‘,‘TIMDBA‘,‘PLCRM‘)
      and t.elapsed_minutes is not null order by t.logon_day desc,t.session_id desc;

 

ORACLE 11g 實現偽審計使用者<登陸退出>的詳細記錄