Leyne, Sean wrote:

>Shouldn't each user have a connection which is assigned to them, so that
>the database knows the user's true identity?
>
>If the cost of the connection seems too high, them use a pool but relate
>the db connection to an web application session. Most web application
>maintain some basic server-side session information.
>
>

A cheap database attachment/connection is a very virtuous
characteristic, but one that requires great attention. The Interbase
attachment used to be dirty cheap, but has gotten quite expensive over
the years and desparately needs a cleanup / shake down. A connection
starts with virtually no state, so creation should be dirty cheap. In
use, a connection acquires state and resources to be released after
use. In theory, at least, it should also be cheaper to creation a new
connection than cleanup and reuse a cached one, though current practice
may not bear this up.

>Jim's proposal would require that the application constantly manage the
>user's active database role.
>
>

I prefer to say that the proposal gave an application the appropriate
tools to manage role(s). If the application doesn't want to manage
roles and security, it can just let everything default. Nobody has to
do 'nuttin.

>My proposal would have the user's database security 'mirroring' the
>application security through the definition of groups which would be
>loaded on opening of the database connection and then cached for the
>duration. There is no need for the application to change roles.
>
>

As far as I can tell, there are no differences between groups and roles
other than their names.

>In both cases, the user's application security is known when they log
>in; so why can't the same apply to the database? (Log in and go)
>
>

Because the "user" is an application acting as an agent for a client
that requires database access to authenticate. You are aware, I presume,
that when you log onto EBay, you aren't actually logging on to the
Oracle database behind the EBay application.