Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Custom Authentication Setup

3332597Feb 5 2018

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:

pastedImage_1.png

Register User Process:

pastedImage_2.png

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;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 5 2018
Added on Feb 5 2018
0 comments
677 views