Roads ? - where we're going, we don't need roads ...
Consuming WS-Security enabled webservices in PL/SQL - part 2 | ORA600
ORA600 content Content RSS
Oracle ORA600 News RRSS Oracle News RSS
ORA600 blog Blog RSS
ORA600 blog Blog Atom


User login

Consuming WS-Security enabled webservices in PL/SQL - part 2

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('')
(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:

Hi I need to consume a

Hi I need to consume a service from a PL/SQL package and the web service is supposed to use username, password in the url itself and certificates. I am using the following command to generate the web servicejpub -user=<dbuser>/<dbpwd> -url='jdbc:oracle:thin:@<dbserver>:1521:<dbSID> -proxywsdl='/home/sbehera/ProductSynchronizationService.wsdl' -endpoint= ' -plsqlpackage=prodSync -proxyopts=port:SyncProduct I am trying your way but the part related to gateway has me confused.“Then set the endpoint to the proxy/gateway address before you consume the WS, like this :
exec referencedatawebservice.setendpoint('')”
 What are we supposed to deploy on this local server? Appreciate your help.

Calling Siebel CRM OD

Hi Kurt,

 Seems a very useful one. I want to call a Siebel CRM OD WebServices (which is deployed on Amazon Cloud) they can be reached only with WS-Security.

I am trying to figure out if anyone has done that using the above methodlogy that you have blogged above, before I try my hands on calling CRM OD. Because I am time constraint at the moment.




i like this


Hello Kurt,

very interesting story. Did you consider using Oracle Web Services Manager? It can act like a proxy, like you describe.

I am asking because, I essentially face the same problem as you did, but I doubt I will be allowed to create my own proxy.

Also - you could potentially create the security injection as Java in the database, to allow for an "almost pure PL/SQL" implementation.

Best Regards,

Morten Tangaa

One more thing ...

If you need a pure PLSQL solution - without java, *and* the webservices also needs a client certificate for authentication (over SSL), that won't work in PLSQL. I tried it - it doesn't work. According to metalink it's a bug and depending on platform and version it sometimes works and sometimes doesn't...

I have to look up that metalink note - it actually made me laugh (does it work? maybe yes, maybe no Laughing).


Is that the WS gateway that comes with SOA Suite ?

A google search for WS gateway|proxy did not give me any good results. I did find a component in Oracle's SOA Suite (I think) that probably did what I wanted to do. However, I don't know if :

a. you can just install that one component - Oracle knowing, it'll be the fully blown Suite you'll have to install

b. relates to a - what are the license costs then.

c. relates to a - We run the proxy on a ubuntu cluster running apache2 as reverse proxy... we don't want it poluted with a huge packaged app just for a simple ws proxy Wink

As for the security injection ... that's true if you consider the statement 'java in the database in a security risk' true.

That would mean JPublisher in combination with PLSQL wrappers is a security risk...

I don't think the java proxy outside the database is a security risk... unless you can hack the firewall, and ubuntu, and get to the client and root certificate of the proxy.  Or if you would be able to decompile the code, make modifications and recompile... I would be chuffed by that as I used a code obfuscator Wink.

I can only say - If you need to produce plsql code for consuming a large amount of webservices, the JPublisher is a *very* productive tool. Unfortunately, even in 11g it does not support the WSS standard - and I couldn't find any other *cheap* solutions.




WSM etc

I have no idea about licensing, but you are probably right about not being able to install the component on its own.

I am considering using WSS4J ( to do the WS-security - possibly in the database.

I will have to have a look at JPublisher, but I am running on



No SSL possibility?

Hi. Thank for your contribution. I've had the same problem than you and was going to use your solution. But I have realized you force the SSL connection instead of putting a parameter in the cfg file to weather using SSL or not. In my case there's no SSL connection between the proxy and the web service server, so the handshake fails and so does the communication.

Given that you haven't put the source code, would you be so kind as to give the possibility not to use the SSL connection?

Thanks in advance. 

No SSL possibility?

No problem - I'll upload a new version later this day ...

However, I believe an http connection should only be used for test purposes. Sending wsse tokens over a non-ssl connection makes it fairly easy to sniff the packets and do a replay attack on the webservice.



You are absolutely right,

You are absolutely right, Kurt. It is an insecure method. The reason for not being using SSL is that the rest of the applications using the web service will be sending the password as "digest", that is, not in the clear.

Anyway we have tested your proxy and it did not work. I couldn't tell weather it was because of the SSL encryption or something else. The error had to do with endofString found or something like that, I can't remember. Maybe I coud send you the java exception that we get, in case you have the time to look at it or are interested.

Thank you very much in any case.

WS_Proxy / HTTP


I've added an option so you can switch between https or http.

I've wrote this particular piece of software to work together with JPublisher - and I've only tested it with Oracle JPublisher ... so I can't garantuee it'll work in other situations.

Anyway - you can download the jar file here and the config here .

At startup you should get something like this :

java -cp WSP2.jar WS_Proxy
Reading ws_proxy.cfg ...
Reading ws_proxy.cfg done !
Current size logfile = 1688 bytes
Warning - connection to WS will be non-SSL!
Init done...
Create socket on port 8000...
Create socket on port 8000 done!
Waiting for incoming connection.

It works... with some delay

Hi again and thank you very much for your new version.

I have been testing this new jar and it works. But I don't know why, there is some delay (around 20-50 seconds) between the moment the application server answers and the moment your programm answers back to Oracle. If I call directly the web service from PL without your programm it answers inmediately, so the delay seems to be somehow introduced by your bridge ¿?

Did it happen to you? Any idea of what it could be?



It works... with some delay

It did not happen to me in the original version which I used to access a webservice over the internet. I had response times well within 5secs at least.

After the time waited - does it come back with an error or the expected data ?

Can you send me the output generated by the proxy ? (


The answer comes with the

The answer comes with the expected data, no problems on that. There's nothing strange in the log either. After I see in TCP monitor that the web service has answered, everything stops for some seconds (nothing in the log during that time)... and finally this appears:

29-10-2008 13:18 : ---> IP response done !
29-10-2008 13:18 : ---> thread finished - exit
That's all ¿? 

I think ...

I think the problem is that your webservice response doesn't end with a new line and therefor the proxy's socket blocks until your webservice provider's socket times out. At that point, the proxy's socket unblocks and you get a result.

So ... either you make sure a newline is passed on at the end of the message or I can add a time-out on the proxy's socket (there already is a time-out on the server socket).

So - you can download a new version here

And a new config file here - the only difference is a new parameter WS_TIMEOUT ...




Hi again.


I've tested your new jar. I don't think this can be the solution because I get this SocketTimeOutException and the client stays unanswered.

About the new line, I've got no idea because I use Axis2 in java and the library takes care of the communication. I wouldn't even know how or where to introduce that new line.

I'll keep on doing some tests and tell you. 

See DUDE primer for info

Product is discontinued.