oracle触发器实例if,【学习笔记】Oracle触发器 实现记录登录数据库的用户信息案例...

天萃荷净

分享一篇关于Oracle数据库安全策略，通过Oracle触发器实现记录存储user用户登录数据库的时间和退出数据库时间信息

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;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle触发器 实现记录登录数据库的用户信息案例