Connection pooling, and auditing ,2 conflicting requirements ?

Problem statement :

We are in the process of maintaining a legacy client server application where the client is
written in PowerBuilder and the backend is using an Oracle database. Almost all business logic is implemented in stored procedures on the database.
When working in client/server mode ,1 PowerBuilder User has a one-to-one relation with
a connection(session) on the oracle database. It is a requirement that the database administrator must see the real user connected to the database
and NOT some kind of superuser, therefore in the PowerBuilder app each user connects to the database
with his own username.(Each user is configured on the database via a seperate powerbuilder security app).
For the PowerBuilder app all is fine and this app can maintain conversional state(setting and
reading of global variables in oracle packages).

The management is pushing for web-based application where we will be using bea weblogic appserver(J2EE based). We have build an business app which is web-based and accessing the same oracle backend app as
the PowerBuilder app is doing.
The first version of this web-based app is using a custom build connector(based on JCA standard and
derived from a template provided by the weblogic integration installation).
This custom build connector is essentially a combination of a custom realm in weblogic terms
and a degraded connection pool , where each web session(browser) has a one-to-one relation
with the back end database.
The reason that this custom connector is combining the security functionality and the pooling
functionality , is because each user must be authenticated against the oracle database(security requirement)
and NOT against a LDAP server, and we are using a statefull backend(oracle packages) which would make it difficult to reuse connections.

A problem that surfaced while doing heavy loadtesting with the custom connector,
is that sometimes connections are closed and new ones made in the midst of a transaction.
If you imagine a scenario where a session bean creates a business entity ,and the session bean
calls 1 entity bean for the header and 1 entity bean for the detail, then the header and detail
must be created in the same transaction AND with the same connection(there is a parent-child relationship between header and detail enforced on the back end database via Primary and Foreing Keys).
We have not yet found why weblogic is closing the connection!

A second problem that we are experincing with the custom connector, is the use of CMP(container managed persistence) within entity beans.
The J2EE developers state that the use of CMP decreases the develoment time and thus also maintenance costs.
We have not yet found a way to integrate a custom connector with the CMP persistence scheme !

In order to solve our loadtesting and CMP persistence problems i was asked to come up with a solution
which should not use a custom connector,but use standard connection pools from weblogic.

To resolve the authentication problem on weblogic i could make a custom realm which connects to the
backend database with the username and password, and if the connection is ok , i could consider this
user as authenticated in weblogic.

That still leaves me with the problem of auditing and pooling.

If i were to use a standard connection pool,then all transaction made in the oracle database
would be done by a pool user or super user, a solution which will be rejected by our local security officer,
because you can not see which real user made a transaction in the database.
I could still use the connection pool and in the application , advise the application developers
to set an oracle package variable with the real user, then on arrival of the request in the database,
the logic could use this package variable to set the transaction user. There are still problems with this approach :
- The administrator of the database can still not see who is connected
, he will only see the superuser connection.
- This scheme can not be used when you want to use CMP persistence ,
since it is weblogic who will generate the code   to access the database.   

I thought i had a solution when oracle provided us with a connection pool known as OracleOCIConnectionPool
where there is a connection made by a superuser, but where sessions are multiplexed over this physical pipe with the real user. I can not seem to properly integrate this OCI connectionpool into weblogic.
When using this pool , and we are coming into a bean (session or entity bean) weblogic is wrapping
this pool with it's own internal Datasource and giving me back a connection of the superuser, but not one for the real user, thus setting me with my back to the wall again.

I would appreciate if anyone had experienced the same problem to share a possible solution with us
in order to satisfy all requirements(security,auditing,CMP).

Many Thanks
Blyau Gino
gino.blyau_at_cec.eu.int Received on Fri Apr 18 2003 - 01:41:17 CDT