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
  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
  4. Hi there, did you get a chance to put together a blog post on how to integrate the package into APEX? Thanks!

    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/PostgresRestSQL
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 architecture: client, application ser…

Oracle APEX – Amazon ALEXA integration WITHOUT Amazon Lambda ! (PART 2)

In the last blog post I prepared Oracle side. In this post I will show, how to prepare ALEXA Skill and how to integrate all layers.

Few weeks ago Amazon implemented new version of Alexa Skill Kit. Alexa Skill Kit is like a APEX builder. Low code technology, so you don't need to write any code :)

Let's start ! Go to Amazon Developer Console

1. Click Get Started on Alexa Skills Kit


2. As you can see on screen below there is already one skill on my list. Click on Create Skill to Create new one.

3. The name of my Skill will be - APEXUTIL Pizza  :) but you can name it as you wish.


4. Select Custom

5. Create Invocation. Users say a skill's invocation name to begin an interaction with a particular custom skill. For example, if the invocation name is "apex pizza", users can say: "Alexa, ask apex pizza ...."

6. One skill can have one or more Intents. An intent represents an action that fulfills a user's spoken request. For demo I'll use only one Intent: &…