Create a Custom Authentication and Authorization Scheme in Oracle APEX

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Before when creating the APEX application based on a Blueprint, we used the standard APEX Authentication, which means the management of users is done in Oracle Application Express itself. As we created our own user table, we want to manage our users there and have some custom authentication scheme for our multiplication application.

In this post I will show you exactly how I built the custom authentication scheme for my Oracle APEX application you see at the https://www.apexrnd.be/ords/f?p=MTABLEmultiplication table app.

I first decided which kind of login page I wanted. A few years ago I blogged about pimping the logging page, which is what I use on the APEX Office Print login page. This type of page is a more traditional way of building a login page in Oracle APEX. For the registration page and forgot password page we used the same techniques.

For the multiplication table project I decided to do it a bit different. I started from the landing page and build the login mechanism into that page. But also the register and forgot password are on the same page, so not like what we did for APEX Office Print, using different pages for forgot password, register and login.

Here's how my page looks like in the Oracle APEX App Builder:

There are a few regions to help with the layout (Top, Left, Right). In the Right region, there are 3 sub-regions: Login, Register, Forgot Password, which will show one at a time. Dynamic Actions (Show/Hide) control which region is seen.

From the front-end this is what it looks like.

When clicking a button an APEX process is being fired, but all the logic is defined in a package.
The package to handle the authentication I typically call [project trigram]_AUTH_PKG. It doesn't only contain the authentication logic, but also the registration, reset password and authorization logic.

The specifications looks like this:

And the body like this:

I typically use dbms_crypto to generate (hash) the passwords, but as that package is not supported on Oracle Exadata Express at the time of writing, I use another SHA256 PL/SQL implementation.

I'm not going into too much detail on the logic in the PL/SQL package. I hope it's kinda self explanatory, but if you have any question, feel free to ask in the comments field.

Now we will focus on creating a Custom Authentication Scheme in APEX.

Go to App Builder > Shared Components > Authentication Schemes and hit the Create button to add a new one:

Enter the custom_authenticate procedure from the package we created earlier:

By default the new authentication scheme will be current, so make sure you have some data in your tables, otherwise you won't be able to login.

Next I typically add some post authentication to fill some Application Items.
Edit the Custom Authentication and add the code and post_auth as in this picture:

We have now made our application accessible to people by defining our own custom authentication scheme.

Next, we want to define which rights you have in the application. To do this, we will create two Authorization Schemes, one for a normal user and one for an administrator.

In our package we already included a function with the logic. Every user has a role defined to him, and depending the role, it's a normal user or an administrator. An administrator can do everything a normal user can do, but can also access the administrator section where we maintain our application.

Blueprint actually already created our Authorization scheme for administrators, but we will adapt it to use our package. Go to Shared Components > Authorization Schemes and modify like this:

I hope it gives you all the components to build your own custom authentication and authorization schemes.

I also recorded a video which goes in more detail on the entire process of signing up, forgetting password and logging in and the different authorization schemes and code being used.