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 10.1.3.3 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.
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 :
(currently I'm running the proxy on the same host as the database)
Then we can consume the service :
select referencedatawebservice.findLanguages from dual
TBL_OBJ_LANGUAGEVO(OBJ_LANGUAGEVO('language.dutch', 'nl'), OBJ_LANGUAGEVO('language.english', 'en'))
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: 127.0.0.1:8000
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
Accept-Encoding: gzip, x-gzip, compress, x-compress
Content-type: text/xml; charset=UTF-8
<?xml version="1.0" encoding="UTF-8"?> <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns0="http://www.x.dom/Webservices" xmlns:ns1="https://www.x.com/services/" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"><env:Header><wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" env:mustUnderstand="1"><wsse:UsernameToken xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"> <wsse:Username>myuser</wsse:Username> <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">mypassword123</wsse:Password> </wsse:UsernameToken> </wsse:Security> </env:Header><env:Body><ns2:findLanguages env:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"/></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/22.214.171.124 Apache/2.0.47 (Unix)
Content-Type: text/xml; charset=utf-8
Keep-Alive: timeout=5, max=100
X-Pad: avoid browser bug
<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soapenv:Body><ns1:findLanguagesResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><findLanguagesReturn soapenc:arrayType="ns2:LanguageVO" xsi:type="soapenc:Array" xmlns:ns2="http://www.x.com/Webservices" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"><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 !
Kurt Van Meerbeeck
Cell : +32 495 580714
Service & Support Manager
Cell: +45 53 74 71 27
South Africa :
Cell : +27 82 7799275