2FA in Oracle ApEx
I bring to your attention the implementation of 2FA in Oracle Application Express. As a second factor, the solution from Google will be used with the Authenticator application installed on the phone.
This implementation does not claim to be assigned the status of best practice, the purpose of writing this article is to share this solution and get recommendations for improving and enhancing the security of the code used.
As soon as I started this task, I discovered that the built-in procedure apex_authentication.login accepts only two parameters as input, the possibility of modifying this procedure to suit my needs was not even considered, 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-writing procedure is called, which checks the login & one time password pair.
')
Initial data: authentication in a web application is based on checking a login and password pair stored in the users table of the corresponding schema (each web application has its own schema). In this table you need to add a column in which secret keys for the second factor will be stored. For more security, you can create a separate table with limited access rights, which will store user id and encrypted keys.
The key is a base32 string of 16 uppercase characters. 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 designer, 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. A process initiated by the After Submit event that launches the Preauth procedure is added on the same page. It looks like this:
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 with the name TOTP, the author of the function published it here .
This function worked as expected, except when generated by one time password had a zero in the high-order bit — 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 was returned.
Important note: Preauth checks for the presence of a private key for a 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 assigned the status of best practice, the purpose of writing this article is to share this solution and get recommendations for improving and enhancing the security of the code used.
As soon as I started this task, I discovered that the built-in procedure apex_authentication.login accepts only two parameters as input, the possibility of modifying this procedure to suit my needs was not even considered, 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-writing procedure is called, which checks the login & one time password pair.
')
Initial data: authentication in a web application is based on checking a login and password pair stored in the users table of the corresponding schema (each web application has its own schema). In this table you need to add a column in which secret keys for the second factor will be stored. For more security, you can create a separate table with limited access rights, which will store user id and encrypted keys.
The key is a base32 string of 16 uppercase characters. 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 designer, 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. A process initiated by the After Submit event that launches the Preauth procedure is added on the same page. It looks like this:
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 with the name 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 generated by one time password had a zero in the high-order bit — 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 was returned.
Important note: Preauth checks for the presence of a private key for a 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.
Source: https://habr.com/ru/post/318692/
All Articles