oracle登录历史删除,oracle通过触发器实现登录登出日志记录

oracle通过触发器实现登录登出日志记录

-----删除表---

drop table userlogininfo

--创建登录登出记录信息表

create table userlogininfo

(

infoid int primary key not null,

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

)

---删除序列---

DROP SEQUENCE seq_userlogininfo

---创建自动增长序列--

CREATE SEQUENCE seq_userlogininfo

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

NOMAXVALUE -- 不设置最大值

NOCYCLE -- 一直累加，不循环

CACHE 10;

-------

-----删除触发器----

DROP TRIGGER TR_LOGIN_RECORD

------创建登录触发器---

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 userlogininfo(infoid,username,logintime,terminal,ipadress,osuser,machine,

program,sid,serial#,ausid)

VALUES(seq_userlogininfo.nextval,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;

-----创建登出更新登出时间触发器---

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 userlogininfo SET LOGOUT_TIME=SYSDATE WHERE sid=mtSession.Sid AND serial#=mtSession.Serial#;

END IF;

CLOSE cSession;

EXCEPTION

WHEN OTHERS THEN

raise;

END;

©著作权归作者所有：来自51CTO博客作者rm_rf_db的原创作品，如需转载，请注明出处，否则将追究法律责任