Ashoke,
If you are on a Unix/Linux system, files will be created in this directory with
the audit information:
SQL> show parameter audit_file_dest
If you want these to easily searchable, you will probably want to come up with
a script to process these files.
If you are on a Windows system, you can disregard the setting for
audit_file_dest as the OS audit_trail goes to the event log (someone correct me
if I am wrong…it’s been years since I ran Oracle on Windows).
My advice? Set the AUDIT_TRAIL = DB, EXTENDED
This will not only log the audit entries to the AUD$ table, but, they will be
easily searchable using a number of views like DBA_AUDIT_TRAIL.
Couple of things:
1. Changing DB_AUDIT_TRAIL requires a database restart
2. You’ll want to come up with a process to manage this audit trail in
the DB.
a. Move the AUD$ table to a dedicated tablespace
b. Remove/archive audit entries on a regular basis to manage the growth of
the AUD$ table. If it gets to large, it can impact performance not to mention
it becomes difficult to search.
See MOS:
Oracle Support Document 1362997.1 (SCRIPT: Basic example to manage AUD$ table
in 11.2 with dbms_audit_mgmt) can be found at:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1362997.1
The above is by no means a complete solution but hopefully it can get you
started. In an ideal world, I like to move audit entries to a audit history
table for long term keeping and then use the above script to keep the aud$
table cleaned up.
Chris..
_____________________________________________________________________
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
cruel@xxxxxxx<mailto:cruel@xxxxxxx> * Desk:317.759.2172 * Cell 317.523.8482
From: Mandal, Ashoke [mailto:ashoke.k.mandal@xxxxxxxxxxxxx]
Sent: Monday, July 13, 2015 7:21 PM
To: Ruel, Chris; oracle-l-bounce@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: How to track all the users/machines, which connect an Oracle
database
Chris, My database has the setting of AUDIT_TRAIL=OS. Is there a way I can
gather my required information with this configuration?
Or I need to set AUDIT_TRAIL=DB and bounce the database.
Thanks,
Ashoke
From: Ruel, Chris [mailto:Chris.Ruel@xxxxxxx]
Sent: Monday, July 13, 2015 2:41 PM
To: Mandal, Ashoke;
oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: How to track all the users/machines, which connect an Oracle
database
I would use the auditing function built into the database. I believe logins
are automatically captured as of 11g and above. It will not only include the
DB username, but, the OS username and terminal/host from which the login
originated.
Check the view DBA_AUDIT_TRAIL. Unless you have changed from default, the
AUDIT_TRAIL parameter should be set to DB to access the data in this view.
Chris..
_____________________________________________________________________
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
cruel@xxxxxxx<mailto:cruel@xxxxxxx> * Desk:317.759.2172 * Cell 317.523.8482
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mandal, Ashoke
Sent: Monday, July 13, 2015 3:32 PM
To: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: How to track all the users/machines, which connect an Oracle database
Hello All,
We have a database, which serves multiple users at the factory production
floor. We like to find out which computer/user connects to the database. As far
as I understand the v$session will provide the info on the computers/users,
which are currently connected to the database but we need to capture all
computers/users, who have been connecting to the database during last few
months.
Any idea?
Thanks,
Ashoke
[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is
proprietary to Medtronic and is intended for use only by the individual or
entity to which it is addressed, and may contain information that is private,
privileged, confidential or exempt from disclosure under applicable law. If you
are not the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are notified that any use or dissemination
of this information in any manner is strictly prohibited. In such cases, please
delete this mail from your records. To view this notice in other languages you
can either select the following link or manually copy and paste the link into
the address bar of a web browser: http://emaildisclaimer.medtronic.com
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
