TOTP two-factor authentication, built entirely inside the database.
If your APEX app still lets people in with just a username and a password, you're one leaked password away from a bad day. On a recent client project we decided to fix that properly and add two-factor authentication (2FA) to our login. The user types the password as usual, then opens an authenticator app on their phone (Microsoft Authenticator, Google Authenticator, whatever they like) and types the 6-digit code it shows.
No SMS gateway, no third-party service, no extra licence. The whole thing lives in one PL/SQL package and a couple of APEX pages, sitting on top of the custom authentication scheme we already had. In this post I'll walk through exactly how we built it, with the real code, so you can do the same in your own app.
NoteThis post assumes you already use a custom authentication scheme (your own username and password check in PL/SQL). If you're on APEX Accounts or SSO, the same ideas apply, but you'd hook in at a different place.
What we wanted from it
Before writing a single line, we agreed on a few rules. They shaped almost every decision later on, so it's worth listing them.
- Opt-in first. Every user starts with 2FA off and switches it on from their profile menu. Making it mandatory later should be a one-line change.
- Standard TOTP (RFC 6238). Any authenticator app has to work. SHA1, 6 digits, 30 second window.
- The secret is never stored in clear text. It's encrypted with AES-256, and the key lives in a file on the DB server, not in a table.
- A code can only be used once. If someone shoulder-surfs your code, they can't replay it a few seconds later.
- Brute force protection. 5 wrong passwords or codes in 15 minutes lock the username for 15 minutes.
- No session data before the code. Until the code is accepted, the APEX session holds nothing useful.
- Lost phone? An admin can reset 2FA for a user with one button.
How TOTP actually works
TOTP sounds fancy, but the idea is simple. When the user sets up 2FA, the app and the phone agree on a random secret (that's what the QR code carries). After that, both sides take the current time, cut it into 30 second slices, and run the slice number through HMAC-SHA1 with the secret as the key. A bit of truncation later you've got a 6-digit number. Same secret plus same time slice gives the same code, on both sides.
The five steps behind every 6-digit code. Oracle can do all of them with DBMS_CRYPTO.
The nice part: Oracle has everything we need already. DBMS_CRYPTO.MAC does HMAC-SHA1, DBMS_CRYPTO.ENCRYPT does AES-256 and DBMS_CRYPTO.RANDOMBYTES gives us a proper random secret. The only thing we have to write ourselves is Base32, because that's the format authenticator apps expect.
The login flow, end to end
Here's the full picture before we get into the code. The trick is in POST_AUTHENTICATE_USER. That's the procedure that normally loads everything into the session after a good password (user ID, role, auth profile and so on). We put a gate at the very top of it. If the user has 2FA on, it returns early and the session stays empty.
With 2FA on, the session only gets loaded after page 9996 accepts the code.
Then an application process called Force 2FA runs before the header of every page. If the state says the code is still missing, it redirects to page 9996, no matter which URL the user typed. Once the code is right, page 9996 calls POST_AUTHENTICATE_USER again and this time the gate lets it through.
The whole thing hangs on one application item, APP_MFA_STATE. It can only have four values:
APP_MFA_STATE is restricted, so nobody can set it from the browser.
| Value | Meaning | What the user sees |
|---|
OK | 2FA is off for this user, or the code was already accepted | The app, as normal |
VERIFY | 2FA is on, the password was right, the code is still missing | Page 9996 only |
ENROLL | 2FA is required (mandatory mode) but not set up yet | Page 9997 only |
LOCKED | 5 failures in the last 15 minutes | Signed out |
Step 1: The key file (DBA, once per environment)
The TOTP secret has to be encrypted, and the encryption key must not sit next to the data it protects. If someone dumps the table, they shouldn't be able to decrypt it. So the key goes into a small file on the database server, readable only by the Oracle OS user.
On the DB server, as the oracle OS user
mkdir -p /u01/app/oracle/keys/myapp
openssl rand -hex 32 > /u01/app/oracle/keys/myapp/mfa.key
chmod 700 /u01/app/oracle/keys/myapp
chmod 600 /u01/app/oracle/keys/myapp/mfa.key
That gives you 64 hex characters, which is exactly 32 bytes, which is exactly an AES-256 key. Now the DBA creates a directory object pointing at that folder and grants what the schema needs. Replace app_owner with your own parsing schema.
01_dba_prerequisites.sql (run as DBA)
-- adjust the path to the folder you created above
create or replace directory app_keys as '/u01/app/oracle/keys/myapp';
grant read on directory app_keys to app_owner;
grant execute on sys.dbms_crypto to app_owner;
grant execute on sys.utl_file to app_owner; -- usually PUBLIC already; harmless if so
-- check (as DBA): the key file must be readable from the database
declare
v_f utl_file.file_type;
v_hex varchar2(200);
begin
v_f := utl_file.fopen('APP_KEYS', 'mfa.key', 'r');
utl_file.get_line(v_f, v_hex);
utl_file.fclose(v_f);
if not regexp_like(trim(v_hex), '^[0-9A-Fa-f]{64}$') then
raise_application_error(-20000, 'mfa.key found but it is not 64 hex characters - create it again with openssl rand -hex 32');
end if;
dbms_output.put_line('OK - key file readable, 64 hex characters.');
end;
/Watch outKeep an offline backup of mfa.key (a password manager or a safe). If you lose it, every user who set up 2FA has to be reset and enrol again. And never put the key into git, an APEX export or a table.
Step 2: The tables
We need three new columns on the users table and a log table. The log table does three jobs at once: it's the audit trail, it's the counter for the lock-out, and it's what stops a code from being used twice (that's the umlg_mfa_step column).
02_ddl.sql
alter table user_ add (
mfa_enabled_yn varchar2(4) default 'NO' not null,
mfa_secret varchar2(400), -- hex(IV) || hex(AES-256-CBC(secret)); key file APP_KEYS/mfa.key
mfa_enrolled timestamp(6),
constraint user_mfa_enabled_yn_ck check (mfa_enabled_yn in ('YES','NO'))
);
comment on column user_.mfa_enabled_yn is '2FA (TOTP authenticator app) switched on - YES/NO';
comment on column user_.mfa_secret is 'TOTP secret, AES-256 encrypted (hex IV || hex cipher). Key is outside the DB.';
comment on column user_.mfa_enrolled is 'When the user confirmed 2FA set-up';
create sequence umlg_seq nocache;
create table user_mfa_log (
umlg_id number default on null umlg_seq.nextval not null,
umlg_user_fk number(10),
umlg_username varchar2(256) not null, -- lower(trim(username)) - lock-out key
umlg_result varchar2(10) not null,
umlg_reason varchar2(30) not null,
umlg_mfa_step number, -- TOTP time step of an accepted code (replay protection)
umlg_ip varchar2(50),
umlg_app_id number,
umlg_remark varchar2(4000),
umlg_created timestamp(6) default systimestamp not null,
umlg_created_by varchar2(256),
constraint pk_umlg_id primary key (umlg_id),
constraint umlg_user_fk foreign key (umlg_user_fk) references user_ (user_id) on delete cascade,
constraint umlg_result_ck check (umlg_result in ('SUCCESS','FAILED','EVENT')),
constraint umlg_reason_ck check (umlg_reason in ('PASSWORD_OK','PASSWORD_FAILED','MFA_OK','MFA_FAILED',
'MFA_ENABLED','MFA_RESET','LOCKOUT'))
);
comment on table user_mfa_log is '2FA / lock-out log for MyApp (APP_MFA_PKG). Written in autonomous transactions.';
create index umlg_username_created_idx on user_mfa_log (umlg_username, umlg_created);
create index umlg_user_step_idx on user_mfa_log (umlg_user_fk, umlg_mfa_step);Adding a column with a DEFAULT is a metadata-only change in modern Oracle, so this is quick even on a big users table, and it won't fire any row-level triggers.
| Column | Why it's there |
|---|
mfa_enabled_yn | YES only after the user confirmed the first code. Until then the secret is "pending". |
mfa_secret | Random IV plus the AES-256 cipher text, both as hex. Never the plain secret. |
mfa_enrolled | When the user switched 2FA on. Handy for support questions. |
umlg_result | SUCCESS, FAILED or EVENT. |
umlg_reason | PASSWORD_OK, PASSWORD_FAILED, MFA_OK, MFA_FAILED, MFA_ENABLED, MFA_RESET, LOCKOUT. |
umlg_mfa_step | The TOTP time step of an accepted code. Used for replay protection. |
Step 3: The package spec
Everything lives in one package, APP_MFA_PKG. The constants at the top are the knobs you'll want to tune: the name shown in the authenticator app, the page numbers, and the lock-out limits.
03_app_mfa_pkg.pks
create or replace package app_mfa_pkg authid definer as
c_issuer constant varchar2(50) := 'MyApp'; -- name shown in the authenticator app
c_page_verify constant pls_integer := 9996;
c_page_enroll constant pls_integer := 9997;
c_max_failures constant pls_integer := 5;
c_lock_minutes constant pls_integer := 15;
c_msg_locked constant varchar2(200) := 'Too many failed attempts. Your account is locked for 15 minutes.';
c_msg_wrong constant varchar2(200) := 'The verification code is not correct or has expired.';
-- TRUE when the user MUST use 2FA (today: never - opt-in). Change here for the mandatory variant.
function fn_mfa_required (pi_user_id in user_.user_id%type) return boolean;
-- state for the APEX session after the password check: OK / VERIFY / ENROLL / LOCKED
function fn_initial_state (pi_username in varchar2) return varchar2;
-- TRUE when the current APEX session has passed 2FA (or needs none). No APEX session (job) = TRUE.
function fn_session_mfa_ok return boolean;
-- lock-out (shared by password and code): 5 failures in 15 min since the last good login
function fn_is_locked (pi_username in varchar2) return boolean;
-- called by APEX_CUSTOM_AUTH.AUTHENTICATE_USER
procedure pr_password_failed (pi_username in varchar2);
procedure pr_password_ok (pi_username in varchar2);
-- enrolment (page 9997): new pending secret for APP_USER, returns the otpauth:// URI for the QR code
function fn_generate_2fa_secret return varchar2;
-- pending secret of APP_USER as Base32 (groups of 4) - only while enrolling, else NULL
function fn_secret_base32 return varchar2;
-- TOTP code for a Base32 secret and time step (tests)
function fn_totp (pi_secret_base32 in varchar2, pi_step in number default null) return varchar2;
-- page 9996: check the code of APP_USER; sets APP_MFA_STATE = OK or raises an error
procedure pr_process_2fa_verify (pi_code in varchar2);
-- page 9997: confirm the enrolment with the first code; sets MFA_ENABLED_YN = YES
procedure pr_confirm_enrollment (pi_code in varchar2);
-- admin app (administrators only): switch 2FA off for a user (lost phone); user can set it up again
procedure pr_reset_mfa (pi_user_id in user_.user_id%type);
-- YES / NO for APP_USER (menu entry "Set up two-factor authentication")
function fn_my_mfa_enabled_yn return varchar2;
-- YES / NO for any user (admin page "Reset 2FA" button condition)
function fn_user_mfa_enabled_yn (pi_user_id in user_.user_id%type) return varchar2;
end app_mfa_pkg;
/
Step 4: The package body, piece by piece
The body is about 370 lines. Rather than dropping it on you in one go, let's go through the parts that matter. The complete listing is at the end of this section.
4.1 Reading the key and encrypting the secret
The key is read once per database session and cached in a package variable. For encryption we generate a fresh random IV every time and store it in front of the cipher text. That way two users with (by some miracle) the same secret still get different stored values.
Key file, encrypt, decrypt
function fn_key return raw is
v_f utl_file.file_type;
v_hex varchar2(200);
begin
if g_key is null then
v_f := utl_file.fopen(c_dir, c_file, 'r');
utl_file.get_line(v_f, v_hex);
utl_file.fclose(v_f);
g_key := hextoraw(trim(v_hex));
end if;
return g_key;
exception
when others then
if utl_file.is_open(v_f) then utl_file.fclose(v_f); end if;
raise_application_error(-20301, 'The key for two-factor authentication is not available. Please inform the application administrator.');
end fn_key;
function fn_encrypt (pi_raw in raw) return varchar2 is
v_iv raw(16) := dbms_crypto.randombytes(16);
begin
return rawtohex(v_iv) || rawtohex(dbms_crypto.encrypt(src => pi_raw, typ => c_enc, key => fn_key, iv => v_iv));
end fn_encrypt;
function fn_decrypt (pi_hex in varchar2) return raw is
begin
return dbms_crypto.decrypt(src => hextoraw(substr(pi_hex, 33)), typ => c_enc, key => fn_key,
iv => hextoraw(substr(pi_hex, 1, 32)));
end fn_decrypt;4.2 Base32 encode and decode
Authenticator apps want the secret in Base32 (A to Z plus 2 to 7). Oracle doesn't ship a Base32 function, so we build one. It's not pretty, it just turns bytes into a string of bits and reads them back 5 at a time. The decoder ignores spaces, padding and lower case, so users can type the key the way they see it.
Base32
function fn_b32_encode (pi_raw in raw) return varchar2 is
v_hex varchar2(400) := rawtohex(pi_raw);
v_bits varchar2(2000);
v_out varchar2(400);
v_n pls_integer;
begin
for i in 1 .. length(v_hex) loop
v_n := to_number(substr(v_hex, i, 1), 'X');
v_bits := v_bits || case when bitand(v_n,8)>0 then '1' else '0' end || case when bitand(v_n,4)>0 then '1' else '0' end
|| case when bitand(v_n,2)>0 then '1' else '0' end || case when bitand(v_n,1)>0 then '1' else '0' end;
end loop;
while mod(length(v_bits), 5) <> 0 loop v_bits := v_bits || '0'; end loop;
for i in 0 .. length(v_bits)/5 - 1 loop
v_n := 0;
for j in 1 .. 5 loop v_n := v_n * 2 + to_number(substr(v_bits, i*5 + j, 1)); end loop;
v_out := v_out || substr(c_b32, v_n + 1, 1);
end loop;
return v_out;
end fn_b32_encode;
function fn_b32_decode (pi_b32 in varchar2) return raw is
v_s varchar2(400) := upper(replace(replace(pi_b32, ' '), '='));
v_bits varchar2(2000);
v_hex varchar2(400);
v_n pls_integer;
begin
for i in 1 .. length(v_s) loop
v_n := instr(c_b32, substr(v_s, i, 1)) - 1;
if v_n < 0 then raise_application_error(-20302, 'Invalid key.'); end if;
for j in reverse 0 .. 4 loop v_bits := v_bits || case when bitand(v_n, power(2, j)) > 0 then '1' else '0' end; end loop;
end loop;
for i in 0 .. trunc(length(v_bits)/8) - 1 loop
v_n := 0;
for j in 1 .. 8 loop v_n := v_n * 2 + to_number(substr(v_bits, i*8 + j, 1)); end loop;
v_hex := v_hex || lpad(trim(to_char(v_n, 'XX')), 2, '0');
end loop;
return hextoraw(v_hex);
end fn_b32_decode;4.3 The TOTP code itself
This is the heart of it and it's surprisingly short. fn_current_step turns the current UTC time into a 30 second step number. fn_totp_raw does the HMAC, the dynamic truncation and the modulo, exactly as RFC 6238 describes.
TOTP (RFC 6238)
function fn_current_step return number is
begin
return floor((cast(sys_extract_utc(systimestamp) as date) - date '1970-01-01') * 86400 / 30);
end fn_current_step;
function fn_totp_raw (pi_key in raw, pi_step in number) return varchar2 is
v_msg raw(8) := hextoraw(lpad(trim(to_char(pi_step, 'XXXXXXXXXXXXXXXX')), 16, '0'));
v_mac raw(20) := dbms_crypto.mac(src => v_msg, typ => dbms_crypto.hmac_sh1, key => pi_key);
v_off pls_integer := bitand(to_number(rawtohex(utl_raw.substr(v_mac, 20, 1)), 'XX'), 15);
v_bin number := bitand(to_number(rawtohex(utl_raw.substr(v_mac, v_off + 1, 4)), 'XXXXXXXX'), 2147483647);
begin
return lpad(mod(v_bin, 1000000), 6, '0');
end fn_totp_raw;
TipAlways use UTC here. sys_extract_utc(systimestamp) gives you UTC no matter what timezone the DB server runs in. If you use sysdate on a server set to local time, every code will be wrong by a few hours.
4.4 Checking a code (with replay protection)
We accept the current step and one step either side, so a phone clock that's a little off still works. For each matching step we look in the log: if this user already logged in with that exact step in the last 5 minutes, the code is refused. That's the replay protection.
fn_check_code
function fn_check_code (pi_row in user_%rowtype, pi_code in varchar2, po_step out number) return boolean is
v_key raw(20);
v_code varchar2(20) := regexp_replace(pi_code, '\s');
v_now number := fn_current_step;
v_used pls_integer;
begin
if pi_row.mfa_secret is null or not regexp_like(nvl(v_code, 'x'), '^\d{6}$') then
return false;
end if;
v_key := fn_decrypt(pi_row.mfa_secret);
for d in -1 .. 1 loop
if fn_totp_raw(v_key, v_now + d) = v_code then
select count(*) into v_used
from user_mfa_log
where umlg_user_fk = pi_row.user_id
and umlg_mfa_step = v_now + d
and umlg_result = 'SUCCESS'
and umlg_created >= systimestamp - interval '5' minute;
if v_used = 0 then
po_step := v_now + d;
return true;
end if;
end if;
end loop;
return false;
end fn_check_code;4.5 Lock-out: one counter for passwords and codes
Wrong passwords and wrong codes share the same counter. That matters. If they had separate counters, an attacker who knows the password could log in again and again and get 5 fresh code guesses every time. The counter only resets after a fully successful login or an admin reset.
fn_is_locked and pr_password_ok
function fn_is_locked (pi_username in varchar2) return boolean is
v_name varchar2(256) := fn_norm(pi_username);
v_since timestamp;
v_cnt pls_integer;
begin
-- counter starts after the last good login (code accepted / password ok without 2FA) or admin reset
select max(umlg_created) into v_since
from user_mfa_log
where umlg_username = v_name
and (umlg_result = 'SUCCESS' or umlg_reason = 'MFA_RESET');
select count(*) into v_cnt
from user_mfa_log
where umlg_username = v_name
and umlg_result = 'FAILED'
and umlg_created >= systimestamp - numtodsinterval(c_lock_minutes, 'MINUTE')
and umlg_created > nvl(v_since, timestamp '1900-01-01 00:00:00');
return v_cnt >= c_max_failures;
end fn_is_locked;
procedure pr_password_ok (pi_username in varchar2) is
v_row user_%rowtype := fn_user_by_name(pi_username);
begin
-- with 2FA on, the password alone is not a good login: the counter keeps running until the code is accepted
-- (otherwise every new password login would give 5 new code guesses)
if nvl(v_row.mfa_enabled_yn, 'NO') = 'NO' then
pr_log(v_row.user_id, pi_username, 'SUCCESS', 'PASSWORD_OK');
end if;
end pr_password_ok;Notice the check in pr_password_ok: for a user with 2FA on, a correct password is not logged as a success. Only the accepted code counts as a good login.
4.6 Deciding the state after the password
This is the function the gate calls. It returns one of the four states we saw earlier. Change fn_mfa_required to return true (or check a role or an organisation) and you've switched from opt-in to mandatory.
fn_mfa_required and fn_initial_state
function fn_mfa_required (pi_user_id in user_.user_id%type) return boolean is
begin
-- OPT-IN: nobody is forced into the enrolment. Mandatory variant: return true (or a role / organisation check);
-- then users without 2FA get state ENROLL and can only open page 9997 (show "Sign out" instead of "Cancel" there).
return false;
end fn_mfa_required;
function fn_initial_state (pi_username in varchar2) return varchar2 is
v_row user_%rowtype := fn_user_by_name(pi_username);
begin
if v_row.user_id is null then
return 'OK'; -- users without a USER_ row are never blocked by 2FA
end if;
if fn_is_locked(pi_username) then
return 'LOCKED';
end if;
if v_row.mfa_enabled_yn = 'YES' then
return 'VERIFY';
end if;
if fn_mfa_required(v_row.user_id) then
return 'ENROLL';
end if;
return 'OK';
end fn_initial_state;4.7 Enrolment and the otpauth:// link
When the user opens the setup page, we generate 20 random bytes, store them encrypted as a pending secret (2FA stays off) and return an otpauth:// URI. APEX 24.2 has a native QR Code item, so we just put that URI into it and the browser draws the QR code. No JavaScript library needed.
fn_generate_2fa_secret
function fn_generate_2fa_secret return varchar2 is
v_row user_%rowtype := fn_me;
v_sec raw(20) := dbms_crypto.randombytes(20);
begin
if v_row.mfa_enabled_yn = 'YES' then
raise_application_error(-20304, 'Two-factor authentication is already set up.');
end if;
update user_
set mfa_secret = fn_encrypt(v_sec) -- pending: MFA_ENABLED_YN stays NO until the first code
where user_id = v_row.user_id;
return 'otpauth://totp/' || utl_url.escape(c_issuer || ':' || fn_norm(v_row.username), true)
|| '?secret=' || fn_b32_encode(v_sec)
|| '&issuer=' || utl_url.escape(c_issuer, true)
|| '&algorithm=SHA1&digits=6&period=30';
end fn_generate_2fa_secret;2FA only gets switched on after the user types the first code from the app. That proves the phone really has the secret, so nobody locks themselves out by scanning half a QR code.
4.8 The logging procedure
Small detail, big impact. Every log row is written in an autonomous_transaction. When the code is wrong we raise an error, APEX rolls back the page submit, and without the autonomous transaction the failed attempt would be rolled back too. The lock-out would never trigger.
pr_log
-- every log row in its own transaction: survives the rollback of a failed page submit
procedure pr_log (pi_user_id in number,
pi_username in varchar2,
pi_result in varchar2,
pi_reason in varchar2,
pi_step in number default null,
pi_remark in varchar2 default null) is
pragma autonomous_transaction;
begin
insert into user_mfa_log (umlg_user_fk, umlg_username, umlg_result, umlg_reason, umlg_mfa_step,
umlg_ip, umlg_app_id, umlg_remark, umlg_created_by)
values (pi_user_id, fn_norm(pi_username), pi_result, pi_reason, pi_step,
fn_client_ip, to_number(sys_context('apex$session','app_id')), substr(pi_remark, 1, 4000),
coalesce(sys_context('apex$session','app_user'), sys_context('userenv','session_user')));
commit; -- autonomous transaction only
end pr_log;4.9 The complete package body
Here's everything together, ready to compile.
04_app_mfa_pkg.pkb
create or replace package body app_mfa_pkg as
c_b32 constant varchar2(32) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
c_enc constant pls_integer := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
c_dir constant varchar2(30) := 'APP_KEYS';
c_file constant varchar2(30) := 'mfa.key';
g_key raw(32);
---------------------------------------------------------------------------------------------------
-- helpers
---------------------------------------------------------------------------------------------------
function fn_norm (pi_username in varchar2) return varchar2 is
begin
return lower(trim(pi_username));
end fn_norm;
function fn_client_ip return varchar2 is
begin
return substr(owa_util.get_cgi_env('REMOTE_ADDR'), 1, 50);
exception
when others then return null; -- no OWA context (SQL*Plus / job)
end fn_client_ip;
-- every log row in its own transaction: survives the rollback of a failed page submit
procedure pr_log (pi_user_id in number,
pi_username in varchar2,
pi_result in varchar2,
pi_reason in varchar2,
pi_step in number default null,
pi_remark in varchar2 default null) is
pragma autonomous_transaction;
begin
insert into user_mfa_log (umlg_user_fk, umlg_username, umlg_result, umlg_reason, umlg_mfa_step,
umlg_ip, umlg_app_id, umlg_remark, umlg_created_by)
values (pi_user_id, fn_norm(pi_username), pi_result, pi_reason, pi_step,
fn_client_ip, to_number(sys_context('apex$session','app_id')), substr(pi_remark, 1, 4000),
coalesce(sys_context('apex$session','app_user'), sys_context('userenv','session_user')));
commit; -- autonomous transaction only
end pr_log;
function fn_key return raw is
v_f utl_file.file_type;
v_hex varchar2(200);
begin
if g_key is null then
v_f := utl_file.fopen(c_dir, c_file, 'r');
utl_file.get_line(v_f, v_hex);
utl_file.fclose(v_f);
g_key := hextoraw(trim(v_hex));
end if;
return g_key;
exception
when others then
if utl_file.is_open(v_f) then utl_file.fclose(v_f); end if;
raise_application_error(-20301, 'The key for two-factor authentication is not available. Please inform the application administrator.');
end fn_key;
function fn_encrypt (pi_raw in raw) return varchar2 is
v_iv raw(16) := dbms_crypto.randombytes(16);
begin
return rawtohex(v_iv) || rawtohex(dbms_crypto.encrypt(src => pi_raw, typ => c_enc, key => fn_key, iv => v_iv));
end fn_encrypt;
function fn_decrypt (pi_hex in varchar2) return raw is
begin
return dbms_crypto.decrypt(src => hextoraw(substr(pi_hex, 33)), typ => c_enc, key => fn_key,
iv => hextoraw(substr(pi_hex, 1, 32)));
end fn_decrypt;
function fn_b32_encode (pi_raw in raw) return varchar2 is
v_hex varchar2(400) := rawtohex(pi_raw);
v_bits varchar2(2000);
v_out varchar2(400);
v_n pls_integer;
begin
for i in 1 .. length(v_hex) loop
v_n := to_number(substr(v_hex, i, 1), 'X');
v_bits := v_bits || case when bitand(v_n,8)>0 then '1' else '0' end || case when bitand(v_n,4)>0 then '1' else '0' end
|| case when bitand(v_n,2)>0 then '1' else '0' end || case when bitand(v_n,1)>0 then '1' else '0' end;
end loop;
while mod(length(v_bits), 5) <> 0 loop v_bits := v_bits || '0'; end loop;
for i in 0 .. length(v_bits)/5 - 1 loop
v_n := 0;
for j in 1 .. 5 loop v_n := v_n * 2 + to_number(substr(v_bits, i*5 + j, 1)); end loop;
v_out := v_out || substr(c_b32, v_n + 1, 1);
end loop;
return v_out;
end fn_b32_encode;
function fn_b32_decode (pi_b32 in varchar2) return raw is
v_s varchar2(400) := upper(replace(replace(pi_b32, ' '), '='));
v_bits varchar2(2000);
v_hex varchar2(400);
v_n pls_integer;
begin
for i in 1 .. length(v_s) loop
v_n := instr(c_b32, substr(v_s, i, 1)) - 1;
if v_n < 0 then raise_application_error(-20302, 'Invalid key.'); end if;
for j in reverse 0 .. 4 loop v_bits := v_bits || case when bitand(v_n, power(2, j)) > 0 then '1' else '0' end; end loop;
end loop;
for i in 0 .. trunc(length(v_bits)/8) - 1 loop
v_n := 0;
for j in 1 .. 8 loop v_n := v_n * 2 + to_number(substr(v_bits, i*8 + j, 1)); end loop;
v_hex := v_hex || lpad(trim(to_char(v_n, 'XX')), 2, '0');
end loop;
return hextoraw(v_hex);
end fn_b32_decode;
function fn_current_step return number is
begin
return floor((cast(sys_extract_utc(systimestamp) as date) - date '1970-01-01') * 86400 / 30);
end fn_current_step;
function fn_totp_raw (pi_key in raw, pi_step in number) return varchar2 is
v_msg raw(8) := hextoraw(lpad(trim(to_char(pi_step, 'XXXXXXXXXXXXXXXX')), 16, '0'));
v_mac raw(20) := dbms_crypto.mac(src => v_msg, typ => dbms_crypto.hmac_sh1, key => pi_key);
v_off pls_integer := bitand(to_number(rawtohex(utl_raw.substr(v_mac, 20, 1)), 'XX'), 15);
v_bin number := bitand(to_number(rawtohex(utl_raw.substr(v_mac, v_off + 1, 4)), 'XXXXXXXX'), 2147483647);
begin
return lpad(mod(v_bin, 1000000), 6, '0');
end fn_totp_raw;
-- the logged-in person is always APP_USER, never USER_ID (a delegated session can point USER_ID somewhere else)
function fn_me return user_%rowtype is
v_row user_%rowtype;
begin
select * into v_row
from user_
where lower(trim(username)) = fn_norm(v('APP_USER'))
fetch first row only;
return v_row;
exception
when no_data_found then raise_application_error(-20303, 'Please sign in again.');
end fn_me;
function fn_user_by_name (pi_username in varchar2) return user_%rowtype is
v_row user_%rowtype;
v_empty user_%rowtype; -- never assigned: every field NULL
begin
select * into v_row
from user_
where lower(trim(username)) = fn_norm(pi_username)
fetch first row only;
return v_row;
exception
when no_data_found then
return v_empty; -- user_id is null = unknown user
end fn_user_by_name;
---------------------------------------------------------------------------------------------------
-- public
---------------------------------------------------------------------------------------------------
function fn_totp (pi_secret_base32 in varchar2, pi_step in number default null) return varchar2 is
begin
return fn_totp_raw(fn_b32_decode(pi_secret_base32), nvl(pi_step, fn_current_step));
end fn_totp;
function fn_mfa_required (pi_user_id in user_.user_id%type) return boolean is
begin
-- OPT-IN: nobody is forced into the enrolment. Mandatory variant: return true (or a role / organisation check);
-- then users without 2FA get state ENROLL and can only open page 9997 (show "Sign out" instead of "Cancel" there).
return false;
end fn_mfa_required;
function fn_is_locked (pi_username in varchar2) return boolean is
v_name varchar2(256) := fn_norm(pi_username);
v_since timestamp;
v_cnt pls_integer;
begin
-- counter starts after the last good login (code accepted / password ok without 2FA) or admin reset
select max(umlg_created) into v_since
from user_mfa_log
where umlg_username = v_name
and (umlg_result = 'SUCCESS' or umlg_reason = 'MFA_RESET');
select count(*) into v_cnt
from user_mfa_log
where umlg_username = v_name
and umlg_result = 'FAILED'
and umlg_created >= systimestamp - numtodsinterval(c_lock_minutes, 'MINUTE')
and umlg_created > nvl(v_since, timestamp '1900-01-01 00:00:00');
return v_cnt >= c_max_failures;
end fn_is_locked;
procedure pr_password_failed (pi_username in varchar2) is
v_row user_%rowtype := fn_user_by_name(pi_username);
begin
pr_log(v_row.user_id, pi_username, 'FAILED', 'PASSWORD_FAILED');
if fn_is_locked(pi_username) then
pr_log(v_row.user_id, pi_username, 'EVENT', 'LOCKOUT', pi_remark => 'password');
end if;
end pr_password_failed;
procedure pr_password_ok (pi_username in varchar2) is
v_row user_%rowtype := fn_user_by_name(pi_username);
begin
-- with 2FA on, the password alone is not a good login: the counter keeps running until the code is accepted
-- (otherwise every new password login would give 5 new code guesses)
if nvl(v_row.mfa_enabled_yn, 'NO') = 'NO' then
pr_log(v_row.user_id, pi_username, 'SUCCESS', 'PASSWORD_OK');
end if;
end pr_password_ok;
function fn_initial_state (pi_username in varchar2) return varchar2 is
v_row user_%rowtype := fn_user_by_name(pi_username);
begin
if v_row.user_id is null then
return 'OK'; -- users without a USER_ row are never blocked by 2FA
end if;
if fn_is_locked(pi_username) then
return 'LOCKED';
end if;
if v_row.mfa_enabled_yn = 'YES' then
return 'VERIFY';
end if;
if fn_mfa_required(v_row.user_id) then
return 'ENROLL';
end if;
return 'OK';
end fn_initial_state;
function fn_session_mfa_ok return boolean is
begin
if sys_context('apex$session','app_session') is null then
return true; -- no APEX session (job / test): 2FA is a session matter
end if;
return nvl(v('APP_MFA_STATE'), 'X') = 'OK';
end fn_session_mfa_ok;
function fn_generate_2fa_secret return varchar2 is
v_row user_%rowtype := fn_me;
v_sec raw(20) := dbms_crypto.randombytes(20);
begin
if v_row.mfa_enabled_yn = 'YES' then
raise_application_error(-20304, 'Two-factor authentication is already set up.');
end if;
update user_
set mfa_secret = fn_encrypt(v_sec) -- pending: MFA_ENABLED_YN stays NO until the first code
where user_id = v_row.user_id;
return 'otpauth://totp/' || utl_url.escape(c_issuer || ':' || fn_norm(v_row.username), true)
|| '?secret=' || fn_b32_encode(v_sec)
|| '&issuer=' || utl_url.escape(c_issuer, true)
|| '&algorithm=SHA1&digits=6&period=30';
end fn_generate_2fa_secret;
function fn_secret_base32 return varchar2 is
v_row user_%rowtype := fn_me;
begin
if v_row.mfa_secret is null or v_row.mfa_enabled_yn = 'YES' then
return null; -- the secret is only shown while enrolling
end if;
return trim(regexp_replace(fn_b32_encode(fn_decrypt(v_row.mfa_secret)), '(.{4})', '\1 '));
end fn_secret_base32;
function fn_check_code (pi_row in user_%rowtype, pi_code in varchar2, po_step out number) return boolean is
v_key raw(20);
v_code varchar2(20) := regexp_replace(pi_code, '\s');
v_now number := fn_current_step;
v_used pls_integer;
begin
if pi_row.mfa_secret is null or not regexp_like(nvl(v_code, 'x'), '^\d{6}$') then
return false;
end if;
v_key := fn_decrypt(pi_row.mfa_secret);
for d in -1 .. 1 loop
if fn_totp_raw(v_key, v_now + d) = v_code then
select count(*) into v_used
from user_mfa_log
where umlg_user_fk = pi_row.user_id
and umlg_mfa_step = v_now + d
and umlg_result = 'SUCCESS'
and umlg_created >= systimestamp - interval '5' minute;
if v_used = 0 then
po_step := v_now + d;
return true;
end if;
end if;
end loop;
return false;
end fn_check_code;
procedure pr_fail (pi_row in user_%rowtype) is
begin
pr_log(pi_row.user_id, pi_row.username, 'FAILED', 'MFA_FAILED');
if fn_is_locked(pi_row.username) then
pr_log(pi_row.user_id, pi_row.username, 'EVENT', 'LOCKOUT', pi_remark => 'code');
apex_util.set_session_state('APP_MFA_STATE', 'LOCKED'); -- process "Force 2FA" ends the session on the next page view
raise_application_error(-20305, c_msg_locked);
end if;
raise_application_error(-20306, c_msg_wrong);
end pr_fail;
procedure pr_process_2fa_verify (pi_code in varchar2) is
v_row user_%rowtype := fn_me;
v_step number;
begin
if fn_is_locked(v_row.username) then
raise_application_error(-20305, c_msg_locked);
end if;
if v_row.mfa_enabled_yn <> 'YES' then
raise_application_error(-20307, 'Two-factor authentication is not set up.');
end if;
if fn_check_code(v_row, pi_code, v_step) then
pr_log(v_row.user_id, v_row.username, 'SUCCESS', 'MFA_OK', v_step);
apex_util.set_session_state('APP_MFA_STATE', 'OK');
else
pr_fail(v_row);
end if;
end pr_process_2fa_verify;
procedure pr_confirm_enrollment (pi_code in varchar2) is
v_row user_%rowtype := fn_me;
v_step number;
begin
if v_row.mfa_enabled_yn = 'YES' then
raise_application_error(-20304, 'Two-factor authentication is already set up.');
end if;
if fn_is_locked(v_row.username) then
raise_application_error(-20305, c_msg_locked);
end if;
if fn_check_code(v_row, pi_code, v_step) then
update user_
set mfa_enabled_yn = 'YES',
mfa_enrolled = systimestamp
where user_id = v_row.user_id;
pr_log(v_row.user_id, v_row.username, 'SUCCESS', 'MFA_OK', v_step);
pr_log(v_row.user_id, v_row.username, 'EVENT', 'MFA_ENABLED');
apex_util.set_session_state('APP_MFA_STATE', 'OK');
else
pr_fail(v_row);
end if;
end pr_confirm_enrollment;
procedure pr_reset_mfa (pi_user_id in user_.user_id%type) is
v_name user_.username%type;
begin
-- only administrators may reset (swap in your own admin check)
if not apex_authorization.is_authorized('Administration Rights') then
raise_application_error(-20308, 'Only the application administrator can reset two-factor authentication.');
end if;
update user_
set mfa_enabled_yn = 'NO',
mfa_secret = null,
mfa_enrolled = null
where user_id = pi_user_id
returning username into v_name;
if v_name is null then
raise_application_error(-20309, 'User not found.');
end if;
pr_log(pi_user_id, v_name, 'EVENT', 'MFA_RESET', pi_remark => 'by ' || v('APP_USER'));
end pr_reset_mfa;
function fn_my_mfa_enabled_yn return varchar2 is
v_yn user_.mfa_enabled_yn%type;
begin
select max(mfa_enabled_yn) into v_yn
from user_
where lower(trim(username)) = fn_norm(v('APP_USER'));
return nvl(v_yn, 'NO');
end fn_my_mfa_enabled_yn;
function fn_user_mfa_enabled_yn (pi_user_id in user_.user_id%type) return varchar2 is
v_yn user_.mfa_enabled_yn%type;
begin
select max(mfa_enabled_yn) into v_yn from user_ where user_id = pi_user_id;
return nvl(v_yn, 'NO');
end fn_user_mfa_enabled_yn;
end app_mfa_pkg;
/Step 5: Hooking into the custom authentication
Our existing package APEX_CUSTOM_AUTH has two procedures that the authentication scheme calls: AUTHENTICATE_USER (the password check, set as the Authentication Function) and POST_AUTHENTICATE_USER (loads the session, set as the Post-Authentication Procedure). We only touch three spots. I'll show just the changed lines, since your own package will look different.
5.1 In AUTHENTICATE_USER
Before comparing the password, check the lock. After a wrong password, count it. After a correct one, reset the counter.
APEX_CUSTOM_AUTH.AUTHENTICATE_USER (changed lines)
-- 2FA: 5 wrong passwords / codes in 15 minutes lock the user name
if app_mfa_pkg.fn_is_locked(l_user_name) then
apex_util.set_authentication_result(7);
apex_util.set_custom_auth_status(app_mfa_pkg.c_msg_locked);
return false;
end if;
select hash_password(l_user_name, p_password) into l_hashed_password from dual;
if l_hashed_password != l_password then
app_mfa_pkg.pr_password_failed(l_user_name); -- 2FA: shared lock-out counter
apex_util.set_authentication_result(4);
return false;
end if;
-- ... your other checks (active user, expiry and so on) ...
app_mfa_pkg.pr_password_ok(l_user_name); -- 2FA: resets the counter (only for users without 2FA)
apex_util.set_authentication_result(0);
return true;5.2 The gate in POST_AUTHENTICATE_USER
This goes right at the start of the procedure, before anything is loaded into the session.
APEX_CUSTOM_AUTH.POST_AUTHENTICATE_USER (the gate)
procedure post_authenticate_user (p_username in varchar2 default v('APP_USER')) is
l_mfa_state varchar2(20); -- 2FA
-- ... your existing variables ...
begin
-- 2FA gate: on a fresh login APP_MFA_STATE is empty (the login page clears it).
-- With 2FA on, stop here: no USER_ID / role / auth profile in the session until the code is entered.
-- Page 9996 calls this procedure again after the code (state = OK).
if nvl(v('APP_MFA_STATE'), '-') <> 'OK' then
l_mfa_state := app_mfa_pkg.fn_initial_state(p_username);
apex_util.set_session_state('APP_MFA_STATE', l_mfa_state);
if l_mfa_state <> 'OK' then
return;
end if;
end if;
-- ... your existing code that loads USER_ID, roles, auth profile and so on ...
end post_authenticate_user;NoteWhy keep the session empty instead of just redirecting? Because the redirect in Force 2FA only happens when a page is rendered. A forged page submit or an AJAX call would skip it. With an empty session, those requests find no user ID and no auth profile, so every query and every authorization scheme returns nothing. It's a second line of defence, and it costs us nothing.
Good news: after steps 1 to 5 nothing changes for your users yet. Nobody has 2FA switched on, so the gate returns OK for everyone. The only new behaviour is the lock-out after 5 wrong passwords.
Step 6: The APEX side
6.1 Application item
Shared Components, Application Items, Create. Name it APP_MFA_STATE, scope Application, and set Session State Protection to Restricted: May not be set from browser. That last setting is important. Without it, someone could try to set the state to OK through the URL.
6.2 Login page: clear the state
In the login process on page 9999, add one line before the login call, so a new login never inherits a state from an old one.
Page 9999, process "Login"
apex_util.set_session_state('APP_MFA_STATE', null); -- 2FA: fresh login, no state from before
apex_authentication.login(
p_username => :P9999_USERNAME,
p_password => :P9999_PASSWORD
);6.3 Application process "Force 2FA"
Shared Components, Application Processes, Create. Sequence 0, point On Load: Before Header, type PL/SQL. Server-side condition: PL/SQL Expression :APP_MFA_STATE in ('VERIFY','ENROLL','LOCKED').
Application process: Force 2FA
case :APP_MFA_STATE
when 'LOCKED' then
apex_authentication.logout(p_session_id => :APP_SESSION, p_app_id => :APP_ID);
when 'VERIFY' then
if :APP_PAGE_ID not in (9996, 9999, 102) then
apex_util.redirect_url(apex_page.get_url(p_page => 9996));
end if;
when 'ENROLL' then
if :APP_PAGE_ID not in (9997, 9999, 102) then
apex_util.redirect_url(apex_page.get_url(p_page => 9997));
end if;
else null;
end case;Page 102 in our app is the logout target. Swap the page numbers for yours.
6.4 Page 9996: enter the code
Create a blank page 9996 with the same page template as your login page, so there's no menu or navigation bar. It must require authentication (it's not public). Then add these components:
| Component | Settings |
|---|
| Region | Static Content. Text: Open your authenticator app and enter the 6-digit code for MyApp. |
P9996_CODE | Text Field, Value Required On, Maximum Length 7, placeholder 123456, icon fa-mobile, Submit when Enter pressed On. Custom Attributes: inputmode="numeric" autocomplete="one-time-code" pattern="[0-9 ]*" |
| Button VERIFY | Hot, Submit Page |
| Button SIGN_OUT | Redirect to URL &LOGOUT_URL. |
The inputmode and autocomplete attributes are worth adding: phones show the number pad, and iOS can even offer the code straight from the keyboard.
Page 9996 on the left, and how it looks after a wrong code on the right.
A Guard process (Pre-Rendering, Before Header) makes sure nobody lands here without a reason:
Page 9996, process "Guard"
if :APP_MFA_STATE is null or :APP_MFA_STATE = 'OK' then
apex_util.redirect_url(apex_page.get_url(p_page => 1)); -- nothing to verify
elsif :APP_MFA_STATE = 'LOCKED' or app_mfa_pkg.fn_is_locked(:APP_USER) then
apex_authentication.logout(p_session_id => :APP_SESSION, p_app_id => :APP_ID);
end if;
And the Verify code process runs when VERIFY is pressed (error display: Inline in Notification):
Page 9996, process "Verify code"
begin
app_mfa_pkg.pr_process_2fa_verify(pi_code => :P9996_CODE); -- raises an error when wrong
-- 2FA passed: load the session now (POST_AUTHENTICATE_USER returned early at login)
apex_custom_auth.post_authenticate_user;
apex_authorization.reset_cache; -- schemes were cached on an empty session
-- the "After Login" application computations ran on the empty session, so run them again here
apex_util.set_session_state('IS_ADMIN',
case when apex_authorization.is_authorized('Administration Rights') then 'YES' else 'NO' end);
end;Watch outThis one bit us during testing. APEX caches authorization scheme results per session, and the first check happened while the session was still empty. Without apex_authorization.reset_cache every menu entry stayed hidden after a successful code. Same story for any After Login application computations: they already ran on the empty session, so you have to repeat them here.
Finish the page with a Clear Session State process for page 9996 and a branch to page 1.
6.5 Page 9997: set up 2FA
This is where users switch it on. Normal page template this time, requires authentication.
| Component | Settings |
|---|
| Region | Static Content: 1. Install an authenticator app. 2. Scan the QR code, or type the key. 3. Enter the 6-digit code the app shows. |
P9997_URI | QR Code item, Data Type URL, Size Medium. Session State: Store value encrypted On. |
P9997_SECRET | Display Only, label Key (if you cannot scan). Store value encrypted On. |
P9997_CODE | Same settings as P9996_CODE |
| Button CONFIRM | Label Switch on 2FA, Hot, Submit Page |
| Button CANCEL | Redirect to page 1 (in mandatory mode, sign out instead) |
Page 9997 with the native APEX QR Code item. The key shown here is a demo key.
Page 9997, process "Guard + secret" (Before Header)
if :APP_MFA_STATE = 'VERIFY' then
apex_util.redirect_url(apex_page.get_url(p_page => 9996));
elsif :APP_MFA_STATE = 'LOCKED' or app_mfa_pkg.fn_is_locked(:APP_USER) then
apex_authentication.logout(p_session_id => :APP_SESSION, p_app_id => :APP_ID);
elsif app_mfa_pkg.fn_my_mfa_enabled_yn = 'YES' then
apex_util.redirect_url(apex_page.get_url(p_page => 1)); -- already switched on
end if;
if :P9997_URI is null then
:P9997_URI := app_mfa_pkg.fn_generate_2fa_secret; -- new pending secret (2FA still off)
end if;
:P9997_SECRET := app_mfa_pkg.fn_secret_base32;
The CONFIRM process is one line: app_mfa_pkg.pr_confirm_enrollment(pi_code => :P9997_CODE); Add a Clear Session State process and a branch to page 1 with the success message Two-factor authentication is switched on. From your next login you will be asked for the code.
6.6 A menu entry
Copy your Change Password button or nav bar entry, point it at page 9997 (clear cache 9997) and give it the server-side condition app_mfa_pkg.fn_my_mfa_enabled_yn = 'NO'. Once the user has 2FA on, the entry simply disappears.
6.7 The admin "Reset 2FA" button
People lose phones. In the admin app, on the user profile page, add a button and a process:
| Component | Settings |
|---|
| Button RESET_2FA | Submit Page. Confirmation: Switch off two-factor authentication for this user? Condition: :P15_USER_ID is not null and app_mfa_pkg.fn_user_mfa_enabled_yn(:P15_USER_ID) = 'YES' |
| Process Reset 2FA | When button RESET_2FA pressed: app_mfa_pkg.pr_reset_mfa(pi_user_id => :P15_USER_ID); |
The admin reset. The user can enrol again after their next login.
pr_reset_mfa checks the caller is an administrator, wipes the secret, and logs a MFA_RESET event, which also clears any lock-out for that user.
Step 7: Testing it
7.1 Automated checks
RFC 6238 comes with official test vectors, so we can prove our PL/SQL produces exactly the right codes. The script also tests Base32 with spaces and lower case, the lock-out counter, and whether the key file is readable. It cleans up after itself.
06_tests.sql
set serveroutput on
declare
c_test_user constant varchar2(30) := 'zz_mfa_selftest_' || to_char(sysdate, 'hh24miss');
v_ok pls_integer := 0;
v_fail pls_integer := 0;
procedure chk (p_name varchar2, p_cond boolean) is
begin
if p_cond then v_ok := v_ok + 1; dbms_output.put_line('PASS ' || p_name);
else v_fail := v_fail + 1; dbms_output.put_line('FAIL ' || p_name); end if;
end;
begin
-- RFC 6238 SHA1 vectors (6-digit versions of 94287082 / 89005924), secret = ASCII "12345678901234567890"
chk('TOTP T=59s -> 287082', app_mfa_pkg.fn_totp('GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ', 1) = '287082');
chk('TOTP T=1234567890s -> 005924', app_mfa_pkg.fn_totp('GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ', 41152263) = '005924');
chk('TOTP T=1111111109s -> 081804', app_mfa_pkg.fn_totp('GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ', 37037036) = '081804');
chk('TOTP T=2000000000s -> 279037', app_mfa_pkg.fn_totp('GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ', 66666666) = '279037');
chk('Base32 with spaces / lower case', app_mfa_pkg.fn_totp('gezd gnbv gy3t qojq gezd gnbv gy3t qojq', 1) = '287082');
-- unknown user (no USER_ row) is never blocked by 2FA
chk('initial state of unknown user = OK', app_mfa_pkg.fn_initial_state(c_test_user) = 'OK');
-- lock-out: 4 failures = not locked, 5th = locked
for i in 1 .. 4 loop app_mfa_pkg.pr_password_failed(c_test_user); end loop;
chk('4 failures -> not locked', not app_mfa_pkg.fn_is_locked(c_test_user));
app_mfa_pkg.pr_password_failed(c_test_user);
chk('5 failures -> locked', app_mfa_pkg.fn_is_locked(c_test_user));
chk('initial state of locked user = LOCKED', app_mfa_pkg.fn_initial_state(c_test_user) = 'LOCKED');
-- clean up (log rows were committed by the autonomous logging)
delete from user_mfa_log where umlg_username = c_test_user;
commit;
-- key file readable? (encrypt needs it; the RFC tests above do not)
declare
v_f utl_file.file_type;
begin
v_f := utl_file.fopen('APP_KEYS', 'mfa.key', 'r'); utl_file.fclose(v_f);
chk('key file APP_KEYS/mfa.key readable by APP_OWNER', true);
exception when others then chk('key file APP_KEYS/mfa.key readable by APP_OWNER (' || sqlerrm || ')', false);
end;
dbms_output.put_line('----');
dbms_output.put_line(v_ok || ' passed, ' || v_fail || ' failed');
end;
/Expected output: 10 passed, 0 failed.
| Unix time | Step | Expected code |
|---|
| 59 | 1 | 287082 |
| 1111111109 | 37037036 | 081804 |
| 1234567890 | 41152263 | 005924 |
| 2000000000 | 66666666 | 279037 |
For manual testing you don't even need a phone. This tiny Python script calculates the same code from the key shown on page 9997:
totp_check.py
"""Independent TOTP (RFC 6238, SHA1, 6 digits, 30 s) - cross-check for APP_MFA_PKG.fn_totp.
python3 totp_check.py -> prints the RFC vectors
python3 totp_check.py <BASE32> -> prints the current code for a secret (e.g. P9997_SECRET while testing)"""
import base64, hmac, hashlib, struct, sys, time
def totp(secret_b32, step=None):
s = secret_b32.replace(' ', '').upper()
key = base64.b32decode(s + '=' * ((8 - len(s) % 8) % 8))
step = int(time.time() // 30) if step is None else step
h = hmac.new(key, struct.pack('>Q', step), hashlib.sha1).digest()
o = h[-1] & 15
return '%06d' % ((struct.unpack('>I', h[o:o + 4])[0] & 0x7fffffff) % 1000000)
if __name__ == '__main__':
if len(sys.argv) > 1:
print(totp(sys.argv[1]))
else:
k = 'GEZDGNBVGY3TQOJQGEZDGNBVGY3TQOJQ'
for t, exp in ((59, '287082'), (1111111109, '081804'), (1234567890, '005924'), (2000000000, '279037')):
got = totp(k, t // 30); print(t, t // 30, got, 'OK' if got == exp else 'MISMATCH ' + exp)Usage
python3 totp_check.py "JBSW Y3DP EHPK 3PXP"
7.2 Manual test plan
| # | Test | Expected |
|---|
| 1 | Log in as a normal user | Nothing changes, no code is asked |
| 2 | Menu, Set up 2FA, type a wrong code, then the right one | Error first, then success. Menu entry disappears |
| 3 | Log out, log in, then type a page 1 URL by hand | You land back on 9996 |
| 4 | Enter the correct code | Page 1 with menus, name and roles as before |
| 5 | Reuse the same code in a new login within 30 seconds | Refused (replay protection) |
| 6 | 5 wrong codes | Locked and signed out. Correct password also refused for 15 minutes |
| 7 | Admin presses Reset 2FA | Next login asks for no code |
| 8 | User with a forced password change and 2FA on | Code first, then the change password page |
And to see what happened behind the scenes:
Audit log
select umlg_created, umlg_username, umlg_result, umlg_reason, umlg_mfa_step, umlg_ip
from user_mfa_log
order by umlg_id desc;
Running it in production
- Clock sync. The DB server must use NTP. We only allow one step either side (+/- 30 seconds), so a drifting server clock will break every login.
- One key per environment. Dev, test and prod each get their own mfa.key. Back them up offline.
- Going mandatory. Change fn_mfa_required to return true (or check a role). Users without 2FA get the ENROLL state and can only open page 9997. Change Cancel to Sign out there.
- Other login paths. If you also have a REST or mobile login that checks the same password, it needs its own second factor. Otherwise it's a side door around all of this.
- Rollback plan. Remove the APEX bits first, reinstall the original APEX_CUSTOM_AUTH body, then run the rollback script below.
99_rollback.sql
drop package app_mfa_pkg;
drop table user_mfa_log purge;
drop sequence umlg_seq;
alter table user_ drop constraint user_mfa_enabled_yn_ck;
alter table user_ set unused (mfa_enabled_yn, mfa_secret, mfa_enrolled); -- fast; "alter table user_ drop unused columns" later in a quiet window
-- DBA (optional): drop directory app_keys; and delete the key file
Pros and cons of this approach
What works well
- ✔No external service, no SMS cost, no API keys. It's all PL/SQL.
- ✔Works with every standard authenticator app.
- ✔The secret is AES-256 encrypted and the key is outside the database.
- ✔Replay protection and a shared lock-out counter come for free from the log table.
- ✔Opt-in today, mandatory tomorrow with one function change.
- ✔Full audit trail of every login attempt.
What to keep in mind
- ✘You own the key file. Lose it and everyone has to re-enrol.
- ✘Base32 has to be hand-written in PL/SQL.
- ✘After Login computations have to be repeated on page 9996.
- ✘Depends on correct server time (NTP).
- ✘No recovery codes yet. A lost phone needs an admin.
- ✘Other login paths (REST, mobile) need separate work.
Summary
- TOTP 2FA in APEX needs nothing more than DBMS_CRYPTO, one package and two pages.
- Put the gate at the top of POST_AUTHENTICATE_USER and keep the session empty until the code is right.
- A restricted application item APP_MFA_STATE plus a Force 2FA process stop anyone from skipping page 9996.
- Encrypt the secret with AES-256 and keep the key in a file outside the database.
- Log in an autonomous transaction, and use that log for replay protection and the lock-out.
- Reset the authorization cache and rerun your After Login computations once the code is accepted.
- Test against the RFC 6238 vectors before you trust a single code.
That's the whole thing, exactly as we run it in production. If you build it in your own app and hit a snag, drop a comment below and I'll do my best to help.
Comments
Post a Comment