2 Replies Latest reply on May 27, 2014 9:15 AM by Alan Lawlor

    "Not Authorized" error when using native Webservices

    Alan Lawlor

      Hi Community


      I am using Oracle 11gr2 ( and attempting my very first SOAP request using XDB


      I followed the instructions at [Using Native Oracle XML DB Web Services | http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_web_services.htm#ADXDB5677] and have browsed many threads on this subject and am still stuck.


      The steps I followed :

      Logged in as SYS:


      -- Configure HTTP Access

      EXEC dbms_xdb.setHttpPort(7776); -- I originally was using 8080, as per samples, but I changed to a port I was sure nothing else was using

      EXEC dbms_xdb.setFtpPort(2100);  -- Not sure its purpose, but did it anyway


      SELECT dbms_xdb.gethttpport() FROM dual;

      -- Result=7776


      -- Check XDB installed

      SELECT * FROM dba_registry WHERE comp_id='XDB';

      -- Result= Comp_ID=XDB; Version=; Status=VALID; Schema=XDB; Procedure=DBMS_REGXDB.VALIDATEXDB


      -- Configure the ORAWSV Servlet --

      -- Native web services are implemented using the "orawsv" servlet, which must be configured in the xdbconfig.xml file stored within XML DB itself.

      -- This can be done using the DBMS_XDB package, as shown below.



           l_servlet_name VARCHAR2(32) := 'orawsv';




         DBMS_XDB.addServlet(     name    => l_servlet_name,     language => 'C',     dispname => 'Oracle Query Web Service',     descript => 'Servlet for issuing queries as a Web Service',     schema  => 'XDB');

         DBMS_XDB.addServletSecRole(     servname => l_servlet_name,     rolename => 'XDB_WEBSERVICES',     rolelink => 'XDB_WEBSERVICES');

         DBMS_XDB.addServletSecRole(     servname => l_servlet_name,     rolename => 'XDB_WEBSERVICES_WITH_PUBLIC',     rolelink => 'XDB_WEBSERVICES_WITH_PUBLIC');

         DBMS_XDB.addServletSecRole(     servname => l_servlet_name,     rolename => 'XDB_WEBSERVICES_OVER_HTTP',     rolelink => 'XDB_WEBSERVICES_OVER_HTTP');

         DBMS_XDB.addServletMapping(     pattern => '/orawsv/*',     name    => l_servlet_name);

      END; /

      -- I added ServletSecRoles for .._WITH_PUBLIC, and .._OVER_HTTP after initial sample did not give me a result



      -- Grant access to the user schema where the Webservices procedures/functions will reside and the login to be used by the WS client:





      -- Set up Dispatchers : A few websites recommended this as a step (my Oracle_Sid = ORMS)

      ALTER system SET dispatchers='(PROTOCOL=TCP)(SERVICE=ORMSXDB)' comment='Dispatcher enabled for XMLDB Protocol Server' scope = BOTH;


      -- I do not think this is required, but I did it, just to rule out problems:





      -- I did this to ensure changes are registered with the database and listener, without having to bounce them




      -- I checked the configuration of orawsv using this:




      SELECT xmlserialize(content column_value)


      FROM XMLTable(


      xmlnamespaces(default 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'),


      'for $doc in fn:doc("/xdbconfig.xml")/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet[servlet-name="orawsv"]


      return $doc'



      -- Result Looks good:

      <servlet xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">



        <display-name>Oracle Query Web Service</display-name>

        <description>Servlet for issuing queries as a Web Service</description>



















      -- I created network ACLs for the user (DUNT1) using both localhost and the DB server IP address:

        dbms_network_acl_admin.create_acl('dvret1.xml', 'ACL for DVRET1', 'DUNT1', true, 'connect');
        dbms_network_acl_admin.assign_acl('dvret1.xml', '');

        dbms_network_acl_admin.create_acl('localhost.xml', 'ACL for localhost', 'DUNT1', true, 'connect');
        dbms_network_acl_admin.assign_acl('localhost.xml', '');








      I can access the root directory using :

      This challenges for username/password and is successful with DUNT1 user and its password

      It provides 2 folders listed : sys and public

      I can browse to to the sys/acls folder and I can view the dvret1.xml file:


      <a:acl description="ACL for DVRET1" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd                                    http://xmlns.oracle.com/xdb/acl.xsd" shared="true">









      My problem is that accessing via a browser, and entering the DUNT1 user and its password, keeps challenging again and again (A username and password are being requested by The site says: "XDB") , and when I cancel, I get 401 Unauthorized


      When I attempt to access via the database using :

      SQL> SELECT  httpuritype( 'http://DUNT1:xxxxx@' ).getXML() FROM dual

      I get:

      ORA-29273: HTTP request failed
      ORA-06512: at "SYS.UTL_HTTP", line 1819
      ORA-29268: HTTP client error 401 - Unauthorized
      ORA-06512: at "SYS.HTTPURITYPE", line 34
      ORA-06512: at "SYS.HTTPURITYPE", line 97
      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.




      Can somebody please help me as to what I could/should check next ?

        • 1. Re: "Not Authorized" error when using native Webservices
          Alan Lawlor



          I also turned on tracing, and found the following SQL statement in the trace file:


          select host, lower_port, upper_port, status

          from (select *

                  from (select p.*, dbms_network_acl_utility.contains_host(:1, host) precedence

                          from sys.user_network_acl_privileges p

                          order by precedence desc, lower_port asc nulls last, upper_port asc nulls last


              where privilege = :2 and precedence is not null and (lower_port <= :3 and :3 <= upper_port or lower_port is null and upper_port is null)


          where rownum <= 1


          Bind #1 =

          Bind #2 = connect

          Bind #3 = 7776


          The result of the above indicates ACL access has been granted:


          HostUpper PortLower PortStatus

          • 2. Re: "Not Authorized" error when using native Webservices
            Alan Lawlor

            I found the answer from a thread regarding anonymous access, even though my issue was nothing to do with it :



            In the above thread's test case, it refers to ensuring that the granted roles also should use the default option to avoid 401 - Unauthorised error message.