Jump to content
btidba

After applying patch 27381640 cannot access to a Web Service (via HTTP routines)

Recommended Posts

Hi,

I have a schema named PROD, I defined the ACL parameters in order to access an HTTP ressource using a web service ,we use a function PROD.getData which calls the Web Services, here is the definition of the ACL:

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_http.xml',
    description => 'HTTP Access',
    principal   => 'PROD',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => null,
    end_date    => null
  );

  dbms_network_acl_admin.add_privilege (
    acl        => 'utl_http.xml',
    principal  => 'PROD',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );

  dbms_network_acl_admin.assign_acl (
    acl        => 'utl_http.xml',
    host       => '10.163.112.225',
    lower_port => 8082,
    upper_port => 8082
  );
  commit;
end;

 

After installing the latest Oracle patch of April 27381640  I can no more call HTTP web service (it worked correctly before applying the patch).
In an Sql PROD session  when utl_http.begin_request is invoked from a  block of Pl/Sql outside a function (getData) it's running without error, when invoked within a function from a Pl/Sql code it gave the ACL error:

 

ORA-24247: network access denied by access control list (ACL)

 

When I add the AUTHID CURRENT_USER to the function, it works perfectly when invoking the function from a Pl/Sql code.
We call the same function getData from a package PKG which is invoked by a Pl/Sql block, I have added the AUTHID CURRENT_USER to the PKG specification and it worked perfectly also.

For your information , the function and the package and all the invloved objects used by them are owned by a user PROD.

 

=> Now it is a bit more complicated we are calling the package PKG (which call the getData function) using a Web service , the service is called by a specific user U_SERVICE which has
some specific roles to access the PROD objects (including the grant execute on the package PKG and the function getData), but now the web service log shows the below error,it cannot run the package:

 

WARN : Execute Procedure exception: ORA-00942: table or view does not exist
ORA-06512: at "PROD.PKG", line 676
ORA-06512: at "PROD.PKG", line 1911
ORA-00942: table or view does not exist

An after LOGON  trigger exists:

create or replace TRIGGER U_SERVICE_trg
AFTER logon
ON U_SERVICE.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'alter session set current_schema = PROD';
END U_SERVICE_trg;

 

After running the Web Service ,some traces added to show the session user information:

 

SESSION_USER =U_SERVICE
CURRENT_SCHEMA=PROD

 

Can you please advise?

 

Thanks and Regards

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×