Ashok,
Below is a database trigger that I use to capture all successfull logins to
a database table.
Works great - and I spin reports off daily to see who is connecting with
what.
Tom Mercadante
Oracle Certified Professional
CREATE OR REPLACE TRIGGER WTWDBA.catch_login_trg
AFTER logon ON DATABASE
DECLARE
client_info_str V$SESSION.CLIENT_INFO%TYPE;
loc_program V$SESSION.PROGRAM%TYPE;
loc_username V$SESSION.USERNAME%TYPE;
loc_osuser v$session.OSUSER%TYPE;
loc_terminal v$session.TERMINAL%TYPE;
loc_machine v$session.MACHINE%TYPE;
kill_Login EXCEPTION;
PRAGMA EXCEPTION_INIT( kill_Login, -20999 );
BEGIN
-- set a unique string
dbms_random.seed(dbms_utility.GET_TIME);
client_info_str := 'WTWLOGIN_' || LTRIM(dbms_random.value,'.');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info_str);
SELECT program, username,
osuser, terminal, machine
INTO loc_program, loc_username,
loc_osuser,loc_terminal,loc_machine
FROM V$SESSION
WHERE client_info=client_info_str;
IF loc_username = 'SYS'
AND loc_program = 'RESRCMON.EXE' THEN
NULL;
ELSE
INSERT INTO CATCH_LOGIN(username,program,login_date,
osuser, terminal, machine)
VALUES(loc_username,loc_program,SYSDATE,
loc_osuser,loc_terminal,loc_machine);
COMMIT;
IF loc_username='TESTLOGIN' THEN
RAISE kill_Login;
END IF;
END IF;
EXCEPTION
WHEN kill_Login THEN
RAISE_APPLICATION_ERROR(-20999,'Login''s using this account and this
tool are Invalid');
WHEN OTHERS THEN
loc_program := SUBSTR(SQLERRM,1,100);
INSERT INTO CATCH_LOGIN(username,program,login_date,
osuser, terminal, machine)
VALUES('*Error*',loc_program,SYSDATE,
USER,NULL,SUBSTR(client_info_str,-3,3));
END;
/
-----Original Message-----
Sent: Monday, January 28, 2002 1:06 PM
To: Multiple recipients of list ORACLE-L
Greetings,
I would like to know if there is a way to track the successful and
un-successful login to an oracle database by any users.
I enabled tracing and I can see some information about the login to
database. But could not differentiate between successful login and the
un-successful attempt to login.
Any ideas.
Thanks,
Ashok
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ashoke Mandal
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
