debug记录登录信息的trigger

今天DBA檢查時發現記錄登錄信息的表中兩台電腦竟然用了同一個IP地址，這兩台電腦都是固定IP，不應該出現這樣的狀況

記錄登入/登出的Trigger是原來一個DBA寫的，他已經離職，我也沒仔細看，這次要好好檢查一下

原來的登入trigger：

DROP TRIGGER SYS.LOGIN_ON_INFO;


CREATE OR REPLACE TRIGGER SYS.login_on_info
   AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO AAA.login_log
               (SESSION_ID,sid,serial#, login_on_time, login_off_time, user_in_db,
                machine, ip_address, run_program)
      SELECT AUDSID,sid,serial#, SYSDATE, NULL, SYS.login_user, machine,
             SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), program
        FROM v$session
       WHERE audsid = USERENV ('SESSIONID')
         AND program NOT LIKE 'JDBC%'
         AND TYPE <> 'BACKGROUND';
      
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

我用toad登進去，用下面SQL一查竟然有三條記錄。。。

SELECT audsid, SID, serial#, SYSDATE, NULL, SYS.login_user, machine,
       SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), program
  FROM v$session
WHERE audsid = USERENV ('SESSIONID');

看樣子這個audsid並不能唯一標識一個session，兩台電腦被記錄同一個IP地址就不奇怪了。

只能用v$mystat了，改一下原來的登入trigger。。。

現在的登入trigger：

DROP TRIGGER SYS.LOGIN_ON_INFO;

CREATE OR REPLACE TRIGGER SYS.login_on_info
   AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO AAA.login_log
               (session_id, SID, serial#, login_on_time, login_off_time,
                user_in_db, machine, ip_address, run_program)
      SELECT audsid, SID, s.serial#, SYSDATE, NULL, s.username, machine,
             SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), s.program
        FROM v$session s, v$process p
       WHERE p.addr = s.paddr
         AND s.SID IN (SELECT SID
                         FROM v$mystat
                        WHERE ROWNUM = 1)
         AND s.program NOT LIKE 'JDBC%'
         AND s.username <> 'SYSMAN'
         AND TYPE <> 'BACKGROUND';
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

這下OK了，登入bug解掉

再看登出trigger。。。

原來的登出trigger：
/* Formatted on 2011/08/10 16:07 (Formatter Plus v4.8.8) */
DROP TRIGGER SYS.login_off_info;

CREATE OR REPLACE TRIGGER SYS.login_off_info
   BEFORE LOGOFF ON DATABASE
BEGIN
   UPDATE aaa.login_log
      SET login_off_time = SYSDATE
    WHERE session_id = USERENV ('SESSIONID');
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

既然audsid不能唯一，那肯定有若干筆記錄的session_id都為一個值，按原來的寫法update，會把很多記錄的log_off_time更新為一個值，再驗證一下

/* Formatted on 2011/08/10 16:10 (Formatter Plus v4.8.8) */
SELECT   session_id
    FROM aaa.login_log
   WHERE login_on_time >= SYSDATE - 1 AND session_id <> 0
GROUP BY session_id
  HAVING COUNT (*) > 1;

返回多個session_id，確實有session_id重複的狀況

SELECT session_id, machine, run_program, login_on_time, login_off_time
  FROM aaa.login_log
WHERE login_on_time > SYSDATE - 1;

多個session的login_off_time被更新為同一個值

通過session_id（audsid)不能唯一標識session，必須加入其它字段，繼續修改登出trigger

現在的登出trigger：

DROP TRIGGER SYS.LOGIN_OFF_INFO;

CREATE OR REPLACE TRIGGER SYS.login_off_info
   BEFORE LOGOFF
   ON DATABASE
BEGIN
   UPDATE   aaa.login_log
      SET   login_off_time = SYSDATE
    WHERE       session_id = USERENV ('SESSIONID')
            AND SID = (SELECT   SID
                         FROM   v$session s
                        WHERE   SID IN (SELECT   SID
                                          FROM   v$mystat
                                         WHERE   ROWNUM = 1))
            AND serial# = (SELECT   serial#
                             FROM   v$session s
                            WHERE   SID IN (SELECT   SID
                                              FROM   v$mystat
                                             WHERE   ROWNUM = 1))
            AND ip_address = SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
            AND machine = SYS_CONTEXT ('USERENV', 'TERMINAL');
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

這下應該是解掉了原來的兩個bug

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38267/viewspace-704609/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38267/viewspace-704609/