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

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 Fri Apr 18 2003 - 17:07:16 CDT