OTP based Authentication in Oracle APEX using Twilio

Recently, a new requirement came up to implement One Time Password (OTP) based authentication in Oracle APEX. In this, the user will enter his mobile number on the login page and will receive an OTP via SMS. The user will then enter the OTP on the login page and be able to authenticate himself in the APEX app. 

When I started working on this requirement, first I identified all the steps that I need to implement for the solution. 

Below are the steps that I followed:

1. Generate the OTP and temporarily store it in a database table.
2. Send the generated OTP to the user by integrating REST API to send the SMS
3. Validate the user entered OTP against the OTP that is stored in the table and authenticate the user.
4. Create a Custom Authentication Scheme.
5. Modify the login page.

Let's understand each step in more detail.

Step 1:  Generate the OTP and temporarily store it in a database table. 

First, create a table to temporarily store the OTP for the validation. Here I created a simple table with two columns: mobile, and OTP. You can also create a normal USER table and capture all the details of the users in that table along with a generated OTP. 
CREATE TABLE OTP_AUTH(MOBILE   VARCHAR2(10), 
                      OTP      VARCHAR2(500));
Next, generate a six-digit random number and store it in the above table. I created a package PKG_OTP_AUTH (I provided the complete code at the end of this post). This package has a generate_otp procedure to generate the OTP for sign-in. This procedure takes mobile as the input parameter and returns OTP as out parameter. It will generate a random six-digit number using  DBMS_RAMDOM package and store the generated value in the OTP_AUTH table in encrypted form. The encrypt_string function is used to encrypt the generated OTP.    
CREATE OR REPLACE PACKAGE BODY pkg_otp_auth IS

  -------------------------------------------------------------------------
    -- Module Name: encrypt_string
    -- Parameters: p_string
    --             
    -- Purpose: This function is used in to encrypt the input string
    --         
    -- 
  -------------------------------------------------------------------------  
  FUNCTION encrypt_string(p_string VARCHAR2) RETURN VARCHAR2
  IS
    L_encrypted_str VARCHAR2(255);
    L_salt          VARCHAR2(100) := '2345USFGOJN2T3HW89EFGOBN23R5SDFGAKL';
  BEGIN
    --
    -- The following encryptes the input string using a salt string and the DBMS_CRYPTO.
    -- This is a one-way encryption
    -- 
     L_encrypted_str := dbms_crypto.hash
                        (dbms_crypto.hash
                            (utl_raw.cast_to_raw(SUBSTR(L_salt,1,15)||p_string||SUBSTR(L_salt,16)),
                             3
                             ),
                             3
                         );
     RETURN L_encrypted_str;
     
  END encrypt_string;

   -------------------------------------------------------------------------
    -- Module Name: generate_otp
    -- Parameters: p_mobile : mobile number of the user
    --             p_otp    : one time password (OTP)
    --
    -- Purpose: This procedure is used to generate the OTP for sign in
    --          
  -------------------------------------------------------------------------
  PROCEDURE generate_otp (p_mobile IN  VARCHAR2,
                          p_otp    OUT NUMBER) IS

    L_msg_body VARCHAR2(255);
    L_otp      NUMBER;
  
  BEGIN
    --
    -- Generate OTP
    --
    L_otp := trunc(dbms_random.value(0,999999),0);
    
    --
    -- store generated OTP in OTP_AUTH table
    --
    INSERT INTO OTP_AUTH (mobile,
                          otp)
                 VALUES ( p_mobile,
                          encrypt_string(L_otp)
                        );  
    COMMIT;
    p_otp := L_otp;

  END generate_otp;

Step 2:  Send the generated OTP to the user by integrating REST API to send the SMS.

To send the generated OTP to the user via SMS, it is required to integrate the REST API provided by the SMS service provider. Here, I used Twilio to send SMS. Twilio provides a very good service to send SMS across the world and it also provides free credit for testing. To integrate the Twilio API, Account SID, Auth Token, and Twilio Phone Number are required. Here is a short video to set up a Twilio Account. 

After setting up Twilio Account navigate to the Twilio console and get the Account SID, Auth Token, and Twilio Phone Number. Now before integrating the API in APEX first, it is always best practice to test the API using the Postman.
After testing the API using Postman, it's time to integrate Twilio with the APEX app. To invoke an API and send the SMS from the APEX app, I created a packaged procedure send_otp in PKG_OTP_AUTH package.
-------------------------------------------------------------------------
    -- Module Name: send_otp
    -- Parameters: p_mobile : Mobile of the user
    --             p_otp    : One Time Password
    --       
    -- Purpose: This procedure is used to send the OTP for sign in using Twilio REST API
    -- 
  -------------------------------------------------------------------------
  PROCEDURE send_otp (p_mobile IN VARCHAR2,
                      p_otp    IN NUMBER )
  IS
    L_api_url     VARCHAR2(100) := 'https://api.twilio.com/2010-04-01/Accounts/#ACCOUNT_SID#/Messages';
    L_account_sid VARCHAR2(100) := '#Add Twilio Account SID here#';
    L_password    VARCHAR2(100) := '#Add Twilio Auth Token here#';
    L_twillio_num VARCHAR2(20)  := '#Add Twilio phone number here#';
    L_msg_body    VARCHAR2(100);
    L_response    CLOB;                 
  BEGIN
   --
   -- Construct message body
   --
   L_msg_body := p_otp||' is your OTP to login into an APEX app';
   --
   -- Build endpoint URL
   --
   L_api_url := REPLACE(L_api_url,'#ACCOUNT_SID#',L_account_sid);
   --
   -- Set request header
   --
   apex_web_service.g_request_headers.delete();

   apex_web_service.g_request_headers(1).name  := 'Content-Type';
   apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded';
   --
   -- Make webservice call
   -- 
   L_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
                    p_url         => L_api_url,
                    p_http_method => 'POST',
                    p_username    => L_account_sid,
                    p_password    => L_password,
                    p_parm_name   => apex_util.string_to_table('To:From:Body'),
                    p_parm_value  => apex_util.string_to_table('+91'||p_mobile||':'||L_twillio_num||':'||L_msg_body||'')
                    ); 
  END send_otp; 

Step 3:  Validate the user entered OTP against the OTP that is stored in the table to authenticate the user.

Create an authentication function to validate the user entered OTP with the one that is stored in the database table. I created a user_login which compares the OTP and return a Boolean value. Use this function as an Authentication Function in the custom authentication scheme. 
-------------------------------------------------------------------------
    -- Module Name: user_login
    -- Parameters: p_username : Mobile number of the user
    --             p_password : OTP
    -- Purpose: This function is used in the custom authentication scheme to authenticate
    --          the user
    -- 
  -------------------------------------------------------------------------
  FUNCTION user_login(p_username IN VARCHAR2, 
                      p_password IN VARCHAR2 ) RETURN BOOLEAN
  IS
    L_pwd           lms_users.password%type;
    L_is_active     lms_users.is_active%type;
    L_user_otp      lms_users.password%type;
    L_otp           lms_users.otp%type;
    e_inactive_user EXCEPTION;
  BEGIN   
       --
       -- get the otp from OTP_AUTH table
       --
       SELECT otp
         INTO L_otp
         FROM otp_auth
        WHERE mobile = p_username ;
        --
        -- encrypt user entered otp to match with the stored otp   
        --
        L_user_otp := encrypt_string(p_string => p_password);
        
        IF L_otp = L_user_otp THEN
           RETURN TRUE;
        ELSE
           RETURN FALSE;
        END IF; 
        
    EXCEPTION
        WHEN OTHERS THEN
            RETURN FALSE; 
  END user_login;

END pkg_otp_auth;

Step 4:  Create a Custom Authentication Scheme.

Create a custom authentication scheme and use user_login function created in step 4 as a authentication function.
Also, add post_authentication procedure to delete the record from the OTP_AUTH table after successful authentication.
Step 5: Modify the login page

In this final step, I did few changes on the login page. Instead of directly displaying Username and Password field on the login page, I created two regions two display username and password field separately for the better UI experience. I also created a button GET_OPT to request an OTP as described in the below image. 
I created a dynamic action on page load to hide the second region containing the OTP field so that when user first navigate to login page, a field to enter mobile number and a button to request OTP will display as below.
For GET_OTP button, I set the Action to Defined by Dynamic Action and created dynamic action "Request OTP" to generate and send OTP to mobile number entered by the User. Under this dynamic action, I created three true Actions. The first action is Execute Server Side code to invoke the procedures that I created earlier to generate and send OTP. The second action is to hide the first region which contains username field and GET_OTP button. The third action is to show the second region to display OTP field and Sign In Button.
Code for Dynamic Action:
DECLARE
    L_otp NUMBER;
BEGIN
 
 pkg_otp_auth.generate_otp (p_mobile => :P9999_USERNAME,
                            p_otp    => L_otp); 
                            
 pkg_otp_auth.send_otp (p_mobile  => :P9999_USERNAME,
                         p_otp    => L_otp );

END;
That's all. Now when user will enter mobile number and click on Request OTP button, user will receive a SMS for OTP. 
User will then enter the OTP in the OTP field and click Sign In button to login into application.

Code for the PKG_OTP_AUTH package. Replace the Account SID, Auth Token and Twilio Phone Number on line 76 to 78.

CREATE OR REPLACE PACKAGE pkg_otp_auth IS

  -------------------------------------------------------------------------
    -- Module Name: encrypt_string
    -- Parameters: p_string
    --             
    -- Purpose: This function is used in to encrypt the input string
    --         
    -- 
  -------------------------------------------------------------------------  
  FUNCTION encrypt_string(p_string VARCHAR2) RETURN VARCHAR2;
  
  -------------------------------------------------------------------------
    -- Module Name: generate_otp
    -- Parameters: p_mobile : mobile number of the user
    --             p_otp    : one time password (OTP)
    --
    -- Purpose: This procedure is used to generate the OTP for sign in
    --          
  -------------------------------------------------------------------------
  PROCEDURE generate_otp (p_mobile IN  VARCHAR2,
                          p_otp    OUT NUMBER); 
                          
  -------------------------------------------------------------------------
    -- Module Name: send_otp
    -- Parameters: p_mobile : Mobile of the user
    --             p_otp    : One Time Password
    --       
    -- Purpose: This procedure is used to send the OTP for sign in uing Twilio REST API
    -- 
  -------------------------------------------------------------------------
  PROCEDURE send_otp (p_mobile IN VARCHAR2,
                      p_otp    IN NUMBER );
                      
  -------------------------------------------------------------------------
    -- Module Name: user_login
    -- Parameters: p_username : Mobile number of the user
    --             p_password : OTP
    -- Purpose: This function is used in the custom authentication scheme to authenticate
    --          the user
    -- 
  -------------------------------------------------------------------------
  FUNCTION user_login(p_username IN VARCHAR2, 
                      p_password IN VARCHAR2 ) RETURN BOOLEAN;

END pkg_otp_auth;  
CREATE OR REPLACE PACKAGE BODY pkg_otp_auth IS

  -------------------------------------------------------------------------
    -- Module Name: encrypt_string
    -- Parameters: p_string
    --             
    -- Purpose: This function is used in to encrypt the input string
    --         
    -- 
  -------------------------------------------------------------------------  
  FUNCTION encrypt_string(p_string VARCHAR2) RETURN VARCHAR2
  IS
    L_encrypted_str VARCHAR2(255);
    L_salt          VARCHAR2(100) := '2345USFGOJN2T3HW89EFGOBN23R5SDFGAKL';
  BEGIN
    --
    -- The following encryptes the input string using a salt string and the DBMS_CRYPTO.
    -- This is a one-way encryption
    -- 
     L_encrypted_str := dbms_crypto.hash
                        (dbms_crypto.hash
                            (utl_raw.cast_to_raw(SUBSTR(L_salt,1,15)||p_string||SUBSTR(L_salt,16)),
                             3
                             ),
                             3
                         );
     RETURN L_encrypted_str;
     
  END encrypt_string;

   -------------------------------------------------------------------------
    -- Module Name: generate_otp
    -- Parameters: p_mobile : mobile number of the user
    --             p_otp    : one time password (OTP)
    --
    -- Purpose: This procedure is used to generate the OTP for sign in
    --          
  -------------------------------------------------------------------------
  PROCEDURE generate_otp (p_mobile IN  VARCHAR2,
                          p_otp    OUT NUMBER) IS

    L_msg_body VARCHAR2(255);
    L_otp      NUMBER;
  
  BEGIN
    --
    -- Generate OTP
    --
    L_otp := trunc(dbms_random.value(0,999999),0);
    
    --
    -- store generated OTP in OTP_AUTH table
    --
    INSERT INTO OTP_AUTH (mobile,
                          otp)
                 VALUES ( p_mobile,
                          encrypt_string(L_otp)
                        );  
    COMMIT;
    p_otp := L_otp;

  END generate_otp;
  
   -------------------------------------------------------------------------
    -- Module Name: send_otp
    -- Parameters: p_mobile : Mobile of the user
    --             p_otp    : One Time Password
    --       
    -- Purpose: This procedure is used to send the OTP for sign in using Twilio REST API
    -- 
  -------------------------------------------------------------------------
  PROCEDURE send_otp (p_mobile IN VARCHAR2,
                      p_otp    IN NUMBER )
  IS
    L_api_url     VARCHAR2(100) := 'https://api.twilio.com/2010-04-01/Accounts/#ACCOUNT_SID#/Messages';
    L_account_sid VARCHAR2(100) := '#Add Twilio Account SID here#';
    L_password    VARCHAR2(100) := '#Add Twilio Auth Token here#';
    L_twillio_num VARCHAR2(20)  := '#Add Twilio phone number here#';
    L_msg_body    VARCHAR2(100);
    L_response    CLOB;                 
  BEGIN
   --
   -- Construct message body
   --
   L_msg_body := p_otp||' is your OTP to login into an APEX app';
   --
   -- Build endpoint URL
   --
   L_api_url := REPLACE(L_api_url,'#ACCOUNT_SID#',L_account_sid);
   --
   -- Set request header
   --
   apex_web_service.g_request_headers.delete();

   apex_web_service.g_request_headers(1).name  := 'Content-Type';
   apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded';
   --
   -- Make webservice call
   -- 
   L_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
                    p_url         => L_api_url,
                    p_http_method => 'POST',
                    p_username    => L_account_sid,
                    p_password    => L_password,
                    p_parm_name   => apex_util.string_to_table('To:From:Body'),
                    p_parm_value  => apex_util.string_to_table('+91'||p_mobile||':'||L_twillio_num||':'||L_msg_body||'')
                    ); 
  END send_otp;
  
  -------------------------------------------------------------------------
    -- Module Name: user_login
    -- Parameters: p_username : Mobile number of the user
    --             p_password : OTP
    -- Purpose: This function is used in the custom authentication scheme to authenticate
    --          the user
    -- 
  -------------------------------------------------------------------------
  FUNCTION user_login(p_username IN VARCHAR2, 
                      p_password IN VARCHAR2 ) RETURN BOOLEAN
  IS
    L_pwd           lms_users.password%type;
    L_is_active     lms_users.is_active%type;
    L_user_otp      lms_users.password%type;
    L_otp           lms_users.otp%type;
    e_inactive_user EXCEPTION;
  BEGIN   
       --
       -- get the otp from OTP_AUTH table
       --
       SELECT otp
         INTO L_otp
         FROM otp_auth
        WHERE mobile = p_username ;
        --
        -- encrypt user entered otp to match with the stored otp   
        --
        L_user_otp := encrypt_string(p_string => p_password);
        
        IF L_otp = L_user_otp THEN
           RETURN TRUE;
        ELSE
           RETURN FALSE;
        END IF; 
        
    EXCEPTION
        WHEN OTHERS THEN
            RETURN FALSE; 
  END user_login;

END pkg_otp_auth;

Comments

  1. JochenMay 13, 2022 at 2:33 PM

    Thanks for sharing your findings Rutvik. Very insightful !

    ReplyDelete
  2. Pataki DávidJune 12, 2022 at 10:59 PM

    I am very grateful for this article. With your help I was able to send my very first SMS from my APEX application. Thank you again.

    ReplyDelete
    Replies
    1. Rutvik PrajapatiJuly 21, 2022 at 4:59 AM

      Thank you Pataki and I am glad that you find this helpful.

      Delete

Post a Comment

Popular posts from this blog

Sorting Order in Oracle APEX Classic Report

Image
In APEX, the sorting option for a classic report is divided into two aspects:  How records will be sorted when the report first renders? Whether the end-user can change the sort order interactively by clicking the column header. A classic report can be developed using either the static query or the dynamic query and the sorting behavior is different for both options. First, we will see the sorting behavior for the report based on the static query.  The below classic report is based on the static query and displays all the records of the EMP_DUP table. For this blog, I created EMP_DUP which is the same as the EMP table but contains duplicate records. The sorting aspects for this report are determined by the properties in each column's  sorting  section. The below image shows the  sorting  section for  JOB. The Default Sequence  property determines the initial sort order. Each column that participates in the initial sort order will have a unique sequence number that will determine
Read more

Multi-select List Item in Oracle APEX | Checkbox Group

Image
In my previous post , I explained how to use Shuttle Item. In this post, I will explain how to use another multi-select item Checkbox Group.  In Oracle APEX, Checkbox Group displays multiple values as checkboxes and enables end-user to select multiple values. Like the Shuttle item, the Checkbox Group also stores values of checked boxes in a colon-delimited string. We need to provide a list of values for values displayed as checkboxes.   How to create a Checkbox Group?  Create a VARCHAR2 column having a large size as it stores a colon-delimited string. Navigate to an APEX page and create a page item in a region that is based on the database table. Change its type to "Checkbox Group". Go to the "List of Values" section in the property section and select the list of values type. Here I selected "Static Values".  Click on the "Static Values" and a popup will open. Add Display Value and Return Value in this popup as shown below.  Enter the number of
Read more