Skip to Main Content

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 running on Oracle 11g 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> 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> 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> 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> 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>
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>
<address>ERR-10480 Unable to run authentication credential check function.</address>
<address>[Return to application.|javascript:window.history.go(-1)]</address>
<address>Location: f?p=4155:1000:4207994091990389&notification_msg=Invalid%20Login%20Credentials/7C79477EC6BC17D94A41537046CEAE29/
What am I missing?

Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Nov 3 2008
Added on Oct 3 2008