DUO Two Factor Authentication Using PL/SQL

This tutorial will teach you how to integrate DUO Two Factor Authentication using PL/SQL in the Oracle database.

What is Two Factor Authentication?

Two-factor authentication (2FA) is a method by which users can verify their identity by using two different forms of identification, such as a password and an additional piece of information. The idea behind 2FA is to protect users from attackers who may be able to steal their account credentials by impersonating them.

One of the most common ways attackers gain access to accounts is by stealing a user's login credentials. In this situation, the attacker would be able to log in to the account with the stolen credentials and have immediate access to all of their data. To combat this threat, 2FA provides users with an additional layer of security by requiring them to confirm their identity with both a password and a one-time code or push notification.

What is DUO?

Duo security is a type of multi-factor authentication that combines two different types of authentication methods. The first type of authentication is something you know, like a password or PIN. The second type is something you have, like a key fob or a smartphone app. When you use Duo security, both of these types of authentication are required to access your account.

Using Duo security helps to prevent unauthorized users from accessing your account and helps to increase the overall security of your account. There are two main ways to use Duo security: on your device and on the website. When you set up Duo security on your device, you need to pair your phone with the app in order for it to work.

Integrating DUO Two Factor Authentication Using PL/SQL

To integrate DUO two-factor authentication using PL/SQL, you need the following three things from the DUO account configured for the application security:

Host (api-xxxxxxx.duosecurity.com)
Secret Key
Integration Key

Also, make sure that all users are configured/enrolled in DUO for their authentication. The users could be the Oracle users or the usernames stored in a table for custom authentication.

When the above two things are ready, you will be able to create a program in PL/SQL for authentication.

To check if the DUO Web API is working with the Hostname and secret keys provided, create and run the following PL/SQL program:

Change the values with your DUO and database values wherever the changeme string appears.

DECLARE
  l_Http_Request          Utl_Http.Req;
  l_Request_Body          CLOB;
  l_Request_Body_Length   NUMBER;
  l_Req_Body              CLOB;
  l_Http_Response         Utl_Http.Resp;
  l_Response_Header_Name  VARCHAR2(256);
  l_Response_Header_Value VARCHAR2(1024);
  l_Response_Body         VARCHAR2(32767);

  d_Date   VARCHAR2(100);
  v_Method VARCHAR2(100);
  v_Host   VARCHAR2(100);
  v_Path   VARCHAR2(200);
  v_Skey   VARCHAR2(1000);
  v_Ikey   VARCHAR2(1000);

  r_Raw RAW(1000);
  v_Hex VARCHAR2(4000);

  v_Auth  VARCHAR2(4000);
  v_Canon VARCHAR2(1000);
  e_Error EXCEPTION;
BEGIN

  v_Method := 'GET';
  v_Host   := 'api-changeme.duosecurity.com';
  v_Path   := '/auth/v2/check';
  v_Skey   := 'changeme';
  v_Ikey   := 'changeme';

  d_Date := To_Char(CAST(Systimestamp At TIME ZONE 'gmt' AS DATE),
                    'Dy, dd Mon yyyy hh24:mi:ss') || ' GMT';

  v_Canon := d_Date || Chr(10) || v_Method || Chr(10) || v_Host || Chr(10) ||
             v_Path || Chr(10);

  Dbms_Output.Put_Line(v_Canon);

  -- convert to sha1
  r_Raw := Dbms_Crypto.Mac(Utl_I18n.String_To_Raw(v_Canon,
                                                  'utf8'),
                           Dbms_Crypto.Hmac_Sh1,
                           Utl_I18n.String_To_Raw(v_Skey,
                                                  'utf8'));

  Dbms_Output.Put_Line('raw: ' || r_Raw);

  -- CONVERT TO HEX

  SELECT Rawtohex(r_Raw) INTO v_Hex FROM Dual;

  Dbms_Output.Put_Line('hex: ' || v_Hex);

  -- dbms_output.put_line('raw '||UTL_ENCODE.BASE64_ENCODE(r_raw));

  Utl_Http.Set_Wallet(Path     => 'file:changeme',
                      Password => 'changeme');

  l_Http_Request := Utl_Http.Begin_Request(Url          => 'https://'||v_host||v_path,
                                           Method       => v_method,
                                           Http_Version => 'HTTP/1.1');

  v_Auth := REPLACE(Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_I18n.String_To_Raw(v_Ikey || ':' ||
                                                                                             v_Hex,
                                                                                             'utf8'))),
                    Utl_Tcp.Crlf,
                    '');

  Dbms_Output.Put_Line(v_Auth);

  Utl_Http.Set_Header(l_Http_Request,
                      'Host',
                      v_Host);

  Utl_Http.Set_Header(l_Http_Request,
                      'Authorization',
                      'Basic ' || v_Auth);

  Utl_Http.Set_Header(l_Http_Request,
                      'X-Duo-Date',
                      d_Date);

  Utl_Http.Set_Header(l_Http_Request,
                      'Content-Type',
                      'application/x-www-form-urlencoded');
  Utl_Http.Set_Header(l_Http_Request,
                      'Connection',
                      'keep-alive');

  l_Http_Response := Utl_Http.Get_Response(l_Http_Request);

  Utl_Http.Read_Text(l_Http_Response,
                     l_Response_Body,
                     32767);

  Dbms_Output.Put_Line('Response> Status Code: ' ||
                       l_Http_Response.Status_Code);
  Dbms_Output.Put_Line('Response> Reason Phrase: ' ||
                       l_Http_Response.Reason_Phrase);
  Dbms_Output.Put_Line('Response> HTTP Version: ' ||
                       l_Http_Response.Http_Version);

  FOR i IN 1 .. Utl_Http.Get_Header_Count(l_Http_Response) LOOP
    Utl_Http.Get_Header(l_Http_Response,
                        i,
                        l_Response_Header_Name,
                        l_Response_Header_Value);
    Dbms_Output.Put_Line('Response> ' || l_Response_Header_Name || ': ' ||
                         l_Response_Header_Value);
  END LOOP;

  -- utl_http.read_text(l_http_response, l_response_body, 32767);
  Dbms_Output.Put_Line('Response body>');
  Dbms_Output.Put_Line(l_Response_Body);

  IF l_Http_Request.Private_Hndl IS NOT NULL THEN
    Utl_Http.End_Request(l_Http_Request);
  END IF;

  IF l_Http_Response.Private_Hndl IS NOT NULL THEN
    Utl_Http.End_Response(l_Http_Response);
  END IF;

END;

After running the above program, you will get the response printed using DBMS_OUTPUT as follows (if the configuration is correct):

{"response": {"time": 28383838837}, "stat": "OK"}

If the configuration is not correct, you will get the error response.

The above PL/SQL program follows the API rules, mentioned in the DUO documentation.

Basically, you will need /auth and /auth_status endpoints for DUO authentication integration. The following PL/SQL procedure is the complete program that authenticates using DUO and then gives the response accordingly.

It sends the DUO Push notification on the user's mobile and waits for one minute for the response. If the user approves it, it will return the SUCCESS status and if the user denies then it returns the FAILURE status. On one minute timeout, it returns the FAILURE status.

Change the values for the string changeme.

CREATE OR REPLACE PROCEDURE Duo_Auth(i_Username    IN VARCHAR2,
                                              o_Auth_Status OUT VARCHAR2) IS
  l_Http_Request          Utl_Http.Req;
  l_Request_Body          CLOB;
  l_Request_Body_Length   NUMBER;
  l_Req_Body              CLOB;
  l_Http_Response         Utl_Http.Resp;
  l_Response_Header_Name  VARCHAR2(256);
  l_Response_Header_Value VARCHAR2(1024);
  l_Response_Body         VARCHAR2(32767);

  d_Date   VARCHAR2(100);
  v_Method VARCHAR2(100);
  v_Host   VARCHAR2(100);
  v_Path   VARCHAR2(200);
  v_Skey   VARCHAR2(1000);
  v_Ikey   VARCHAR2(1000);

  r_Raw RAW(2000);
  v_Hex VARCHAR2(4000);

  v_Auth   VARCHAR2(4000);
  v_Canon  VARCHAR2(1000);
  v_Params VARCHAR2(1000);
  e_Error EXCEPTION;
  v_Url         VARCHAR2(1000);
  v_Un0         VARCHAR2(100) := 'username';
  v_Un          VARCHAR2(100);
  v_Fc          VARCHAR2(100) := 'factor';
  v_Fc_Vl       VARCHAR2(100);
  v_Dv          VARCHAR2(100) := 'device';
  v_Dv_Vl       VARCHAR2(100);
  v_As          VARCHAR2(100) := 'async';
  v_As_Vl       VARCHAR2(100);
  v_Tx          VARCHAR2(100) := '';
  v_Tx_Vl       VARCHAR2(200);
  v_Txid        VARCHAR2(200);
  v_Auth_Result VARCHAR2(100);
  In_Time       INT := 5;
  v_Now         DATE;
  v_Error_Msg   VARCHAR2(1000);
  FUNCTION Form_Url_Encode(Data IN VARCHAR2, Charset IN VARCHAR2)
    RETURN VARCHAR2 AS
  BEGIN
    RETURN Utl_Url.Escape(Data,
                          TRUE,
                          Charset); -- note use of TRUE
  END;
BEGIN
  v_Un    := Form_Url_Encode(i_Username,
                             'utf8');
  v_Un0   := Form_Url_Encode('username',
                             'utf8');
  v_Fc    := Form_Url_Encode('factor',
                             'utf8');
  v_Fc_Vl := Form_Url_Encode('push',
                             'utf8');
  v_Dv    := Form_Url_Encode('device',
                             'utf8');
  v_Dv_Vl := Form_Url_Encode('auto',
                             'utf8');
  v_As    := Form_Url_Encode('async',
                             'utf8');
  v_As_Vl := Form_Url_Encode('1',
                             'utf8');

  v_Method := 'POST';
  v_Host   := 'changeme.duosecurity.com';
  v_Path   := '/auth/v2/auth';
  v_Params := v_As || '=' || v_As_Vl || Chr(38) || v_Dv || '=' || v_Dv_Vl ||
              Chr(38) || v_Fc || '=' || v_Fc_Vl || Chr(38) || v_Un0 || '=' || v_Un;

  v_Skey := 'changeme';
  v_Ikey := 'changeme';

  d_Date := To_Char(CAST(Systimestamp At TIME ZONE 'gmt' AS DATE),
                    'Dy, dd Mon yyyy hh24:mi:ss') || ' GMT';

  v_Canon := d_Date || Chr(10) || v_Method || Chr(10) || v_Host || Chr(10) ||
             v_Path || Chr(10) || v_Params;

  Dbms_Output.Put_Line(v_Canon);
  Dbms_Output.Put_Line(Utl_I18n.String_To_Raw(v_Canon,
                                              'utf8'));
  -- convert to sha1
  r_Raw := Dbms_Crypto.Mac(Utl_I18n.String_To_Raw(v_Canon,
                                                  'utf8'),
                           Dbms_Crypto.Hmac_Sh1,
                           Utl_I18n.String_To_Raw(v_Skey,
                                                  'utf8'));

  Dbms_Output.Put_Line('raw: ' || r_Raw);

  -- CONVERT TO HEX

  SELECT Rawtohex(r_Raw) INTO v_Hex FROM Dual;

  v_Hex := Lower(v_Hex);

  Dbms_Output.Put_Line('hex: ' || v_Hex);
  -- dbms_output.put_line('raw '||UTL_ENCODE.BASE64_ENCODE(r_raw));

  Utl_Http.Set_Wallet(Path     => 'file:changeme',
                      Password => 'changeme');

  v_Url := 'https://' || v_Host || v_Path; -- || '?' || v_params;

  Dbms_Output.Put_Line('URL ' || v_Url);

  l_Http_Request := Utl_Http.Begin_Request(Url          => v_Url,
                                           Method       => v_Method,
                                           Http_Version => 'HTTP/1.1');

  v_Auth := REPLACE(Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_I18n.String_To_Raw(v_Ikey || ':' ||
                                                                                             v_Hex,
                                                                                             'utf8'))),
                    Utl_Tcp.Crlf,
                    '');

  Dbms_Output.Put_Line(v_Auth);

  Utl_Http.Set_Header(l_Http_Request,
                      'Host',
                      v_Host);

  Utl_Http.Set_Header(l_Http_Request,
                      'Authorization',
                      'Basic ' || v_Auth);

  Utl_Http.Set_Header(l_Http_Request,
                      'Date',
                      d_Date);

  Utl_Http.Set_Header(l_Http_Request,
                      'Content-Type',
                      'application/x-www-form-urlencoded');
  /*UTL_HTTP.SET_BODY_CHARSET(l_Http_Request,
  'utf8');*/
  Utl_Http.Set_Header(l_Http_Request,
                      'Transfer-Encoding',
                      'Chunked');
  Utl_Http.Set_Header(l_Http_Request,
                      'Connection',
                      'keep-alive');

  Utl_Http.Write_Text(l_Http_Request,
                      v_Params);

  l_Http_Response := Utl_Http.Get_Response(l_Http_Request);

  Utl_Http.Read_Text(l_Http_Response,
                     l_Response_Body,
                     32767);

  Dbms_Output.Put_Line('AUTH RESPONSE **** ');
  Dbms_Output.Put_Line('Response> Status Code: ' ||
                       l_Http_Response.Status_Code);
  Dbms_Output.Put_Line('Response> Reason Phrase: ' ||
                       l_Http_Response.Reason_Phrase);
  Dbms_Output.Put_Line('Response> HTTP Version: ' ||
                       l_Http_Response.Http_Version);

  FOR i IN 1 .. Utl_Http.Get_Header_Count(l_Http_Response) LOOP
    Utl_Http.Get_Header(l_Http_Response,
                        i,
                        l_Response_Header_Name,
                        l_Response_Header_Value);
    Dbms_Output.Put_Line('Response> ' || l_Response_Header_Name || ': ' ||
                         l_Response_Header_Value);
  END LOOP;

  -- utl_http.read_text(l_http_response, l_response_body, 32767);
  Dbms_Output.Put_Line('Response body>');
  Dbms_Output.Put_Line(l_Response_Body);

  IF l_Http_Request.Private_Hndl IS NOT NULL THEN
    Utl_Http.End_Request(l_Http_Request);
  END IF;

  IF l_Http_Response.Private_Hndl IS NOT NULL THEN
    Utl_Http.End_Response(l_Http_Response);
  END IF;

  Dbms_Output.Put_Line('AUTH STATUS RESPONSE **** ');

  IF l_Http_Response.Status_Code = Utl_Http.Http_Ok THEN
    --- GET AUTH STAUS
    Apex_Json.Parse(l_Response_Body);
  
    v_Txid := Apex_Json.Get_Varchar2(p_Path => 'response.txid');
  
    Dbms_Output.Put_Line('txid ' || v_Txid);
  
    v_Tx    := Form_Url_Encode('txid',
                               'utf8');
    v_Tx_Vl := Form_Url_Encode(v_Txid,
                               'utf8');
  
    v_Method := 'GET';
    v_Path   := '/auth/v2/auth_status';
    v_Params := v_Tx || '=' || v_Tx_Vl;
    
    d_Date := To_Char(CAST(Systimestamp At TIME ZONE 'gmt' AS DATE),
                      'Dy, dd Mon yyyy hh24:mi:ss') || ' GMT';
  
    v_Canon := d_Date || Chr(10) || v_Method || Chr(10) || v_Host ||
               Chr(10) || v_Path || Chr(10) || v_Params;
  
    Dbms_Output.Put_Line(v_Canon);
    Dbms_Output.Put_Line(Utl_I18n.String_To_Raw(v_Canon,
                                                'utf8'));
    -- convert to sha1
    r_Raw := Dbms_Crypto.Mac(Utl_I18n.String_To_Raw(v_Canon,
                                                    'utf8'),
                             Dbms_Crypto.Hmac_Sh1,
                             Utl_I18n.String_To_Raw(v_Skey,
                                                    'utf8'));
  
    Dbms_Output.Put_Line('raw: ' || r_Raw);
  
    -- CONVERT TO HEX
  
    SELECT Rawtohex(r_Raw) INTO v_Hex FROM Dual;
  
    v_Hex := Lower(v_Hex);
  
    Dbms_Output.Put_Line('hex: ' || v_Hex);
    -- dbms_output.put_line('raw '||UTL_ENCODE.BASE64_ENCODE(r_raw));
  
    Utl_Http.Set_Wallet(Path     => 'file:changeme',
                        Password => 'changeme');
  
    v_Url := 'https://' || v_Host || v_Path || '?' || v_Params;
  
    Dbms_Output.Put_Line('URL ' || v_Url);
  
    l_Http_Request := Utl_Http.Begin_Request(Url          => v_Url,
                                             Method       => v_Method,
                                             Http_Version => 'HTTP/1.1');
  
    v_Auth := REPLACE(Utl_Raw.Cast_To_Varchar2(Utl_Encode.Base64_Encode(Utl_I18n.String_To_Raw(v_Ikey || ':' ||
                                                                                               v_Hex,
                                                                                               'utf8'))),
                      Utl_Tcp.Crlf,
                      '');
  
    Dbms_Output.Put_Line(v_Auth);
  
    Utl_Http.Set_Header(l_Http_Request,
                        'Host',
                        v_Host);
  
    Utl_Http.Set_Header(l_Http_Request,
                        'Authorization',
                        'Basic ' || v_Auth);
  
    Utl_Http.Set_Header(l_Http_Request,
                        'Date',
                        d_Date);
  
    Utl_Http.Set_Header(l_Http_Request,
                        'Content-Type',
                        'application/x-www-form-urlencoded');
    /*UTL_HTTP.SET_BODY_CHARSET(l_Http_Request,
    'utf8');*/
  
    Utl_Http.Set_Header(l_Http_Request,
                        'Connection',
                        'keep-alive');
  
    FOR i IN 1 .. 3 LOOP
      l_Http_Response := Utl_Http.Get_Response(l_Http_Request);
      Utl_Http.Read_Text(l_Http_Response,
                         l_Response_Body,
                         32767);
    
      Apex_Json.Parse(l_Response_Body);
      v_Auth_Result := Apex_Json.Get_Varchar2(p_Path => 'response.result');
      IF v_Auth_Result = 'waiting' THEN
        Utl_Http.End_Request(l_Http_Request);
        Utl_Http.End_Response(l_Http_Response);
        Dbms_Output.Put_Line('Result ' || v_Auth_Result);
       /* SELECT SYSDATE INTO v_Now FROM Dual;
        LOOP
          EXIT WHEN v_Now +(In_Time * (1 / 86400)) <= SYSDATE;
        END LOOP; */
      ELSE
        EXIT;
      END IF;
      l_Http_Request := Utl_Http.Begin_Request(Url          => v_Url,
                                               Method       => v_Method,
                                               Http_Version => 'HTTP/1.1');
      Utl_Http.Set_Header(l_Http_Request,
                          'Host',
                          v_Host);
    
      Utl_Http.Set_Header(l_Http_Request,
                          'Authorization',
                          'Basic ' || v_Auth);
    
      Utl_Http.Set_Header(l_Http_Request,
                          'Date',
                          d_Date);
    
      Utl_Http.Set_Header(l_Http_Request,
                          'Content-Type',
                          'application/x-www-form-urlencoded');
      /*UTL_HTTP.SET_BODY_CHARSET(l_Http_Request,
      'utf8');*/
    
      Utl_Http.Set_Header(l_Http_Request,
                          'Connection',
                          'keep-alive');
    END LOOP;
    Dbms_Output.Put_Line('Final Result ' || v_Auth_Result);
  ELSE
    o_Auth_Status := 'FAILED';
  END IF;
  IF Lower(v_Auth_Result) = 'allow' THEN
    o_Auth_Status := 'SUCCESS';
  else
    o_Auth_Status := 'FAILED';
  END IF;
  Dbms_Output.Put_Line('Response> Status Code: ' ||
                       l_Http_Response.Status_Code);
  Dbms_Output.Put_Line('Response> Reason Phrase: ' ||
                       l_Http_Response.Reason_Phrase);
  Dbms_Output.Put_Line('Response> HTTP Version: ' ||
                       l_Http_Response.Http_Version);

  FOR i IN 1 .. Utl_Http.Get_Header_Count(l_Http_Response) LOOP
    Utl_Http.Get_Header(l_Http_Response,
                        i,
                        l_Response_Header_Name,
                        l_Response_Header_Value);
    Dbms_Output.Put_Line('Response> ' || l_Response_Header_Name || ': ' ||
                         l_Response_Header_Value);
  END LOOP;

  -- utl_http.read_text(l_http_response, l_response_body, 32767);
  Dbms_Output.Put_Line('Response body>');
  Dbms_Output.Put_Line(l_Response_Body);

  IF l_Http_Request.Private_Hndl IS NOT NULL THEN
    Utl_Http.End_Request(l_Http_Request);
  END IF;

  IF l_Http_Response.Private_Hndl IS NOT NULL THEN
    Utl_Http.End_Response(l_Http_Response);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    IF l_Http_Request.Private_Hndl IS NOT NULL THEN
      Utl_Http.End_Request(l_Http_Request);
    END IF;
  
    IF l_Http_Response.Private_Hndl IS NOT NULL THEN
      Utl_Http.End_Response(l_Http_Response);
    END IF;
  
    ROLLBACK;
    o_Auth_Status := 'FAILED';
END;

After creating this procedure successfully, you can call it by passing the username as shown in the below example:

Declare
   v_status varchar2(100);
Begin
   duo_auth('YourUserName', v_status);
   dbms_output.put_line(v_status);
End;

You can call duo_auth procedure in your authentication program for the two-factor authentication using DUO.

I hope, this will help you a lot.

What is Two Factor Authentication?

What is DUO?

Integrating DUO Two Factor Authentication Using PL/SQL

Vinish Kapoor

Related Posts

Running an Oracle Procedure in Background

Oracle Pipelined Function Example

Get BLOB from the BFILE Column in Oracle