Ilmar Kerm

Oracle, databases, Linux and maybe more

Starting from 11.2 its possible to use SSL client certificates to authenticate yourself to a remote web service using SSL client certificates. I did not find much information on it using Google or documentation, that is why I’m writing this post.

Please refer to this post by Tim Hall to get started on connecting to HTTPS service using UTL_HTTP, all of this is needed before continuing with SSL client certificate authentication.

The first thing you need is to generate user certificate request inside Oracle Wallet, sign it by CA and load the returned certificate back to Wallet. I’m not going to very detailed steps here, but basically (using Oracle Wallet Manager OWM):

  1. Open the wallet you created using Tim Hall’s post mentioned previously.
  2. Go to Operations > Add Certificate Request
  3. Fill in all the needed fields
  4. After certificate request has been created, go to Operations > Export Certificate Request
  5. Send the request to a Certification Authority (that the remote service trusts) for signing and wait for a reply (in a form of signed certificate)
  6. Import the signed certificate to wallet – go to Operations > Import User Certificate

If you are using 11g OWM/ORAPKI and when importing the user certificate to wallet OWM displays an error or ORAPKI corrupts your wallet, you can just use OWM/ORAPKI programs from 10gR2 database client. This is due to bug Bug 9395937: UNABLE TO IMPORT USER CERTIFICATE IN OWM 11.1, WORKS IN 10.2.

Next thing is to add ACL privileges inside the database. UTL_HTTP documentation requires the use of use-client-certificates ACL privilege. How to do that I’ll refer to Tim Hall’s post again Fine-Grained Access to Network Services in Oracle Database 11g Release 1. In the example below I already have ACL all_access.xml and I’m granting connect and use-client-certificates privileges to CLTEST schema.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','CLTEST', true, 'connect');

PL/SQL procedure successfully completed.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','CLTEST', true, 'use-client-certificates');

PL/SQL procedure successfully completed.

Now the step that is not mentioned in UTL_HTTP documentation and got me stuck for weeks until I opened SR to Oracle Support. The network ACL needs also privileges on the Wallet file using DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL('all_access.xml','file:/path/to/oracle/wallet');

PL/SQL procedure successfully completed.

After the privileges have been assigned, you can use UTL_HTTP to query remote web service like you do with normal HTTPS connection. If the remote web service requests client to be authenticated using certificates, UTL_HTTP automatically handles it in the background and uses the user certificate located in the wallet. For example:

SQL> SELECT utl_http.request('https://secure.service.com/status', '', 'file://path/to/oracle/wallet', 'WalletPassword') FROM dual;

7 comments

  1. Unknown says:

    An organization needs to install the SSL Certificate onto their web server to initiate SSL sessions with browsers. Depending on the type of SSL Certificate applied for, the organization will need to go through differing levels of vetting.

  2. meerkat2u says:

    I really appreciate you posting this piece on your blog Ilmar. I've spent the last week pulling my hair out trying to determine why my web service https request wouldn't utilise the client certificate in my Oracle wallet. Never dreamed of looking for use-client-certificates privilege and DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL. Thank you for sharing.

  3. J says:

    Hi,

    First thanks a lot for this post. It's really awesome and useful.
    But in my case I've hit this error:

    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1722
    ORA-29024: Certificate validation failure
    ORA-06512: at line 6

    The certificate works correctly on browser. I've imported it to firefox and I can connect. What can be wrong here?

    Regards
    Joao

  4. Ilmar Kerm says:

    Have you imported all CA certificates to the wallet as trusted certificates? It could also indicate, that Oracle declined the server certificate, because it is not trusted.

  5. Thanks iLmar Kerm for this solution. I am also getting this error.
    How do you make it accesible then, instead of buying a certificate?

  6. Prabha Shankar says:

    llmar Kerm,
    Thank you so much for this info. I was working on an issue related to connecting to https & webservice with utl_http call.
    It worked as system/sys but not as regular oracle user even though I had created the correct ACL. I was missing the below 2 statements
    I searched several days on google without luck until I found your page.

    SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘all_access.xml’,’CLTEST’, true, ‘use-client-certificates’);
    SQL> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(‘all_access.xml’,’file:/path/to/oracle/wallet’);

  7. Ralph Keck says:

    Hello Ilmar Kerm,

    thanks a lot for your blog.

    We are using 12.2 DB and are working for a long time with wallets, containing just CA certificates, before. Now for the first time we have to use a client certificate. I want to import an external issued client certificate, which I already have in pkcs#12 format, “cert.p12”. I rename it to “ewallet.p12” and I can open it in my 12.2 OWM with no password. I am able to import several CA certificates, am still able to save the wallet again and enable auto-login.
    I can set a password with an empty old password, save the wallet, but when I try to open it again, I say’s wrong password, nor with the password, I saved in the previous step, neither with the empty password.

    What am I doing wrong?

Comments are closed.