Implementing Email OTP Verification After Login in Oracle APEX

Email-based One-Time Passwords (OTP) add an essential layer of security to your Oracle APEX applications. This tutorial will guide you through implementing a robust OTP verification system that triggers after a user logs in with their database credentials. We'll cover the complete workflow from generating the OTP to verifying user input.

To send an OTP (One-Time Password) via email after login with a custom database user in Oracle APEX, you'll need to follow these steps:

1. Set up email configuration in APEX
2. Create a function to generate OTP
3. Implement the authentication process using before authentication or post authentication processes

Here's how to implement this:

1. Configure Email Settings

First, ensure your APEX instance has email configured:

• Navigate to Administration > Instance Settings > Email Configuration
• Set up your SMTP settings

2. Create an OTP Generation Function

Create a PL/SQL function to generate OTPs:

CREATE OR REPLACE FUNCTION generate_otp RETURN VARCHAR2 IS
    v_otp VARCHAR2(6);
BEGIN
    -- Generate a 6-digit random number
    v_otp := LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 999999)), 6, '0');
    RETURN v_otp;
END generate_otp;
/

3. Create Table to Store OTPs

Create a table to store the OTPs with expiration times:

CREATE TABLE user_otps (
    user_id       VARCHAR2(255),
    otp           VARCHAR2(6),
    created_at    TIMESTAMP,
    expires_at    TIMESTAMP,
    is_verified   CHAR(1) DEFAULT 'N'
);

4. Implement the Authentication Process

1. In your existing user authentication procedure, add this PL/SQL code:

DECLARE
    v_otp           VARCHAR2(6);
    v_user_email    VARCHAR2(255);
BEGIN
    -- Get email from your users table
    SELECT email INTO v_user_email 
    FROM your_users_table 
    WHERE username = V('APP_USER');
    
    -- Generate OTP
    v_otp := generate_otp;
    
    -- Store OTP with expiration (e.g., 5 minutes)
    DELETE FROM user_otps WHERE user_id = V('APP_USER');
    
    INSERT INTO user_otps (
        user_id, 
        otp, 
        created_at, 
        expires_at
    ) VALUES (
        V('APP_USER'), 
        v_otp, 
        SYSTIMESTAMP, 
        SYSTIMESTAMP + INTERVAL '5' MINUTE
    );
    
    -- Send email with OTP
    APEX_MAIL.SEND(
        p_to        => v_user_email,
        p_from      => 'your-system@example.com',
        p_subj      => 'Your OTP Code for Login',
        p_body      => 'Your One-Time Password is: ' || v_otp || '. This code will expire in 5 minutes.',
        p_body_html => '<p>Your One-Time Password is: <strong>' || v_otp || '</strong></p><p>This code will expire in 5 minutes.</p>'
    );
    
    -- Push mail to mail queue
    APEX_MAIL.PUSH_QUEUE;
    
EXCEPTION
    WHEN OTHERS THEN
        -- Handle exceptions
     Raise;
END;

Below is the screenshot of the email the user will get:

5. Create the OTP Verification Page

Create a page (e.g., "OTP_VERIFICATION") with:

• An input field for the OTP
• A submit button
• A process to verify the OTP (after the login, you will redirect the user to this page)

DECLARE
    v_stored_otp    VARCHAR2(6);
    v_expiry        TIMESTAMP;
BEGIN
    -- Get stored OTP and expiry time
    SELECT otp, expires_at 
    INTO v_stored_otp, v_expiry
    FROM user_otps
    WHERE user_id = :APP_USER AND is_verified = 'N';
    
    -- Check if OTP matches and is not expired
    IF v_stored_otp = :P2_OTP AND v_expiry > SYSTIMESTAMP THEN
        -- Mark OTP as verified
        UPDATE user_otps 
        SET is_verified = 'Y' 
        WHERE user_id = :APP_USER AND otp = :P2_OTP;
        
        -- Redirect to home page or dashboard
        
    ELSE
        -- OTP invalid or expired
        apex_application.g_print_success_message := '<span style="color:red">Invalid or expired OTP. Please try again.</span>';
    END IF;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        apex_application.g_print_success_message := '<span style="color:red">Invalid or expired OTP. Please try again.</span>';
END;

6. Add Session Timeout and Security Measures

For better security:

1. Set reasonable session timeouts
2. Add maximum OTP attempts
3. Consider implementing rate limiting
4. Log failed attempts

This solution provides a complete flow for sending OTP via email after a successful login with custom DB authentication in Oracle APEX. The OTP expires after a set time for security, and the user must verify it before continuing to the application.

Conclusion

You've now implemented a secure two-factor authentication system using email OTP verification in your Oracle APEX application. This adds a significant security layer to your application by ensuring that even if user credentials are compromised, an attacker would still need access to the user's email to gain entry.