RE: 'Auditing' user connections

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Mon, 16 Aug 2004 15:27:26 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93C70@qtiexch2.qgraph.com>

An easy way is to look at your $ORACLE_HOME/network/log/listener.log, if you're using it. The trick is if you want to do any heavy-duty analysis, you'll probably want to get that info in the DB. Because listener logging formats can vary between instances (I *think* it has to do with how it's defined on the client -- in TNSNAMES.ORA, ONAMES, or OID), you might need something icky like this for SQL*Loader: load data
infile '/tmp/listener.log'
badfile 'listener.err'
append
into table MY_DBA.listener_log
when (43) = 'Q'
trailing nullcols

(timestamp position(1:20) date "DD-MON-YYYY HH24:MI:SS",

        skip1 FILLER char terminated by '=',
        sidname enclosed by "(SID=" and ")",
        skip2 FILLER char terminated by "=",
        program enclosed by "(PROGRAM=" and ")",
        hostname enclosed by "(HOST=" and ")",
        osuser enclosed by "(USER=" and ")))",
        skip3 FILLER char terminated by '=',
        skip4 FILLER char terminated by '=',
        skip5 FILLER char terminated by ')',
        ipaddr enclosed by "(HOST=" and ")"
        )

into table MY_DBA.listener_log
when (43) = 'M'
trailing nullcols

(timestamp position(1:20) date "DD-MON-YYYY HH24:MI:SS",

        skip1 FILLER char terminated by '=',
        sidname enclosed by "(SID=" and ")",
        skip2 FILLER char terminated by "=",
        program enclosed by "(PROGRAM=" and ")",
        hostname enclosed by "(HOST=" and ")",
        osuser enclosed by "(USER=" and ")))",
        skip3 FILLER char terminated by '=',
        skip4 FILLER char terminated by '=',
        skip5 FILLER char terminated by ')',
        ipaddr enclosed by "(HOST=" and ")"
        )

into table MY_DBA.listener_log
when (43) = 'E'
trailing nullcols

(timestamp position(1:20) date "DD-MON-YYYY HH24:MI:SS",

        skip1 FILLER char terminated by '=',
        sidname enclosed by "(SID=" and ")",
        skip2 FILLER char terminated by "(CID=",
        program enclosed by "(PROGRAM=" and ")",
        hostname enclosed by "(HOST=" and ")",
        osuser enclosed by "(USER=" and ")))",
        skip3 FILLER char terminated by '=',
        skip4 FILLER char terminated by '=',
        skip5 FILLER char terminated by ')',
        ipaddr enclosed by "(HOST=" and ")"
        )

Not having used it recently, I'm not sure how this would look for an external file's definition.

Hope this drivel helps! GL!

Rich

-----Original Message-----
Sent: Monday, August 16, 2004 2:51 PM
Subject: 'Auditing' user connections

I have been asked to track user connections to a database (9.2) to see what logins are being used, where they are coming from, etc. Auditing first comes to mind, but our maintenance window for a db restart is not for a few weeks and the information is being requested ASAP. The first thing that comes to mind is a database logon trigger and a table to capture the relevant information.

Before I go down this path, I want to make certain that other, more viable, options don't really exist. All connections (except dba) are coming from external sources. I'm looking into SQL*Net settings right now, but nothing jumps out at me as a complete solution.

Any assistance, ideas, gotchas are appreciated.

Daniel Fink

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 16 2004 - 15:22:57 CDT