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:
$ openssl s_client -starttls smtp -connect smtp.gmail.com:587 -showcerts
CONNECTED(000001B4)
---
Certificate chain
0 s:/C=US/ST=California/L=Mountain View/O=Google LLC/CN=smtp.gmail.com
i:/C=US/O=Google Trust Services/CN=Google Internet Authority G3
-----BEGIN CERTIFICATE-----
MIIEgjCCA2qgAwIBAgIIYYzIf9CCTNQwDQYJKoZIhvcNAQELBQAwVDELMAkGA1UE
BhMCVVMxHjAcBgNVBAoTFUdvb2dsZSBUcnVzdCBTZXJ2aWNlczElMCMGA1UEAxMc
R29vZ2xlIEludGVybmV0IEF1dGhvcml0eSBHMzAeFw0xODEwMDkxMzA4MDBaFw0x
OTAxMDExMzA4MDBaMGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIDApDYWxpZm9ybmlh
MRYwFAYDVQQHDA1Nb3VudGFpbiBWaWV3MRMwEQYDVQQKDApHb29nbGUgTExDMRcw
FQYDVQQDDA5zbXRwLmdtYWlsLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC
AQoCggEBAObRl1CBmcUdG0DKuFBKZUrtiI9LEjkWdLkCTG5GZN2dmmyoVF2Um+3Q
8GkxPSp4cNE/pV/+eKpX0swjsX7OSr+H75tG3SwPAwgsxKK/hciP5+UZn3hNxIGe
vkNZUIIiZcLpSmUm2C13AVYO+RrSQoswy72Pqq9L5MOin40RJq2zF1MCqA8AG3S6
c5uNevdHhRAfF9PuFc+FS+KRfMIOKZ1mnE6iMR9rBV4n3I5muVb0F24NlwtnQfKq
8KvUBIZzgZsoj9ED7WeTunTny1n70ElE9AgrOx7+YOpx8UiBsN1uMhszzb+l+Fck
Y6sJl4bjb7HfnYBKbihqauFcxGbsocECAwEAAaOCAUIwggE+MBMGA1UdJQQMMAoG
CCsGAQUFBwMBMBkGA1UdEQQSMBCCDnNtdHAuZ21haWwuY29tMGgGCCsGAQUFBwEB
BFwwWjAtBggrBgEFBQcwAoYhaHR0cDovL3BraS5nb29nL2dzcjIvR1RTR0lBRzMu
Y3J0MCkGCCsGAQUFBzABhh1odHRwOi8vb2NzcC5wa2kuZ29vZy9HVFNHSUFHMzAd
BgNVHQ4EFgQUHPvi7USz6x/8/XVkgFyMk9ndjJAwDAYDVR0TAQH/BAIwADAfBgNV
HSMEGDAWgBR3wrhQmmd2drEtwobQg6B+pn66SzAhBgNVHSAEGjAYMAwGCisGAQQB
1nkCBQMwCAYGZ4EMAQICMDEGA1UdHwQqMCgwJqAkoCKGIGh0dHA6Ly9jcmwucGtp
Lmdvb2cvR1RTR0lBRzMuY3JsMA0GCSqGSIb3DQEBCwUAA4IBAQC+CB4l2nvFPQck
8aJZRZ8vvT1G3gs5BGIPnpB3wIUEp6Hg4xHuPt3CAGktttMEu/wGJABxzfIr4RFo
qgafq3cg7/w5R9+4kaDX0YcNNdgvC6/8UXlHHTjEsNgJiGFDJGRzttHmGGhUvsxg
MwCihV1N0E5YZYIMfxVFHmbLTZe0tI4Ma0n+hxxbXSlSCOH4fjbaZ+sKX0RbWTDx
wweZxQM9JIn7Lwz4Fe34e+YfpyNqOow6FdvaOf5J3dEmqHoS5rXJ+dp8jTSTld5L
wn+ganMirE5mLNJNh9AvfIVtz4sHw1Wg5v+TkvWZ6t5J1oJY3UJPSfJrPQy6fssu
9rs+s4BX
-----END CERTIFICATE-----
1 s:/C=US/O=Google Trust Services/CN=Google Internet Authority G3
i:/OU=GlobalSign Root CA - R2/O=GlobalSign/CN=GlobalSign
-----BEGIN CERTIFICATE-----
MIIEXDCCA0SgAwIBAgINAeOpMBz8cgY4P5pTHTANBgkqhkiG9w0BAQsFADBMMSAw
HgYDVQQLExdHbG9iYWxTaWduIFJvb3QgQ0EgLSBSMjETMBEGA1UEChMKR2xvYmFs
U2lnbjETMBEGA1UEAxMKR2xvYmFsU2lnbjAeFw0xNzA2MTUwMDAwNDJaFw0yMTEy
MTUwMDAwNDJaMFQxCzAJBgNVBAYTAlVTMR4wHAYDVQQKExVHb29nbGUgVHJ1c3Qg
U2VydmljZXMxJTAjBgNVBAMTHEdvb2dsZSBJbnRlcm5ldCBBdXRob3JpdHkgRzMw
ggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDKUkvqHv/OJGuo2nIYaNVW
XQ5IWi01CXZaz6TIHLGp/lOJ+600/4hbn7vn6AAB3DVzdQOts7G5pH0rJnnOFUAK
71G4nzKMfHCGUksW/mona+Y2emJQ2N+aicwJKetPKRSIgAuPOB6Aahh8Hb2XO3h9
RUk2T0HNouB2VzxoMXlkyW7XUR5mw6JkLHnA52XDVoRTWkNty5oCINLvGmnRsJ1z
ouAqYGVQMc/7sy+/EYhALrVJEA8KbtyX+r8snwU5C1hUrwaW6MWOARa8qBpNQcWT
kaIeoYvy/sGIJEmjR0vFEwHdp1cSaWIr6/4g72n7OqXwfinu7ZYW97EfoOSQJeAz
AgMBAAGjggEzMIIBLzAOBgNVHQ8BAf8EBAMCAYYwHQYDVR0lBBYwFAYIKwYBBQUH
AwEGCCsGAQUFBwMCMBIGA1UdEwEB/wQIMAYBAf8CAQAwHQYDVR0OBBYEFHfCuFCa
Z3Z2sS3ChtCDoH6mfrpLMB8GA1UdIwQYMBaAFJviB1dnHB7AagbeWbSaLd/cGYYu
MDUGCCsGAQUFBwEBBCkwJzAlBggrBgEFBQcwAYYZaHR0cDovL29jc3AucGtpLmdv
b2cvZ3NyMjAyBgNVHR8EKzApMCegJaAjhiFodHRwOi8vY3JsLnBraS5nb29nL2dz
cjIvZ3NyMi5jcmwwPwYDVR0gBDgwNjA0BgZngQwBAgIwKjAoBggrBgEFBQcCARYc
aHR0cHM6Ly9wa2kuZ29vZy9yZXBvc2l0b3J5LzANBgkqhkiG9w0BAQsFAAOCAQEA
HLeJluRT7bvs26gyAZ8so81trUISd7O45skDUmAge1cnxhG1P2cNmSxbWsoiCt2e
ux9LSD+PAj2LIYRFHW31/6xoic1k4tbWXkDCjir37xTTNqRAMPUyFRWSdvt+nlPq
wnb8Oa2I/maSJukcxDjNSfpDh/Bd1lZNgdd/8cLdsE3+wypufJ9uXO1iQpnh9zbu
FIwsIONGl1p3A8CgxkqI/UAih3JaGOqcpcdaCIzkBaR9uYQ1X4k2Vg5APRLouzVy
7a8IVk6wuy6pm+T7HT4LY8ibS5FEZlfAFLSW8NwsVz9SBK2Vqn1N0PIMn5xA6NZV
c7o835DLAFshEWfC7TIe3g==
-----END CERTIFICATE-----
---
Server certificate
subject=/C=US/ST=California/L=Mountain View/O=Google LLC/CN=smtp.gmail.com
issuer=/C=US/O=Google Trust Services/CN=Google Internet Authority G3
---
No client certificate CA names sent
Peer signing digest: SHA256
Server Temp Key: ECDH, P-256, 256 bits
---
SSL handshake has read 3240 bytes and written 469 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES128-GCM-SHA256
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol : TLSv1.2
Cipher : ECDHE-RSA-AES128-GCM-SHA256
Session-ID: 5DF77BA747412BE89EB308BEAFE46CB25A88516376424ED100CA56C2BDD34225
Session-ID-ctx:
Master-Key: 565E04E20E6D8200A4FECD2A5F09ED4549EA2FA982350A598B6A9D4EDA2B9FD26F434F66475CA6524B8EF469A1A0A563
Key-Arg : None
PSK identity: None
PSK identity hint: None
SRP username: None
TLS session ticket lifetime hint: 100800 (seconds)
TLS session ticket:
0000 - 00 85 11 42 e7 58 60 f5-50 28 ca bc 82 57 ec 48 ...B.X`.P(...W.H
0010 - 68 64 84 5a 2b ca 65 e3-2f 92 2a 46 16 43 f3 7b hd.Z+.e./.*F.C.{
0020 - 13 e2 99 52 c2 8f bb 9a-de d1 89 3e 22 de 5a 60 ...R.......>".Z`
0030 - 90 fc 24 8c d6 4f f6 40-d0 f4 e7 2c bc f9 46 e7 ..$..O.@...,..F.
0040 - 7f 7b a1 96 0f da da 6e-14 cd 32 2a 98 f1 97 e6 .{.....n..2*....
0050 - 09 06 9f 59 d5 ef 93 a3-e9 7f c7 b6 88 64 b0 1d ...Y.........d..
0060 - 98 93 06 a9 01 57 9b b7-fc 57 14 45 0a cb 3a 6a .....W...W.E..:j
0070 - 39 4e 80 43 d7 04 e9 16-62 61 d5 ef 36 7b 75 b4 9N.C....ba..6{u.
0080 - 0a db bb 0b 2d 58 bd 32-7e 15 e7 20 b6 61 9d 39 ....-X.2~.. .a.9
0090 - 2d 74 b1 e8 67 da f1 3d-40 2d 62 e3 b2 ab 51 3e -t..g..=@-b...Q>
00a0 - 56 0b ec 51 90 df d4 90-54 76 a2 5b b0 e3 3c 95 V..Q....Tv.[..<.
00b0 - 1b af 75 d5 70 10 c7 e9-e1 e0 b4 1d 8c ea 7a 85 ..u.p.........z.
00c0 - e5 89 f8 81 81 02 22 99-b7 a9 2f 80 e3 37 d3 c6 ......".../..7..
00d0 - 88 be 7d 33 b7 ..}3.
Start Time: 1540348953
Timeout : 300 (sec)
Verify return code: 0 (ok)
---
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:
$ openssl s_client -connect google.com:443 -showcerts
The application `openssl` is commonly found in Linux distributions and probably in the MacOS as well. For Windows users, use Git Bash.
## Create the Oracle Wallet
Do this only if you have not already done so. Create the Oracle Wallet using the following command:
```bash
$ orapki wallet create -wallet /home/oracle/wallets/mywallet \
-pwd averysecurepassword
Add Certificates
Now add the certificate using the following command against every certificate that was retrieved in the previous step.
$ orapki wallet add -wallet /home/oracle/wallets/mywallet \
-trusted_cert -cert /home/oracle/certs/Google-Internet-Authority-G3.pem
Don't Leave Home Without It
Check the wallet to make sure that the certificate has been successfully added:
$ orapki wallet display -wallet /home/oracle/wallets/mywallet/
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.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".
That's it!
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.
[opc@hostserver ~]$ docker exec -it -u oracle oracledb bash
[oracle@oracledb /]$ . oraenv
ORACLE_SID = [XE] ?
The Oracle base remains unchanged with value /opt/oracle
[oracle@oracledb /]$
Hope this helps. Lock it down!