Oracle APEX Social Sign-in Authentication Scheme

Recently I was involved in setting up a Social Sign-in Authentication Scheme, so despite the doom & gloom of this post, we are breaking some interesting rock.

I say 'involved', since I had the support of one of the nerdiest nerds in Perth, for all the server tinkering. I just had to paste in some URLs and other config settings within APEX.

He expected every error, and it always seemed just due process to the finish line. We had it all sorted within a couple of hours, and I was really doing other things while they configured SSL & wallets.

I can't really divulge all the details, since, you know, security, but I can certainly help describe the steps, to maybe help future tinkerers.

This post from Ciprian Iancu was a major reference for the team, though it wasn't entirely accurate for us. Details below.

<handwave> this is handwaving I'll use when I either don't fully understand what was done / is a security issue / just noted as a prompt. I'm the data/UX guy ;p
We also moved so quickly, I didn't have a chance to copy all the errors.

1 - SSL 

Our new Tomcat server needed to be running with SSL before Azure would accept a handshake.

<handwave> There is something they do that allows me to use https

2 - Wallet

<handwave>A wallet was configured on the database server. There is talk about ensuring the one wallet contains numerous certificates, since we now need it for a number of exchanges.

I entered the wallet's file location in the Internal workspace under Manage Instance -> Instance Settings -> Wallet.
file:/home/oracle/wallet

Without it, we get a warning regarding certificate failure.
At one point the permissions on the wallet file were wrong, which I saw by opening the login page in debug mode, then checking the debug log.

https://myhost:port/vbs/f?p=100::::YES::

You should see an entry starting with apex_authentication.callback

Debug example

3 - ACL

ACLs allow the database to communicate to certain hosts in the outside world. Ciprian's blog was helpful, but the ACLs weren't quite right. The ACL API has changed in 18c, the graph host was incorrect, and we only needed http, not connect.

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'graph.microsoft.net',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => l_user,
                        principal_type => xs_acl.ptype_db));

  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'login.microsoftonline.com',
    lower_port => 443,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => l_user,
                        principal_type => xs_acl.ptype_db));

4 - Web Credentials

It took a little while to know this is where we needed to define something prior to creating the authentication scheme, as noted in this bug description. Without it, we are unable to define the authentication scheme.

Web Credentials

<handwave> Mr Sysadmin set up a Client Secret somehow in server land.

I then pasted the long string under Application Builder -> Workspace Utilities -> Web Credentials.

5 - Create Authentication Scheme

I always find some strange satisfaction in setting up an authentication scheme, and to do one that integrates with Azure using contemporary methods was particularly gratifying.

First round of settings

The User Info Endpoint URL allows us to harvest more information from Azure. By default, a small set of attributes are returned in a JSON packet, but we can extend the attributes supply by extending the URL

https://graph.microsoft.com/v1.0/me/?$select=userPrincipalName,onPremisesSamAccountName,mail,officeLocation,department,displayName,givenName,jobTitle,mobilePhone,surname,id

Post-Authentication Attributes

I found an OTN post from Christian Neumueller describing exactly how to apply the apex_json package to get attributes listed in the settings - I just made some adjustments to make it look a little prettier, and add the fields we wanted to look at.

I'd really love to see examples like this in the inline help for attributes more often.
Here is what I used in the post-authentication procedural code:

:G_USER_INFO := 'Authenticated via Azure. '||chr(10)||
  '<br>Details from Graph:<br>'||
  '<table class="t-Report-report">'||
  '<tr><td class="t-Report-cell">id:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('id')||'</td></tr>'||
  '<tr><td class="t-Report-cell">userPrincipalName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('userPrincipalName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">onPremisesSamAccountName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('onPremisesSamAccountName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">mail:</td><td class="t-Report-cell"> '||apex_json.get_varchar2('mail')||'</td></tr>'||
  '<tr><td class="t-Report-cell">displayName:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('displayName')||'</td></tr>'||
  '<tr><td class="t-Report-cell">givenName: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('givenName')||'</td></tr>'    ||              
  '<tr><td class="t-Report-cell">surname: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('surname')||'</td></tr>'||
  '<tr><td class="t-Report-cell">officeLocation: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('officeLocation')||'</td></tr>'||
  '<tr><td class="t-Report-cell">department:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('department')||'</td></tr>'||
  '<tr><td class="t-Report-cell">jobTitle:</td><td class="t-Report-cell">  '||apex_json.get_varchar2('jobTitle')||'</td></tr>'||
  '<tr><td class="t-Report-cell">mobilePhone: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('mobilePhone')||'</td></tr>'||
  '<tr><td class="t-Report-cell">Access Token: </td><td class="t-Report-cell"> '||apex_json.get_varchar2('access_token')||'</td>'||
  '</table>';

I had a simple region on the home page to display the data.

Region on Home Page, showing G_USER_INFO

Then when we launch the application, we're redirected to login.microsoftonline.com; enter our Azure credentials, accompanied with the client's splash; then see our APEX page.

G_USER_INFO displayed at runtime

We were prompted to stay signed in, but I keep pressing No until we have a chance to experiment further.

APP_USER

APEX uses the Username Attribute to source the APP_USER variable. In our case the userPrincipalName returned an email address, but we wanted to match this up to our existing login IDs which use the WESLEYS style format.

We first tried using the onPremisesSamAccountName attribute, but that returned "wesleys"

Christian Neumueller once again provided an easy solution to ensuring our APP_USER returned the same uppercase value, by adding this to the post authentication process:

apex_custom_auth.set_user(p_user => upper(v('APP_USER')));

And in 19.1, there's already a declarative "Convert Username to Uppercase" attribute.

Logout URL

This was originally pointing to the application home page, which just re-authenticated me - which was quite handy as I refined the handling and temporary display of attributes from JSON.

We still need to experiment with authentication behaviours when it comes to remembering who you are, and logging out. For now, I know we can go to portal.office.com to log out of Azure.

No doubt there are other experiences & behaviours to refine as we move forward, but this is a nice step away from database accounts!

7 - Link Applications

This experiment was done on a fresh application, but I was able to use the principles behind sharing application authentication, to then create a button that linked to our main application - already authenticated.

I hear there is now a way to programmatically change the current authentication scheme, via a parameter. I'd like to experiment with that to see how it might change my answer when people ask if we can authenticate into the one application using two authentication schemes.

Conclusion

We had this hooked up within 2-3 hours. That can't be a normal experience, but it's still been a really good exercise in understanding how this technology works. Kind of.

It's also pretty cool that we have such declarative options for setting this up in our APEX applications.

If you want more examples to help get your configuration working, maybe filling in the blanks I left, see other posts by
- Dimitri - Social Sign-in with Google and Facebook
- Ciprian - Social Sign-in with Microsoft Azure
- Adrian - Social Sign-in without a wallet

- Adrian - Certificates
- Morten - Authentication with Microsoft
- Mahmoud - Forum solution with Google