2FA in Oracle ApEx
I bring to your attention the implementation of 2FA in Oracle Application Express. As a second factor, a solution from Google with the Authenticator application installed on the phone will be used.
This implementation does not claim to be best practice, the goal of this article is to share this decision and get recommendations for improving and improving the security of the code used.
As soon as I started this task, I found that the built-in procedure apex_authentication.loginaccepts only two parameters as input, I did not even consider the possibility of modifying this procedure to my needs, since this could affect other web applications. In this implementation, before calling the built-in authentication procedure that checks the login & password pair, the Preauth self-written procedure is called, which checks the login & one time password pair.
Initial data: authentication in a web application is based on checking a pair of login and password stored in the users table of the corresponding scheme (each web application has its own scheme). In this table you need to add a column in which the secret keys for the second factor will be stored. For greater security, you can create a separate table with limited access rights, which will store user id and keys in encrypted form.
The key is a base32 string of 16 characters in uppercase. In order to save the secret key in the Google Authenticator application, you can generate a QR code, for example, using this solution .
On the Login page in the ApEx constructor, there are usually only two fields: username and password. You need to add an additional field, for example with the name P101_TOTP, here the user will enter 6 digits from the Google Authenticator application. On the same page, a process is triggered by the After Submit event, which starts the Preauth procedure. It looks like this:
The Preauth procedure compares one time password with the code that is generated on the server with the same secret key:
As you can see, the procedure refers to a function called TOTP, the author of the function published it here .
This function worked as expected, except when the generated one time password had the number zero in the high order - the return value was of type Number, and this zero was ignored by Oracle. Therefore, I made changes to it so that the VARCHAR2 type is returned.
Important Note: The Preauth procedure checks for a secret key for the user attempting to log in. This is done intentionally, in order to “smoothly” enable the use of the second factor in the web application, i.e. not all users at once. To turn off this check, just comment out the line:
Thanks for attention.
This implementation does not claim to be best practice, the goal of this article is to share this decision and get recommendations for improving and improving the security of the code used.
As soon as I started this task, I found that the built-in procedure apex_authentication.loginaccepts only two parameters as input, I did not even consider the possibility of modifying this procedure to my needs, since this could affect other web applications. In this implementation, before calling the built-in authentication procedure that checks the login & password pair, the Preauth self-written procedure is called, which checks the login & one time password pair.
Initial data: authentication in a web application is based on checking a pair of login and password stored in the users table of the corresponding scheme (each web application has its own scheme). In this table you need to add a column in which the secret keys for the second factor will be stored. For greater security, you can create a separate table with limited access rights, which will store user id and keys in encrypted form.
The key is a base32 string of 16 characters in uppercase. In order to save the secret key in the Google Authenticator application, you can generate a QR code, for example, using this solution .
On the Login page in the ApEx constructor, there are usually only two fields: username and password. You need to add an additional field, for example with the name P101_TOTP, here the user will enter 6 digits from the Google Authenticator application. On the same page, a process is triggered by the After Submit event, which starts the Preauth procedure. It looks like this:
The Preauth procedure compares one time password with the code that is generated on the server with the same secret key:
Procedure code
create or replace PROCEDURE "PREAUTH"
(p_username IN VARCHAR2
,p_totp IN VARCHAR2)
AS
l_value NUMBER;
usersToken VARCHAR2(20);
tempToken VARCHAR2(20);
l_current_sid number;
BEGIN
SELECT token INTO usersToken FROM users WHERE upper(users.login) = upper(p_username);
IF usersToken != '0' THEN
BEGIN
tempToken := TOTP(cSecret => usersToken);
SELECT 1 INTO l_value FROM users
WHERE 1 = 1
AND upper(users.login) = upper(p_username)
AND USERS.IS_LOCKED = 0
AND p_totp = tempToken;
EXCEPTION
WHEN no_data_found
OR too_many_rows THEN
l_value := 0;
WHEN OTHERS THEN
l_value := 0;
END;
END IF;
l_current_sid := apex_custom_auth.get_session_id_from_cookie;
IF l_value = 0 THEN
raise_application_error (-20000,'Please, try again');
apex_util.set_authentication_result(4);
APEX_AUTHENTICATION.LOGOUT(
p_session_id => l_current_sid,
p_app_id => v('APP_ID'));
END IF;
END PREAUTH;As you can see, the procedure refers to a function called TOTP, the author of the function published it here .
TOTP Function Code
create or replace FUNCTION "TOTP"
(cSecret IN VARCHAR2) RETURN VARCHAR IS
cBASE32 CONSTANT VARCHAR2(32) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
szBits VARCHAR2(500) := '';
szTmp VARCHAR2(500) := '';
szTmp2 VARCHAR2(500) := '';
nPos NUMBER;
nEpoch NUMBER(38);
szEpoch VARCHAR2(16);
rHMAC RAW(100);
nOffSet NUMBER;
nPart1 NUMBER;
nPart2 NUMBER := 2147483647;
nPart3 NUMBER;
l_obfuscated_password users.pass%TYPE;
calculatedCode VARCHAR2(6);
FUNCTION to_binary(inNum NUMBER) RETURN VARCHAR2
IS
szBin VARCHAR2(8);
nRem NUMBER := inNum;
BEGIN
IF inNum = 0 THEN
RETURN '0';
END IF;
WHILE nRem > 0
LOOP
szBin := MOD(nRem, 2) || szBin;
nRem := TRUNC(nRem / 2 );
END LOOP;
RETURN szBin;
END to_binary;
BEGIN
FOR c IN 1..LENGTH(cSecret)
LOOP
nPos := INSTR( cBASE32, SUBSTR(cSecret, c, 1))-1;
szBits := szBits || LPAD( to_binary(nPos), 5, '0');
END LOOP;
nPos := 1;
WHILE nPos < LENGTH(szBits)
LOOP
SELECT LTRIM(TO_CHAR(BIN_TO_NUM( TO_NUMBER(SUBSTR(szBits, nPos, 1)), TO_NUMBER(SUBSTR(szBits, nPos+1, 1)), TO_NUMBER(SUBSTR(szBits, nPos+2, 1)), TO_NUMBER(SUBSTR(szBits, nPos+3, 1)) ), 'x'))
INTO szTmp2
FROM dual;
szTmp := szTmp || szTmp2;
nPos := nPos + 4;
END LOOP;
SELECT EXTRACT(DAY FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*86400+
EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*3600+
EXTRACT(MINUTE FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*60+
EXTRACT(SECOND FROM (CURRENT_TIMESTAMP-TIMESTAMP '1970-01-01 00:00:00 +00:00')) n
INTO nEpoch
FROM dual;
SELECT LPAD(LTRIM(TO_CHAR( FLOOR(nEpoch/30), 'xxxxxxxxxxxxxxxx' )), 16, '0')
INTO szEpoch
FROM dual;
rHMAC := DBMS_CRYPTO.MAC( src => hextoraw(szEpoch), typ => DBMS_CRYPTO.HMAC_SH1, key => hextoraw(szTmp) );
nOffSet := TO_NUMBER( SUBSTR( RAWTOHEX(rHMAC), -1, 1), 'x');
nPart1 := TO_NUMBER( SUBSTR( RAWTOHEX(rHMAC), nOffSet*2+1, 8), 'xxxxxxxx');
calculatedCode := SUBSTR(BITAND( nPart1, nPart2), -6, 6);
RETURN calculatedCode;
END "TOTP";
This function worked as expected, except when the generated one time password had the number zero in the high order - the return value was of type Number, and this zero was ignored by Oracle. Therefore, I made changes to it so that the VARCHAR2 type is returned.
Important Note: The Preauth procedure checks for a secret key for the user attempting to log in. This is done intentionally, in order to “smoothly” enable the use of the second factor in the web application, i.e. not all users at once. To turn off this check, just comment out the line:
IF usersToken != '0' THEN Thanks for attention.