IP in aud$ table

Few days back, i was asked by a friend “HOW DO I  GET CLIENT’s IP , CONNECTING TO THE DATABASE”???? and i said it can be done by UTL_INADDR package. Within this package GET_HOST_ADDRESS subprograms takes a varchar2 datatype within which give the host name for which we want to get IP, and this host name can be obtained from the MACHINE column in V$session view.

SYS @ oracle >select UTL_INADDR.GET_HOST_ADDRESS('ANAND') from dual;

UTL_INADDR.GET_HOST_ADDRESS('ANAND')
--------------------------------------------------------------------
192.168.1.10
SYS @ oracle > COL ip format a20

SYS @ oracle >select sid, machine,UTL_INADDR.get_host_address
(substr(machine,instr(machine,'\')+1))ip from v$session where
type='USER' and username is not null order by sid;

But then, he wanted to audit these IPs for some purpose.Very truly saying, i had no idea how and where do we get IP addresses in aud$ table.Few search in Google gave me a good PPT on Oracle Auditing by Natalka Roshak.

AUD$ table records IP when and only when session auditing is enabled.The column COMMENT$TEXT in AUD$ contains the IP and port address of the client.

Now was the time to test:-

SYS @ oracle >sho parameter audit

NAME                                 TYPE        VALUE                                                                     
------------------------------------ ----------- ------------------------------                                            
audit_file_dest                      string      D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORACLE\ADUMP                                                             
audit_sys_operations                 boolean     FALSE                                                                     
audit_trail                          string      NONE

SYS @ oracle >sho parameter timed
NAME                                 TYPE        VALUE                                                                     
------------------------------------ ----------- ------------------------------                                            
timed_os_statistics                  integer     0                                                                         
timed_statistics                     boolean     TRUE                                                                      

SYS @ oracle >alter system set audit_trail=db scope=spfile;

System altered.

SYS @ oracle >shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS @ oracle >startup
ORACLE instance started.

Total System Global Area  289406976 bytes                                                                                  
Fixed Size                  1296308 bytes                                                                                  
Variable Size             104859724 bytes                                                                                  
Database Buffers          176160768 bytes                                                                                  
Redo Buffers                7090176 bytes                                                                                  
Database mounted.
Database opened.
SYS @ oracle >
SYS @ oracle >sho parameter audit
NAME                                 TYPE        VALUE                                                                     
------------------------------------ ----------- ------------------------------                                            
audit_file_dest                      string      D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORACLE\ADUMP                                                             
audit_sys_operations                 boolean     FALSE                                                                     
audit_trail                          string      DB                                                                        

SYS @ oracle >audit select on scott.emp by access whenever successful;

Audit succeeded.

LOGGED IN AS SCOTT IN ANOTHER SESSIONS AND FIRED FEW SELECT QUERIES

SYS @ oracle >select count(1) from aud$;

 COUNT(1)                                                                                                                 
----------                                                                                                                 
 4                                                                                                                 

 SYS @ oracle >col USERHOST for a30
 SYS @ oracle >select SESSIONID,USERHOST,TERMINAL,ACTION#,COMMENT$TEXT from aud$;

 SESSIONID         USERHOST                  TERMINAL       ACTION#          COMMENT$TEXT                          
----------  ------------------------------  ----------     ---------- ----------------------------                                                                                                                  
 70821           WORKGROUP\ANAND                ANAND         3                                       
 70821           WORKGROUP\ANAND                ANAND         3                                       
 70821           WORKGROUP\ANAND                ANAND         3                                       
 70821           WORKGROUP\ANAND                ANAND         3

The COMMENT$TEXT column is blank as the audit was not a session audit.It was an object level auditing.

REM “AUD$ records IP and PORT address when session auditing is enabled”

Lets enable session audit for user scott and check it!!!!

SYS @ oracle >audit session by scott;

Audit succeeded.

SYS @ oracle >audit session by hr;

Audit succeeded.

SYS @ oracle >

SYS @ oracle >truncate table aud$;    --- Truncated to remove the previous audit entries.

Table truncated.

SYS @ oracle >select count(1) from aud$;

 COUNT(1)                                                                                                                 
----------                                                                                                                 
 0

SYS @ oracle >REM “Logged in as  SCOTT and HR using another session”

SYS @ oracle >col COMMENT$TEXT for a60
SYS @ oracle >select SESSIONID,USERID,TERMINAL,LOGOFF$TIME,COMMENT$TEXT from aud$;

 SESSIONID    USERID            TERMINAL      LOGOFF$TI     COMMENT$TEXT
---------- ---------------    -------------  ---------   ------------------------------------------------------------
 80377        SCOTT             ANAND         06-NOV-09   Authenticated by: DATABASE;
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.3)(PORT=4538))
 80387         HR               ANAND         06-NOV-09   Authenticated by: DATABASE;
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.3)(PORT=4977))

REM “EXTRACTING THE IP”

SYS @ oracle >select userid, terminal, spare1, substr(s1,1,instr(s1,')')-1) IP  from
(select A.*, substr(comment$text,instr(comment$text,'HOST=')+5,100) s1 from aud$ a)
order by IP;

USERID                         TERMINAL      SPARE1     IP
------------------------------ ------------- ---------- ------------------------------
HR                             ANAND         user       192.168.1.3
SCOTT                          ANAND         user       192.168.1.3

note:- SPARE1 = OS username

It was a nice learning for me…so thought to blog it 🙂

For more references on AUDIT refer to:-

http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4007.htm