Bravery Family

User Certificates in Oracle Wallet

Since I spent an age getting Oracle Wallet working with User Certificates, so I decided to blog about it, collecting all that I needed into one place.

Why do you need user certificates in an Oracle Wallet?

We have several Oracle ApEx applications at work. A new one that is being proposed will need to access a web service on a remote server. To control access to the server, the vendor has decided to use client certificates. So this means that I need to get a client certificate into a place were the Oracle database can make use of it when the PL/SQL ApEx will use tries to make connection to the remote server.

Solution Components

The first problem is that client certificates aren’t supported until Oracle 11g, so we will have to do an Oracle database upgrade. For what it’s worth, 11.1.0.6 will support client certificates without further patching. 11.1.0.7 does support certificates, but will also need an additional patch to re-enable it. Please note, the patch required isn’t released for Microsoft platform at time of writing so if you plan on doing this on a Window based database instance you are restricted to 11.1.0.6

You will also need an Oracle iAS installation for one of the commands that are used below. Version doesn’t seem to matter. In this case I am using the Linux version of iAS and it’s command line. There will be a slightly different command needed if Windows is used.

You will also need openssl installed, as we need to rip the supplied wallet apart.

We requested the user certificate, and the vendor supplied it (as a .pfx wallet file) along with the chain of authentication. I then used the wallet to connect to the server we would use in the end, and downloaded the certificate chain for the server as well. N.B. There is generally a password associated with the wallet. We will need this if there is one.

Creating the new Oracle Wallet.

I binary transferred the wallet file and both required certificate chains to the ApEx server (meaning the chain for User Certificate, and chain for web site). Then login to the ApEx server.

We need to extract the private key:

openssl pkcs12 -in [pfx file name] -nocerts -out private.key
Enter Import Password: [password for end user]
MAC verified OK
Enter PEM pass phrase: [new key password]
Verifying - Enter PEM pass phrase: [new key password]

And then extract the certificate:

openssl pkcs12 -in [pfx file name] -clcerts -nokeys -out user_certificate.crt
Enter Import Password: [password for end user]
MAC verified OK

Now set the Oracle home to be an iAS home and create the wallet using (need to make directory first):

mkdir –p /etc/ORACLE/WALLETS/[user]
$ORACLE_HOME/Apache/Apache/bin/ssl2ossl -cert user_certificate.crt -key private.key -chain [user certificate chain file] –wallet /etc/ORACLE/WALLETS/[user] -ssowallet yes
Enter PEM pass phrase:[new key password]
Enter wallet password:[new wallet password]
Verifying password - Enter wallet password:[new wallet password]
SUCCESS

Keep a note of the new wallet password, you will need this now and forever more!
Don’t delete the files created using openssl. We will need one again shortly.

Open the new Oracle wallet using iAS Wallet Manager:

$ORACLE_HOME/bin/owm

Make sure it looks correct (mainly that the certificate is there and is ready, and that the user certificate chain is there).

Then save it and exit. The wallet has been created, and is useable. However, we will probably use the 11g Wallet Manager associated with the database in the future and that means we need to do some extra work.

Convert Oracle Wallet to an Oracle 11g Wallet

Set Oracle home to DB home and start the database Wallet Manager:

$ORACLE_HOME/bin/owm

You will notice that thinks don’t look correct anymore. The steps to resolve this are:

  1. Re-import user certificate.
  2. Save.
  3. Remove user certificate
    Just the certificate, not the request for it.
  4. Save
    You should see that the ensure the blank trusted certificate entry has gone and the user certificate is back to requested.
  5. Re-import user certificate.

The wallet will now look correct again (user certificate is ready, trusted certificates are the ones in the chain to the user certificate.

Now import trust chain for server as normal (either one at a time, or as a single combined file).

Finally, save wallet. It should now contain all the certificates that are needed.

Testing the Oracle Wallet holding the User Certificate

Please remember, depending on who the database is actually running as, you may need to change the files in the wallet directory to open to the group, rather than just the user (i.e. this is the case if you use a shared software installation).

Test by running this as sys (to avoid ACL issues):

select utl_http.request('[secure url]'
,null
,'file:[path to wallet directory]'
,'[password for wallet]') a
from dual
/

If it works, you won’t get any errors and you’ll see the http code that is returned by the secure web page.

Troubleshooting

All I can really suggest for this is some things I have seen.

  1. Check the certificate chain for the server is really correct
  2. Check the permissions of the wallet files
  3. 11g Database ACLs (Oracle have embedded a firewall in the database… if you don’t open it for the right user, you’ll see nothing).
  4. Obviously, that you are using the correct password.

Posted

in

by

gsb

Tags:

,