Home / Questions / 291895
Accepted
Cristian Budzicz
Asked: 2020-09-11 10:46:42 +0800 CST 2020-09-11 10:46:42 +0800 CST

oracle - consume webservice soap

  • 772

Hello, when running an example of the following page https://oracle-base.com/articles/9i/consuming-web-services-9i

The oracle developer generates an error with the acl that says the following:

ORA-29273: fallo de la solicitud HTTP
ORA-06512: en "SYS.UTL_HTTP", línea 1130
ORA-24247: acceso de red denegado por la lista de control de acceso (ACL)
ORA-06512: en "xxxxxx.SOAP_API", línea 150
ORA-06512: en "xxxxx.ADD_NUMBERS", línea 34
         29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
       Fix the error and retry the HTTP request.

How can I solve it? What dou you recommend?

oracle

1 Answers

  1. Best Answer

    First things first: I don't understand why you're following an Oracle 9i example if you're using Oracle 11g.

    The error is self explanatory. You have to build an access control list and assign that permission to the user you want to allow access to external resources over HTTP.

    In Oracle 11g, using any of these packages requires that configuration:

    UTL_TCP
UTL_SMTP
UTL_MAIL
UTL_HTTP
UTL_INADDR

    To validate that such configuration has not been done, one executes this query:

     SELECT * FROM dba_network_acls;

    Creating ACLs is a two-step procedure. The first step is to create the ACL and define the privileges to give it. The standard syntax is:

    BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => "file_name.xml",
description => "file description",
principal => "user_or_role",
is_grant => TRUE|FALSE,
privilege => "connect|resolve",
start_date => null|timestamp_with_time_zone,
end_date => null|timestamp_with_time_zone);
END;

    The value for the includes connectparameter , which is required for the UTL_INTADDR package.privilegeresolve!

    • The parameter principalspecifies the first username assigned to the ACL and is case sensitive!
    • If you want to assign it to multiple users, you must use the DBMS_NETWORK_ACL.ADD_PRIVILEGE privilege to add users.

    An example of creating ACL with data is:

    BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl => 'sysdba-ch-permissions.xml',
  description => "Permissions for sysdba network',
  principal => "LUTZ',
  is_grant => TRUE,
  privilege => 'connect');
END;

    This creates an xml file with a list of users and privileges. It is located low /sys/acl/in the XML DB. How did I know you use Oracle 11g? Because this error does not happen in Oracle 9i, there is no XML DB in 9i and there is no fine-grained access control (control of access to external resources using HTTP).

    The second step is to assign network hosts to the ACL. The standard syntax is:

    BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => "file_name.xml",
host => "network_host",
lower_port => null|port_number,
upper_port => null|port_number);
END;

    And an example with data:

    BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl => 'sysdba-ch-permissions.xml',
  host => "*.sysdba.ch',
  lower_port => 80,
  upper_port => null);
END;

    Here you have to take into account that:

    • It is possible to use wildcards in the parameter hosts. Allows access to all hosts in a given domain.
    • Hostnames are case-sensitive
    • You can use an IP or a DNS hostname.
    • Only one ACL can be assigned to a host or domain or IP subnet or port range
    • You can assign multiple hosts to the same ACL by calling DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL multiple times.
    • 2