Oracle-L: RE: Auditing logons

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 09 Aug 2002 10:13:25 -0800
Message-ID: <F001.004B0C63.20020809101325@fatcity.com>

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

-----Original Message-----
Sent: Friday, August 09, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L

Folks,

Before I go off re-inventing the wheel once again I'll ask the group is anyone has tried this before. What I have is a request from damanagement to tell them when someone connects to our PeopleSoft database using the schema username, but outside of PeopleTools. The reason is that there have been some
"unexplained" changes to data that have occurred over the last month that is causing a pile of concern. It is believed that someone who has the schema password is using SQL*Plus or Toad to update the data when they should not be
doing so. Now auditing connects for the schema account is not a problem, but
determining which are suspicious and which are due to the damned PeopleSoft panel processor I can't see a way around easily from sys.aud$. Anyone else been
there, done that??

Dick Goulet
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

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: 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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------