Oracle IP Address 1

[matssundin]

I have a vb front-end access an Oracle database through Oracle ODBC connection, I like to validate user base on the user's IP address, not through their computer terminal name, which accessable through the userenv('TERMINAL') function.&nbsp;&nbsp;I just like to know if there is any Oracle function to findout the hostname/ip-address of the current user session, similar to that of the cgi environment variable in a web http request environment, but only this time it is in a windows application environment.<br><br>An answer would be greatly appreciated<br><br>Reach me at <A HREF="mailto:casey.cheung@hrdc-drhc.gc.ca">casey.cheung@hrdc-drhc.gc.ca</A>

 

[MikeLacey]

matssundin,

Have a look at sys.v_$session.machine

 

[matssundin]

Hi Mike, I thank you so much for replying me, my question this time is what is v_$session, i have seen it on the web several time, but don't know what it is.
And how do I go about using sys.v_$session.machine

Again, thankyou very much

 

[gold2000]

v_$session is a view on schema sys.
You can select some information from this dynamic view, for example machine.
You must have select rights on this view.
Example> select * from v$session
By Gold

 

[matssundin]

Thankyou for replying, I have tried the method to selecting from the v$session table and search for the machine field, but it seems that it is not returning the IP address that I initially needed just the computer name, can anyone verify that for me?

 

[MikeLacey]

Wouldn't the computer name be just as good for this job?

 

[matrixcd]

IF you are using DHCP for all of your client machine the IP will change periodically. I agree that machine name will stay consistant. But if you must have the IP address try using windows API calls.

 

[matssundin]

The machines that will be authenticated will only be the server machines, and they will not be using DHCP, so the IP address will not change, your suggestion of calling Windows API method is intriguing, can that be done through Oracle, or is that what you really meant?

 

[MikeLacey]

ok, how about this?

maintain a list of allowable IP addresses in your database

get your app to authenticate itself against this allowed list after retrieving its own IP address

 

[matssundin]

thanks Mike for you paying attention to my posting and replying numerous times, then thing right now is that my application will be a central place for other group's application to authenticate their users, I will have to provide oracle procedure to validate their requests in numerous possible environment, such as web or a client/server environment, and yet i can't really control how the user should code their program to encorporate my procedure, therefore, supposedly if I ask for their IP address as a parameter in my procedure, someone could potentially just hardcode a valid IP address and send that value to my procedure, which is a security problem that I must be aware, I hope you understood what I just wrote.

 

[MikeLacey]

Hi matssundin,

I see what you mean.

Ok then, sounds like it's more of a general security issue.

Could you talk me through why you've decided not to use the more usual way of doing things? (I mean Oracle user-names and roles assigned to user-names)

 

[matssundin]

To say it in the simplistic way is that my application will have a table of applications (whether it be a oracle web app, an asp app, or vb client/server app) that will use my current application to authenticate their own application's user, the reason is that I work for the government, so the user list from one application to another is practically the same, so my application will be just a gateway for all user authentification.
So for some application like a vb application, it wouldn't need any user interface from me, except a procedure for them to execute to authenticate the user that is trying to use their application, so I must match the application's name with their server IP address to ensure that the valid application is trying to execute my procedure.
Do you think this explaination is okay?

 

[MikeLacey]

I've got to ask the question again I'm afraid -- Wouldn't the computer name be just as good for this job? I would have thouhght that the computer name *was* the server name in this case.

 

[matssundin]

To be honest, I think the computer name would be of sufficient, but this is something I must discuss with my team member and leader, hopefully this will be enough, it just somewhat surprises me that Oracle have no way of retrieving the IP information.
Again I thank you Mike for replying me numerous times about this question, you are of great help to me, if I run into more problem, I am sure will post it here and maybe you will help me again.

 

[MikeLacey]

a pleasure Mat -- and come back anyway, maybe you can answer a few!