Friday, September 15, 2006

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.

Thursday, September 14, 2006

Back online ...

Hi guys,

I have been offline for quite a while being busy with my Apex projects at my clients ;). Apex is really taking off!!!

And just recently my little and beautiful daughter Lavinia Alessia Marie was born!!!

The first album on Flickr

The album as a slide show

So this has kept me busy lately. We are really happy and things are going well.

Happy posting again.

Regards,
~Dietmar.