Tips and wisdom from 15 years of DBA experience

Tuesday, August 18, 2009

Audit Logons in Oracle

There is more than one way to audit logons in Oracle. I will provide what I beleive to be the simplest method. This will give you the who,when and where of Oracle logons. A couple of notes about this method: It will store a new record at each Oracle session creation. If you use OEM, then you may get spammed with records from DBSNMP (or whatever user you are telling OEM to log in as).

Setup:


$ sqlplus "/ as sysdba"
SQL> show parameter audit_trail;
(if it shows "db", you are done with setup. Go onto the next section)
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown;
SQL> startup;
SQL> show parameter audit_trail;
(verify that the parameter equals "db")


Turning on Auditing:


$ sqlplus "/ as sysdba"
SQL> audit create session by session;
SQL> select count(*) from dba_audit_trail;
(should be zero)
SQL> exit;
$ sqlplus non-sys-user/pw
SQL> exit;
$ sqlplus "/ as sysdba"
SQL> select count(*) from dba_audit_trail;
(should be non-zero)


Done. Short and Sweet.





Update:


If you want to capture logons from SYS or anyone connecting with SYSOPER or SYSDBA, you need to issue the following, and bounce the database:


alter system set audit_sys_operations=true scope=spfile;


1 comment:

lverma said...

Hey Craig, Are you the one in SJM???

Just addtion to logging that you mentioned above, if you want all the audit data to be redirected to /var/log/messages instead of aud$, then you need to set this,

AUDIT_TRAIL=OS
AUDIT_SYSLOG_LEVEL=USER.NOTICE ( I am using USER.NOTICE to log the lowest level of auditing.)


-Lokesh

Followers