Re: Connection pooling, and auditing ,2 conflicting requirements ?

Hello,

    Other option (given that you are coming from PowerBuilder) is to use Datawindow-to-Java convertor (www.xmlsp.com) and do master/detail or multi-datawindow update in one HTTP request. Hope this helps,
Anatole

jocave_at_yahoo.com (Justin Cave) wrote in message news:<233b7a65.0304181407.1901466b_at_posting.google.com>...
> Tom Kyte has an excellent discussion on proxy authentication
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:940263688159
>
> Justin Cave
>
> gino.blyau_at_cec.eu.int (Gino Blyau) wrote in message news:<a4e1aee6.0304172241.25fc15d1_at_posting.google.com>...
> > 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 Wed Apr 30 2003 - 12:41:38 CDT