Tuesday, August 29, 2017

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 mtable.online.

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.


Jeffrey Kemp said...

Thanks for this - I've used a similar scheme for a long time but it's due for a renewal and you've given me some ideas.

Have you considered using per-user salts instead of a site-wide salt? This should reduce the value of your database of hashes to someone looking to brute-force people's passwords (which are often reused on multiple sites).

Refer https://stackoverflow.com/a/2999247/103295

LC said...

Great blog so far.

Was going to mention the same thing as Jeff. I use l_salt := dbms_crypto.randombytes(256); for every user password and store this in the database along side the hashed value of password||l_salt||username.

Appreciate you cant use crypto as mentioned but something as an alternative may suffice as long as its based on a Pseudo-Random Number Generator.

Dimitri Gielis said...

Hi Jeffrey and LC,

Thanks for your comments.

I've simplified the password algorithm in this project and typically use different algorithms per project/customer.
But your recommendation is absolutely correct.

Also for example for the password reset, in higher secure apps I let the ability to reset your password expire and use another table for that.

Thanks again,

Mark said...

Great posts Dimitri, really appreciate the view behind your scenes!
I really enjoy reading about how you do your work.

Quick note, I know its still in beta but thought you'd wanna know: for me mtable.online didnt work. When I tried www.mtable.online, it did work.

Trevis said...

That's awesome Dimitri!

Thanks for all time and effort in sharing your knowledge.

I know it's still beta but the link in the post end up in an URL like https://apexrnd-a481992.db.em2.oraclecloudapps.com/apex/f?p=MTABLE:HOME/ZbTRZ/ which doesn't work. But, when I change the URL to https://apexrnd-a481992.db.em2.oraclecloudapps.com/apex/f?p=MTABLE:HOME it works perfectly.

Thanks again.

Reybis Ceballos said...

Hi, Dimitri, this post is amazing! Great work!!

Btw did you have a good guide for session timeout with custom authentication using apex api.

Anonymous said...

APEX Gurus, we are looking at options to move our VB application to the 21st century. APEX is one option.

What we want to know is, Is APEX driven by Java??? Does APEX need a Java application server like Tomcat?

If APEX does not use Java for anything (any layer), how does APEX create dynamic web-pages, or how does APEX work, WITHOUT the use of Java??

Thanks, Rohan

Dimitri Gielis said...

Hi Rohan,

APEX doesn't need an application server like Java, the dynamic web pages are build in PL/SQL.
It's advisable to install ORDS, which is build in Java, but only serves to throttle the connections to the APEX engine inside the database (and also for REST webservices). And ORDS contains jetty, a webserver, so you don't need an other application server, but you could if you wanted.

Hope that explains,

Anonymous said...

Thanks Dimitri.

"It's advisable to install ORDS": This is a bit confusing.

Because I asked this same question from the Forum: https://community.oracle.com/thread/4079229

and one of the answers was "In order to fully utilise the current APEX feature set it is necessary to use Oracle REST Data Services (ORDS) running in a Java application server as the APEX web listener."

So, looks like APEX cannot be used WITHOUT the use of Java after-all???

Dimitri Gielis said...

If you want all the features of APEX you have to configure APEX with ORDS (which is written in Java) yes.
see http://docs.oracle.com/database/apex-5.1/HTMIG/installing-AE-and-configuring-Oracle-REST-data-services.htm#HTMIG29143

Is there any objection to run with Java? If it is you can go with two options as specified in the Oracle doc:
- the EPG configuration
but not recommended for production
- Apache + mod_plsql
The mod_plsql module included with Oracle HTTP Server 12.1.3 is fully supported and is tied to the overall Oracle HTTP Server support dates. Future versions of OHS don't support mod_plsql anymore.

So I stay with my statement that it's advisable to run with ORDS (and yes you need Java for that), but if for whatever reason you don't want Java, you have a few alternatives.


Anonymous said...

Thanks Dimitri.

TBH we are NOT fond of Java.

It's too heavy and with over 30 frameworks is not manageable or maintainable.

Java is OVERKILL (i.e. much more of something than is needed, resulting in less effectiveness).

Java requires you to devote more time on Java than the business requirements and other work at hand.

We are trying to eliminate Java from all our projects in all layers.

David Grimberg said...

Thanks Dimitri,

It's great to see good quality examples of how to do custom authentication in APEX. One thing you may want to look into for enhancing your authentication code is to use the apex_util.set_authentication_result and apex_util.set_custom_auth_status procedures to provide more details back to APEX about the success or failure of the authentication attempt. This is especially useful when authentication fails as they properly update the APEX_WORKSPACE_ACCESS_LOG view with the authentication results. Otherwise the access log typically indicates a successful authentication result even when the user failed to log in.

Properly updating the APEX authentication result can aid in auditing authentication issues.


Peter Ocelka said...

Thanks Dimitri for this blog series. It's really good for begginers.

You are saying in the video that you have two user roles - admin and user. But in the code admin role is 2, and user should be in 1,2. Shouldn't it be actually oposite? In my opinion user should have just one role, but admin both of them...

Dimitri Gielis said...

Hi Peter,

When somebody is an administrator, he has automatically the user role.
That is why the user is in 1,2.

Hope that clarifies,

Paul said...

Hi Dimitri,
This is great! I'm trying to use it to learn more and have the package and an application I am starting with.
What is the code behind the login button please? I'm new and just need a tiny bit more help please with the process but am excited to be starting with APEX having just a PL/SQL background I can see how powerful this is.
Kind wishes

Dimitri Gielis said...

Hi Paul,

See video around min 2:15 :)
It's basically the default login process that APEX creates itself.

Hope that helps,

Rotan said...

Hi Dimitri,
wish you a Happy New Year :)
I see that the function mtl_auth_pkg.custom_authenticate has 2 parameters .
But when we call it in the Apex cutom Authentication Page we do not pass the parameters (username, Password)
How are those values Passed to the function from the Login page.

Thank you.

Dimitri Gielis said...

Hi Rotan,

That is pre-defined by APEX - so you have to have those parameters as part of your definition.

Hope that explains,

Anonymous said...

Hi Dimitry
Happy new year 2018 to you.
Many thanks for your video.
I'm new to Oracle Apex so was wondering if I could somehow get the property definition of the sub region items . Eg P2_NO_ACCOUNT etc and where and how is .register_link and .login_link defined.

Any info on that will help heaps.
Many thanks

Michael Artenian said...

Hi Dimitry,

I have the entire login screen setup but am not able to commit new users to my table. I think because my table is not the same as yours. Would you be able to share the code to create the mtl_user table that you've setup for your application. My table consists of "ID, Email, Password, Verification Code, & Role ID". Im not sure what I am missing. When I click the register new user button nothing is committed to my table.

I would really appreciate your help.



Dimitri Gielis said...

) ;


before insert or update
for each row
if inserting then
:new.created := SYSDATE;
:new.created_by := NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := SYSDATE;
:new.updated_by := NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'),user);
:new.EMAIL := LOWER(:new.EMAIL);

Dimitri Gielis said...

Hi Russel,

The P2_NO_ACCOUNT is a static value:
No account yet? a href="javascript:///" class="register_link" >Register for free!</a

Shane said...

Hi Dimitri,

Thanks for this excellent post. I have implemented most of what you presented here, however I'm struggling a bit with the password reset functionality. I have the email working but it looks like you direct the user to a different page with the link that is sent in the email. I'd like to know how you implement the verify_reset_password and reset_password functions.

Again, thanks for this post

Anonymous said...

Hi Dimitri
I'm getting close to implementing this.
Can you tell me what is set in attributes of item P2_ACTION
For somehow I cannot get the reset passwd form to submit the email value.
I think its something to do with my properties of P2_ACTION.
For some reason its not getting the value of LOGIN or RESET i think.

Juan Carlos PĂ©rez said...

Hello Dimitri,
If you use your own authentication system, can users save private reports using IG? how?
Thank you very much and greetings
Juan Carlos

Anonymous said...

Hi Dimitri I sorted it out .
I had to set type Item = Value , LOGIN for the other 2 processes Clear page cache and set username cookie.

That seems to have done it.
Now I need to create the reset password page.
Thanks heaps for this post.


Hi Dimitri
I'm getting close to implementing this.
Can you tell me what is set in attributes of item P2_ACTION
For somehow I cannot get the reset passwd form to submit the email value.
I think its something to do with my properties of P2_ACTION.
For some reason its not getting the value of LOGIN or RESET i think.

Dimitri Gielis said...

Hi Juan,

sure people can save their own IG, it's not tight to the authentication system, but to the APEX user.
It's build-in, you just need to enable the ability to save Private reports.


Penwick said...
This comment has been removed by the author.