Skip to Main Content
  • Questions
  • Get client remote client IP address,

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, NGUYEN.

Asked: June 21, 2003 - 9:33 pm UTC

Last updated: May 26, 2010 - 7:57 am UTC

Version: 9i

Viewed 50K+ times! This question is

You Asked

Hi Tom

I would like to know how to get a remote client IP adress and local client IP adress.
Is it right to say that the local client IP adress equal to IP adress of a Server ( client and server on the same machine)?
I suppose we use sys_contex to get it
and could I kill a session of SYS user?
Have a good day and best regards


and Tom said...

ops$tkyte@ORA920> select utl_inaddr.get_host_address(host_name), host_name from v$instance;

UTL_INADDR.GET_HOST_ADDRESS(HOST_NAME)
-----------------------------------------------------------------------------------------------------------------------------------
HOST_NAME
----------------------------------------------------------------
192.168.0.3
tkyte-pc-isdn.us.oracle.com


gets you the server ip address. sys_context( 'userenv', 'ip_address' ) gets you the client address - returns NULL if the user is not using TCP/IP.

However, a login trigger is NOT going to disallow SYS from logging in. (login triggers cannot "fail" a DBA login). You could send yourself an EMAIL, you could schedule a DBMS_JOB to kill the session shortly (but hey, sys could quickly set job_queue_processes=0 to subvert you)

Best bet -- never but never use SYS -- never. By default people cannot log in as SYS.

Let your best and brightest DBA's have SYSDBA or SYSOPER and don't allow for remote sysdba/sysoper login and you'll never have a remote SYS login.



Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

For Oracle 7.3

A reader, June 22, 2003 - 9:20 am UTC

If i want to run this commands in oracle 7.3?

Tom Kyte
June 22, 2003 - 1:24 pm UTC

not going to happen, neither of them.

you can get the client ip address if you audit connect, it is in the comments column of the audit trail, you have to parse it out.

Remote login

A reader, June 23, 2003 - 12:07 am UTC

Hi TOM
Thank you very much for your answer it help to resolve an important problem
But you say "Let your best and brightest DBA's have SYSDBA or SYSOPER and don't allow for
remote sysdba/sysoper login and you'll never have a remote SYS login"
How can I do IT ? It was 3 month, I try to looking for a solution for this question
Thank you very much
Have a good day and best Regards
NGUYEN ORACLE DBA



Tom Kyte
June 23, 2003 - 8:07 am UTC

grant sysdba/sysoper to the dba's that should have it.

they can now 

SQL> connect their_user/their_password AS SYSDBA;

they'll be in as sys but using their credentials.

Now, if you don't create a password file with orapwd and don't set up the remote login password file, they won't be able to connect over the net.  they'll have to be ON the machine itself.  consider:


idle> connect ops$tkyte/xxxx@ora920.us.oracle.com AS SYSDBA;
Connected.

idle> show user
USER is "SYS"

<b>i'm able to remotely log in as SYSDBA (sys) using my credentials</b>

idle> show parameter remote_log

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE


idle> alter system set remote_login_passwordfile=NONE scope=spfile;

System altered.

idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

idle> connect / as sysdba
Connected to an idle instance.

<b>I can still connect as sysdba locally (ops$tkyte is my os authenticated account)</b>


idle> startup
ORACLE instance started.

Total System Global Area  370217796 bytes
Fixed Size                   451396 bytes
Variable Size             100663296 bytes
Database Buffers          268435456 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

idle> connect ops$tkyte/xxx@ora920.us.oracle.com AS SYSDBA;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

<b>but I cannot connect remotely as sysdba anymore</b>

idle> connect ops$tkyte/xxx@ora920.us.oracle.com
Connected.
idle> show user
USER is "OPS$TKYTE"
idle>


<b>I have to use a local connection to get "SYSDBA"</b>
 

Oracle 7.3

A reader, June 23, 2003 - 4:39 am UTC

Can you explain in full steps ,how can i get IP address with audit trail option cuz i don;t know what is audit trail(i am new programmer)?

Tom Kyte
June 23, 2003 - 8:17 am UTC

simple search for


7.3 ip address


on this site......

REMOTE LOGIN PASSWORD

A reader, June 23, 2003 - 3:54 pm UTC

Hi Tom
Thank you very much for your quickly answers.
Following your method 'I need to use OS authentification'
( Remote_login_password=None). But my problem IS :
If an user is SYS then I disallow a remote login and
for a remote client the login should be authentificated by ORACLE normally not by operating system ( Remote_login_password=exclusive).
Could you please help me to find out a solution for this headache problem
Best regards
NGUYEN ORACLE DBA (PARIS FRANCE)

Tom Kyte
June 24, 2003 - 7:22 am UTC

that is fine, what I've shown you supports that (and I demonstrated that)

I can connect locally:


user/pass as sysdba

I can connect remotely

user/pass@db

I CANNOT remote connect as sysdba

user/pass@db as sysdba <<<=== fails.


You got what you want.

client ip address in psp

A reader, March 20, 2007 - 3:09 pm UTC

sir, how would i get a client ip address who is connect through pl/sql server pages for auditing purpose?
Tom Kyte
March 20, 2007 - 4:23 pm UTC

the client would be the application server, and sys_context can be used to get that if they (the middle tier) connected over sqlnet.

the browser - you might be able to use owa_util.get_cgi_env('REMOTE_ADDR') - but firewalls and such can obscure the true IP address (eg: my ip address is really 192.168.1.12 but I'm behind a nat enabled router with a firewall so you would never see that)

Getting error while getting IP in domain

Avi, January 06, 2010 - 11:51 pm UTC

Dear Tom,

When I try to get IP address of a particular user then I'm getting below error. Any solution for getting IP address in domain.

11:11:03  > select sid,machine,username, logon_time from v$session where username ='A';

  SID MACHINE                                                          USERNAME   LOGON_TIME
----- ---------------------------------------------------------------- ---------- --------------------------
 1092 LGE\IL-MF10-NE0141                                               A          07-JAN-2010 11:11:18

11:11:31  > select sid from v$session where machine='LGE\IL-MF10-NE0141';

  SID
-----
 1092

11:16:22  > select SID,USERNAME,MACHINE,utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) IP from v$session where sid in (1092);
select SID,USERNAME,MACHINE,utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) IP from v$session where sid in (1092)
                            *
ERROR at line 1:
ORA-29257: host IL-MF10-NE0141 unknown
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1


11:18:56  >

Tom Kyte
January 11, 2010 - 8:11 am UTC

$ oerr ora 29257
29257, 00000, "host %s unknown"
// *Cause:  The specified host was unknown.
// *Action: Check the spelling of the host name or the IP address.  Make sure
//          that the host name or the IP address is valid.



if you logged onto the server and did:

$ ping IL-MF10-NE0141

you'd likely see the same thing.

the machine name is not able to be resolved into an IP address, that is all, nothing wrong in the database.

help me plz

micheal, May 20, 2010 - 2:16 am UTC

hi how are you my Question someone do bad something in my database in domain so How can I know the ip address of the person Booze
Tom Kyte
May 24, 2010 - 11:29 am UTC

plz? German postal codes - can you explain what they have to do with this?


booze?


sorry, but unless you are a bit more 'precise' about what you mean by "bad something" - we cannot help

ok ok

michael, May 26, 2010 - 4:44 am UTC

am new dba and i have database so i gev users permission for work and in last week i catch destruction in my database (user)
when i ask him about what happend he say am not do that som one know my user and password , so how can i know who know his user name and password ip address <i work in domain>
Tom Kyte
May 26, 2010 - 7:57 am UTC

if you have all of the archive redo logs, you can use logminer to mine them to see what users did (very very tedious).

You don't say still what they actually did, so I cannot possibly give you any shortcuts (not that a short cut must exist, but if one does - I cannot tell you what it is because you haven't told us anything useful yet...)

But - and this is important - I fail to see how the user logging in as themselves could do anything disastrous. They can only do what you PERMIT them to do via grant - have you granted users too many privileges - if so, it is not their fault, it is the fault entirely of the person that granted the privileges.

You might want to employ auditing for the future.


(Jordan is very far away from the USA isn't it?)

thx dear tom

michael waked, May 30, 2010 - 3:15 am UTC

i get it and am sorry about location am from jordan nice to meet u too agean good job brother

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library