Login trigger

Here is a message I found in my mail.

Change the: IF loc_username='TESTLOGIN' THEN RAISE kill_Login; to whatever test you need.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, August 09, 2002 8:13 PM

> Dick,
>
> Here is my database log-on trigger. It obviously saves stuff to a
database
> table for later review.
>
> I developed this for your same reason - to catch people logging on via c
> ertain account with an illegal tool.
>
> Give it a try!
>
> CREATE OR REPLACE TRIGGER WTWDBA.Wtw_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 WTW_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 WTW_CATCH_LOGIN(username,program,login_date,
> osuser, terminal, machine)
> VALUES('*Error*',loc_program,SYSDATE,
> USER,NULL,SUBSTR(client_info_str,-3,3));
>
>
> END;
>
> /
>
>
>
> Tom Mercadante
> Oracle Certified Professional
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).