监控登陆数据库的trigger

监控登陆数据库的trigger

1. 赋予权限

SQL> grant select on v_$session to wrj;

Grant succeeded.

SQL>
SQL> grant select on v_$mystat to wrj;

Grant succeeded.

2. 创建表

create table log_information (login_user varchar2(10),login_time date,ip_adress varchar2(20),ausid number,terminal varchar2(30),
osuser varchar2(30),machine varchar2(64),program varchar2(48),sid number,serial# number)；

3 . 创建触发器

CREATE OR REPLACE TRIGGER tr_login_record
AFTER logon ON DATABASE
DECLARE
miUserSid NUMBER;
mtSession v$session%rowtype;
CURSOR cSession(iiUserSid IN NUMBER) IS
SELECT * FROM v$session WHERE sid = iiUserSid;
BEGIN
SELECT sid INTO miUserSid FROM v$mystat WHERE rownum <= 1;
OPEN cSession(miUserSid);
FETCH cSession
INTO mtSession;
--if user exists then insert data
IF cSession%FOUND THEN
INSERT INTO log_information
(login_user,
login_time,
ip_adress,
ausid,
terminal,
osuser,
machine,
program,
sid,
serial#)
VALUES
(ora_login_user,
SYSDATE,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
userenv('SESSIONID'),
mtSession.Terminal,
mtSession.Osuser,
mtSession.Machine,
mtSession.Program,
mtSession.Sid,
mtSession.Serial#);
ELSE
--if user don't exists then return error
--sp_write_log('Session Information Error:'||SQLERRM);
null;
CLOSE cSession;
raise_application_error(-20099, 'Login Exception', FALSE);
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
--sp_write_log('Login Trigger Error:'||SQLERRM);
null;
END tr_login_record;

/