John Burns wrote:
> <<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.>>
>
> <<Craig McClanahan wrote:
>
> >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.
>
> I like this solution. Forgive me for asking, but how would you obtain the
> user's role without accessing a database first (requiring a connection)?
> Mandatory role ID prefix in the user ID?
>
Again, this is my solution, but it works like this:
* Database connections are made at server startup time, and stored in a pool.
* These connections have a common Oracle username/password.
* The only database object visible to these connections, until a role is set,
is a view that maps usernames to the role(s) that should be enabled
for that username.
* When a servlet grabs a connection from the pool, it immediately consults
this view, constructs an appropriate SET ROLE statement, and executes it.
* Prior to returning the connection to the pool, the role is reset to the
logged-on
state, where only the user->role mapping view is visible again.
In fact, since I rolled my own connection pool, I've embedded most of the logic
described above into the allocate and deallocate methods of the pool. I can say:
Connection conn = pool.allocate("cmcclanahan");
and it gives me a connection with the role preset for me, based on the rules
above. Then, when I call:
pool.deallocate(conn);
the pool resets the default role before making it available again.
"Works fine, lasts a long time" :-)
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
