Connection Audit

Tracking what users are coming and going on a database is an important process.  One of the main reasons for this is because when something goes wrong or data is missing it maybe a good idea to have a starting place to review what could have potentially happened.    Also, from a SOX compliance stand point, it will keep a lot of people out of trouble when the auditors come a calling.  In oracle, auditing is easy to setup.

 

The first step in setting up auditing on Oracle is to set the init.ora parameter.  This parameter can be either manually added to the init.ora file.  I find it easier if, with Oracle10g, to just update the spfile.  Remember though, if you update the spfile, you need to recreate the pfile so this option will be enabled is you ever need to use the pfile.

 

From SQL*Plus as SYS user turn on auditing:

 

Alter system set audit_trail=DB scope=spfile;

 

The database will need to be shutdown and started up again to read this parameter into the database configuration.

 

Now, you will want to enable auditing on the database.  What!, auditing is not enabled when you set the option in the init.ora file!  That would be correct, the init.ora file just tell Oracle that you want to use auditing.  Now we are going to tell Oracle what to audit for.  In this case we are going to audit for logon and logoff connections.

 

From SQL*Plus as SYS user issue the following command:

 

Audit connect;

 

This will now log every logon and logoff that connect to the database.

 

To check and see what is being audited by session, the dba_audit_session view is used.  This view will contain all information for sessions that are being audited since auditing was enabled.  The return code column should report a 0 if the logon/logoff was successful.  Other reason codes you should look out for will be 1070 and 28000.

 

Ora-1070 = invalid logon/password

Ora-28000 = account is locked/expired

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Mrs. Jaye's Class Blog

Coordinate Algebra & Advanced Algebra

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

HeliFromFinland

Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet.com

Stuff that interests me, if not you!

%d bloggers like this: