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:
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
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.