Custom authentication via DB credentials

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;