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.

10 comments:

John Scott said...

Excellent post Dietmar, very very useful.

Anonymous said...

Simple, quick and useful.
What else can you ask?

Anonymous said...

Dietmar. Works very nicely. I have tried to modify it to authenticate on a remote database by changing the values in the package. It doesnt seem to work however. Any pointers?

daust_de said...

1) Did you get any error messages?

2) Try to create a database link to the remote database and see if this works.

3) Does the authentication work with the local database? If so, the setup should be ok.

Could be a firewall, wrong port, wrong username/password.

4) Can you connect via sqlplus from the database server (in which you installed the jar files) to the remote database?

Regards,
~Dietmar.

Anonymous said...

Fixed! Had to run dbms_java.grant_permission for remote machine. I also notice you dont close your connection in the java class. Does this get cleaned up?

daust_de said...

Good that you have it working now. Thanks for the hint on Had to run dbms_java.grant_permission for remote machine..

The connection should be cleaned up automatically but it is always better to do it explicitly. I will post an update shortly. Thanks.

~Dietmar.

Anonymous said...

Where is the updated.

daust_de said...

Hi,

sorry, but my daily job is keeping me busy ;).

It is quite easy to change the Java class yourself. You have the full source code available. Just close the conn object and recompile the class.

Interesting that this technique is still used.

Why are you using it? What is your use case?

Regards,
~Dietmar.

Sujay Dutta, PMP said...

Is there a way to make a JDBC connection from within Oracle Apex to a non Oracle database? We have a unique product at work. It's a GTM database (based on Mumps) that can be accessed via the in-house JDBC driver.

For me to build any Oracle Apex UI around it, I would need to get to the database via the JDBC driver. Seems easy, but can I do it with Oracle Apex?

daust_de said...

Hi Sujay,

this should be feasible.

But I would rather use database links over odbc (heterogenous services) than a direct jdbc implementation.

Anyhow, Oracle APEX is not really meant to work against non-Oracle datasources. Even using database links I would only rarely recommend this approach.

When you only want to access the data via read-only means using materialized views and such, you should be fine. Than you could refresh the data from the other datasource on demand.

Aside from that, I would not recommand using APEX.

Regards,
Dietmar.