Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

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,285 views