August 21st, 2024

Implementing TOTP-Authenticator to Oracle Apex Apps


Hi all,

As my first post, I chose Time-based one-time password(TOTP) implementation I recently completed. For me, understanding the logic of how TOTP works was more time consuming than the implementation itself.

First, I want to shortly mention what TOTP is. It is one of the security measures where user needs to enter (mostly) 6-digit token retrieved from any Authenticator app. 

There are several apps offered on the mobile app market places (very popular ones might be Google Authenticator, 2FA Authenticator, Microsoft Authenticator etc.). But the logic behind is basically the same: providing a time based token based on another token so-called "shared secret" to let users authenticate themselves against the system.

In my case, the flow is pretty simple:


This is the high-level overview of the logic, now let's break it down:

1. When a new user is created, a shared secret is generated and stored for the user:

In my application, I already had a basic User Management logic implemented. There is a table called APEX_USERS with the necessary columns like email, user_name, encrypted_pwd. 

First step is to extend this table with 2 new columns: mfa_enabled(varchar2) and shared_secret(raw):

Since we have columns that stores the shared secret, we need a function that generates the secret key:

Now we need to call this function to store it in our users table. I used my user table's trigger to do so:

Notice here that I have another Multi-factor authentication type in my system so I needed to make sure the secret is only generated when MFA type is Authenticator App.

Another point is, when user gets updated, I am only generating the secret if it is not present yet. By doing so, I make sure the secret (therefore the registry in user's authenticator app) stays the same when user updates address, language etc.

Now we can go a step further:

2. This shared secret is sent to the user as text and QR code.

We have implemented the logic of secret  code is generated and stored in our users table. Now we should send this code to user so s/he can register it in a authenticator app.

For that, we need to implement a procedure and call this procedure when user is created in Apex application. 

Let's start with the procedure:

Now we can create a page process in our Apex Page to call this procedure. In the Apex page where a new user is created, it is a pretty simple page process of type "Execute Code" and "PL/SQL code":

As a result, user should receive an email similar to:

3. Based on this secret, a new entity within the Authenticator app is created on the user's device.

Scanning the QR code in a authenticator app, or entering the secret key manually will register a record in the authenticator app:

4. A new step after password confirmation is implemented that it asks user for the TOTP.

Now, it is time check if the user has TOTP enabled when logging in. For that, on my Login page, I have added a new Branch "GO_TO_MFA" with a server side condition of:

Also don't forget to add/edit your usual login process a server side condition contradicting that if user is using totp_enabled='Y' and mfa_type='APP', the process should not work and authenticate user.

This branch we created, should redirect to a new page where user can enter the token. In this page, a page item P9998_TOKEN shall allow users to enter the value they get from the authenticator app. Then, we should have the same process calling the existing authentication function of the application on this page: 

with the server side condition of:

5. A function that compares the user's input and the Valid code(Valid code is actually generated by a function using current time and shared secret we already have).

Finally, the last step: the implementation of the function f_check_totp. The logic of this function is to get 2 input parameters: username and the token user entered. By the username, we will get the shared_secret value from users table, and generate a token by the current time. Then we will compare this generated token and the token user entered. If they match, the function will return true. Pretty simple, isn't it:

And lastly, we need this helper function SECRET_TO_TOTP:

With that, we have implemented a basic Multi-factor Authentication to our app: TOTP. Of course,  there is room for improvement like the shared secret we store in the users table shall be encrypted and decrypted. But in my case, the app is not publicly available and encryption was not needed. 

I hope you find this post helpful. When I was implementing this functionality, I also followed some tutorials. You might also want to check it as a different approach and ideas.