How can I check IP/Hostname 1

[m2001331]

Hi,

I have encountered incidents where table structure of a certain table have been altered by some unauthorised person - how do i check for the IP/Hostname of the machine of the unauthorised person by using the logs in oracle database? I can see that the sessions log do not hold such information.
thanks and regards.

 

[alexhu]

If you look in the listener log then the hostname of the machine is shown, but if you have users who telnet to the server then use sqlplus the information is not logged as the listener is bypassed. Even if it was logged it would log the server IP address not the PC connecting to it.

I have been looking at this problem all this week and stll can't see a way round it.

Alex

 

[m2001331]

Hi,

Most of the access to the oracle server in my environment is thru oracle client - sqlplus. Therefore checking the listener log will not be possible.However I have enabled the sql_trace = true, but i coudn't find the *.trc file that was generated, i couldn't locate the init.ora as well!
I also cannot locate the user_dump_dest folder.
This is Oracle 9i on Solaris 8.
i will try to find a solution and update this thread if i find any.

rgds.

 

[AOLEURO]

Hi M2001331

From your 2nd reply it sounds like you are the hacker! - How come you are DBA on a Solaris Oracle9i and can't found files and folders?

I would use LOGMINER to trace exact change on a table. LOGMINER use Redo logs and Archive logs to show DML and DDL commands on a table. It is possible to "rollback" manually by extracting information with LOGMINER from v$logmnr_contents.sql_undo.

Oracle doesn’t care about ip-number og hostname then saving information - it use USERNAME. However you can create a trace file by creating an AFTER LOGON ON DATABASE|SCHEMA trigger.

And you extract information about the client with SYS_CONTEXT.

First I create a tracer table:

 create SYSTEM.table tracer_log( session_user varchar2(30), 
                                sessionid    varchar2(30), 
                                host         varchar2(54), 
                                ip_no        varchar2(30),
                                os_user      varchar2(30) )
/

You need to grant insert rights to all users !!!

When I create the trigger:

CREATE OR REPLACE TRIGGER track AFTER LOGON ON DATABASE
BEGIN
     insert into SYSTEM.tracer_log 
         select sys_context('USERENV','SESSION_USER'),
                sys_context('USERENV','SESSIONID' ),
                sys_context('USERENV','HOST'),
                sys_context('USERENV','IP_ADDRESS'),
                sys_context('USERENV','OS_USER')
         from dual;
END;

In v$logmnr_contents you search SEGMENT_OWNER (schema) and SEGMENT_NAME (table name) and maybe SEGMENT_TYPE (table) and use TIMESTAMP, like this

select SCN,
       TIMESTAMP,
       SQL_REDO
from v$logmnr_contents
where segment_owner = 'AOLEURO'
and   segment_name  = 'EMPLOYEE'
and   timestamp between to_date('01-01-2003','dd-mm-yyyy') and sysdate;

You then search the trace file like this:

Select Trc.Username,
       Trc.Host,
       Trc.ip_no,
       Trc.Os_User
from tracer_log Trc, v$logmnr_contents Mnr
Where Trc.sessionid    = Mnr.session#
And   Trc.session_user = Mnr.username
and   Mnr.SCN          =

'Found value'

group by Trc.UserName,
         Trc.Host,
         Trc.ip_no,
         Trc.Os_User;

None can hide from you except DBAs. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !