Category: Wallet

May 15th, 2020 by Mathias

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.

Th us 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.

Posted in Wallet

April 29th, 2020 by Mathias

I held a live stream for SWEOUG that presented what my last two blogs covered. It was my first ever and while it was a bit uncomfortable to set up to not see your audience it worked well. In fact it was really enjoyable and I look forward to doing more.

In the stream Ilmar Kerm made a few comments about what I said. I was not able to fully grasp it during the stream, but I talked to Ilmar after to find out what if anything was to be corrected and what was just additional info.

The feedback from Ilmar was centered around three parts.

  1. When I stated the last certificate in the chains of certificates was a root certificate it really wasn’t it was just an intermediate certificate.
  2. There is no need to download certificates from the website, you’d just look at the chain to know what root you need in your trust store.
  3. Oracle provides a few certificates out of the box that one might want to remove.

Let’s review these questions in order.

Root Certificate is not sent by the website

If one looks at the chain of certificates from a website you will see the following lines for each certificates, these are the ones for oracle.com for the last one in the list.

 1 s:/C=US/O=DigiCert Inc/CN=DigiCert SHA2 Secure Server CA
   i:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Global Root CA

That means that this certificate is “DigiCert SHA2 Secure Server CA” and it was issues by “DigiCert Global Root CA”. That is the certificate that issued it, the root was not sent to us. Ilmar is correct on this.

However, if we look at the site I used in the demo, example.com the lines for the last certificate looks like this.

 2 s:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Global Root CA
   i:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Global Root CA

Thus, this is the root certificate. So while it was correct that it is the root, it is not typical. The website does not have to send it and in fact it probably shouldn’t. All places receiving will use their own trust store for what root certificates they will trust and not use the root the website sends.

You only need root

It is true, ask any Linux sysadamin. 🙂

The argument here was that grabbing intermediate certificates off of the website is not needed. All that matters is the root certificate. This for sure sounded strange to me. I was sure I had seen it require to have the intermediate ones loaded in also. Every person I talk to talks of how they grab them from websites using a web browser.

But it is true and some blogs has an update stating this too. I have somehow reread them and not understood that change in the process.

Furthermore, you want to grab the trusted certificate from a source you are comfortable with. So you may use mozilla, I am opting to use what openssl ships with.

To know where you find it with openssl, you use this command.

~ $ openssl version -d
OPENSSLDIR: "/private/etc/ssl"

In the path of OPENSSLDIR you will find a file “certs.pem”. In it you’ll find a list of root certificates that ships with openssl. If the one you are looking for is not in this bundle, think twice about adding it from somewhere else. It may be OK, just make sure you know it is secure.

From this file, search for the CN (Common Name) of the cert you want to trust and grab the lines from “—–BEGIN CERTIFICATE—–” to “—–END CERTIFICATE—–” including those two lines and save it to cert1.txt, just like we did with them from the openssl output in the last blog post.

With that we can now create the wallet we need for the same function as that blog post showed.

orapki wallet create -wallet blogwallet -auto_login -pwd BlogWallet1
orapki wallet add -wallet blogwallet -trusted_cert -pwd BlogWallet1 -cert cert1.txt

That is it, the wallet will now work without adding the intermediate certificate.

Verdict – Ilmar is correct again.

Oracle provides default trusted certificates

A newly created wallet seems to have had these certificates installed.

Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

However, I guess that stopped in 12c. I can however not find a note about that change. I see a wallet in my 18c XE being created with no trusted certs to start with.

Verdict – Ilmar is correct again. At least in some versions of Oracle.

Conclusion

Getting feedback i great. Here it took me a few days to work trough and test it out. But I ended up correcting some of my understanding and understanding other bits better for how this works.

Thanks Ilmar for taking the time to provide feedback and reply to my follow up questions as I wrapped my head around what this meant for what I presented and how I do this. In the end it simplifies the process of setting up the wallet considerably.

Posted in DBA, Wallet