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.
