Providing logout functionailty for Secure OC4J Apps in 10.1.3.x

I recently had to determine how to logout of an OC4J 10.1.3.x application which was secured against a 3rd party LDAP server, using HTTP Basic authentication. Normally for web based applications you can control when to logout calling invalidate() from the HTTPSession object as shown below or wait until the session times out which it will then do for you automatically.

HttpSession.invalidate()

The problem here is when using HTTP basic authentication browsers typically 'cache' HTTP basic authentication credentials and re-send them when necessary.

Specifying FORM as the authentication scheme in web.xml (along with HttpSession.invalidate()) will address this issue of provinding a logout link which gives you control of when your user wants to logout. The authentication scheme chosen is depending on your requirements so if logout isn't important then HTTP basic will do, but typically I would expect HTTP basic to be used very rarely.

Getting debug output from utl_dbws PLSQL package

When calling web services from an oracle database you can use utl_dbws package. There are times though that you need to debug it to determine why it may be failing. This can be achieved as follows.

call dbms_java.set_output(100000);

Example output from a SQL file as follows. This example is calling a JAXRPC 1.4 web service from OAS 10.1.3.4

SQL:

set serveroutput on

call dbms_java.set_output(100000);

-- call web service methods here now for end point below
-- http://beast.au.oracle.com:7777/wsrocks/DemoWSSoapHttpPort
-- WSDL = http://beast.au.oracle.com:7777/wsrocks/DemoWSSoapHttpPort?WSDL

prompt
prompt Calling method todaysDate from web service
prompt

select PasWS102.todaysDate from dual;

prompt
prompt Calling method echoString from web service
prompt

select PasWS102.echoString('Pas') from dual;

Output:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 17 08:56:38 2009

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

Enter password: *****

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Call completed.

Calling method todaysDate from web service


TODAYSDATE
----------------------------------------------------------------------------------------------------
Tue Mar 17 07:57:37 EST 2009

ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@bb101899
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@ce6cb3d5
*** Created service: -677524827 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@2968f5e4 ***
ServiceProxy.get(-677524827) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@2968f5e4
** Calling web service method {todaysDate} **
setProperty(javax.xml.rpc.soap.http.soapaction.use, true)
setProperty(javax.xml.rpc.soap.http.soapaction.uri, http://pas.au.ws//todaysDate)
setProperty(javax.xml.rpc.soap.operation.style, document)
** Invoking web service method {todaysDate} **
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
createElement(todaysDateElement,null,http://pas.au.ws/types/)
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
request:

response:

Tue Mar 17 07:57:37 EST 2009

** Successfully invoked web service method {todaysDate} **

Calling method echoString from web service


PASWS102.ECHOSTRING('PAS')
----------------------------------------------------------------------------------------------------
Pas

ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@bb101899
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@d05260ad
*** Created service: -677524826 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@7789dbfd ***
ServiceProxy.get(-677524826) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@7789dbfd
** Calling web service method {echoString} **
setProperty(javax.xml.rpc.soap.http.soapaction.use, true)
setProperty(javax.xml.rpc.soap.http.soapaction.uri, http://pas.au.ws//echoString)
setProperty(javax.xml.rpc.soap.operation.style, document)
** Invoking web service method {echoString} **
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
createElement(echoStringElement,null,http://pas.au.ws/types/)
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
dbwsproxy.element.node.child.0: 1, null
createElement(s,null,http://pas.au.ws/types/)
dbwsproxy.text.node.child.0: 3, Pas
request:

Pas

response:

Pas

** Successfully invoked web service method {echoString} **
SCOTT@linux10g>

OAS 10.1.3.x : SSL With Oracle JDBC Thin Driver

The following how-to demonstrates, creating a self signed oracle wallet for the server and client, configuring the database to use TCPS and finally create a data source on OC4J 10.1.3.x.

Create the wallets

For an example on how to create the server and client keys/certificates see the following metalink note. The examples are created using orapki utility which creates a standard PKCS12 format to store X.509 certificates and private keys.

Note 762286.1 - End To End Examples of using SSL With Oracle's JDBC THIN Driver

Setup OC4J to use JDBC/THIN with SSL

1. Create a java class as follows, and package it up in a JAR file called "JDBCStartup.jar". This startup class file enables Oracle PKI provider dynamically.

/**
*
* add the following lines to your server.xml
<init-library path="<path_to_JDBCSSLSetup.jar>" />
<startup-classes>
<startup-class classname="JDBCSSLSetup" failure-is-fatal="false">
<execution-order>0</execution-order>
</startup-class>
</startup-classes>

*/

import javax.naming.*;
import java.util.*;
import java.security.Security;
import oracle.security.pki.OraclePKIProvider;
import oracle.j2ee.server.OC4JStartup;

/**
* JDBC over SSL setup startup class for OC4J
*/

public class JDBCSSLSetup implements OC4JStartup
{
/**
* Public, no-argument constructor: required by the Oracle startup class spec
*/
public JDBCSSLSetup()
{
}

public String preDeploy(Hashtable args, Context context) throws Exception
{
// instantiate OraclePKIProvider and put it into provider slot #3
Security.insertProviderAt(new OraclePKIProvider(), 3);
System.out.println("JDBCSSLSetup startup class: OraclePKIProvider has been successfully instantiated");

return "ok";
}

public String postDeploy(Hashtable args, Context context) throws Exception
{
return null;
}
} 

2. Place the JAR file into $ORACLE_HOME/j2ee/{container-name}/applib directory.

3. Edit $ORACLE_HOME/j2ee/{container-name}/config/server.xml as follows to add the startup class to the container.

<init-library path="../applib/JDBCStartup.jar" />
<startup-classes>
<startup-class classname="JDBCSSLSetup" failure-is-fatal="true" >
<execution-order>0</execution-order>
</startup-class>
</startup-classes>

4. Restart the container as shown below.

> opmnctl stopproc process-type=pas

> opmnctl startproc process-type=pas

5. OAS 10..1.3.x comes with JDBC driver 10.1.0.5 , you will need to use the latest 10g JDBC driver or 11g JDBC driver, to switch to that driver follow the following metalink how to document. You should add the required JAR files below to the shared library along with the JDBC JAR file.

Note 420303.1 - How to Use The Latest Thin JDBC Driver Across All Applications For a 10.1.3.x OAS Container

If you are using 11g database you will also need these 2 JAR files added to the shared library.

• $ORACLE_HOME/jlib/osdt_cert.jar
• $ORACLE_HOME/jlib/osdt_core.jar

If you are using 10g database you will also need this JAR file added to the shared library.

• $ORACLE_HOME/jlib/ojpse.jar

6. You should be using the latest JDBC driver as per your $ORACLE_HOME/j2ee/{container-name}/config/system-application.xml. In this example we are using the 11.1.0.7 JDBC driver.

<import-shared-library name="oracle.jdbc" min-version="11.1.0.7"/>

7. Now re-start the container to ensure it picks up the latest JDBC driver which should have been done in the note above.

> opmnctl stopproc process-type=pas

> opmnctl startproc process-type=pas

8. Now you can go ahead and create a data source which is using TCPS either through asconsole or manually editing $ORACLE_HOME/j2ee/{container-name}/config/data-sources.xml

Here we will manually edit data-sources.xml and add a managed data source and connection pool to support our SSL setup. We must use the factory class "oracle.jdbc.OracleDriver" as we need to pass properties to the driver.

<managed-data-source
connection-pool-name="TCPSConnectionPool"
jndi-name="jdbc/jdbcSSLDS"
name="jdbc/jdbcSSLDS"/>

<connection-pool name="TCPSConnectionPool">
<connection-factory
factory-class="oracle.jdbc.OracleDriver"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)
(HOST=beast.au.oracle.com)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=linux11g)))"
 commit-record-table-name="">
<property
 name="javax.net.ssl.trustStore" 
 value="/home/u01/app/oracle/product/1013AS_red/network/admin/wallets/client/ewallet.p12"/>
<property 
 name="javax.net.ssl.trustStoreType" 
 value="PKCS12"/>
<property 
 name="javax.net.ssl.trustStorePassword" 
 value="myclient123"/>
</connection-factory>
</connection-pool>

In the example above we connect to the database through TCPS for SSL with Encryption and Authentication.

Note: The connect string should include TCPS as shown above.

9. Now in this example we manually edited data-sources.xml so we must re-start the container once again to pick up those changes.

> opmnctl stopproc process-type=pas

> opmnctl startproc process-type=pas

10. Finally we can test our data source using asconsole Data Sources page as shown below.




More Information

For more information regarding SSL With Oracle JDBC Thin Driver see the following white paper

http://www.oracle.com/technology/tech/java/sqlj_jdbc/pdf/wp-oracle-jdbc_thin_ssl.pdf

Java Stored Procedure remotely connecting to another database

I very rarely use JavaSP but I had a request to connect to a remote database from a JavaSP. My first thought was to use a database link but the request required a direct connection to the remote database using JDBC/THIN driver. here is how I got it working.

1. Create a class as follows which contains one method to connect to a remote database.

import java.sql.Connection;

import java.sql.SQLException;

import oracle.jdbc.pool.OracleDataSource;

public class RemoteDBTest
{
public static String remoteConnection()
 throws SQLException
{
  StringBuffer sb = new StringBuffer();
  String userId = "scott";
  String password = "tiger";
  String url =
   "jdbc:oracle:thin:@acampanaro-pc2.au.oracle.com:1521:linux102";
  Connection conn = null;

  OracleDataSource ods = new OracleDataSource();
  ods.setUser(userId);
  ods.setPassword(password);
  ods.setURL(url);
  conn = ods.getConnection();

  sb.append("Auto commit = " + conn.getAutoCommit());
  conn.close();

  return sb.toString();
}
}

2. Load the class into the database and create a call specification, I used JDeveloper 10.1.3.4 as this makes light work of the task as shown by the output below. The class was loaded as user SCOTT.

Invoking loadjava on connection 'scott-linux102' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL Statement:
CREATE OR REPLACE FUNCTION remoteConnection RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'RemoteDBTest.remoteConnection() return java.lang.String';
Success.
Publishing finished.

3. Now I need to grant some privileges to enable me to remotely connect from my database to another database using JAVA. There 2 are as follows, which must be executed as SYS.

SYS@LINUX10G> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', 'acampanaro-pc2.au.oracle.com', 'resolve');

PL/SQL procedure successfully completed.

SYS@LINUX10G> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '10.187.80.13:1521', 'connect,resolve');

PL/SQL procedure successfully completed.

4. Now we can call our JavaSP and verify if we have successfully connected.

SCOTT@LINUX10G> select remoteconnection from dual;

REMOTECONNECTION
------------------------------------------------------------

Auto commit = true

What you will find is if you don't execute the required privileges then you will be told when you try to run the JavaSP that you don't have required permissions to do this. His an example of what error you may receive. Lucky for me oracle gives me the command I need to run which made this easy to setup.

SCOTT@LINUX10G> select remoteconnection from dual;
select remoteconnection from dual
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.net.SocketPermission 10.187.80.13:1521 connect,resolve) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '10.187.80.13:1521', 'connect,resolve' )