We all know that ApEx offers an authentication scheme that will validate a user against a database account. I don't know how it works exactly - it's a black box. But I became interested while trying to help someone in this forum post. In the post, a developer wanted to create a custom authentication scheme that could do both LDAP authentication and DB authentication based on what the end user selected at the login page.
The problem is that to use ApEx's DB authentication scheme it must be the active scheme so you can't do both. Furthermore, Oracle does not offer a means to test DB credentials programmatically. However, with a little searching I found a technique that allows this to be done. With a little modification, I've made it available below. I don't know if this is the best way to achieve this so if anyone knows of a better method, feel free to let me know and I'll update the function.
Using this function, a custom authentication function can be created that uses a little boolean logic (checking the value of an item on the login page) to decide how to authenticate the user - LDAP or DB.
- 08-AUG-2008 - Updated to use DBMS_ASSERT package as suggested by Patrick Wolf.
- 10-AUG-2008 - Simplified code a little.
create or replace FUNCTION is_valid_db_user ( p_username IN VARCHAR2 , p_password IN VARCHAR2 , p_host_service IN VARCHAR2 ) RETURN BOOLEAN IS l_db_link_exists BOOLEAN; e_bad_username_pw EXCEPTION; PRAGMA exception_init(e_bad_username_pw,-1017); CURSOR db_link_exists_cur IS SELECT 'Y' FROM user_db_links WHERE db_link = 'PASSWORD_TEST'; db_link_exists_rec DB_LINK_EXISTS_CUR%ROWTYPE; FUNCTION is_valid_username_pw RETURN BOOLEAN IS l_test_result CHAR(1) := 'N'; BEGIN EXECUTE IMMEDIATE 'SELECT ''Y'' FROM DUAL@password_test' INTO l_test_result; COMMIT; EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK password_test'; RETURN l_test_result = 'Y'; EXCEPTION WHEN e_bad_username_pw THEN RETURN FALSE; END is_valid_username_pw; BEGIN OPEN db_link_exists_cur; FETCH db_link_exists_cur INTO db_link_exists_rec; l_db_link_exists := db_link_exists_cur%FOUND; CLOSE db_link_exists_cur; IF l_db_link_exists THEN EXECUTE IMMEDIATE 'DROP DATABASE LINK password_test'; END IF; EXECUTE IMMEDIATE 'CREATE DATABASE LINK password_test ' || 'CONNECT TO ' || sys.dbms_assert.enquote_name(dbms_assert.simple_sql_name(p_username)) || ' ' || 'IDENTIFIED BY ' || sys.dbms_assert.simple_sql_name(p_password) || ' ' || 'USING ' || sys.dbms_assert.enquote_literal(REPLACE(p_host_service,'''','''''')); RETURN is_valid_username_pw; END is_valid_db_user;