OOS_UTIL_TOTP

• Constants

• GENERATE_SECRET Function

• FORMAT_KEY_URI Function
• GENERATE_OTP Function
• VALIDATE_OTP Function

Constants

gc_base32 constant varchar2(32) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';

gc_step constant number := 30;

GENERATE_SECRET Function

Generates a sixteen-character alphanumeric, Base32-encoded [1] string.

[1] - https://en.wikipedia.org/wiki/Base32

Syntax

function generate_secret (p_length number default 16) return varchar2

Parameters

Example

select generate_secret
from dual;

FORMAT_KEY_URI Function

Returns a URI that can be used to create a QR Code for setting up a entry
in Google Authenticator by scanning [1]. After obtaining the URI, create
a QR Code to make it easier to create an entry in Google Authenticator.

[1] - https://github.com/google/google-authenticator/wiki/Key-Uri-Format

Syntax

function format_key_uri(
  p_type number default null
  , p_label_accountname varchar2
  , p_label_issuer varchar2
  , p_secret varchar2
  , p_issuer varchar2 default null
  , p_algorithm varchar2 default null
  , p_digits number default null
  , p_counter number default null
  , p_period number default null
) return varchar2

Parameters

Example

select
  oos_util_totp.format_key_uri(
    p_label_accountname => 'adrian.png@wonderland.com'
    , p_label_issuer => 'Superworld'
    , p_secret => 'JBSWY3DPEHPK3PXP'
    , p_issuer => 'Superworld'
  )
from dual;

GENERATE_OTP Function

Generates a six-digit number

Syntax

function generate_otp(p_secret varchar2, p_offset number default 0) return varchar2

Parameters

Example

select generate_otp(p_secret => 'JBSWY3DPEHPK3PXP')
from dual;

select generate_otp(p_secret => 'JBSWY3DPEHPK3PXP', p_offset => -30)
from dual;

VALIDATE_OTP Function

Validate an OTP. The skew parameter allows for a customizable degree of
tolerance for clocks that are not in sync.

Syntax

function validate_otp(
  p_secret varchar2
  , p_otp number
  , p_skew number default 30
) return number

Parameters

Example

begin
  if oos_util_totp.validate_otp(
    p_secret => 'JBSWY3DPEHPK3PXP'
    , p_otp => 123456
    , p_skew => 30
  ) = 1 then
    dbms_output.put_line('Valid');
  else
    dbms_output.put_line('Failed');
  end if;
end;