Certs for APEX Social Sign-In
Updated November 18, 2018 Added a help utility to populate an Oracle Wallet with root certificates
Oracle Database 18c Express Edition comes with a usable Oracle Wallet. In a previous article, I had described how to:
- Obtain the certificates necessary to make a successful SSL/TLS connection.
- Create and add these certificates to the Oracle Wallet.
- Configure the APEX instance to use the wallet.
Someone had asked in the comments, what certificates were necessary for Social Sign-in in Oracle Application Express (APEX) to work. It's something that I think most of us would like to know too, so I did a little more digging and here's what I found for three of the more popular social platforms: Google, Facebook and Microsoft.
Fortunately, the discovery I did for another previous work on circumventing wallet issues helped in determining which URLs were called by the authentication workflow. To add a Generic OAuth2 Provider, three of these are required: Authorization, Token and User Info endpoints.
When the authentication process begins, the user is first redirected to the Authorization endpoint and that interaction is performed through the browser. Upon successful authentication, the Identity Provider (IdP) then calls the APEX authentication callback URL with the necessary parameters. APEX calls the Token and User Info to obtain an access token and user information respectively. These are made through the APEX_WEB_SERVICE APIs and hence, the certificates for these two endpoints will be required. Here they are:
| IdP | Endpoint | URL |
|---|---|---|
| Token | https://www.googleapis.com/oauth2/v4/token | |
| User Info | https://www.googleapis.com/oauth2/v2/userinfo | |
| Facebook* | Token | https://graph.facebook.com/v2.10/oauth/access_token |
| User Info | https://graph.facebook.com/v2.10/me | |
| Microsoft | Token | https://login.microsoftonline.com/common/oauth2/v2.0/token |
| User Info | https://graph.microsoft.com/v1.0/me |
* Based on the findings of Dimitri Gielis in this comprehensive blog post
Using the OpenSSL client, we can obtain the necessary certificates like we did before. However, I'd like to highlight an important issue that I had missed in the previous post, and that is how to obtain the root certificates.
During the certificate validation process, the full chain is needed to verify and trust the server certificate presented by the web service. Servers however, are not required to include the root certificate. Clients are expected to have those in the trust store and in fact, should ignore any root certificates that a server might send over the channel. Hence, when we attempted to retrieve the certificates for import into the Oracle Wallet, only the server and intermediate certificates were returned.
By importing only the intermediate certificates though, it looks like there's an implicit trust that the certificates were correctly signed and will validate even if you do not have the root certificates in the wallet. Always download these intermediate certificates with eyes wide open. The OpenSSL client does attempt to verify the certificates, so keep a look out for a message like this:
$ openssl s_client -connect sha1-intermediate.badssl.com:443 -showcerts
...
---
SSL handshake has read 1938 bytes and written 302 bytes
Verification error: unable to verify the first certificate
---
...
Or towards the end:
Verify return code: 21 (unable to verify the first certificate)
Assuming that these web services always transmit all the intermediate certificates, then we could reduce the amount of work by adding the root certificates to complete the trust chain. Based on the OpenSSL outputs, today, these are:
| IdP | Root Certificate | Source |
|---|---|---|
| GlobalSign (expected to change, see announcement) | ||
| DigiCert High Assurance EV Root CA | Digicert | |
| Microsoft | Baltimore CyberTrust Root | Digicert |
If you trusted Google enough, they maintain a collection of root certificates that contains all the certificates above. Adding all the root certificates in this file will probably allow access to nearly every legitimate and secure web service on the planet. Alternatively, if OpenSSL is installed, and it probably is, then these certificates can also be found in /etc/ssl/certs or /etc/pki/tls.
Helper Script
A simple Bash script to help populate an Oracle Wallet with bundled root certificates obtained from a reliable source.