Multiplication Table: Custom Authentication package body for Oracle APEX

mtl_auth_pkg.pkb

create or replace package body mtl_auth_pkg
as

/**
* Constants
*/

c_from_email constant varchar2(100) := 'no-reply@my.email';
c_website    constant varchar2(100) := 'my site';
c_hostname   constant varchar2(100) := 'my hostname';


/**
*/
function custom_hash(
    p_username in varchar2,
    p_password in varchar2)
  return raw
is
  l_username varchar2(100);
  l_password varchar2(100);
  l_salt     varchar2(100) := 'my secret';
begin
  apex_debug.message(p_message => 'Begin custom_hash', p_level => 3) ;
  -- This function should be wrapped, as the hash algorhythm is exposed here.
  -- You can change the value of l_salt, but you much reset all of your passwords if you choose to do this.
  l_username := upper(p_username);
  l_password := upper(p_password);
  l_password := sha256.ENCRYPT(l_salt || l_username || l_password);
  apex_debug.message(p_message => 'End custom_hash', p_level => 3) ;
  return l_password;
end custom_hash;


/**
* Reset password email
*/
procedure mail_reset_password(
  p_email    in varchar2,
  p_url      in varchar2)
is
  l_body     clob;  
begin
  apex_debug.message(p_message => 'Reset password Multiplication Table account', p_level => 3) ;  
  l_body := '<p>Hi,</p>
             <p>We received a request to reset your password in the Multiplication Table app.</p>
             <p><a href="'||p_url||'">Reset Now.</a></p>
             <p>If you did not request this, you can simply ignore this email.</p>
             <p>Kind regards,<br/>
             The Multiplication Table Team</p>';

  apex_mail.send (
    p_to        => p_email,
    p_from      => c_from_email,
    p_body      => l_body,
    p_body_html => l_body,
    p_subj      => 'Reset password Multiplication Table account');

  apex_mail.push_queue;    

exception
when others 
then
  raise_application_error( - 20002, 'Issue sending reset password email.') ;
end mail_reset_password;


/**
*/
procedure create_account(
    p_email    in varchar2,
    p_password in varchar2)
is
  l_message varchar2(4000) ;
  l_password raw(64) ;
  l_user_id number;
begin
  apex_debug.message(p_message => 'Begin create_site_account', p_level => 3);

  l_password := utl_raw.cast_to_raw(DBMS_RANDOM.string('x',10));
  apex_debug.message(p_message => 'verify email exists', p_level => 3) ;

  begin
    select password
      into l_password
      from mtl_user
     where upper(email) = upper(p_email) ;
    l_message       := l_message || 'Email address already registered.';

  exception
  when no_data_found then
    apex_debug.message(p_message => 'email doesn''t exist yet - good to go', p_level => 3) ;
  end;

  if l_message is null then
    apex_debug.message(p_message => 'password ok', p_level => 3) ;
    l_password := custom_hash(p_username => p_email, p_password => p_password) ;
    apex_debug.message(p_message => 'insert record', p_level => 3) ;
    insert into mtl_user (email, password)
    values (p_email, l_password) 
    returning id into l_user_id;
  else
    raise_application_error( -20001, l_message) ;
  end if;
  
  apex_authentication.post_login(p_username => p_email, p_password => p_password);

  -- no activation email

  apex_debug.message(p_message => 'End create_site_account', p_level => 3) ;
end create_account;


/**
*/
function custom_authenticate
  (
    p_username in varchar2,
    p_password in varchar2
  )
  return boolean
is
  l_password        varchar2(100) ;
  l_stored_password varchar2(100) ;
  l_boolean         boolean;
begin
  -- First, check to see if the user is in the user table and look up their password
  select password
    into l_stored_password
    from mtl_user
   where upper(email) = upper(p_username);
  -- hash the password the person entered
  l_password := custom_hash(p_username, p_password) ;
  -- Finally, we compare them to see if they are the same and return either TRUE or FALSE
  if l_password = l_stored_password then
    return true;
  else
    return false;
  end if;
exception
when no_data_found then
  return false;
end custom_authenticate;


/**
*/
procedure post_authenticate(
    p_username in varchar2,
    out_user_id out number,
    out_time_zone out varchar2
)
is
  l_id         number;
  l_first_name varchar2(100) ;
begin
  select id
    into l_id
    from mtl_user
   where upper(email)    = upper(p_username);
  out_user_id        := l_id;

end post_authenticate;


/**
*/
procedure request_reset_password(
    p_email in varchar2)
is
  l_id                number;
  l_verification_code varchar2(100);
  l_url               varchar2(200);
begin
  -- First, check to see if the user is in the user table
  select id
    into l_id
    from mtl_user
   where upper(email)    = upper(p_email);

  dbms_random.initialize(to_char(sysdate, 'YYMMDDDSS')) ;
  l_verification_code := dbms_random.string('A', 20);

  l_url := apex_util.prepare_url(p_url => c_hostname||'f?p='||v('APP_ID')||':RESET_PWD:0::::P9999_ID,P9999_VC:' || l_id || ',' || l_verification_code, p_checksum_type => 1);
  
  update mtl_user
    set verification_code = 'RESET_' || l_verification_code
  where id = l_id;

  mail_reset_password(p_email => p_email, p_url => l_url);

exception
when no_data_found then
  raise_application_error( - 20001, 'Email address not registered.') ;
end request_reset_password ;


/**
*/
function verify_reset_password(
    p_id in number,
    p_verification_code in varchar2)
  return number
is
  l_id number;
begin
  select u.id
    into l_id
    from mtl_user u
   where u.verification_code = 'RESET_'||p_verification_code
     and u.id = p_id;

  return l_id;
exception
  when no_data_found
  then
    raise_application_error( - 20001, 'Invalid password request url.') ;
    return null;
end verify_reset_password ;


/**
*/
procedure reset_password(
    p_id       in number,
    p_password in varchar2)
is
  l_username        varchar2(100) ;
  l_hashed_password varchar2(100) ;
begin
  select email
    into l_username
    from mtl_user
   where id = p_id;

  l_hashed_password := custom_hash(l_username, p_password) ;

  update mtl_user
    set password = l_hashed_password,
        verification_code = null
  where id = p_id;
end reset_password;


/**
*/
function authz_administrator(
    p_username in varchar2)
  return boolean
is
  l_is_admin varchar2(1) ;
begin
  select 'Y'
    into l_is_admin
    from mtl_user a
   where upper(a.email) = upper(p_username)
     and a.role_id = 2;
  --
  return true;
exception
when no_data_found then
  return false;
end authz_administrator;


/**
*/
function authz_user(
    p_username in varchar2)
  return boolean
is
  l_is_user varchar2(1) ;
begin
  select 'Y'
    into l_is_user
    from mtl_user a
   where upper(a.email) = upper(p_username)
     and a.role_id in (1,2);
  --
  return true;
exception
when no_data_found then
  return false;
end authz_user;

end mtl_auth_pkg;