Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Google Authenticator (TOTP)

Rabbit (user528481)Mar 6 2016 — edited Mar 6 2016

Should anyone be searching for a TOTP authentication method which works with the Google Authenticator here is a not so pretty PL/SQL block which should do the trick. Needs to be wrapped in a function and some user/key management added.

DECLARE

  cBASE32 CONSTANT VARCHAR2(32) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';

  cSecret CONSTANT VARCHAR2(20) := 'JBSWY3DPEHPK3PXP';

  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;

  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');

  DBMS_OUTPUT.PUT_LINE( SUBSTR(BITAND( nPart1, nPart2), -6, 6) );

END;

/

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 3 2016
Added on Mar 6 2016
0 comments
3,889 views