1 person found this helpful
Assuming you are talking about application server ( not web server ).
It is common practice to create new user on Oracle database (often called proxy user, so you don't have to use schema credentials). Grant this user privileges to call package you need. Then on application server store encrypted credentials for this new user.
Application (java or .net) then connects to Oracle using new user credentials and executes packaged procedure.
Using Apex is the same. You need to have Authentication enabled (hence to store user id and password). If not, it is not secure.
Making any application (including Apex) to respond to request from specific IP is possible by inspecting ip from request header (Apex : UTL_HTTP.GET_HEADER ).
There are problems however:
1.If connection is made by proxy, request ip can change.
2.Hackers can use "ip spoofing" to emulate request from permitted server.
Another option would be to set up proxy server rules, permitting traffic between selected servers only.
It is a web server that is attempting to call the stored procedure using Application Express as a gateway to the database. However, granting execute privileges on it for the APEX_PUBLIC_USER allows anybody on the network to call the procedure through APEX, when we only want it to be called by the web server.
However, I am on the trail of possibly using encrypted timestamps that the procedure can then use to determine whether or not it should return anything.
You can create apex application, make it as secure as you need (LDAP, single sign on, etc ...)
Add page to this application with process that calls your procedure (ON_LOAD before header or ON_DEMAND) ...
Now you can call your procedure through html.
What exactly are you trying to do? That is not so clear. A process on a web server can usually connect to the database via JDBC, ODBC, SQLNet. In a 3 tier solution that is the normal way. Okay, you do need to store the login credentials on the server, but is that a problem?
From what I have been told, since we may have so many simultaneous users connecting to the the database through the web server, it would be better to use the shared connection pool that APEX uses and have APEX manage the traffic.
Depending on the technology used, you can open JDBC or .Net connection pool as efficient as Apex.
InoL is right, keep it simple.
OP's name is Nick.
Regardless where and how the authentication is enforced, OP must login to the application and that means storing user id and password in one form or another on his web server.
It doesn't matter if his program connects directly to Oracle or he calls Apex. Authentication must be there.
(SSO could be an exception, but it is quite a work to implement)
Using the APEX_PUBLIC_USER bypasses the need to authenticate, unless authentication is the only way to secure whom the procedure responds to.
Sorry, of course Nick.
If OP wants security, he needs authentication of some form.
But if he uses APEX it will be easier to implement.
APEX_PUBLIC_USER bypasses the need to authenticate to Oracle Database, but you should enforce authentication on your Apex application.
Otherwise access to the procedure is public, and you are trying to secure it by filtering IP.
One way or another you have to do some programming. I'd say is easier and simpler to connect from web server to Oracle.
Just my 2c.
There is some internal concern that using ODBC would result in each user having their own database connection, whereas by using Apex all users would share the same connection. And with the possibility of thousands of simultaneous users, it might put too much of a load or result in dropped connections. Do you think that should not be so much of a concern?