Oracle 10g User Auditing

I start receiving  oracle alerts for excessive sessions. So I decided to audit user to find source IP that request for session.


view audit status
SQL> SHOW PARAMETER AUDIT

audit_trail options:
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:
    none or false - Auditing is disabled.
    db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
    db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
    xml- Auditing is enabled, with all audit records stored as XML format OS files.
    xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
    os- Auditing is enabled, with all audit records directed to the operating system's audit trail
   
   
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

SQL> SHUTDOWN

SQL> STARTUP

--Auditing only single user connection
CONNECT sys/password AS SYSDBA

SQL> audit connect by MyUser;
   
    --View audit info       
SQL> select userhost, terminal, timestamp, action_name,comment_text from dba_audit_session where username=’MYUser’;

comment_text holds IP address.

You can format your results with the following query.

select count(*),action_name,audi.username,substr(audi.s1,1,instr(audi.s1,')')-1) IP from
(
select username,action_name,substr(comment_text,instr(comment_text,'HOST=')+5,100) s1
from dba_audit_trail
where username='MYUSER'
--and action_name ='LOGON'
--and action_name in ('LOGON','LOGOFF')
)audi
group by audi.username,substr(audi.s1,1,instr(audi.s1,')')-1),action_name
order by 1 desc ;



-- Fetch Audit related views in database
    SELECT view_name
    FROM   dba_views
    WHERE  view_name LIKE 'DBA%AUDIT%'
    ORDER BY view_name;

    VIEW_NAME
    ------------------------------
    DBA_AUDIT_EXISTS
    DBA_AUDIT_OBJECT
    DBA_AUDIT_POLICIES
    DBA_AUDIT_POLICY_COLUMNS
    DBA_AUDIT_SESSION
    DBA_AUDIT_STATEMENT
    DBA_AUDIT_TRAIL
    DBA_COMMON_AUDIT_TRAIL
    DBA_FGA_AUDIT_TRAIL
    DBA_OBJ_AUDIT_OPTS
    DBA_PRIV_AUDIT_OPTS
    DBA_REPAUDIT_ATTRIBUTE
    DBA_REPAUDIT_COLUMN
    DBA_STMT_AUDIT_OPTS

    14 rows selected.

    SQL>

The three main views are shown below.

    DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
    DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
    DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.