Updated November 17, 2018 Please read this follow-up post as well. It contains an important note about intermediate certificates and where to download the root certificates.
One of the difficulties working with web services in the previous version of Oracle Database Express Edition (XE) was the lack of a usable Oracle Wallet. This was fast becoming a huge problems for developers, as many API providers started enforcing requirements to that clients accessed services through secured channels. When working with 11g XE, I often relied on proxies within a sandbox to mask the need for SSL/TLS. I discussed this somewhat at length in a previous blog post.
With the 18c release, Oracle has opened up and provided us developers the opportunities to make our apps safer. We now have access to the (orapki) tools needed to manage an Oracle Wallet. I don’t do this a lot, so I keep a snippets of what I do to make this easy.
I had to revisit this procedure while configuring Oracle Application Express (APEX) to support email sending using the APEX_MAIL package. The target SMTP server is Google’s smtp.google.com, and because we want to secure the user credentials during authentication, we have to use TLS. We will use this for context.
Obtain Root and Intermediate Certificates
To obtain the certificates for Google’s SMTP servers, execute the following command:
The first certificate in the chain (lines 7 to 33) belongs to the server you intend to connect to. You do/should NOT put this in the wallet. The remaining entries are the intermediate(s) and root certificate. These are needed. In this case, there is only an intermediate certificate. Take the content from lines 36 to 61 and save it in a file named /home/oracle/certs/Google-Internet-Authority-G3.pem.
For regular websites over SSL/TLS, you would use this instead:
Check the wallet to make sure that the certificate has been successfully added:
1 2 3 4 5 6 7 8 9 10
$ orapki wallet display -wallet /home/oracle/wallets/mywallet/ Oracle PKI Tool Release 188.8.131.52.0 - Production Version 184.108.40.206.0 Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: Requested Certificates: User Certificates: Trusted Certificates: Subject: CN=Google Internet Authority G3,O=Google Trust Services,C=US
Set Up APEX to Use the Wallet
Login to APEX as the instance administrator and navigate to “Manage Instance > Instance Settings”.
APEX Instance Setting - Wallet
One Last Thing
I did most of this in a Docker container using this image build. The image doesn’t have openssl available, so I grabbed the certs from the host operating system environment. For the Oracle Wallet management, you can access the tool by obtaining a Bash session in the database container.