Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Thoi.

Asked: August 08, 2000 - 2:11 am UTC

Answered by: Tom Kyte - Last updated: October 28, 2020 - 2:57 am UTC

Category: Database - Version: version 8.1.1

Viewed 1000+ times

You Asked

I have got an Oracle version 8.1.1
I'd like to know how you get the IP address from users when they have already logged on the Database.
Would you, please, send me infomations about versions older than 8.i?

Thanhs beforehand.

and we said...


In Oracle8i, release 8.1, the SYS_CONTEXT builtin may be used to get an IP address:

$ sqlplus scott/tiger@ora8idev

SQL*Plus: Release 8.1.5.0.0 - Production on Wed Aug 9 08:17:55 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

scott@DEV8I.WORLD> select sys_context('userenv','ip_address') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
------------------------------------------
138.2.5.52


Prior to that, one method would be to enable auditing and retrieve the information from sys.aud$...




and you rated our response

  (31 ratings)

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

Reviews

forms 6i

October 12, 2001 - 9:48 am UTC

Reviewer: bipin from india

Sir u can not use select statement sys_context statement in forms 6i it gives error while compling itself i want to use it in forms & run it on web browser

Tom Kyte

Followup  

October 12, 2001 - 11:30 am UTC

Well, if you run the forms from the web browser -- you will have to understand that the IP address will be the IP address of the forms SERVER -- not the client. If that is what you want there are many ways to solve your issue:

1) create a view, hide the sys_context function from Forms in the first place

2) use dynamic sql, hide the sys_context function from forms. You can do this with exec_sql

3) use a stored procedure

4) use a record group:

DECLARE
rg_id RecordGroup;
errcode NUMBER;
BEGIN
rg_id := Create_Group_From_Query('ip_address',
'select sys_context(''userenv'',''ip_address'') ip from dual' );

errcode := Populate_Group( rg_id );

message( 'your ip address is ' || get_group_char_cell( 'ip_address.ip', 1 ) );
END;




ip address

October 12, 2001 - 12:23 pm UTC

Reviewer: Abdal-Rahman Agha from Yemen

Hi Tom,

very useful information which i need to display in my application.

Tom, i am still waiting for you to ask my question, but there is no chance. Look Tom, my question is very very important. Please, tell me when can I do that?

Tom Kyte

Followup  

October 12, 2001 - 1:10 pm UTC

Sorry, I cannot give specifics on that cause it is purely "catch as catch can".

I am one guy. I travel and meet with customers. I have people who work for me and I work for. I try to answer as many as I can. In fact -- right as I'm writing this now, I'm accepting 10 more questions.

When I get 10, i stop taking them until I answer them. This allows me to answer in a timely fashion. If I know I'll be busy (for example, yesterday I was giving a talk in Boston, I didn't accept any questions till I got off the plane and got home) I turn it off.

ip address

October 12, 2001 - 5:04 pm UTC

Reviewer: Abdal-Rahman Agha from Yemen

Hi Tom,

very useful information which i need to display in my application.

Tom, i am still waiting for you to ask my question, but there is no chance. Look Tom, my question is very very important. Please, tell me when can I do that?

IP from App. Server

July 02, 2002 - 7:01 am UTC

Reviewer: hrishab from India

Tom,
The solution was very useful, but I have one question. If my Client Appication connects to the database through Application server, then using userenv will give the IP Address of the Application server. My question is how can I get the IP Address of client connected.

Thanks and Regards,

Tom Kyte

Followup  

July 02, 2002 - 8:30 am UTC

You'll have to ask the client to send it over to you. The database has no clue that there is a third party involved in this transaction -- all it sees is the app server. The app server can see there is a client and the app server will have to send the IP address over to the database (perhaps as an input to a stored procedure or whatnot)

Firewalls, NAT, and many other things will also prevent you from "seeing" the real client ip address.



Where can we get a complete list?

July 02, 2002 - 12:41 pm UTC

Reviewer: Robert from Memphis

Hi Tom,

Does Oracle documentation have a complete list of all the information one can retrieve using sys_context (e.g. 'userenv', 'ip_address')?

Thanks,

Robert.

Tom Kyte

Followup  

July 02, 2002 - 2:44 pm UTC

yes it does. Surprisingly we keep it with all of the other SQL functions in the SQL reference manual:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/functions122a.htm#1038178 <code>


;)



OWA_UTIL packages

July 02, 2002 - 12:47 pm UTC

Reviewer: andrew from ca, usa

If the OWA* packages are nstalled (part of the PL/SQL gateway) you'd use owa_util.get_cgi_env( 'REMOTE_ADDR' )

To avoid "ORA-06502: PL/SQL: numeric or value error" when you call this code from a non-Web connection - search for owa.init_cgi_env on this site - or try:

IF OWA.num_cgi_vars IS NOT NULL
THEN
-- PL/SQL gateway connection (WEB client)
v_ip := OWA_UTIL.get_cgi_env ('REMOTE_ADDR');
ELSE
-- Direct connection over tcp/ip network
v_ip := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
END IF;


Tom Kyte

Followup  

July 02, 2002 - 4:42 pm UTC

if the owa packages are installed and you are using mod_plsql and not using NAT and firewalls -- correct, you can use this.

The problem with this question is -- that answer totally hinges on what is in between the end user and the database -- what technologies they are using, what languages they are using and so on.

Perhaps Technet's search facilities could be better?

July 02, 2002 - 3:21 pm UTC

Reviewer: Doug from Danbury, CT USA

Tom - if I go to technet.oracle.com and search for sys_context.. I get many hits on an article called Establishing Security Policies. With some digging and following through I can get to the link you posted in the SQL Reference manual. I recently had a similar problem with Directory object. It seems to me that technet does not look for articles with TITLES of the term I may be searching for documentation on but some other criteria. Do you agree?

Tom Kyte

Followup  

July 02, 2002 - 7:32 pm UTC

You could just search the documentation instead of the website. Searching the website you'll find tons of stuff, but you are looking for doc refs.

try

</code> http://tahiti.oracle.com/ <code>

goto the 8i doc set, search for it -- you'll find it in 2 major docs, concepts guide and sql ref. click on the sql ref and it's right there.

a way to do this

July 03, 2002 - 7:00 am UTC

Reviewer: Ryan Gaffuri from Northern Virginia

There is a java bean in the forms demo called GETCLIENTINFO

I think you can use this to get the users IP barring firewall issues, etc...

July 12, 2002 - 9:35 am UTC

Reviewer: M.P.KIRAN KUMAR from India

hi tom
for oracle version
PL/SQL Release 8.0.4.0.0 - Production
SYS_CONTEXT IS NOT WORKING


Tom Kyte

Followup  

July 12, 2002 - 10:00 am UTC

As I said:


In Oracle8i, release 8.1,
the SYS_CONTEXT builtin may be used to get an IP
address:

$ sqlplus scott/tiger@ora8idev

....

it was added in that release.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:526822275627 <code>

for an 8.0 approach (well 7.3 and up)

no result

October 10, 2002 - 7:51 am UTC

Reviewer: Fenng from China

Hi ,Tom ,why I get no result ?
I do the following operation:

SQL> connect scott/tiger
ÒÑÁ¬½Ó¡£
SQL> select sys_context('userenv','ip_address') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
-----------------------------------------------------


SQL>  

Tom Kyte

Followup  

October 11, 2002 - 7:05 pm UTC

you did not connect over the network -- that is why. This only works when you actually "network" into the database.

Else you need not have an IP address or anything, we are using a protocol (bequeath) that is not TCP/IP based.

YES

October 12, 2002 - 3:17 am UTC

Reviewer: Fenng from China

Yes ,Tom, in the world of Oracle ,you are the one :-)

Where to locate the IP address ?

May 19, 2003 - 1:15 pm UTC

Reviewer: John Murphy from USA

The question sound more of unix to me than the oracle but it is not independent of oracle. We have users who connect to the unix server using some X-Windows software(Proterm,Reflection etc). Through that they are able to connect to oracle using sqlplus.

One of the user is creating some nuisance by deleteing some data from a major table. We cannot do auditing of the database for DML because the database come to its knees for some other reason. Hence we wrote a trigger to track but found that he uses a username which is known to everybody in the project (flaw in design done in 1995) and also the machine name is shown as the server name.

Now my question: I wanted to know the ip address of his client machine through which he has connected to the unix and then to oracle. Is it possible to track him down and how ?

Answer Not Received

May 27, 2003 - 10:47 am UTC

Reviewer: A reader

Tom,

The above mentioned question is raising eye balls over our team and none of us have any solution. You are our last hope. Please find sometime to answere the above mentioned question.

Warm Regards,
John.

Tom Kyte

Followup  

May 27, 2003 - 11:10 am UTC

basically you are saying "the user is logged directly onto the server"

the rest of the stuff -- x windows, reflection X and such is not really relevant. The only thing that counts here is:

the user is logged directly onto the server.
since they are, they can log into the database using os authentication.


we cannot tell you ANYTHING more then that. You should prevent them from logging into the OS -- change that password. You should lock down the server.

Ip Address of clients connected to database

February 08, 2005 - 2:55 pm UTC

Reviewer: NN from CT

By using
select sys_context('userenv','ip_address') from dual
we can get the ip_address of the connected user.
But how to get the ip address of all the clients connected to database. Is there any view or table from where I can get this information.

Tom Kyte

Followup  

February 09, 2005 - 1:42 am UTC

if you have auditing enabled, it'll be in the audit trail.

restricting access based on ip address

August 01, 2006 - 5:52 am UTC

Reviewer: azx

Ok, now we know that we can get the ip address of the client from which the user logged in.

My question is I want to restrict database logons to
only specific IP addresses, for e.g. users can login
only from 10.1.1.23 client or they can login from
the local server using bequeth, but no one else is allowed.

Or, if some one tries to login from a disallowed IP, he
should be immediatly KILLED.


Tom Kyte

Followup  

August 01, 2006 - 7:33 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref571 <code>

thanks, but a little confusion

August 01, 2006 - 8:49 am UTC

Reviewer: abz

We know that if nothing is specified in the sqlnet.ora
file then it will allow all IP addresses. But if
TCP.VALIDNODE_CHECKING = YES
AND
TCP.EXCLUDED_NODES= (10.1.25.23)

will it allow users other than 10.1.25.23, note
that there is nothing specified about INVITED.

Also,
IF
TCP.VALIDNODE_CHECKING = YES
AND
TCP.INVITED_NODES= (10.2.2.2)

will it disallow users other than 10.2.2.2, note
that there is nothing specified about EXCLUDED.




Tom Kyte

Followup  

August 01, 2006 - 10:25 am UTC

can you clarify the question - you seem to have stated what it does accurately.

You either exclude
Or you invite

You do not do both (doesn't really make sense)

If you invite X, Y and Z - you don't need to exclude A

if you excluded A, B, and C, you don't need to invite X

sorry

August 03, 2006 - 6:45 am UTC

Reviewer: abz

Sorry, you are right.

But I got confused because in net manager,
I provided an exclude list and an invite list and then
saved, it saved it without giving any error, if this
thing doesnt make sense shouldnt it give error that
only one list can be provided.


listner

August 04, 2006 - 9:04 am UTC

Reviewer: abz

shouldnt it give error?

Also , is it necessary to restart the listner for
changes in sqlnet.ora file to take effect.



Tom Kyte

Followup  

August 04, 2006 - 11:50 am UTC

it is "harmless" for it to not really care that you specified both. You'll want to restart the listener - yes.

abz follow up

August 05, 2006 - 4:11 am UTC

Reviewer: A reader

Hi Tom
we use the mechanism as you discussed with abz to prevent direct access from user PCs to the database in our prod environment (to ensure seucurity, minimize performance impact, prevent end users modifying the data using other tools then a client).
However there is a need to allow to specific users (support) direct access. We were thinking of using lets say 2 listeners (to prevent guessing the listener port), one for end users (accessing the system through application server ... so only AS ip in sqlnet.ora) and the second listener for those users.
1. What's your view on this?

In case of second type of users, having direct access we were thinking of giving them specific access acount with limited privileges (security) and specific user profile (performance).
2 Is there any other way to achieve this?
We are on Oracle 9iR2 EE.

Tom Kyte

Followup  

August 05, 2006 - 10:52 am UTC

I don't understand.

If you are using "invited" nodes (most likely -right), just "invite" the support users?

August 05, 2006 - 2:02 pm UTC

Reviewer: A reader

right - invited nodes on 2nd listener to invite support users; that is the objective to allow support users direct access but to ensure they don't degrade performance or don't do something with the data they shouldn't

so, the question is whether this 2 listener concept as described ok - or there is better way to achieve this
- prevent end users getting in the database using tools other then the client, and ensuring it's possible to get in the db using dedicated listener

another thing - is there any other way to ensure support users get in the database using user with limited privileges and specific profile, and not any other user



Tom Kyte

Followup  

August 05, 2006 - 5:08 pm UTC

just invite them on the first listener.

I don't get the point of the second listener at all. what is it providing that the single listener would not.


You could use a logon trigger that peeks at the IP address and does whatever sort of checks you want.

August 05, 2006 - 6:12 pm UTC

Reviewer: A reader

thanks for feedback
2nd listener in case there are several database running on the same box and ensuring they get to the right one only - should have mentioned this earlier

Tom Kyte

Followup  

August 06, 2006 - 8:57 am UTC

indeed - because they I would say "on a production machine, the most number of instances one should have is one" - see, I would not have anticipated this at all.

I would not do it "just in case", do it because you have a real identified need to do it first and foremost.

August 06, 2006 - 9:28 am UTC

Reviewer: A reader

thanks tom!
is there anything else out there apart from logon trigger that could do the "whatever needs to be done" work

I could see your comment about renaming program name to "something_you_are_not_looking_for.exe" - </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:15155052972187 <code>
... so logon trigger would have to be quite intelligent to fit the purpose

Tom Kyte

Followup  

August 06, 2006 - 2:08 pm UTC

why - you only said:

...
another thing - is there any other way to ensure support users get in the
database using user with limited privileges and specific profile, and not any
other user
........


you know their IP addresses, seems like all you would need is a sys_context call to get the current user IP address and if it is a "support user" make sure they are connected as the proper set of user accounts (which has a profile associated with it)

seems very straightforward, you don't care about the program - you just want it when the support machine connects directly - they only have "X" privileges.

Really great... !!!

October 10, 2006 - 9:34 pm UTC

Reviewer: Star Nirav from Wish, I could stay with TK, I could learn so much...

You are magician... truly, genious.

hats off sir... Words are less to desc u...

Regards,
Star Nirav
+91 9227258878

Can we know the IP address ?

November 03, 2006 - 3:48 pm UTC

Reviewer: Star nirav

Hi tom,

From v$session, we can find the OSuser, terminal name, program, username but can we find IP address ?

if yes then please let us know.

Regards,
Star Nirav

Tom Kyte

Followup  

November 04, 2006 - 12:17 pm UTC

if you enable auditing of connections, it would be captured in the audit trail

you could also use a logon trigger to place this information into v$session using dbms_appliation_info

Pls. ignore the previous thread abt finding IP address

November 03, 2006 - 3:56 pm UTC

Reviewer: Star nirav

hi tom,

Pls. ignore the previous thread. Actually wanted to ask you that what is this sys_context (view / table ) and what else information it has...

It is by default created in 9i ? What previlige user required to run this ? if all user can access and find the ip address, then what about the security... ?

Regards,
Star Nirav

Tom Kyte

Followup  

November 04, 2006 - 12:18 pm UTC

sys_context is a function - it is actually documented (imagine!). suggest you check out the documentation:

</code> http://www.oracle.com/pls/db92/db92.docindex?remark=homepage <code>

you will find it of great use.

sys_context function

December 20, 2006 - 8:04 am UTC

Reviewer: A reader


Get IP address in job

April 20, 2007 - 4:41 am UTC

Reviewer: Shahzad Khan from Pakistan

HI TOM,
In our scenario an oracle job is executing at the client side and this job is inserting some rows in our server through db link.
we want to catch the host name and IP address of that client machine. the problem is that the sys_context function return null if i use that in our job. if we execute the job manually then it return the required values otherwise if we schedule it, then it didn't return anything.
what do you suggest in this context?
Tom Kyte

Followup  

April 20, 2007 - 7:10 am UTC

what is "your job" - I'm missing the flow here entirely.

IP address

May 02, 2007 - 2:30 am UTC

Reviewer: shahzad khan

I am refering to the database job. and by manually executing i mean running the command exec dbms_job.run(id).

Tom Kyte

Followup  

May 02, 2007 - 8:18 am UTC

there is no ip address for a job running locally - I mean, it is local, the job does not use a network to be connected - so I'm not sure what you are trying to capture?

Oracle Job Logon Event?!?!?

April 21, 2009 - 3:25 pm UTC

Reviewer: Victor Leal from The Woodlands, TX USA

I believe what Shahzad Khan is asking is a similar question that I have.

Why would the "AFTER LOGON ON DATABASE" trigger event capture jobs that run, as LOGON events?

For example, I have the following.

CREATE TABLE LOGON_AUDIT
(
  LOGIN_DATE  TIMESTAMP(6) WITH TIME ZONE,
  USERNAME    VARCHAR2(30 CHAR),
  HOST        VARCHAR2(30 CHAR),
  OS_USER     VARCHAR2(30 CHAR),
  TERMINAL    VARCHAR2(30 CHAR),
  IP_ADDRESS  VARCHAR2(30 CHAR)
);

CREATE OR REPLACE TRIGGER logon_success
AFTER LOGON ON DATABASE
BEGIN
 INSERT INTO logon_audit
 (
  login_date,
  username,
  host,
  os_user,
  terminal,
  ip_address
 )
 VALUES
 (
  SYSTIMESTAMP,
  USER,
  sys_context('USERENV','HOST'),
  sys_context('USERENV','OS_USER'),
  SYS_CONTEXT('USERENV','TERMINAL'),
  SYS_CONTEXT('USERENV','IP_ADDRESS')
);
    EXCEPTION
    WHEN OTHERS THEN
        null;
END logon_audit;
/

When a job runs it makes an entry in the table noted above. Why would my jobs that run every 5 minutes make the DB think that this is a logon event? I have an entry everytime the job runs.
Tom Kyte

Followup  

April 21, 2009 - 4:04 pm UTC

how could a question about an ip address be related to your question? I don't see any similarity at all here.


..
EXCEPTION
WHEN OTHERS THEN
null;

...

I hate your code
http://asktom.oracle.com/pls/ask/search?p_string=%22i+hate+your+code%22


ask yourself this - whom do the jobs run as? When the job isn't running is there a session associated with it? When the job is running is there a session associated with it? Does the job run as the person that submitted it? Do they in fact not therefore 'login' and create a session for the job?


Add ip to tcp.invited_nodes

August 19, 2009 - 4:17 am UTC

Reviewer: Ninh from Vietnam

Hi Tom,
I want to add ip address to tcp.invited_nodes param in sqlnet.ora, I want to ask you how many IP address could I add to this parameter?
And does it affective to performance?
Thanks









Tom Kyte

Followup  

August 24, 2009 - 4:58 pm UTC

there are no documented limits and I'm not aware of any undocumented ones (does not mean they do not exist...)

It would only affect the initial connect, if you could even measure it

MAC Address

October 27, 2020 - 11:46 am UTC

Reviewer: A reader

How to find mac address of client pc using plsql.
Connor McDonald

Followup  

October 28, 2020 - 2:57 am UTC

I doubt there is any mechanism to do that.

MAC addresses are typically even hidden from things like the client browser so asking a remote database server to do it is unlikely.