Wednesday, June 06, 2018

Facebook, Google and Custom Authentication in the same Oracle APEX 18.1 app

Oracle APEX 18.1 has many new features, one of them is called Social Login.

On the World Cup 2018 Challenge, you can see the implementation of this new feature. The site allows you to sign-up or login with Facebook, Google, and your own email address.

It's even nicer that if you register with your email, but later decide to sign-up with Google or Facebook, it will recognize you as the same user if the email address is the same.

To get the Social Login to work I had to do the following...


To enable Facebook login in your own app, you first have to create an app on Facebook. Creating an application is straightforward by following the wizards, just make sure you create a website app.


To enable Google login in your own app, you first have to create a project on Google. Adrian did a really nice blog post which walks you through creating your project and setup Google authentication in your APEX application.

To hook-up Google and Facebook to our own APEX app, we have to let APEX know which credentials it should use, namely the info you find in the previous screenshots.

Web Credentials 

Go to App Builder > Workspace Utilities > All Workspace Utilities and click on the Web Credentials link

I added the Web Credentials for Facebook and Google. Web Credentials store the necessary info (Client ID = App ID and Client Secret = App Secret) of the OAuth2 authentication. OAuth2 is a standard these days which most sites are using to authenticate you as a user. Web Credentials are stored on Workspace Level so you can reuse those credentials in all the APEX apps in the same workspace.

Authentication Scheme 

We need to create the different authentication schemes. The Custom Authentication is to authenticate with email, next we have FACEBOOK, and GOOGLE (and Application Express Authentication which is there by default, but not used in this app).

Custom Authentication Scheme

I blogged before about Create a Custom Authentication and Authorization Scheme in Oracle APEX. The package I use in that blog post is pretty similar to the one of the World Cup app. In the Authentication Scheme, you define the authentication function. I also have a post-authentication procedure that sets some application items.

Facebook Authentication Scheme

Normally the authentication scheme of Facebook would look a bit different as Oracle APEX has built-in Facebook authentication, but for that to work, you need to load the SSL certificate in the Oracle wallet. On the platform the World Cup is running, the database is 12.1 and unfortunately, there's a bug in the database with multi-site or wildcard certificates (which Facebook has). So I had to workaround the issue, but I still used a new feature of APEX 18.1, instead of Facebook Authentication I used Generic OAuth2 Provider.

This is how it looks like:

As we are using the Generic OAuth2 Provider, we have to define the different OAuth URLs manually. When you look at my URLs they look a bit strange...

To get around the SSL issue I set up a reverse proxy in Apache which handles the SSL, so anytime the database does a call to http://apexrnd.localdomain it goes through the reverse proxy.
The reverse proxy in Apache is configured like this:

Note that in Oracle DB 12.2 and above the SSL bug is not there, so you don't need to do the above. I've been using the technique many times before if I don't want to deal with the SSL certificates and configuring the Oracle wallet. Adrian did a post about APEX Social Sign-In without a wallet, which might be of interest if you are on Oracle XE for example.

So what else is happening in the authentication scheme? You have to give the scope of what you want to get back from Facebook. In our case, we use the email as username and for additional attributes, we also want to know the first name, last name and the picture. It's really important you set those additional attributes, otherwise, APEX won't pass the full JSON through and takes a shortcut as it just needs the email.

The User info Endpoint URL is special:

Special thanks to Christian of the APEX Dev team, without his help, I wouldn't have figured that one out. Thanks again, Christian!

The next big bit is the post_authenticate procedure which contains the logic to map the Facebook user to the World Cup app user. If it finds the user, it will set some application items again, just like in the custom authentication, but if it doesn't find the user (the first time somebody connects through Facebook), it will create a World Cup user. The most important part of that logic is the part to get the name and picture. Here we parse the JSON the authentication scheme holds in memory.


And then the final bit you have to be careful with, that in the authentication scheme "Switch in Session" is set to "Enabled". This setting is the magic bit to have your APEX application multiple authentication schemes and be able to use one or the other.

Google Authentication Scheme

The Google authentication is simpler than the Facebook one, as we don't have to do the workaround for the certificate as Oracle understands the Google certificate. So here I use the standard APEX 18.1 feature to authenticate against Google. The username attribute is again the email, and the "additional user attribute" is "profile" as that holds the name and picture of the person.

The rest of the authentication scheme is very similar to the one of Facebook. Again don't forget to switch in session to enable.

Login buttons

To call the different authentication schemes on our login page we included different buttons:

The Login button is a normal Submit and will do the Custom Authentication as that is the default authentication (see - Current in Shared Components > Authentication Schemes).

The Facebook button has a Request defined in the link: APEX_AUTHENTICATION=FACEBOOK, this is the way that APEX let you switch authentication schemes on the fly. Very cool! :)

The Google button is similar, but then the request is APEX_AUTHENTICATION=GOOGLE
(note the name after the equal sign needs to be the same as your authentication scheme)

I hope by me showing how the Social Authentication of Oracle APEX 18.1 was implemented in the World Cup 2018 Challenge, it will help you to do the same in your own APEX application.

I really love this new feature of APEX 18.1. The implementation is very elegant, user-friendly and flexible enough to handle most of the OAuth2 authentications out there. Note that Facebook and Google upgrade their APIs to get user info, so depending on when you read this, things might have changed. Facebook is typically backward compatible for a long time, but know that the current implementation in APEX is for API v2.10 and the default Facebook authentication is v3.0. As far as I experienced, the user info didn't change between the API versions. I'll do another blog post how you can debug your authentication as it might help you get other info than the one I got for the World Cup app. Feel free to add a comment if you have any question.


smarj said...

I am getting this error when i am trying to login with google.

"Your session has expired
Click here to create a new session."

Please help me

smarj said...

I was able to resolve the error i reported earlier however I am facing this error now "API calls from the server require an appsecret_proof argument". If I disable the "Require App Secret" in FB apps then its working but not when it is enabled.
Suggest me some solution for this please.

Unknown said...

im getting the below error while applying the facebook authentication can you help?

error_backtrace: ORA-06512: at "APEX_180200.WWV_FLOW_WEB_SERVICES", line 1017 ORA-06512: at "APEX_180200.WWV_FLOW_WEB_SERVICES", line 1523 ORA-06512: at "APEX_180200.WWV_FLOW_WEBSERVICES_API", line 369 ORA-06512: at "APEX_180200.WWV_FLOW_AUTHENTICATION_NATIVE", line 452 ORA-06512: at "APEX_180200.WWV_FLOW_AUTHENTICATION_NATIVE", line 687 ORA-06512: at "APEX_180200.WWV_FLOW_AUTHENTICATION_NATIVE", line 1790 ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN", line 2907 ORA-06512: at "APEX_180200.WWV_FLOW_PLUGIN", line 3067 ORA-06512: at "APEX_180200.WWV_FLOW_AUTHENTICATION", line 1748 ORA-06512: at "APEX_180200.WWV_FLOW", line 3983

Mandy Walsh said...

Hi Dimitri!

Thanks so much for this post, it really helped!

I have nearly got it hanging together in my app, but can I have a peek at your CREATE_SITE_ACCOUNT procedure in your x_AUTH_PKG?

Mandy :)

Unknown said...

Thank you for a great documentation on the start on my project.

I have APEX 18.2, I'm using SSO authentication scheme however my team has a common google web-calendar which users all share. My goal is to have the fully functional google calendar as its home page. Following your documentation I've created the google credentials as the owner of the calendar. How can I create the calendar such that a user of the app can create, modify or delete google calendar events within APEX? It seems there are a couple of references I've found however they discuss APEX 5, and I believe this can be done more elegantly in version 18.2.

Dimitri Gielis said...

You can integrate with Google Calendar through the REST API.
APEX has great support these days for REST Webservices.

An alternative is to run an iframe or embed the Google calendar in your APEX page.

Hope that helps,

Bruno Amorim said...

Hi Dimitri,

I have a domain in Oracle Cloud Apex, and I used Google sig-in.

But when Google return de redirect uri, return the other domain (the domain default Oracle) -

In this case the message is "session expired".

Any tip for this case? I tried setup a redirect uri, but I don't find him.

Tks. Bruno

Franco Soldera said...

Thanks Dimitri, really great article!!

Unfortunately I am stuck like Bruno at the same point: same error and same redirection of the URI to the domain.

Any chance that we can get some help from you? ;-)

Phil Winfield said...

Great article Demitri,
Is this now possible on the Oracle Cloud instance given there is less ability to change webserver aspects in this environment please?

Thamaraiselvan said...

Dear Dimitri,

Great post, thanks for sharing this. Do we need any certificate to load into my instance for google sign-in if yes can you please share the certification link.

Thamaraiselvan M

Sunny said...

Hi Dimitri,
Thanks a lot for sharing this post.

Stirl said...

Hi Dimitri,
Thanks for this blog post I've followed it closely and its been very helpful.

It works fine if I'm logged into fb or google. However I have some strange behavior if not. I click on the FB button for instance and it takes me back into the app to a public page not logged in. I click a 2nd time and it takes me to the FB login page. Its the same with google. I'd expect if I'm not signed in to FB on the browser then it would take me straight to the FB loggon screen!

Do you have any idea what might be causing this? Is this the behavior you are getting?

Stirl said...

ahh you can cancel my last comment. I've worked it out I think. Its to do with the page the login button takes me too....I had a public page there.

I've fixed that issue. However the behaviour now is that if they have the url to any private page and they are taken to the login screen they will to taken to a standard first page once you have logged in that requires authentication. They won't continue to the page they had chosen which is a slight shame. I wanted there to be two links to different private pages available

olecramon said...

1. Should I retain the non-CDB setup when I upgrade my DB from 12.2 to 19c or move to a multitenant setup, since the non-CDB will be deprecated under 20c?
2. Should I upgrade the APEX version from existing 5.1 to 20.1 or just install 20.1? Should I do the APEX upgrade before the DB upgrade?
Should I install APEX into one of the 2 PDBs.
Right now our APEX software resides outside the Oracle Home directory. We have 2 database instances in the server.
3. Can I still use the embedded PL/SQL gateway under APEX version 20.1 and higher?
4. What are the practical advantages of using ORDS with APEX?

Anonymous said...

I've put a question on stackoverflow that relates to this method of setting up a social login....Dimtri please have a look if you have time!

adnansaleem said...

Hi Dimitri,

Thank you for sharing such a great peice of knowledge. I tried the steps but unable to do that redirecting thing because I am using free Oracle Cloud with APEX. After pressing the login with FB button it shows me “null user name” passed after authentication with FB. Can you please help?

Thank you once again.

Anonymous said...

Hi Dimitri,
Can you log out of Facebook with the latest version of APEX? I'm having a lot of trouble trying to capture the token, I really need help with this please Dimitri.

Anonymous said...

Hi Dimitri, could you explain how to capture the facebook token and close the session using Apex in its latest version? I am having problems, I get that the login works correctly but when I try to close the session in facebook it does not work, I have a DA with the function in JS for the logout but it tells me that it is executing a null value: Dynamic Action Fired: null (NATIVE_JAVASCRIPT_CODE)

please help !

sanket said...

Thanks for sharing this post with us.Wikivela

Anonymous said...

Simply can't get this to work. There are numerous Blogs out there and using them all, there seems to be a flaw in each one.

Can Oracle or more likely you, Demitri, please do this in a step by step manner for the latest APEX please?

Or better still, release a template App we can use and just configure the web credentials and one or two other settings to get this working maybe?

Many thanks if you can help as I am sure there are plenty of us not using this far superior way of authenticating

Hervé said...

I am having an issue with this implementation. Logging out of Apex does not return me to Apex login page. How to fix it?