Skip to main content

TWO-Factor Authentication with APEX application.

Two-factor authentication means that you must have two factors, to access your account. The first factor is something you know, like your login and password combination. The second is something you have, like a mobile device with a security app installed. That second factor — the mobile device with a security app — adds an extra layer of protection to your account. Even if hackers steal your password, they can’t log in, because they don't have your mobile device.




It is the most popular 2FA option based on Times-based One-Time Password (TOTP) alghoritm described in RFC 6238 and RFC 4226. TOTP is supported by Google Authenticator, Microsoft Authenticator, Authy 2-Factor Authentication and many other mobile apps. The most popular online services like Facebook, Google, Twitter use TOTP to protect user's accounts.

TOTP is a standard algorithm based on cryptographic hash method SHA-1. SHA-1 algorithm's implementation is available since Oracle 10g DBMS_CRYPTO package.

Our wish to improve the security layer in applications inspired us to create TOTP server, which is compatible with Google Authenticator, Authy 2-Factor Authentication and others. The solution is implemented in pure PL/SQL and encapsulated in one PL/SQL package.
Feel free to use it with your Oracle APEX application !



create or replace PACKAGE TOTP_PKG AS 
/*

* Original idea and first implementation: Andrzej Nowakowski @ApexUtil_andrew
* Code rewrite and co-writer: Patryk Mrozicki
* Help and credits for Demo presentation to: Łukasz Szymański @ApexUtil_lukas

 MIT License

 Copyright (c) 2018 APEXUTIL www.apexutil.com

 Permission is hereby granted, free of charge, to any person obtaining a copy
 of this software and associated documentation files (the "Software"), to deal
 in the Software without restriction, including without limitation the rights
 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 copies of the Software, and to permit persons to whom the Software is
 furnished to do so, subject to the following conditions:

 The above copyright notice and this permission notice shall be included in all
 copies or substantial portions of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 SOFTWARE.

*/

/*
    Functions get_random_base32_json and get_random_base32 will be useful to generate 
    random 20 characters original and 32 characters long base32 strings
    We do not provide DECODE for base32, thats why we encourage to save both original and encoded strings
*/
  function get_random_base32_json -- orginal, base32code
  return varchar2;
  
  procedure get_random_base32
  (
    p_orig in out nocopy varchar2
  , p_base in out nocopy varchar2
  );
  
  /* If you want to use it in GOOGLE AUTHENTICATOR then just use p_key */
  /* This function returns 6 digit code -- which you compare to the one users give */
  function totp_code( 
    p_key       in varchar2 -- original code
  , p_unix_time in pls_integer default null -- used for custom time (not current)
  , p_interval  in pls_integer default 30  -- for other algorithms where interval is different than 30 second
  ) return varchar2;

  
  
  
  
  --- SUB FUNCTIONS | might be useful ---

  -- returns GMT date
  function get_greenwich_time return date;

  -- change date to unix time ( or POSIX/Epoch time ) - warning: problems in 2038 ( if PLS_INTIGER = 32bit then BOOM('!!!'); end if;)
  function date_to_unix_time( p_date in date ) return pls_integer;

  -- encodes string to base32 -- warning: max is varchar2(2500)
  function encode_base32( p_str in varchar2 ) return varchar2;

  -- this function can be useful if you want to change an integer to hashed message which later can be translated into 6 digit code
  function unix_to_hash_message( p_time in pls_integer ) return varchar2;
  
  -- returns 6 digit code from hash ( has to be: 40 digit long hexadecimal number )
  function code_from_hash( p_hash in varchar2 ) return varchar2;
  
  -- hash function
  function hmac_sha1_hash(
    p_message in varchar2
  , p_key     in varchar2
  ) return varchar2;

  
END TOTP_PKG;
/
create or replace PACKAGE BODY TOTP_PKG AS

  function generate_string(
    p_length  in number   default 20
  , p_options in varchar2 default 'ULAXPD' -- U - upper, L - lower, A - alphanumeric, X - alphanumeric with upper case letters, P - printable characters only, D - numbers
  ) return varchar2
  as

    l_options apex_application_global.vc_arr2;
    l_string varchar2(4000);
    l_length number := coalesce( p_length, 20 );
    l_options_cnt number;
    l_index  number;

    l_char varchar2(2);
    l_new_char varchar2(2);

  begin

    -- get available characters (ULAXPD) from p_options. Then change them to be delimited by ':'
    l_options := apex_util.string_to_table( coalesce( trim( both ':' from regexp_replace( regexp_replace( upper(p_options), '[^ULAXPD]', '' ), '*', ':') ),'D'), ':' );
    l_options_cnt := l_options.count;

    for x in 1..p_length loop

      l_index := dbms_random.value( 1, l_options_cnt );
      l_char   := l_options( l_index );

      if ( l_char = 'U' ) then
        l_new_char := dbms_random.string( 'U', 1 );
      elsif ( l_char = 'L' ) then
       l_new_char := dbms_random.string( 'L', 1 );
      elsif ( l_char = 'A' ) then
        l_new_char := dbms_random.string( 'A', 1 );
      elsif ( l_char = 'X' ) then
        l_new_char := dbms_random.string( 'X', 1 );
      elsif ( l_char = 'P' ) then
        l_new_char := dbms_random.string( 'P', 1 );
      elsif ( l_char = 'D ' ) then
        l_new_char := trunc( to_char( dbms_random.value(0,9) ) );
      else
        l_new_char := trunc( to_char( dbms_random.value(0,9) ) );
      end if;

      l_string := l_string || l_new_char;

    end loop;

    return l_string;

  end generate_string;
  
  
  function get_random_base32_json -- orginal, base32code
  return varchar2
  as
    l_string varchar2(20);
  begin
    l_string := generate_string( p_length => 20, p_options => 'ULD' );
    
    -- it never returns characters that should be escaped in json
    return '{ "original" : "' || replace( l_string, '"', '\"' ) || '", "base32code" : "' || totp_pkg.encode_base32( p_str => l_string ) || '" }';
  end get_random_base32_json;
  
  procedure get_random_base32
  (
    p_orig in out nocopy varchar2
  , p_base in out nocopy varchar2
  )
  as

  begin

    p_orig := generate_string( p_length => 20, p_options => 'ULD' );
    p_base := totp_pkg.encode_base32( p_str => p_orig );

  end get_random_base32;
  
  
  function get_greenwich_time
  return date
  as
    l_offset_str varchar2(16) := TZ_OFFSET(SESSIONTIMEZONE);
    l_sign       number;
    l_time       date := sysdate;
  begin 

    if( l_offset_str <> '+00:00' ) then

      l_sign := -to_number( substr(l_offset_str, 1, 1) || '1440' );
      l_time := l_time + ( substr(l_offset_str, 2, 2)*60 + substr(l_offset_str, 5, 2) ) / l_sign ;

    end if;

    return l_time;

  end get_greenwich_time; 

  function date_to_unix_time( p_date in date ) 
  return pls_integer
  as
    l_zero_date constant date := to_date( '19700101', 'YYYYMMDD' ); -- 1970-01-01
  begin

    return trunc( ( p_date - l_zero_date ) * 86400 );

  end date_to_unix_time;

  function encode_base32
  ( 
    p_str in varchar2
  ) return varchar2
  as
    l_base_str varchar2(4000);
    l_num number(12);
    l_length constant pls_integer := ceil( length(p_str)/5 );

    l_result varchar2(160);

    function number_encode_base32
    ( 
      p_val in number 
    ) return varchar2 
    as
      l_str varchar(8) := null;
      l_val number     := p_val;
      l_base32str constant varchar(32) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';

    begin
      for i in 1..8 loop
        l_str := substr( l_base32str, mod( l_val, 32 ) + 1, 1 ) || l_str;
        l_val := trunc( l_val / 32 );
      end loop;

      return l_str;
    end number_encode_base32;
  begin
    l_base_str := rpad(p_str, l_length*5, CHR(0));

    for i in 0..l_length-1 loop

      l_num := UTL_RAW.cast_to_binary_integer( UTL_RAW.cast_to_raw(substr(l_base_str, 1+i*5, 4)) );
      l_result := l_result || number_encode_base32( l_num * 256 + ascii(substr( l_base_str, (i+1)*5, 1)) );

    end loop;

    return l_result;
  end encode_base32;

  function unix_to_hash_message( p_time in PLS_INTEGER )
  return varchar2
  as
    function unix_to_hex( p_time in PLS_INTEGER ) -- warning: problem occur in 36812 (year)
    return varchar2
    as
    begin

      return replace( to_char( p_time, 'XXXXXXXXXX' ), ' ', '' );

    end unix_to_hex;
  begin

    return utl_raw.cast_to_varchar2( lpad(unix_to_hex(p_time), 16, '0') );

  end unix_to_hash_message;

  function hmac_sha1_hash(
    p_message in varchar2
  , p_key     in varchar2

  ) return varchar2
  as
    l_message RAW(512);
    l_key     RAW(512);

  begin

    l_message := UTL_RAW.cast_to_raw( c => p_message );
    l_key     := UTL_RAW.cast_to_raw( c => p_key );

    return DBMS_CRYPTO.mac(
      src => l_message
    , key => l_key
    , typ => DBMS_CRYPTO.hmac_sh1
    );

  end hmac_sha1_hash;

  function code_from_hash( p_hash in varchar2 )
  return varchar2
  as
    l_offset   number;

  begin
    -- at first find offset ( will be used twice )
    l_offset := ( to_number( substr(p_hash, 40, 1), 'X' ) * 2 ) + 1; -- +1 because index starts from 1 not 0

    --Explanation to the next line:
    -- (we have 40 digit long hexadecimal number )
    --1) take specific letter from hash (from l_offset)
    --2) change to number
    --3) use "mod" to negate MSB ( most significant bit )
    --4) change to varchar2 ( to_char adds space... so delete space by taking second char )
    --5) then take 7 more chars and add to previous string ( point 4 )
    --6) change it to number
    --7) get last 6 digits ( the code ) by dividing by million
    --8) change back to string, then left pad with '0' (so we have 6 digits )
    --9) Enjoy! :)
    return lpad( to_char( mod( to_number( substr( to_char( mod( to_number( substr( p_hash, l_offset, 1 ), 'X' ), 8 ), 'X' ), 2, 1 ) || substr(p_hash, l_offset+1, 7 ), 'XXXXXXXX' ), 1000000 )), 6, '0' );

  end code_from_hash;

  --- end function
  
  function totp_code( 
    p_key       in varchar2 -- original code
  , p_unix_time in pls_integer default null -- used for custom time (not current)
  , p_interval  in pls_integer default 30  -- for other algorithms where interval is different than 30 second
  ) return varchar2
  as
    l_date      date;
    l_unix_time pls_integer;
    l_message   varchar2(64);  
    l_interval  pls_integer;
  begin
    
    -- interval = 0 will raise exception, negative interval allowed...
    l_interval := coalesce(p_interval, 30); 
    
    -- get current time if user didn't provide one (negative unix_time allowed..)
    l_unix_time := coalesce(p_unix_time, date_to_unix_time( get_greenwich_time() ));
    -- here we get one unit of time
    l_unix_time := trunc(l_unix_time / l_interval);
    
    -- get hash
    l_message   := unix_to_hash_message( l_unix_time );
    
    -- return translated hash
    return code_from_hash (
        hmac_sha1_hash(
          p_message => l_message
        , p_key     => p_key
        )
    );

  end totp_code;

END TOTP_PKG;


You can watch wideo with implementation:



Comments

  1. Hi Andrzej Nowakowski,

    Well written code for a nice feature.

    It'd be cool to also see how you integrated the package (and all its functions) in your APEX app.

    May be having a fDEMO_APP.sql to download and importing it to the user workspace could help a lot.

    Cheers

    ReplyDelete
    Replies
    1. Hi Trevis
      We have published our sandbox env. you can try to check online: https://www.apexutil.com/apex/f?p=SANDBOX

      Delete
    2. Hi Andrzej,

      Thanks for publishing it! I just had a look at it!

      I still think it may be not clear for some users how to integrate the TOTP within APEX. For i.e., in page 1000 when I hit the "Create User" button, what is happened in the back-end? Is the page saving some value to the USERS table to check against the Authenticator? What should be the USERS table structure? Also, when a TOTP user logs in, how the app pop up the page asking for the 6 digit code and how it's validate? I imagine some branch or even application process to redirect, but it'd be nice to see how you've approached those points.

      Delete
    3. Hi Andrzej,

      I agree with Trevis. I would be nice to import your APEX into our environment to see how it works with your PL/SQL package.

      Delete
  2. Either I'm not doing something right, or there's something wrong with the code above. I copied the above code to my sandbox, went to your sandbox and created a new user, adding the generated code to my authenticator app, and copying the displayed key code for testing with the TOTP package. The codes generated by my authenticator app do not match those generated by the TOTP_PKG.TOTP_CODE function.

    Is there something that I'm missing?

    ReplyDelete
    Replies
    1. OK... I have taken 0,5h break in my holiday ; )

      Let's analyse this code.. I hope it will help you:

      declare
      l_orig varchar2(4000);
      l_base varchar2(4000);
      begin

      Totp_Pkg.Get_Random_Base32
      (p_orig =>l_orig
      ,p_base =>l_base) ;

      Dbms_Output.Put_Line('Save this code in user context='||l_orig);
      Dbms_Output.Put_Line('Paste this code to G. Authenticator='||l_base);

      end;

      --Check result with G. Authenticator
      select Totp_Pkg.totp_code( p_key =>l_orig) from dual;

      Delete
    2. Thank you that got my issue sorted out.

      Delete
  3. Now I'm on holiday, when I back I will create new post blog how to use it.

    ReplyDelete
    Replies
    1. PL put together a blog post on how to integrate the package into APEX? Thanks!

      Delete
  4. Hi there, did you get a chance to put together a blog post on how to integrate the package into APEX? Thanks!

    ReplyDelete
    Replies
    1. PL put together a blog post on how to integrate the package into APEX? Thanks!

      Delete
    2. Gdzie można znaleźć ten wpis o integracji pakietu z APEX???

      Delete
  5. Hi, Great post.
    Can you please elaborate it how to use it.
    Thanks

    ReplyDelete
  6. Hi Andrzej,
    thanks for the package. I implemented this solution in Apex login successfully using Google and / or Microsoft Authenticator.
    The last one problem I face is synchronising the code generated by server and mobile device - the code from server is delayed to code from mobile, the delay is less than 1 minute. I have checked that the times on computer and mobile are identical. I tried to add 1 minute to the time generated by totp_pkg - works when user waits for the new code from mobile. It seems to be uncomfortable for users ...
    Any advice what to check, explore, set, ... ?

    ReplyDelete
    Replies
    1. It can happen... so the solution is to check also -1 min and +1 min. BTW. Did you synchronize your server with NTP servers ?

      Delete
    2. Yes, we synchronize with NTP. I will verify this. You mean to check mobile generated code against 3 server generated codes - current time plus -1 min and +1 min, am I right?

      Delete
    3. Yes. Try generate "array" with 3 codes. And check these.

      Delete
  7. I'm trying to login to the demo using a Google Authenticator, but my pass code is never recognised - is there a problem at the moment?

    ReplyDelete
  8. Thanks for sharing Multi Factor Authentication tips. for more info i rfer cion systems Multi Factor Authentication in USA.

    ReplyDelete
  9. Hi! Did anybody managed to make it work ? I'm trying but with no success.

    ReplyDelete
    Replies
    1. It might have been the problem i describe below

      Delete
  10. Thank for sharing!

    I ran into a problem with the function GET_GREENWICH_TIME;

    this uses SYSDATE and SESSIONTIMEZONE; but the SYSDATE doesn't have to be in the SESSIONTIMEZONE, as it was in my case(ORACLE CLOUD):

    SELECT TO_CHAR(CURRENT_DATE, 'HH24:Mi') current_date
    , TO_CHAR(SYSDATE, 'HH24:Mi') sdate
    , TZ_OFFSET(SESSIONTIMEZONE) offset
    , TO_CHAR(TOTP_PK.get_greenwich_time, 'HH24:mi') get_greenwich_time UTC
    FROM DUAL

    CURRENT_DATE SDATE OFFSET UTC
    15:36 13:36 +02:00 11:42

    Changing the SYSDATE to CURRENT_DATE fixed this

    ReplyDelete
  11. Hi Andrzej, thanks for sharing the information. You have information on how to implement the use of the package in the application in APEX. It would be very useful for me.

    ReplyDelete

Post a Comment

Popular posts from this blog

ORACLE APEX with MySQL !

About 10 years ago I tried to build APEX application on MS SQLServer. It was possible but performance was not satisfactory. (You can read this post here) ... Today I will describe how to use APEX with MySQL. Obviusly I don't think it is good idea to build APEX app only on MySQL. It can be used as part of Hybryd solution (Oracle + MySQL or Postrgres). My description covers integration with MySQL. As a result will be APEX application with Interactive Report and CRUD form based on MySQL data. Description contains two parts: First part is a MySQL site, Second part is a APEX application part Prerequisites: Oracle 11g+ APEX 18.1+ MySQL/Postgres RestSQL I. MySQL Part 1. MySQL contains simple  apexdb database with one apexutil table. 2. To use this database install RestSQL. What is RestSQL? RestSQL is an open-source, ultra-lightweight data access layer for HTTP clients. RestSQL is a persistence framework or engine in the middle tier of a classic three tier archi

How to return json with null value using apex_json.write(ref_cursor)

Few days ago I had to develop dynamic query json export module. As a sandbox I prepared PLSQL block: DECLARE c sys_refcursor; l_sql varchar2(4000) := 'select null as "col1", 100 as "col2" from dual union all select 200 as "col1", null as "col2" from dual'; l_file clob; BEGIN open c for l_sql; apex_json.initialize_clob_output; apex_json.open_object; apex_json.write('rowset', c); apex_json.close_object; l_file := apex_json.get_clob_output; apex_json.free_output; DBMS_OUTPUT.PUT_line(l_file); END; { "rowset": [{ "col2": 100 }, { "col1": 200 } ] } As you can see above PLSQL code doesn't return null value pairs : ( apex_json.write(p_name IN VARCHAR2, p_cursor IN OUT NOCOPY sys_refcursor) procedure Signature 14 doesn't support null. How to fix it... ? APEX API provides apex_js