Roads ? - where we're going, we don't need roads ...
Consuming WS-Security enabled webservices in PL/SQL | 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

updated - see also 

As a DBA, I sure do a lot of non-DBA stuff these days - the next story is a perfect example.

For the last 2,5years I've been spending about 60% of my time at a customer's site. Because I have a java background and know a thing or two about Oracle application server and networking, they give me projects you normally don't give to a DBA.
For example :
- setting up linux based clustered load balancers (instead of using blue coat or big-ip f5)
- setting up linux/apache based clustered reverse proxies/ssl terminators
- build a SAML v2 extension to Oracle SSO server
- Oracle SSO server integration with  Belgian e-ID passports
(mental note - blog about above topics)
People might say those are not a dba's task - and I guess that's true but I need a challenge now and then to keep the job interesting ...

Anyway - I was in a meeting with this customer a couple of weeks ago and I got a new challenge.
They asked me if I would be able to consume webservices from within PLSQL.
So I said sure - no problem - piece of cake - bring it on.
They prefer PLSQL - as they have a lot of PLSQL experienced developers in-house, some VB/.net developers and no java
developers. So it would either be PL/SQL or .net.

I must admit - I do not have a lot of experience with webservices but I know the basics, ... being, send some XML using a HTTP post to the webservice, get XML back, parse the XML.
Besides, I had seen a lot of examples in blogs and on otn.
It involved consuming about 20 webservices with each about 5 methods.
I don't know if a webservice has methods - I don't speak 'webservice' - but just to say, it wasn't just one tiny webservice that needed invocation.
The webservices were situated on a server on the internet - so to top it off, I had to use HTTPs.

Now because of the number of webservices, I decided to give JPublisher a shot.
For the proof of concept I was using an Oracle 11g RDBMS that was placed in the DMZ. JPub can be found on the Oracle 11g client cd, so as soon as I installed it (with the necessary packages/java code in the database), I was ready to run JPub.

The benefit of JPublisher is productivity - I don't want to loose my time investigation the WS' WSDL document my self, and then write a large amount of PL code to invoke it (times 20, remember).
Oh - and I don't need to dive into JDeveloper - bonus !
Instead, JPublisher will :
- query the WSDL document
- generate the java code according to the WSDL
- generate plsql wrappers for the java code
- generate java grants
all within seconds !

So things were going great ... until I hit a bump when I got this error back when invoking the webservice in sqlplus :

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
javax.xml.rpc.soap.SOAPFaultException:WSDoAllReceiver: Request does not contain required Security header

The webservices were using WS-Security.

WS-Security requires extra security tokens (wsse) in the SOAP header. Tokens can be a simple username/password or even X.509 certificates.
It makes sense protecting your webservices if you are making them available over the internet.
Unfortunately - all examples and documentation assume you are cosuming local webservices with no security whatsoever.
Luckely - the webservice expected only a username and password.

I was using Oracle 11g just so I wouldn't run into situations where I would hit a brick wall with new technologies.
So surely I would be able to use JPublisher 11g and Oracle 11g to consume WS-Security enabled webservices in PL/SQL ?

No such luck - after hours of investigating I decided to open a call at Orace support just to be sure Oracle 11g/JPublisher did or did not support the WS-Security standard and, well, to get some advice, because it was a real showstopper.
If it would not work, the implementation would be done in .net.
The support guy was really helpful - I must admit that for the last 2years I have had more and more positive experiences with metalink... or either I have been really lucky to get support engineers who are passionate and don't give up.
Nevertheless - wsse headers were not yet supported - and so I had only the following options left :
- a complete plsql solution - building the xml, doing http posts, parse the xml all in PLSQL - doable but not productive enough
- use JDeveloper to generate the java code and write the pl/sql wrappers myself - also doable, because JDev supports WS-Security enabled webservices, however due to the large amount of webservices and the complexity of the datatypes I wasn't really convident in mapping it all manually to pl/sql wrappers
- hand the project over to the .net guys as they seemed convident in doing the job

And so I lost the project ... until the .net guys started asking me about what all the fuzz was all about. So I explain them about the WS-Security standards and the extra wsse headers in the soap envelope...

... and so the project came back to me.

I decided to give another solution a try - one that I had been reluctant to use... because it meant an extra component in the architecture : a WS-Security proxy.
I had written a proxy before - one that proxied SAML artifacts between identity providers and a WS-Security proxy wouldn't be that different (meaning I could reuse lots of code).
So after 1.5 days I had written a fully working WS-Security proxy.

The WS-Security proxy basically :
- sets up the https connection to the webservice provider
- parses the incoming requests
- injects the wsse headers in the original xml
- adjusts the http headers (host and content-length)
- returns the result back to the requestor (the database session)


There are probably some commercially available WS-Security proxies on the market - there's probably one in Oracle's SOA Suite... but it just isn't the same thrill as writing your own ;-)

The only downside is that the endpoint for the webservices needs to point to the proxy - other then that - the JPub generated code works like a charm with the proxy.
Also - we can run the proxy in the DMZ and leave the database deep down in the network.

Example :

select referencedatawebservice.getendpoint from dual ;

Here you see that Jpub made an error when it generate it's code (http://https://).
But that's ok as we have to let it point to the proxy anyway - like this :

exec referencedatawebservice.setendpoint('')

(currently I'm running the proxy on the same host as the database)

Then we can consume the service :

select referencedatawebservice.findLanguages from dual
SQL> /

TBL_OBJ_LANGUAGEVO(OBJ_LANGUAGEVO('language.dutch', 'nl'), OBJ_LANGUAGEVO('language.english', 'en'))

Bingo !

Here's a snippet of the debug log from my proxy server - it'll show you the injected security tokens (sensitive data has been remove of course):

14-08-2008 18:39 : Incoming connection accepted
14-08-2008 18:39 : +-- spawn new worker thread
14-08-2008 18:39 : +-- Thread started
14-08-2008 18:39 : Current number of threads = 1
14-08-2008 18:39 : Waiting for incoming connection.
14-08-2008 18:39 : ---> Open SSL socket to server
14-08-2008 18:39 : ---> thread - received - size 67 : POST /services/ReferenceDataWebservice HTTP/1.1
14-08-2008 18:39 : ---> thread - strip Host header = Host:
14-08-2008 18:39 : ---> thread - received - size 26 : Connection: Keep-Alive, TE
14-08-2008 18:39 : ---> thread - received - size 37 : TE: trailers, deflate, gzip, compress
14-08-2008 18:39 : ---> thread - received - size 32 : User-Agent: RPT-HTTPClient/0.3-3
14-08-2008 18:39 : ---> thread - received - size 14 : SOAPAction: ""
14-08-2008 18:39 : ---> thread - received - size 51 : Accept-Encoding: gzip, x-gzip, compress, x-compress
14-08-2008 18:39 : ---> thread - received - size 37 : Content-type: text/xml; charset=UTF-8
14-08-2008 18:39 : ---> thread - replace Content-length header = Content-length: 501
14-08-2008 18:39 : ---> thread - end http headers
14-08-2008 18:39 : ---> thread - body length = 1341
14-08-2008 18:39 : ---> thread - incoming = POST /services/ReferenceDataWebservice HTTP/1.1
Connection: Keep-Alive, TE
TE: trailers, deflate, gzip, compress
User-Agent: RPT-HTTPClient/0.3-3
SOAPAction: ""
Accept-Encoding: gzip, x-gzip, compress, x-compress
Content-type: text/xml; charset=UTF-8
Content-length: 1341

<?xml version="1.0" encoding="UTF-8"?> <env:Envelope xmlns:env="" xmlns:xsd="" xmlns:xsi="" xmlns:ns0="http://www.x.dom/Webservices" xmlns:ns1=""  xmlns:wsu=""><env:Header><wsse:Security xmlns:wsse="" xmlns="" xmlns:env="" env:mustUnderstand="1"><wsse:UsernameToken xmlns:wsse="" xmlns=""> <wsse:Username>myuser</wsse:Username> <wsse:Password Type="">mypassword123</wsse:Password> </wsse:UsernameToken> </wsse:Security> </env:Header><env:Body><ns2:findLanguages env:encodingStyle=""/></env:Body></env:Envelope>
14-08-2008 18:39 : ---> response = HTTP/1.1 200 OK
Date: Thu, 14 Aug 2008 14:13:15 GMT
Server: IBM_HTTP_Server/ Apache/2.0.47 (Unix)
Content-Type: text/xml; charset=utf-8
Content-Language: en-US
Keep-Alive: timeout=5, max=100
Connection: Keep-Alive
Transfer-Encoding: chunked
X-Pad: avoid browser bug

<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="" xmlns:xsd="" xmlns:xsi=""><soapenv:Body><ns1:findLanguagesResponse soapenv:encodingStyle=""><findLanguagesReturn soapenc:arrayType="ns2:LanguageVO[2]" xsi:type="soapenc:Array" xmlns:ns2="" xmlns:soapenc=""><findLanguagesReturn xsi:type="ns2:LanguageVO"><code xsi:type="xsd:string">nl</code><id xsi:type="xsd:int">1</id><name xsi:type="xsd:string">language.dutch</name></findLanguagesReturn><findLanguagesReturn xsi:type="ns2:LanguageVO"><code xsi:type="xsd:string">en</code><id xsi:type="xsd:int">2</id><name xsi:type="xsd:string">language.english</name></findLanguagesReturn></findLanguagesReturn></ns1:findLanguagesResponse></soapenv:Body></soapenv:Envelope>

14-08-2008 18:39 : ---> IP response done !
14-08-2008 18:39 : ---> thread finished - exit

To end the story - I'm always open for better solutions - so if you have alternatives to cosume a large amount of WS-Security enabled webservices in PL/SQL - please let me know !


Technorati Tags:

See DUDE primer for info

Product is discontinued.