Articles by: Mathias

Scripting the Oracle Wallet truststore

As you may recall, I do not like processes that requires a GUI for things that should be possible to do repeatedly and be part of automatic deployment of a full environment.

Thus I want the whole wallet creation to be done in scripts. No, the root certs don’t change often and no it would bot be a bad thing to just commit a complete wallet to git. But I still like to quickly with scripts recreate a wallet.

The script doing the heavy lifting is one that allows figuring out what the root certificate is from a given domain, grab the cert and install it into a wallet.

Let’s go through the pieces we need step by step.

First up is basic setup.

#!/bin/sh
HOST=$1
WALLET=$2
PWD=$3

I’m using the Bourne shell to hopefully provide compatibility on different systems. This is the classic location and it is at least correct on OS X and if you run Oracle DB in a docker container based on Oracles Docker project.

Then we accept three parameters – Host, Wallet location, and password for the wallet.

Next, let’s grab the path for the bundle of certificates shipped with openssl.

BUNDLE=$(openssl version -d|cut -d \" -f2)/cert.pem

“openssl version -d” shows where the openssl directory is located. We keep just the path and tag on “/cert.pem” as we’re only interested in the certificate bundle in this script. We now have BUNDLE containing the path to the certificate bundle shipped with openssl.

Our next step is to grab the very first line in the list of certificates used by a domain. From that line we’ll extract the name of the certificate.

CERT=$(echo QUIT|openssl s_client -connect ${HOST}:443 -showcerts \
     >/dev/null 2> /tmp/mycert
head -1 /tmp/mycert|rev|cut -f1 -d=|rev|xargs)

The first line calls the domain on port 443 and gets the list of certificates. For oracle.com it would look like this.

depth=2 C = US, O = DigiCert Inc, OU = www.digicert.com, CN = DigiCert Global Root CA
verify return:1
depth=1 C = US, O = DigiCert Inc, CN = DigiCert SHA2 Secure Server CA
verify return:1
depth=0 C = US, ST = California, L = Redwood City, O = Oracle Corporation, OU = Oracle PEO ADC, CN = *.oracle.com
verify return:1
DONE

We’re only interested in the first line as that will be the one referencing the root certificate. On this line we only care about the part after the last = sign, that is the name it is referenced by in the bundle.

The second line in the command takes the first line, reverses it and grabs the part before the first = sign and then reverses that back to its original. The reversing back and forth is due to “cut” not having a function to specify last occurrence.

One piece that looks odd may be the ending “xargs”. It is usually used to run a command one time per input in a list. Here it is used for a completely different reason, it returns a string without leading and trailing spaces.

With that in place CERT now holds the name of the root certificate. In the example with Oracle it would be “DigiCert SHA2 Secure Server CA”

Let’s now grab the actual certificate to lead to the wallet.

sed -n "/${CERT}/,/^-----END CERTIFICATE-----$/p" ${BUNDLE} \
| sed -n "/^-----BEGIN CERTIFICATE-----/,/^-----END CERTIFICATE-----$/p" \
> /tmp/cert.txt

The first sed-command goes to the bundle-file and grabs the text for the bundle. It can look like this:

=== /C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Global Root CA
Certificate:
 Data:
 Version: 3 (0x2)
 Serial Number:
 08:3b:e0:56:90:42:46:b1:a1:75:6a:c9:59:91:c7:4a
 Signature Algorithm: sha1WithRSAEncryption
 Validity
 Not Before: Nov 10 00:00:00 2006 GMT
 Not After : Nov 10 00:00:00 2031 GMT
 Subject: C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root CA
 X509v3 extensions:
 X509v3 Key Usage: critical
 Digital Signature, Certificate Sign, CRL Sign
 X509v3 Basic Constraints: critical
 CA:TRUE
 X509v3 Subject Key Identifier: 
 03:DE:50:35:56:D1:4C:BB:66:F0:A3:E2:1B:1B:C3:97:B2:3D:D1:55
 X509v3 Authority Key Identifier: 
 keyid:03:DE:50:35:56:D1:4C:BB:66:F0:A3:E2:1B:1B:C3:97:B2:3D:D1:55

SHA1 Fingerprint=A8:98:5D:3A:65:E5:E5:C4:B2:D7:D6:6D:40:C6:DD:2F:B1:9C:54:36
SHA256 Fingerprint=43:48:A0:E9:44:4C:78:CB:26:5E:05:8D:5E:89:44:B4:D8:4F:96:62:BD:26:DB:25:7F:89:34:A4:43:C7:01:61
-----BEGIN CERTIFICATE-----
MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBh
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3
d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBD
QTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVT
MRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5j
b20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG
9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsB
CSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97
nh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt
43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7P
T19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4
gdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAO
BgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbR
TLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUw
DQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/Esr
hMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg
06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJF
PnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0ls
YSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQk
CAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=
-----END CERTIFICATE-----

Good, but we only want from START CERTIFICATE to END CERTIFICATE. The second sed does just that, it takes the above output and keeps that part we want. The result is saved in /tmp/cert.txt.

Great now, we have extracted just the root certificate we want. All that is left is to add it to an existing wallet.

orapki wallet add -wallet ${WALLET} -trusted_cert -pwd $PWD -cert /tmp/cert.txt

This should look familiar if you have read the previous posts (here and here). We’re adding the extracted certificate to the wallet at path WALLET and using the password PWD to allow changes. The certificate we’re adding is of course the one we just extracted.

The only thing left is to clean up our two workfiles.

The complete script looks like this.

Now with that sscript handled we can of course write a small script that automates creating a wallet with the certificates we want added.

Assuming you have set up ACL for port 443 as shown in a previous blog post you can now access the html of example.com and www.oracle.com using the wallet that you just created.

select utl_http.request('https://www.oracle.com', null
 , 'file:/opt/oracle/oradata/XE/XEPDB1/wallet_79741') from dual;

select utl_http.request('https://example.com', null
 , 'file:/opt/oracle/oradata/XE/XEPDB1/wallet_79741') from dual;

Of course, change the path and name of the wallet to the one you just created.

That completes this series of blog posts covering ACL and Wallets for access from the database.