Script--How to record user login on oracle database

客户又有新的要求，要看所有开立的account有没有在用，哪些人在哪些PC上用哪些account，最快的方法写个trigger来做记录。下面是一个操作步骤。

=========================================
step 01 grant select privilege as sys user login:
grant select on v_$session to public;
grant select on v_$mystat to public;

Step 02 create login$information table:
create table LOGIN$INFORMATION
(
LOGIN_USER VARCHAR2(30),
LOGIN_TIME DATE,
IP_ADRESS VARCHAR2(256),
AUSID NUMBER,
TERMINAL VARCHAR2(16),
OSUSER VARCHAR2(30),
MACHINE VARCHAR2(64),
PROGRAM VARCHAR2(64),
SID NUMBER,
SERIAL# NUMBER
)

Step 03 create trigger on database level:

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 login$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
null;
CLOSE cSession;
raise_application_error(-20099, 'Login Exception', FALSE);
END IF;
CLOSE cSession;
EXCEPTION
WHEN OTHERS THEN
null;
END tr_login_record;

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

转载于:http://blog.itpub.net/29987/viewspace-51917/