Oracle-L: Re: Auditing logons

From: <Jared.Still_at_radisys.com>
Date: Fri, 09 Aug 2002 09:18:36 -0800
Message-ID: <F001.004B0B78.20020809091836@fatcity.com>

Dick,

I'm doing something similar. If your're on 8i+, this may work for you.

create table session_logon_audit (

   username varchar2(30) not null,
   logon_date date not null--,
   osuser varchar2(30),

   machine      varchar2(64),
   program      varchar2(64)

)
tablespace tools
/

create or replace trigger session_logon_audit_trigger after logon on database
declare

pragma autonomous_transaction;

   vUsername varchar2(30);
   vSql varchar2(200);
   vOsuser v$session.osuser%type;
   vMachine v$session.machine%type;
   vProgram v$session.program%type;

begin

vUsername := upper(sys_context ('userenv', 'session_user') );

vSql := 'insert into session_logon_audit(username, logon_date, osuser, machine, program)';

vSql := vSql || ' values( :1, :2, :3, :4, :5)';

begin

if vUsername <> 'CIMUSER' and vUsername <> 'SYSTEM' then

get row for this session from v$session select osuser, machine, program into vOsuser, vMachine, vProgram from v$session s where sys_context('userenv', 'SESSIONID') = s.audsid;

execute immediate vSql using vUsername, sysdate, vOsuser, vMachine, vProgram;

commit;

      end if;
   exception
   when others then

don't let errors propogate and knock user off the DB null; end;

end;
/

show errors trigger session_logon_audit_trigger

HTH, Jared

dgoulet_at_vicr.com
Sent by: root_at_fatcity.com
08/09/2002 09:48 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Auditing logons

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:
INET: Jared.Still_at_radisys.com

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