Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Pro ApEx Book - Problem with pkg_auth.authenticate function

jazzbitsOct 3 2008 — edited Oct 6 2008
I'm running into this problem, so this is a question primarily for John and/or Scott, but I will appreciate input from anyone that has run into this problem:

First things first...
ApEx version is 3.1.2.00.02 running on Oracle 11g 11.1.0.6.0. Platform is Linux (Fedora 7).

Table user_repository is created. I deleted the password field but then added it back for debugging purposes. I have made sure that what's on the password_hash field is the output of md5hash(UPPER(username) || password).

In my sample app, I created an application item called 'LOGIN_MESSAGE', and a new region called "Login Message" with the region source set to &LOGIN_MESSAGE and condition set to value of LOGIN_MESSAGE not null. (BTW, all of this stuff is on pages 99-104 and thereabouts.)

Here is the pkg_auth source:

<address>create or replace PACKAGE BODY pkg_auth AS</address>
<address> FUNCTION md5hash (p_input IN VARCHAR2)</address>
<address> RETURN VARCHAR2 IS</address>
<address> BEGIN</address>
<address> RETURN UPPER(dbms_obfuscation_toolkit.md5(input =&gt;</address>
<address> utl_i18n.string_to_raw(p_input)));</address>
<address> END md5hash;</address>
<address></address>
<address> FUNCTION authenticate(p_username IN VARCHAR2,</address>
<address> p_password IN VARCHAR2)</address>
<address> RETURN boolean IS</address>
<address> v_locked_flag CHAR(1);</address>
<address> v_hash VARCHAR2(32);</address>
<address> BEGIN</address>
<address> v_hash := md5hash(UPPER(p_username) || p_password);</address>
<address></address>
<address> SELECT locked_flag</address>
<address> INTO v_locked_flag</address>
<address> FROM user_repository</address>
<address> WHERE UPPER(username) = UPPER(p_username)</address>
<address> AND UPPER(password_hash) = v_hash;</address>
<address></address>
<address> IF v_locked_flag = 'N' THEN</address>
<address> RETURN true;</address>
<address> ELSE </address>
<address> apex_util.set_session_state('LOGIN_MESSAGE',</address>
<address> 'Your account is currently locked');</address>
<address> RETURN false;</address>
<address> END IF;</address>
<address></address>
<address> EXCEPTION </address>
<address> WHEN no_data_found THEN</address>
<address> apex_util.set_session_state('LOGIN_MESSAGE', </address>
<address> 'Invalid username or password');</address>
<address> RETURN false;</address>
<address> END authenticate;</address>
<address>END;</address>
If an account is not locked, all is well and the function returns true, and the user goes on to the first (HOME) page. If the account is locked, the call to apex_util.set_session_state is failing with:

<div><div class="ErrorPageMessage"><address>ORA-01400: cannot insert NULL into ("FLOWS_030100"."WWV_FLOW_USER_ACCESS_LOG2$"."SECURITY_GROUP_ID")</address>
</div>
<address>ERR-10480 Unable to run authentication credential check function.</address>
<address>[Return to application.|javascript:window.history.go(-1)]</address>
</div>
<!--
//-->
<address>Location: f?p=4155:1000:4207994091990389&notification_msg=Invalid%20Login%20Credentials/7C79477EC6BC17D94A41537046CEAE29/
</address>
What am I missing?

Thx!
Marc

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 3 2008
Added on Oct 3 2008
#11g, #apex, #apex-discussions, #error, #oracle, #pl-sql, #plsql
2 comments
596 views