If you have full control over the environment (it sounds like you do) and
security is the main focus, you can create something like this:
- In your main database, create a dedicated user that will own a private
database link
- In your new web-enabled database, create a dedicated user that will own a
stored procedure or package that you call via the database link
- The database link will exist between these two new users
- In your main database, you can then create a stored procedure or package
that calls the remote procedure
- Whereever you need that data, you then call the local procedure.
Pseudo code it would look like this:
create or replace procedure get_data
as
output varchar2(1000);
begin
execute immediate 'get_data@dblink(arg1, arg2,arg3,out)' using l_arg1,
l_arg2, l_arg3, output;
-- process data received from web call
end;
/
With proper exception handling in place, this should be fairly secure. Your
"local" procedure needs to handle the errors related to the database link,
whereas your remote procedure should handle the exceptions that can occur
during the call that goes out to the web.
Stefan
On Thu, May 3, 2018 at 4:57 AM, Jeff Chirco <backseatdba@xxxxxxxxx> wrote:
We have a requirement to make a web service call out to the internet. We
want to do this from within pl/sql. We've review the code to do that and
that is no a problem. The problem is our security admin doesn't want it to
our main database server to make the call and do the processing and is
asking that we have a separate database a different server to make the call
and do the processing of the return data. This seems overly complicated of
a process because we have to call a diff database, processes it there and
then it will need to send the results back. versus doing it all in one
database.
Anybody run into similar issue with your network team or how do you handle
external calls? We are an private company and extermley private with our
data
Jeff
