forms 6i
bipin, October 12, 2001 - 9:48 am UTC
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
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
Abdal-Rahman Agha, October 12, 2001 - 12:23 pm UTC
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?
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
Abdal-Rahman Agha, October 12, 2001 - 5:04 pm UTC
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
hrishab, July 02, 2002 - 7:01 am UTC
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,
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?
Robert, July 02, 2002 - 12:41 pm UTC
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.
OWA_UTIL packages
andrew, July 02, 2002 - 12:47 pm UTC
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;
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?
Doug, July 02, 2002 - 3:21 pm UTC
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?
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
Ryan Gaffuri, July 03, 2002 - 7:00 am UTC
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...
M.P.KIRAN KUMAR, July 12, 2002 - 9:35 am UTC
hi tom
for oracle version
PL/SQL Release 8.0.4.0.0 - Production
SYS_CONTEXT IS NOT WORKING
no result
Fenng, October 10, 2002 - 7:51 am UTC
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>
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
Fenng, October 12, 2002 - 3:17 am UTC
Yes ,Tom, in the world of Oracle ,you are the one :-)
Where to locate the IP address ?
John Murphy, May 19, 2003 - 1:15 pm UTC
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
A reader, May 27, 2003 - 10:47 am UTC
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.
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
NN, February 08, 2005 - 2:55 pm UTC
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.
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
azx, August 01, 2006 - 5:52 am UTC
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.
August 01, 2006 - 7:33 am UTC
thanks, but a little confusion
abz, August 01, 2006 - 8:49 am UTC
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.
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
abz, August 03, 2006 - 6:45 am UTC
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
abz, August 04, 2006 - 9:04 am UTC
shouldnt it give error?
Also , is it necessary to restart the listner for
changes in sqlnet.ora file to take effect.
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
A reader, August 05, 2006 - 4:11 am UTC
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.
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?
A reader, August 05, 2006 - 2:02 pm UTC
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
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.
A reader, August 05, 2006 - 6:12 pm UTC
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
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.
A reader, August 06, 2006 - 9:28 am UTC
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... !!!
Star Nirav, October 10, 2006 - 9:34 pm UTC
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 ?
Star nirav, November 03, 2006 - 3:48 pm UTC
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
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
Star nirav, November 03, 2006 - 3:56 pm UTC
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
sys_context function
A reader, December 20, 2006 - 8:04 am UTC
Get IP address in job
Shahzad Khan, April 20, 2007 - 4:41 am UTC
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?
April 20, 2007 - 7:10 am UTC
what is "your job" - I'm missing the flow here entirely.
IP address
shahzad khan, May 02, 2007 - 2:30 am UTC
I am refering to the database job. and by manually executing i mean running the command exec dbms_job.run(id).
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?!?!?
Victor Leal, April 21, 2009 - 3:25 pm UTC
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.
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
Ninh, August 19, 2009 - 4:17 am UTC
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
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
A reader, October 27, 2020 - 11:46 am UTC
How to find mac address of client pc using plsql.
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.
Get Logged User IP address
A reader, September 22, 2022 - 8:31 pm UTC
select owa_util.get_cgi_env('REMOTE_ADDR') AS IP_Adress from dual
You can get logged user IP Address
A reader, September 25, 2022 - 6:30 am UTC
You can get logged user IP Address using the next SQL
Select owa_util.get_cgi_env('REMOTE_ADDR') AS IP_Adress From Dual