Using the PL/SQL UTL_HTTP interface with HTTPS and certificates, requires an Oracle Wallet with the relevant certificates.
Client certificate authentication, as far as I recall, is configured in the sqlnet.ora file of the database instance. Still need a wallet with relevant certificates though.
Once the Wallet is configured and loaded with the correct client authentication certificate, will APEX_WEB_SERVICE calls (or perhaps UTL_HTTP underneath that) just sort of automatically realize it needs to supply that certificate during HTTPS handshaking? Or is there some kind of additional parameter we need to supply? As an aside, I do not see in ApEx App Builder's Web Source Modules management wizard a way to specify certificate authentication. I see only Basic Authentication and OAuth2, and even Advanced mode seems to be "post-SSL session establishment" (i.e., cookies, headers, request URI, request body, etc.)
mentions parameter p_https_host:
The host name to be matched against the common name (CN) of the remote server's certificate for an HTTPS request.
But that seems like it is not for client authentication but for server authentication (the more common part of HTTPS connections). Or perhaps the documentation is not totally clear on the point, but it is for both?
There does not seem to be a specific mention in the Web Source Module documentation found here:
I agree. I have found no mention about client certificate authentication. It seems I will have to use java for that...
the p_https_host is related to the server certificate (not client) and relevant only for 12.2 (more info in this thread utl_http.request on ssl site fails with ORA-29024 ), 18x does it automatically.
I do not see in ApEx App Builder's Web Source Modules management wizard a way to specify certificate authentication.
I'm afraid there is no such option (only commonly used authentication methods).
It would take a "while" to setup some testing environment, so it would be highly helpful if you could provide some publicly accessible service with client authentication. Unfortunately there is not too many info about client certificate authentication and what is actually happening, but it's still "just" a http request, no black magic.
This would be worth trying https://devcentral.f5.com/questions/-inserting-ssl-client-certificate-into-the-header-of-the-http-session , i.e. try to add a request header X-Client-Cert to your request and as a value the entire content of the client certificate https://medium.com/@sevcsik/authentication-using-https-client-certificates-3c9d270e8326 . If it does not work, try to base64 encode https://docs.oracle.com/database/apex-18.2/AEAPI/CLOBBASE642BLOB-Function.htm#AEAPI1940 the certificate. If none of this works, I have no idea what to do and you'll probably have to thoroughly inspect in SoapUI what is being sent in a request. It looks like you're a pioneer in sending client certificates in PL/SQL.
Thanks for the reply.
Am I wrong or this is what this document describes?
Is It Supported To Set Up Mutual (2-way) SSL Authentication From Oracle Database To Web Service Call Through UTL_HTTP ? (Doc ID 2291134.1)
Good find, @CInglez! It does seem that client certificate authentication is described there, as you say. I followed that article back to the bug and patch description and found this snip in there:
]] The UTL_HTTP package does not send the client certificate in an Oracle
]] wallet when accessing a remote HTTP server.
When a UTL_HTTP user tries to access a HTTPS server that requires
using a client certificate and authentication fails even when the valid
certificate is present in the Oracle wallet, the user may hit this bug.
Still not 100% clear, but I think this implies that the UTL_HTTP package will indeed "automatically" detect client certificate authentication challenge and automatically supply the correct certificate for you, as long as all the DB-level configuration and permissions are in place, and you have the correct client certificate and the certificate is fully standards-compliant and correctly created (sometimes people do sloppy, incorrect SSL certificate creation without all the correct flags, which can still be "forced" to work, if you have total control over the SSL session creation).
Also, thanks Pavel_P for those additional details; they seem mostly to square up with this find, and also gives some other interesting avenues for work-around, should it become necessary.
indeed that note is the same I referred to for this authentication scenario.
In the past months I have also been trying to make work the client certificate authentication to access a web service from the database, but I had to give up for lack of time.
Anyway our DBA discovered that the client certificate we set up was accepted successfully only if the utl_http.request was made by the SYS user, while it did not work at all under other less privileged database users.
I think our ACLs were set correctly as per MOS note 2291134.1, but the perspective to open a long running SR to make it work on db 11g made us desist.
Since we are still stuck with Release 22.214.171.124.0 (hopefully not for long), I did not spend any more time on it, but I would be very glad to hear if anybody else has found a solution.
Hope it helps,
actually I am still trying, could not make it work yet. I have a wallet with several root certificates for SSL with remote WS, but I still must load the client certificate (I have a .pfx one) into it, and test it.
If somebody has new clues, please let me know. I will post my results soon.
It looks like some secret rite for the most faithful Oracle devotees.
Well, after a lot of effort, I give up. Now I am hitting the error:
"ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side".
I will use the traditional approach, java.
Thanks for any comments
That is exactly the same error we got in the same scenario. It seems it works only for the SYS user...