ISR Computing -

Oracle Database Auditing

Show connection attempts

SELECT username, terminal, action_name, to_char (timestamp, 'DDMMYYYY:HHMISS') timestamp, to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time, returncode FROM dba_audit_session

Output:

Username | Timestamp | Terminal | Logoff_time | Action | Returncode

Show total connection attempts per user

SELECT count(*), username, terminal, to_char(timestamp, 'DDMMYYYY:HHMISS') FROM dba_audit_session WHERE returncode <> 0 GROUP BY username, terminal, to_char(timestamp, ('DD-MON-YYYY');

Output:

Number of failed attempts | Username | Terminal | Timestamp

Show connection attempts with invalid (non-exist) users

SELECT username, terminal, to_char (timestamp, 'DDMMYYYY:HHMISS') FROM dba_audit_session WHERE returncode <> 0 AND not exists (SELECT 'x' FROM dba_users WHERE dba_users.username=dba_audit_session.username)

Output:

Invalid usernames | timestamps | terminal

Show access at Unusual Hours (19:30PM - 8:00AM)

SELECT username, terminal, action_name, returncode, to_char (timestamp, 'DD-MONYYYYHH24: MI:SS') to char(logoff_time,'DD-MON-YYYYHH24:MI:SS') FROM dba_audit_session WHERE to_date(to_char(timestamp,'HH24:MI:SS'), 'HH24:MI:SS') < to_date ('08:00:00, 'HH24:MM:SS') or to_date(to_char(timestamp('HH24:MI:SS'),'HH24:MI:SS') < to_date('19:30:00','HH24:MI:SS')

Output:

Username | Terminal | Action | Returncode | Timestamp

Show users accessing database with same username from multiple locations

SELECT count(distinct(terminal)), username FROM dba_audit_session having count (distinct(terminal))>1) GROUP BY username

Output:

Username | Number of Terminals user connected from

Show users accessing database using multiple usernames and coming from the same network location

SELECT count(distinct(username)), terminal from dba_audit_session having count (distinct(username))>1) group by terminal

Output:

Terminal | Number of accounts logged in from this location

Reference Oracle Security Guide to enable Database Auditing:

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984