A couple of weeks ago I wrote about consuming ws-security enabled webservices in PLSQL.

The problem was that, even using Oracle 11g and Jpublisher 11g, I was not able to generate a usertoken and password in the SOAP header according to the WS-Security standard.

My twisted solution was to put a WS proxy (or gateway if you like) in between the consumer (database) and the provider.
I would then place the proxy in the DMZ - and on behalf of the consumer :
- the proxy would set up an SSL connection to the provider
- receive the plsql/jpub generated XML
- inject a WS-Security header in the SOAP envelop
- adjust http headers (especially HOST & Content-Length)
- send the new SOAP message to the provider
- receive the response from the provider
- send the response to the consumer

It's not a real proxy in the sense that it proxies the complete http traffic. The proxy/gateway needs to alter the message and that wouldn't be possible if we would use SSL encryption straight out of the database :

Oracle rdbms --- http/s ----> proxy ---- http/s -----> WS provider (endpoint)

So instead we do something like this :

Oracle rdbms --- http ---> proxy (endpoint) ---> http/s --> WS provider (endpoint)
|                                             |
+->(set endpoint to proxy)    |
                                              +-> alter soap message + set endpoint to WS provider
                             
In order for it to work you need to run jpub and use the WS provider as endpoint.
Then load all the generated plsql packages and java classes.
Then set the endpoint to the proxy/gateway address before you consume the WS, like this :
exec referencedatawebservice.setendpoint('http://127.0.0.1:8000/services/ReferenceDataWebservice')
(in this case we are running the gateway on localhost on port 8000)
And then the proxy will send the soap message further on over http/s.

Anyway I've made my little proxy/gateway available for download here
It's only tested in combination with Oracle 11g rdbms & jpub - I used it and it works, but use it at your own risk.

How to run it :
- download the ws_proxy.cfg config file here
- download WSP.jar here
- open ws_proxy.cfg and adjust the parameters to your needs/requirements
PORT - server socket port the gateway binds on
SO_TIMEOUT - socket timeout in msec
LOG_DIR - directory where the proxy will place its logfiles
LOG_FILE - logfile name - the logfile will automatically rotate every 10Mb
SERVER_PORT - WS provider socket port (443)
SERVER_HOST - WS provider virtual hostname
WSSE_USERNAME - WS-Security wsse usertoken
WSSE_PASSWORD- - password for the above username
XMLNS - additional namespaces - these will be injected in the SOAP header - look at the xml jpub is generating based on the wsdl
- make sure the root/CA certificate that signed the WS provider's server certificate is available in java's certificate wallet
(typically placed in JAVA_HOME/lib/security/cacerts - check with the keytool utility - if the root certificate is not
available then the proxy won't be able to setup a SSL connection with the ws provider)
- start the proxy : >java -cp WSP.jar WS_proxy
Reading ws_proxy.cfg ...
Reading ws_proxy.cfg done !
Current size logfile = 260 bytes
Init done...
Create socket on port 8000...
Create socket on port 8000 done!
Waiting for incoming connection.

- connect to the Oracle database
- set the endpoint of the webservice to the proxy
- consume the webservice - all done !

I have an idea or two to make it better ... but for now that's all I need.

Technorati Tags: pl/sql WS-Security