struts-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Bollmeyer <j...@christianbollmeyer.de>
Subject Re: Connection Pooling + User Authentication
Date Tue, 23 Sep 2003 19:17:02 GMT
Am Dienstag, 23. September 2003 19:41 schrieb Craig R. McClanahan:

Just for completeness, yet another approach. As I can't explain it 
better, I'll just cite the relevant passage from Hans Bergsten's 
ever-resourceful JSP book (the O'Reilly one with the wolf on the
front cover, 2nd edition, p. 475):

"A connection pool doesn't solve all problems, however. Because
all users are using the same Connection objects, you can't rely
on the database engine to limit access to protected data on a
per-user basis. Instead, you have to define data-access rules
in terms of roles (groups of users with the same access rights).
You can then use separate pools for different roles, each pool
creating Connection objects with a database account that
represents the role."

Considering the commons-dbcp implementation (in particular
the one that gets shipped with Tomcat 4.1.24), I may add
that not all Oracle driver versions run well in this environment,
with some of them issuing ORA-xxxx messages with very
low numbers recommending to consult Oracle developer
support, regards of 'thin' or OCI-based types. IIRC even
the ones that get shipped with Oracle 9i R2 or JDev 9.0.3
failed in this direction. If you experience the same problem,
try the latest 9i R2 drivers available from OTN. Note that
Oracle recently changed the driver architecture, so there
are the legacy 'classes12..zip |. jar' ones plus the 'new'
ones with a different naming scheme (was it ojdbc4.jar?)
designed for 1.4.x and later now. For details, you might
want to give the documentation that accompanies the
file a closer look. 

-- Chris

> Kapadia Mitesh-C23457 wrote:
> >Hello.
> >I would like to explore Connection Pooling as opposed to direct JDBC
> > calls to an Oracle V8.1.6 database in a STRUTS application.
> >The queries that are being executed have some kind of security built
> > into them such that they only return results based on the User Id (
> > a look up of the User Id is conducted in this query to determine
> > the level of security) My question: If I were to implement
> > Connection Pooling, is it possible to individually identiffy each
> > user in the connection pool when a connection is being used? If a
> > user is using a pool from the DB Connection Pool, can the user
> > still be individually identified by their User ID as opposed to the
> > User ID used to create the DB Connection Pool?
> >This would be a requirement since the user id would be used to
> > determine the level of security in the queries on the DB.
> >Any assistance you can provide would be most appreciated.
> >
> >Thanks in advance.
> >
> >Mitesh
>
> Some connection pools (not including commons-dbcp) do support the
> ability to pool connections that are registered to individual
> database usernames.  However, this is going to reduce the reusability
> of the connections -- the connection that user "foo" just put back
> into the pool cannot be used by user "bar".
>
> For Oracle in particular, I've had a lot of success doing things
> based on database roles, rather than usernames.  Then, in your DAO
> object you would acquire a generic connection (one that has a valid
> username/password, but no access to any tables) and then do a SET
> ROLE statement based on who the current user is.  Then, before you
> return the connection to the pool, you will want to reset the role
> again.  Using this approach maximizes the usefulness of any
> connection pool.
>
> Craig


---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org


Mime
View raw message