Often, as a DBA you get dragged into the mess that a developer has brought upon themselves. It so happened that a critical project deliverable was dependent on the ability to fetch data using URL.HTTP_REQUEST, and the target URL was a secure one (HTTPS). Now, HTTP and HTTPS is all the same, but with an extra S, isn’t it? 🙂
If you try to fetch the URL without doing anything, you will get an ORA-29024. This is expected as the database, does not have the means to validate the certificate presented by https://google.co.in.
Why is this error thrown?
But it works from my browser when I try from my local you say? When you access https://google.co.in via your browser, the browser is able to traverse the certificate chain, pulling details from the web if needed.
In case of https://www.google.co.in, the certification path is as below. A browser is able to fetch “Google Internet Authority G2” which is an intermediate certificate authority (CA) to validate *.google.com and “Geotrust Global CA” which is a Root CA to validate authenticity of “Google Internet Authority G2”.
In case of a database, it cannot go out to the internet and fetch the intermediate and root CA that are needed to establish the chain of trust. For this, Oracle DB relies on Wallets. Creating a wallet is straightforward.
Download the Certificates
First, we download the necessary certificates. In the above example, we will need GeoTrust Global CA and Google Internet Authority G2 certificates. I’ll explain this using Chrome as the example
Open the site in Chrome, click the green padlock icon on the address bar, then click on the Details link
Now a new windows opens on the right hand side, similar to below. Here, click on View Certificate.
On the new window that appears, click on the Certification Path
Now double click on Geotust Global CA, Go to details tab on the window that opens.
Click on the Copy to File. Follow prompts to save the certificate as Base-64 encoded. I saved mine as /tmp/GeoTrustGlobalCA.cer
Repeat the same for the next certificate in the chain, which is “Google Internet Authority G2”. I saved it as /tmp/GoogleIAG2.cer.
Note that we do not need the actual site certificate.
Create Wallet & Import Certificate.
Login to DB node as the database owner. Source environment.
orapki wallet create -wallet /d01/wallet -pwd Welcome123 -auto_login
Here, /d01/wallet is on a mount point that holds other datafiles. This ensures that if there is a failover, the wallet also travels with the datafiles and the functionality is not broken. the auto_login option enables that db to open the wallet at each startup, without is having to manually specify the password.
The next step is to add the certificates to the wallet, that we downloaded earlier.
orapki wallet add -wallet /d01/wallet -trusted_cert -cert "/tmp/GeoTrustGlobalCA.cer" -pwd Welcome123 orapki wallet add -wallet /d01/wallet -trusted_cert -cert "/tmp/GoogleIAG2.cer" -pwd Welcome123
Note: if your DB runs on windows (say you have one running on your laptop), theoretically, you could use the GUI tool to create wallet. But I have never been able to get it work. orapki command is platform independent, and I stick to it always.
Now if you query, you will be able to fetch details from https://www.google.co.in