Apex: Database authentication - an alternative solution

Using native Oracle accounts for an Apex application has been a long requested feature.

Up until version 2.0 of Oracle Application Express (aka HTML DB) your choice of using authentication schemes (out of the box) were:

- Show Built-In Login Page and Use Open Door Credentials
- Show Login Page and Use HTML DB Account Credentials
- Show Login Page and Use LDAP Directory Credentials
- No Authentication (using DAD)
- Oracle Application Server Single Sign-On (HTML DB Engine as Partner App)
- Oracle Application Server Single Sign-On (My Application as Partner App)

With the release of Oracle Express Edition you could also use existing database accounts for authentication purposes out of the box, but this would only work with Oracle Express Edition.


In this release the developers of the database implemented an internal password validation check which is used to provide the functionality. The code for Oracle XE is based on the code release of Oracle 10gR2 (10.2.0.1).

Unfortunately this functionality was not available for Apex applications running version 2.0.

With the release of Apex 2.2 you can now also use the database authentication scheme out of the box. Unfortunately this has some limitations / drawbacks:

If you install Apex 2.2 into an Oracle version 10.2.0.3 and above, it will use the native password validation function and everything will be fine.

If you install Apex 2.2 instead into an Oracle version below 10.2.0.3, it will provide the same functionality but use a different approach. They store the current password, change the password on the fly, compare the password and reset the password afterwards if they were not equal (alter user identified by values [old_password_hash]): http://forums.oracle.com/forums/message.jspa?messageID=869036#869036

This can cause problems (i.e. ORA-28007: the password cannot be reused) in the case you use password policies in your Oracle database (http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=422914)

In a recent project we have come up with a different solution to this problem. Many thanks to Ulrich Kämmerer (http://www.creon-it.de/) for figuring this out with me.

We used a jdbc thin connection to validate the password from whithin the database using java stored procedures.

Here is the solution (you can download all the required files at the end of this post):

We assume the following values for this sample, please change accordingly to match your system:
- Apex application schema: APEX_TEST
- Password for this schema: apex_test
- Listener port for this Oracle instance: 1521
- Tnsnames entry for the instance: o920
- Oracle SID for the instance: o920

1) Create the java class PasswordValidator:

import java.sql.*;
import oracle.jdbc.*;

public class PasswordValidator 
{
  public PasswordValidator()
  {
  }

  public static int isValidPassword(String pServerName, String pListenerPort, String pSID, String pUserName, String pPassword) throws SQLException
  {
    boolean bIsValid = false;
    
    try{
      DriverManager.registerDriver(new OracleDriver());
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + pServerName + ":" + pListenerPort + ":" + pSID, pUserName, pPassword);
      bIsValid = true;
      
    } catch (SQLException e)
    {
      //e.printStackTrace();
      //System.out.println("ErrorCode=" + e.getErrorCode());
      
      if (e.getErrorCode() == 1017)
      {
        // ORA-01017: invalid username/password
        bIsValid = false;
      } else {
        //result = e.getMessage();
        // re-raise exception
        throw(e);
      }
    }
    
    return bIsValid == true ? 1 : 0;
  }
}

2) Compile the java class

3) Load the java class into the database:

loadjava -user APEX_TEST/apex_test@o920 PasswordValidator.class

4) Grant the proper rights using the SYS account:

begin
  dbms_java.grant_permission('APEX_TEST','SYS:java.net.SocketPermission',  'localhost:1521', 'connect,resolve' );
end;
/

5) Modify the package variables to match your settings:

   c_server_name     CONSTANT VARCHAR2 (50) := 'localhost';
   c_listener_port   CONSTANT VARCHAR2 (50) := '1521';
   c_sid             CONSTANT VARCHAR2 (50) := 'o920';

6) Install the package in your Apex schema (e.g. APEX_TEST):

CREATE OR REPLACE PACKAGE apex_sec_pck IS
/******************************************************************************
   NAME:       APEX_SEC_PCK
   PURPOSE:    Security Package for database authentication
               (using jdbc thin connections)
               
      
   REVISIONS:
   Ver        Date        Author            Description
   ---------  ----------  ---------------   -----------------------------------
   1.0        14.06.2006  D. Aust           Initiale Erstellung

******************************************************************************/

/**************************************************************************
  Constants and Types
**************************************************************************/

  /*======================================================
    FUNCTION is_valid_password_fc
 
 Tests, wether this username/password is a valid combination
 for this instance.
 
    Parameter : p_username
 Parameter : p_password
    Return    : boolean
 Exception : 
  =======================================================*/
   FUNCTION is_valid_password_fc (p_username IN VARCHAR2, p_password IN VARCHAR2)
      RETURN boolean;
   
   
END;
/

CREATE OR REPLACE PACKAGE BODY apex_sec_pck IS
   c_server_name     CONSTANT VARCHAR2 (50) := 'localhost';
   c_listener_port   CONSTANT VARCHAR2 (50) := '1521';
   c_sid             CONSTANT VARCHAR2 (50) := 'o920';

   
   FUNCTION is_valid_password_java_wrp_fc (p_server_name IN VARCHAR2, p_listener_port IN VARCHAR2, p_sid IN VARCHAR2, p_username IN VARCHAR2, p_password IN VARCHAR2)
      RETURN NUMBER AS
      LANGUAGE JAVA
      NAME 'PasswordValidator.isValidPassword(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
         
   -----------------------------------------------------------------------------
   --
   --
   --
   FUNCTION is_valid_password_fc (p_username IN VARCHAR2, p_password IN VARCHAR2)
      RETURN BOOLEAN IS
      l_user     VARCHAR2 (30);
      l_retval   NUMBER;
   BEGIN
      l_retval := is_valid_password_java_wrp_fc (p_server_name => c_server_name, p_listener_port => c_listener_port, p_sid => c_sid, p_username => p_username, p_password => p_password);

      IF l_retval = 1 THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.put_line (SQLERRM);
         RETURN FALSE;
   END;

END;
/

7) Now test your password validation function in sql/plus:

SET serveroutput on

BEGIN
   DBMS_OUTPUT.put ('Password test 1 (correct password): ');

   IF apex_sec_pck.is_valid_password_fc ('apex_test', 'apex_test')
   THEN
      DBMS_OUTPUT.put_line ('password is valid');
   ELSE
      DBMS_OUTPUT.put_line ('password is *not* valid');
   END IF;

   DBMS_OUTPUT.put ('Password test 2 (incorrect password): ');

   IF apex_sec_pck.is_valid_password_fc ('apex_test', 'incorrect_pwd')
   THEN
      DBMS_OUTPUT.put_line ('password is valid');
   ELSE
      DBMS_OUTPUT.put_line ('password is *not* valid');
   END IF;
END;
/

8) Create a new authentication scheme in Apex:

The only three relevant entries are (leave everything else empty / use defaults):

a) Invalid session target : Page in this application => 101

b) Credentials Verification Method: Use my custom function to authenticate
=> return apex_sec_pck.is_valid_password_fc

c) Logout url: wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1

You can download all source files, the compiled java class and a sample application (f118.sql) using this authentication scheme here: http://www.opal-consulting.de/downloads/db_authentication_jdbc.zip

Regards,
~Dietmar.