RE: 'Auditing' user connections
- From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Aug 2004 15:27:26 -0500
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
