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.

Tuesday, April 11, 2006

Apex: New Frappr Group for Apex Services

Most people in the Forum love Oracle Application Express, but we feel it needs some more visibility.

I have created a new group on Frappr for Oracle Application Express Services :
http://www.frappr.com/oracleapplicationexpressservices


There you can find services related to Oracle Application Express: consultants, developers, hosting services and products based on Apex.

Please add yourself to the map if you provide any services related to Oracle Application Express.
There is also another listing of services on the Wiki page for Application Express.

We also feel that we might need some more prebuilt applications like in the Apex Studio on OTN.

I don't know yet what we (the community) will come up with, but it will be interesting. So stay tuned ...

~Dietmar.

Sunday, April 09, 2006

XE: Calling stored procedures

Using the Oracle web toolkit and the PL/SQL gateway (either via mod_plsql or DBMS_EPG) you can call stored procedures via http(s) to generate output for the browser (HTML, XML, basically any content for any mime-type).

The syntax for this is
http://<server>:<port>/<dad>/<schema>.<procedure>

For example, if you wanted to call the procedure called HELLO_WORLD in the schema HR, you would use the following url (in a default Oracle XE installation on port 8080): http://127.0.0.1:8080/apex/hr.hello_world .

This doesn't work with Oracle XE (production) any longer but will result in an 403 Forbidden error:


In Oracle XE (production) you will have to "allow" a stored procedure to
be called through the embedded plsql gateway (DBMS_EPG) due to a stricter security
control.


This is documented in the Oracle XE documentation here.

The behaviour is controlled via the DAD-attribute request-validation-function ,
which maps to the stored function wwv_flow_epg_include_modules.authorize in
the schema FLOWS_020100. You can modify the existing
stored function called wwv_flow_epg_include_mod_local to
add more procedures to the list of modules that are allowed to be called.

Below you will see a full example of allowing the procedure hello_world to
be called:

1) create procedure hello_world
conn HR/<pwd>@xe

create or replace procedure hello_world as
begin
htp.p('hello world');
end;
/

grant execute on hello_world to anonymous
/

2) connect as SYSTEM and modify the function in the schema FLOWS_020100
conn SYSTEM/<pwd>@xe

alter session set current_schema=FLOWS_020100;

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(
procedure_name in varchar2)
return boolean
is
begin
--
-- Administrator note: the procedure_name input parameter may be in the format:
--
-- procedure
-- schema.procedure
-- package.procedure
-- schema.package.procedure
--
-- If the expected input parameter is a procedure name only, the IN list code shown below
-- can be modified to itemize the expected procedure names. Otherwise you must parse the
-- procedure_name parameter and replace the simple code below with code that will evaluate
-- all of the cases listed above.
--
if upper(procedure_name) in (
'HR.HELLO_WORLD') then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;
/

3) Now you can call it via http://127.0.0.1:8080/apex/hr.hello_world

// Make sure you compare the procedure name in upper case, i.e HR.HELLO_WORLD and not hr.hello_world!!!

Update: 22.05.2006

How to modify the stored function directly using SQL developer

You can also use the free Oracle tool SQL Developer to modify the stored function directly.

The stored function is located in the schema FLOWS_020100. This account is locked by default, thus you would have to unlock the account first in order to use the user FLOWS_020100 to modify the stored function.

Another option is to use a DBA account like SYSTEM to modify the stored function. The following step by step instructions explain how to do it.

1) After starting SQL Developer, right-click with the mouse on the connection and select "New Database Connection" .

2) Fill in the following details to establish a connection to your local XE instance, then click on "connect"


3) Next, click on XE, then Other Users.


4) Click on FLOWS_020100, then Functions, after that on WWV_FLOW_EPG_INCLUDE_MOD_LOCAL:


5) Click on "Edit" in the right panel, where you can see the function you want to edit

6) Edit the function

7) Compile the function

That's it. You don't have to restart the database, it works right away.

~Dietmar.

Wednesday, March 29, 2006

Apex: Workaround for SVG chart hyperlink bug with Firefox 1.5

I have come across a nasty problem using SVG charts in Apex.

In order to create drill-down charts with Oracle Application Express you provide a SQL statement that will return the data to display a SVG chart's series. This SQL statement must be provided using this syntax:
SELECT link, label, value FROM...
For example:
select
'f?p=141:6:'||:app_session||'::::F141_P6_EMPNO:'||empno link,
ename,
sal
from emp
A simple chart might look like this:


When clicking on the link you would want to see a detail page in
the same browser window
:

While this works well in Internet Explorer 6, Firefox 1.0.7 and Mozilla, what you see in Firefox 1.5 is:


This is due to a bug in Firefox 1.5 ( https://bugzilla.mozilla.org/show_bug.cgi?id=300868 )
. Unfortunately does Firefox ignore the target="_top" attribute for the xlink tag:

<a xlink:href="newdoc.html" target="_top">

The suggested workaround is :
xlink:href="javascript:window.top.location.href='newdoc.html'"

Using this fix to implement a workaround for Apex means changing a query like this:
SELECT *
FROM (SELECT 'f?p=&APP_ID.:14:' || :app_session || '::::P14_EMPNO:' || empno || ':' LINK
, ename label, sal VALUE
FROM HTMLDB_SAMPLES.EMP
WHERE sal IS NOT NULL
ORDER BY ename)

to a query like this:

SELECT *
FROM (SELECT 'javascript:window.top.location.href=''f?p=&APP_ID.:14:' || :app_session
|| '::::P14_EMPNO:' || empno || ':''' LINK
, ename label, sal VALUE
FROM HTMLDB_SAMPLES.EMP
WHERE sal IS NOT NULL
ORDER BY ename)

This works fine for all of the above mentioned browsers.

~Dietmar.

Sunday, March 19, 2006

XE: Oracle Home Selector

When you installed Oracle on a Windows machine (previous to version 10g), it would also install the Oracle Home Selector:


This way you could switch between different Oracle versions installed on the same machine. It would set the environment appropriately (including the modification of the PATH variable).

With the release of Oracle 10g (and above) the Home Selector has been moved into the Oracle Universal Installer, but it has become a quite "hidden" feature.

Oracle Express Edition (XE) doesn't come with a Home Selector at all.

There is a freeware tool called "Oracle Locator Express" from DbMotiveTools, which does an excellent job here.

Once you download and double-click on it, you will find an additional icon in your taskbar.
Right-click on it and it will show you all Oracle_Homes it can find on this machine. On my machine it recognized all homes ranging from version 8.0 up until 10g.


It certainly found XE and the XE client as well.
Cool stuff!

~Dietmar

Thursday, March 16, 2006

Where are the /images of Application Express in OracleXE?

In a standalone Apex installation, the required images, css and javascript files are accessible via the virtual path /i.

This virtual path mapping looks something like this in your marvel.conf (or dads.conf) file:
Alias /i/ /opt/oracle/oracle/product/10.2.0/htmldb/images/

But where are the images in OracleXE?

When you look at http://127.0.0.1:8080/i you will see that the images are available, but they cannot be found in the filesystem.


In OracleXE these resources are stored directly in the database, using the Oracle XMLDB feature.
You can access the resources and modify them directly by using the WebDAV feature of the XMLDB.

(1) With Internet Explorer, just go to File > Open > http://127.0.0.1:8080/i (check "open as webfolder"):

(2) You will be prompted for user credentials, enter username and password for the database user SYSTEM:
(3) After that you can access the files via WebDAV directly, using Drag&Drop:


Update (19.03.2006)

If you don't have an Internet Explorer for connecting via WebDAV available, you can also use another WebDAV client like sitecopy or cadaver ( http://www.webdav.org/projects/ ).

Or you can simply use a plain FTP client to modify the files:

(1) Enable FTP access to the database:
www:oraclexe[XE]> sqlplus system@xe
SQL*Plus: Release 10.2.0.1.0 - on Sun Mar 19 22:20:36 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0

SQL> exec dbms_xdb.setftpport('2100');

PL/SQL procedure successfully completed.

SQL> alter system register;

System altered.

SQL> select dbms_xdb.getftpport as "FTP-Port" from dual;

FTP-Port
----------
2100

SQL> exit


(2) Modify the files via FTP:
www:oraclexe[XE]> ftp localhost 2100
Connected to localhost.localdomain.
220- www
Unauthorised use of this FTP server is prohibited and may
be subject to civil and criminal prosecution.
220 www FTP Server (Oracle XML DB/Oracle Database) ready.
Name (localhost:oraclexe): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> cd i/themes
250 CWD Command successful
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r-- 1 SYS oracle 3075 OCT 10 03:41 generic_list.gif
-rw-r--r-- 1 SYS oracle 1307 OCT 10 03:41 generic_nochild.gif
-rw-r--r-- 1 SYS oracle 1463 OCT 10 03:41 generic_open.gif
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 opal
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 otn
-rw-r--r-- 1 SYS oracle 216 OCT 10 03:41 rollup_minus_dgray.gif
-rw-r--r-- 1 SYS oracle 215 OCT 10 03:41 rollup_plus_dgray.gif
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_1
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_10
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_11
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_12
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_2
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_3
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_4
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_5
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_6
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_7
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_8
drw-r--r-- 2 SYS oracle 0 OCT 10 03:41 theme_9
226 ASCII Transfer Complete
ftp> bye
221 QUIT Goodbye.
www:oraclexe[XE]>

(3) Disable FTP access for security:

www:oraclexe[XE]> sqlplus system@xe

SQL*Plus: Release 10.2.0.1.0 - on Sun Mar 19 22:24:28 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0

SQL> exec dbms_xdb.setftpport('0');

PL/SQL procedure successfully completed.

SQL> alter system register;

System altered.

SQL> exit


~Dietmar.

Tuesday, February 28, 2006

XE: Oracle XE is in production now!

Oracle Express Edition has been released on 02/24/2006 and is in production now.

You can download the software here: http://www.oracle.com/technology/software/products/database/xe/index.html

The downloads come in two flavours: A Western European Edition and an Universal Edition
  • Oracle Database 10g Express Western European Edition - Single-byte LATIN1 database for Western European language storage, with the Database Homepage user interface in English only. The character set is WE8MSWIN1252.
  • Oracle Database 10g Express Universal Edition - Multi-byte Unicode database for all language deployment, with the Database Homepage user interface available in the following languages: Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish. The character set is AL32UTF8.
There is also a separate download of a
  • Oracle Database 10g Express Client
    Client software to be installed on client systems for remotely accessing Oracle Database 10g Express Edition.
But you can just as well connect to Oracle Express Edition just like any other Oracle Database using SQL*Net.

The downloads are available for both Windows and Linux x86 platforms. The software is not installed using the standard Oracle Universal Installer (OUI) but uses the native installers, Microsoft Installer packages and RPMs for Linux. Specific Debian packages will be released shortly.

~Dietmar.

Wednesday, January 25, 2006

XE: Changing the default http port

Oracle XE uses the embedded http listener that comes with the XML DB (XDB) to serve http requests. The default port for HTTP access is 8080.

It also supports the WebDAV protocol just as well as FTP.

You can determine the current configuration using the following commands when you connect to XE as the oracle user SYSTEM (or any other DBA):

C:\WINDOWS\system32>sqlplus system@xe

SQL*Plus: Release 10.1.0.2.0 - Production on Mi Jan 25 11:44:33 2006

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> -- get current status
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
, dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port FTP-Port
---------- ----------
8080 0

You can change the http port and the ftp port to whatever you like (keep in mind that you need special privileges for ports < 1024 on Unix/Linux systems).

SQL> -- set http port and ftp port
SQL> begin
2 dbms_xdb.sethttpport('80');
3 dbms_xdb.setftpport('2100');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
, dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port FTP-Port
---------- ----------
80 2100

If you only want to use the database without allowing access via http or ftp then you can disable both:

SQL> -- disable http and ftp access
SQL> begin
2 dbms_xdb.sethttpport('0');
3 dbms_xdb.setftpport('0');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> -- get current status
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
, dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port FTP-Port
---------- ----------
0 0

~Dietmar

XE: Where in the World is Oracle XE Being Used?

Mark Townsend from Oracle XE Marketing invites us to join the Oracle XE group on http://www.frappr.com:
Oracle Database 10g Express Edition (Oracle XE) is being used all over the world. If you are using Oracle XE why not add yourself to the map - see the OracleXE group on frapper - http://www.frappr.com/oraclexe.
You can also find other people in your area using Oracle XE as well.


Simply add yourself to the map (no registration required) and join the community!



You can also check out the Frappr group for HTML DB users ( http://www.frappr.com/htmldbusers ) and see who is using it in the world.

~Dietmar.

Tuesday, January 10, 2006

I have started this BLOG to spread the word on the really cool web development framework HTML DB and the free Oracle database Oracle Express Edition (XE) .

Enjoy the ride!

~Dietmar.