Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.7K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 387 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Pro ApEx Book - Problem with pkg_auth.authenticate function

jazzbits
Member Posts: 135
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 =></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¬ification_msg=Invalid%20Login%20Credentials/7C79477EC6BC17D94A41537046CEAE29/
</address>
What am I missing?
Thx!
Marc
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 =></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¬ification_msg=Invalid%20Login%20Credentials/7C79477EC6BC17D94A41537046CEAE29/
</address>
What am I missing?
Thx!
Marc
Answers
-
Marc,
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.
Scott -
Thank you Scott! That's exactly what it was.
Marc
This discussion has been closed.