Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 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.