2 Replies Latest reply on Mar 2, 2013 9:33 AM by Billy~Verreynne

    PL/SQL proc as a web service

      Oracle 11.2.0.x
      Reference: Accessing PL/SQL Stored Procedures using a Web Service (<i>Oracle® XML DB Developer's Guide</i>).

      The feature works fine. Have tested a number of custom written PL/SQL procs this way - using it as a web service, and SoapUI and UTL_HTTP procedures as clients, calling the web service.

      Can one support WSS (Web Services Security) with this feature?

      I've added a WSSE to the SOAP envelope header when making the call - using standard password (no digest) and default addressing. The orawsv XDB servlet accepts the call, parses the SOAP envelope, and successfully executes the relevant PL/SQL procedure.

      It however uses Basic Authentication (schema name and password). Not WSSE.

      As the WSSE authentication data is part of the SOAP header (and not envelope body), the relevant PL/SQL procedure of course does/can not see the WSSE details. (also would not make sense ito how XDB abstracts orawsv as a web service interface and allows standard vanilla PL/SQL procedures and functions to serve as web service endpoints).

      So if WSSE is to be supported, it would likely mean it needs to be supported in XDB itself. And that is outside my little area of Oracle expertise.

      Not much on the net (lots about UTL_DBWS), and just a couple of basic orawsv supports notes on Metalink.

      Any ideas, suggestions or pointers will be much appreciated.

        • 1. Re: PL/SQL proc as a web service
          WSS will not be supported by Database Native Web Services. This is a deliberate decision to help differentiate when an application server should be used Vs when DBNWS is appropriate. If you are in an environment where WSS is a requirement you should be usiing an application server, such as Oracle WebLogic, to provide your Web Services.

          That said the next version of the database willl support DIGEST as well as BASIC authenitcation for HTTP. If the current model of BASIC authentication is an issue, then you should force the use of an SSL (HTTPS) connection. This is why we have the XDB_WEBSERVICES_OVER_HTTP role. If this role is not granted then the DBNWS can only be accessed using HTTPS.
          • 2. Re: PL/SQL proc as a web service
            Thanks for the feedback.

            Java app servers are not a consideration. Our app layer is PL/SQL and the database.

            But our app layer needs to talk to other app layers - not a problem using SOAP as we can roll that (including WSSE) ourselves, using PL/SQL.

            However, app layers want to talk with us and use WSSE.. and HTTPS. At the same time. Do not grasp exactly why WSSE in addition to HTTPS - perhaps arguing that one is a "technical" security layer (wire protocol) and the other is an "application" security layer (app protocol).

            What would be quite flexible on the XDB servlet side is a configurable call-out (event) that one can point to a PL/SQL proc to pre-process the SOAP header, prior to orawsv pulling the SOAP envelope out for calling the actual web service PL/SQL end-point. The pre-processing can then check, for example WSSE credentials, and return a boolean or raise an exception, tellling orawsv that pre-processing failed and access need to be denied to the PL/SQL end-point.

            Anyway, appreciate the response. Am busy implementing SSL/HTTPS for access to orawsv, and orawsv at the moment ignores WSSE details in the SOAP header. So if the 3rd party sends that (in addition to using Basic Auth), orawsv just silently ignores WSSE. Which should address the 3rd party issue. I hope. :-)