oracle 利用触发器记录用户登录登出信息

1.建表用来存储登录信息

 

create table LOG$INFORMATION

(

  USERNAME VARCHAR2(30),

  TERMINAL VARCHAR2(50),

  IPADRESS VARCHAR2(20),

  OSUSER VARCHAR2(30),

  MACHINE VARCHAR2(64),

  PROGRAM VARCHAR2(64),

  SID NUMBER,

  SERIAL# NUMBER,

  AUSID NUMBER,

  LOGINTIME DATE default sysdate,

  LOGout_TIME date

)

/

 

2.记录登录信息

 

CREATE OR REPLACE TRIGGER TR_LOGIN_RECORD

AFTER logon ON DATABASE

DECLARE

mtSession v$session%ROWTYPE;

CURSOR cSession(iiQuerySid IN NUMBER) IS

   SELECT * FROM v$session

      WHERE nvl(osuser,'x') <> 'SYSTEM'   and type <> 'BACKGROUND' and audsid = iiQuerySid;

BEGIN

OPEN cSession(userenv('SESSIONID'));

  FETCH cSession INTO mtSession;

  IF cSession%FOUND THEN

INSERT INTO log$information(username,logintime,terminal,ipadress,osuser,machine,

program,sid,serial#,ausid)

       VALUES(USER,SYSDATE,mtSession.Terminal,

              SYS_CONTEXT ('USERENV','IP_ADDRESS'),mtSession.Osuser,

          mtSession.Machine,mtSession.Program,mtSession.Sid,mtSession.Serial#,userenv('SESSIONID'));

  END IF;

  CLOSE cSession;

EXCEPTION

  WHEN OTHERS THEN

    raise;

end;

 

/

3.记录登出信息

 

create or replace trigger TR_LOGOFF_RECORD

before LOGOFF ON DATABASE

DECLARE

mtSession v$session%ROWTYPE;

CURSOR cSession(iiQuerySid IN NUMBER) IS

   SELECT * FROM v$session where

       nvl(osuser,'x') <> 'SYSTEM'   and type <> 'BACKGROUND' and audsid = iiQuerySid;

BEGIN

OPEN cSession(userenv('SESSIONID'));

  FETCH cSession INTO mtSession;

  IF cSession%FOUND THEN

            UPDATE LOG$INFORMATION SET LOGOUT_TIME=SYSDATE WHERE sid=mtSession.Sid AND serial#=mtSession.Serial#;

  END IF;

  CLOSE cSession;

EXCEPTION

  WHEN OTHERS THEN

    raise;

END;

 

 

 

select *from LOG$INFORMATION;