RE: How to track all the users/machines, which connect an Oracle database

From: Ruel, Chris <Chris.Ruel_at_lfg.com>
Date: Tue, 14 Jul 2015 14:25:51 +0000
Message-ID: <1AFD62082EEAF0448EF1815139687F132BCB9D88_at_NC2PWEX504.us.ad.lfg.com>

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:

Changing DB_AUDIT_TRAIL requires a database restart
You’ll want to come up with a process to manage this audit trail in the DB.
Move the AUD$ table to a dedicated tablespace
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_at_lfg.com<mailto:cruel_at_lfg.com> * Desk:317.759.2172 * Cell 317.523.8482

From: Mandal, Ashoke [mailto:ashoke.k.mandal_at_medtronic.com] Sent: Monday, July 13, 2015 7:21 PM
To: Ruel, Chris; oracle-l-bounce_at_freelists.org; oracle-l_at_freelists.org 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_at_lfg.com] Sent: Monday, July 13, 2015 2:41 PM
To: Mandal, Ashoke; oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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_at_lfg.com<mailto:cruel_at_lfg.com> * Desk:317.759.2172 * Cell 317.523.8482

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mandal, Ashoke Sent: Monday, July 13, 2015 3:32 PM
To: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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.**

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 14 2015 - 16:25:51 CEST

This message: [ Message body ]
Next message: Dba DBA: "11g RAC TAPI/Custom services : how to query in data dictionary"
Previous message: Sweetser, Joe: "RE: Where, oh where, did my directory go?"
Maybe in reply to: Mandal, Ashoke: "How to track all the users/machines, which connect an Oracle database"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message