2 Replies Latest reply: May 27, 2014 4:15 AM by Alan Lawlor RSS

    "Not Authorized" error when using native Webservices

    Alan Lawlor

      Hi Community

       

      I am using Oracle 11gr2 (11.2.0.3.0) 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=11.2.0.3.0; 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.

      --

      DECLARE 

           l_servlet_name VARCHAR2(32) := 'orawsv';

      BEGIN

         DBMS_XDB.deleteServletMapping(l_servlet_name);

         DBMS_XDB.deleteServlet(l_servlet_name);

         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:

      GRANT "XDB_WEBSERVICES_WITH_PUBLIC" TO "DUNT1";

      GRANT "XDB_WEBSERVICES_OVER_HTTP" TO "DUNT1" ;

      GRANT "XDB_WEBSERVICES" TO "DUNT1" ;

       

      -- 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:

      ALTER USER "ANONYMOUS" ACCOUNT UNLOCK;

      ALTER USER "XDB" ACCOUNT UNLOCK;

       

       

      -- 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">

        <servlet-name>orawsv</servlet-name>

        <servlet-language>C</servlet-language>

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

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

        <servlet-schema>XDB</servlet-schema>

        <security-role-ref>

          <description/>

          <role-name>XDB_WEBSERVICES</role-name>

          <role-link>XDB_WEBSERVICES</role-link>

        </security-role-ref>

        <security-role-ref>

          <description/>

          <role-name>XDB_WEBSERVICES_WITH_PUBLIC</role-name>

          <role-link>XDB_WEBSERVICES_WITH_PUBLIC</role-link>

        </security-role-ref>

        <security-role-ref>

          <description/>

          <role-name>XDB_WEBSERVICES_OVER_HTTP</role-name>

          <role-link>XDB_WEBSERVICES_OVER_HTTP</role-link>

        </security-role-ref>

      </servlet>

       

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

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

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

       

       

      ----


       

       

       

       

      I can access the root directory using : http://10.1.0.228:7776/

      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">

        <a:security-class>plsql:network</a:security-class>

        <a:ace>

            <a:grant>true</a:grant>

            <a:principal>DUNT1</a:principal>

            <a:privilege><plsql:connect/></a:privilege>

        </a:ace>

      </a:acl>

       

      My problem is that accessing http://10.1.0.228:7776/orawsv?wsdl via a browser, and entering the DUNT1 user and its password, keeps challenging again and again (A username and password are being requested by http://10.1.0.228:7776. 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@10.1.0.228:7776/orawsv?wsdl' ).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

          Hi

           

          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 = 10.1.0.228

          Bind #2 = connect

          Bind #3 = 7776

           

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

           

          HostUpper PortLower PortStatus
          10.1.0.228GRANTED
          • 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 :

            https://community.oracle.com/message/2825591#2825591

             

            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.

             

            ALTER USER "DUNT1" DEFAULT ROLE "XDB_WEBSERVICES_WITH_PUBLIC","XDB_WEBSERVICES_OVER_HTTP","XDB_WEBSERVICES";