Hi All,
I've been struggling to get my custom authentication working. I started out by following this blog Dimitri Gielis Blog (Oracle Application Express - APEX) to the best of my abilities. I have all the page items and packages loaded per his setup. However, I am not able to save any values into the database. It's almost like my process is not referencing the input field values. Here are some screenshots of my project below. I'm hoping someone might be able to pinpoint what I'm missing to get the login/ register user functionality working. It's driving me crazy as I'm sure its just one small thing I'm missing. I've set up the custom authentication in the shared components. I've loaded the package and necessary mtl_user table and mtl_role. For whatever reason when I try to pass the username/ password values to either login or register a new user nothing happens. Any all and suggestions welcome. If you are willing to log in to my workspace and check it out for yourself to try an diagnose the problem, I can message you the credentials. I appreciate your help!
Login Authentication Process:
Register User Process:
Package:
create or replace package mtl_auth_pkg
as
/**
* Project: Multiplication Table
* Author: APEX RnD - Dimitri Gielis
* Description: Custom Authentication and Authorization
* Note: None
* @headcom
*/
/**
* Create account
*
* @param p_username username
* @param p_password password
*/
procedure create_account(
p_email in varchar2,
p_password in varchar2
);
/**
* Custom authenticate
*
* @param p_username username
* @param p_password password
*/
function custom_authenticate(
p_username in varchar2,
p_password in varchar2)
return boolean;
/**
* Post authenticate
*
* @param p_username
* @param out_user_id
* @param out_first_name
*/
procedure post_authenticate(
p_username in varchar2,
out_user_id out number,
out_time_zone out varchar2) ;
/**
* Request reset password
*
* @param p_email
*/
procedure request_reset_password(
p_email in varchar2) ;
/**
* Verify reeset password
*
* verify the token of the password request and retun the id of the user
*
* @param p_token
*/
function verify_reset_password(
p_id in number,
p_verification_code in varchar2)
return number;
/**
* Reset password
*
* @param p_id
* @param p_password
*/
procedure reset_password(
p_id in number,
p_password in varchar2) ;
/**
* Authorization: administrator
*
* @param p_username username
*/
function authz_administrator(
p_username in varchar2)
return boolean;
/**
* Authorization: registered user
*
* @param p_username username
*/
function authz_user(
p_username in varchar2)
return boolean;
end mtl_auth_pkg;
Package Body:
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;