Skip to main content

Build Two Walls

· 4 min read
Adrian Png

1280 720 The Great Wall of China

Authentication is an essential component of any enterprise application. These days though, it isn't enough to protect your applications with only a username and password. These are easily stolen by key loggers, sniffing non-encrypted HTTP traffic, phishing, hacked Internet services and the list goes on. To make matters worse, many users have a poor habit of reusing passwords that thieves then use to penetrate other systems. I have had my fair share of that experience.

The question is, can we implement two-factor authentication (TFA) in Oracle Application Express (APEX)?

Yes we can!

The Code

Right around the time when I grew interested in implementing the Time-based One-time Password (TOTP) algorithm, I stumbled upon code written by Rabbit on the Oracle Community Forums, only months earlier. Hurrah! I didn't have to deal with the mathematical intricacies of implementing the algorithm in PL/SQL code.

After discussing with Martin D'Souza, we decided that the best way to share this useful algorithm was through OraOpenSource oos-utils. All it needed was some additional functionality to make it useful for implementing a custom authentication scheme with TFA support in APEX. We also swapped out the dbms_crypto requirement and used Anton Scheffer's oos_util_crypto library to perform the HMAC hashing instead. This means, the code should be usable in your workspace on

What was missing in the release though, was a demo application to showcase its use, so two years later, here it is!


The following are a few key points about the implementation that I thought might be of interest.


The registration does not automatically "turn on" TFA for new accounts. The new user is expected to login to the system and activate the TFA protection. During registration, a random and unique key was generated for the account. Whenever TFA is activated, a QR code is generated using the function oos_util_totp.format_key_uri, user's "shared secret" and a suitable, trusted QR code generation JavaScript library. This allows the user to setup the security application and generate one-time passwords (OTP). The user must validate a OTP before the feature is enabled for the account.


The authentication process will allow users to authenticate with or without OTP validation depending on the user's preference. The original (default) login page 9999 was modified to support both authentication workflows.

The Login process typically calls apex_authentication.login that triggers a sequence of events listed in the official APEX documentation. The process calls an authentication function that matches usernames and passwords. If the function returns true, then the APEX engine will, in a nutshell, set the username for the session and redirect to the requested resource. This function call was replaced with a procedure, pkg_tfa_apex.p_authenticate_user.

if l_password_hash = f_hash_password(p_password => p_password) then
if l_tfa_enabled = 0 then
p_username => p_username
, p_password => null
end if;
raise login_failed;
end if;

If TFA is not enabled, then apex_authentication.post_login is called. We have already established that the username and password are correct, so this allows us to bypass the first two steps in a typical authentication sequence.

Usually, the default login page has a final process that clears the session state for the page, but that was removed. A branch to a second-step login page (10000) was added. It passes the username entered on page 9999 to the the next page for OTP validation. The username is used to retrieve the user's shared secret that is is required by oos_util_totp.validate_otp. Hence, I opted to call apex_util.clear_page_cache only if TFA was turned off.

OTP Validation

The login process redirects to a second login page that requires the user to enter the OTP from a "registered" security app. This is verified (must match with what the system generates) using a page validation. If that checks out, then we will proceed to call the apex_authentication.post_login procedure and complete the login process.


As always, security is an important issue in any software application we build. I am always learning, so please drop me a note if you spot anything wrong with the approach, or have suggestions for improvement.

Thanks for reading!