RE: Auditing logons

This is what I use ...

CREATE OR REPLACE TRIGGER DBT_USERS_LOGON AFTER LOGON ON DATABASE

--
DECLARE
  CURSOR cur_sess IS
    SELECT *
      FROM v$session
     WHERE AUDSID = USERENV('SESSIONID')
	   AND USERNAME NOT IN ('HEARTBEAT');


  --

  recSess    cur_sess%ROWTYPE;


  --

  PRAGMA AUTONOMOUS_TRANSACTION;


  --

BEGIN
   OPEN cur_sess;
   FETCH cur_Sess INTO recSess;
   CLOSE cur_sess;
   --
   INSERT INTO USER_LOGON_AUDIT
     (SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM,
      TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME)
   VALUES
     (USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser),
      recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL);
   COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
END DBT_USERS_LOGON;
/

As you can see USER_LOGON_AUDIT is a simple table in SYSTEM schema.

Raj
______________________________________________________
Rajendra Jamadagni		MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


-----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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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).



text/plain attachment: InterScan_Disclaimer.txt