Thursday, June 21, 2012

Using SSL client certificates for authentication in UTL_HTTP

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;

5 comments:

  1. 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.

    ReplyDelete
  2. 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.

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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.

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

      Delete