Friday, October 3, 2008

APEX proxy user support with DBPrism, aka middle tier authentication

Here a simple quick list to get Apex using proxy user support with DBPrism standard distribution which by default includes Jetty web server.
The guide can be useful for other J2EE servlet container such as OC4J or WebLogic which can re-use Jetty WAR/EAR configuration to deploy Apex applications.
First Download and Install latest Apex distribution, if you don't have it already installed.
Download from:
http://www.oracle.com/technology/products/database/application_express/download.html

In my test using Oracle 11g standard edition One, I have unzipped it into /usr/local directory and executed
sqlplus "sys/syspass as sysdba" @apexins SYSAUX SYSAUX TEMP /i/
Requirements to run DBPrism and Jetty
Compress as Jar archive all images and static Apex's resources.
-bash-3.2$ cd /usr/local/apex/images
-bash-3.2$ jar cvf /tmp/i.jar *
added manifest
adding: 16admin.gif(in = 130) (out= 133)(deflated -2%)
adding: 1pixelbeige.gif(in = 43) (out= 38)(deflated 11%)
....
-bash-3.2$ cp/tmp/ i.jar /home/mochoa/jdevhome/mywork/cms-2.1-applib/i.war
Edit Apache Ant build.xml file into DBPrism distribution and un-comment the step which un-compress above file after the WAR is deployed, build.xml file look like:

<target name="jetty-deploy" description="Deploy Jetty files on tmp">
....
<unzip src="${EXTRA_LIB_DIR}/i.war" dest="${tmp}/webapps/${project.web}/i"/>

Edit common.xml file with your target database, specially properties:

<property name="database" value="test"/>
<property name="thin_string" value="localhost:1521:test"/>
<property name="jdbc_string" value="jdbc:oracle:thin:@${thin_string}"/>
<property name="apex_pass" value="apex_pass" value="apex"/>

Unlock database user apex_public_user:
SQL> alter user apex_public_user identified by apex account unlock;

Edit a user who want to connect with proxy user support, for example SCOTT:
SQL> alter user scott grant connect through apex_public_user;

Edit $PRISM_HOME/conf/prism.xconf file and activate proxy user support for apex DAD:

<caetgory name="DAD_apex"/>
<property name="dbusername" value="apex_public_user"/>
<property name="dbpassword" value="@APEX_PASS@"/>
<property name="connectString" value="${demo.db}"/>
<property name="useProxyUser" value="true"/>
....
</category>

Another important point is that build.xml and common.xml files relies on JAVA_HOME and ORACLE_HOME environments variables, please check them before start Ant.
Edit $PRISM_HOME/conf/demoRealm.properties activating scott to be validated by middle tier authentication:
apex_demo: apex_demo,apex_users
scott: tiger,apex_users,apex_admins
admin: admin,apex_admins

Un-comment security section at $PRISM_HOME/conf/web.xml

<security-constraint>
<web-resource-collection>
<web-resource-name>DBPrism Apex Admin</web-resource-name>
<url-pattern>/apex/apex_admin</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>apex_admins</role-name>
</auth-constraint>
</security-constraint>

<security-constraint>
<web-resource-collection>
<web-resource-name>DBPrism Apex</web-resource-name>
<url-pattern>/apex/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>apex_admins</role-name>
<role-name>apex_users</role-name>
</auth-constraint>
</security-constraint>

Enable Basic or Form authentication for middle tier authentication:

<login-config>
<auth-method>FORM</auth-method>
<realm-name>DBPrism Demo Realm</realm-name>
<form-login-config>
<form-login-page>/logon.html?param=test</form-login-page>
<form-error-page>/logonError.html?param=test</form-error-page>
</form-login-config>
</login-config>

Start Jetty web server with:
ant run
Log with your browser into http://localhost:8888/
A DPPrism demo page is show
Also there are links to log into Apex administrative and regular pages:
http://localhost:8888/apex/
http://localhost:8888/apex/apex_admin

First Jetty will ask you for your middle tier credentials (defined into demoRealm.properties) use scott/tiger.
Then APEX will ask you for your application workspace/username and password.
With Apex administrations pages you can execute a SQL script to check your Oracle credentials (select user from dual;), instead of returning apex_public_user as connected user it should return scott (proxy user).
Also you can see the report Home>Manage Service>Session State>Recent Sessions.
If you any problem, edit $PRISM_HOME/conf/log4j.properties un-commenting last two lines and look for /tmp/dpls.log information, then reports problems using SourceForge.net Forums.
Remember that using proxy user support you can add to Apex applications Virtual Private Database support (VPD) or any other security layer provided by Oracle which relies on regular databases users, now your credential are a middle tier authenticated user (SCOTT in the example) not APEX_PUBLIC_USER!!!.
Also middle tier authentication includes any JAAS supported scheme such as SSO, LDAP, etc. check your application server documentation to see which JAAS modules are included.
Enjoy APEX/DBPrism combination....