Andres Aguiar wrote:
> I every user connects with a different user/password to the database, no
> connection pool can't do anything to help you... But if you have the users
> grouped (ie Marketing users, etc), and everyone in each group connects to
> the database with the same user/password, you can pool connections for each
> group. You can't assign a 'marketing connection' to a 'superuser
> connection', but you can share them between groups.
To address this issue, I have used an Oracle mechanism that lets you take
advantage of database-level security controls and still uses a generic
username/password to log on (so you can benefit from a single connection
pool). Instead of granting access privileges to users, we grant them to
roles. The generic username/password used to sign on has the ability to use
any of these roles (optionally with a role password that the user is required
to provide at login time, and gets saved in their session).
Now, when I allocate a connection from the connection pool, I do a SET ROLE
command with the appropriate role for the individual user, which limits
database access for the rest of this transaction to the privileges assigned to
that role. This does give up auditing at the database username level, but that
can often be dealt with at the app level.
Why do this? Because I want my app to be more scalable. Let's say you have a
license for 16 concurrent connections to your database. Allocating a
connection per user means you can have 16 simultaneously logged on users, even
on an Internet based app. Using a connection pool with a common username,
those same 16 connections are shared across many more simultaneous users --
perhaps even hundreds if they are not hitting the server very rapidly. While
the user is viewing the HTML pages delivered by the app, those connections are
available to support others, instead of being hidden away in a particular
user's session.
Craig McClanahan
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
