Forum Stats

  • 3,780,660 Users
  • 2,254,421 Discussions


Pro ApEx Book - Problem with pkg_auth.authenticate function

jazzbits Member Posts: 135
edited Oct 6, 2008 1:30PM in APEX Discussions
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?



  • 60437
    60437 Member Posts: 16,564

    Change your authentication scheme so that it uses a login page (101, perhaps) in your application. As it is, it's using the built-in login page which is not an actual page in your application. So when you try to set session state you're not in your application but rather in a proxy application (4155) that displays the login page. The set session state call fails, issues an error message, rolls back work, and unsets critical global variables, resulting in the "cannot insert null ..." message.

  • jazzbits
    jazzbits Member Posts: 135
    Thank you Scott! That's exactly what it was.

This discussion has been closed.