Dave,

> The traditional work around has been to open up the database with a
> generic user ID for the connection pool (Jim would say no security),
> and handle the authorization in the application.

But given the nominal processing cost of opening a separate connection,
why is it necessary to use a 'generic' connection?

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.

> Jim's proposal would allow the capacity to move this application layer
> security into the database. His example was changing roles for
> testing, which is of interest to developers. But that is really a
> side effect of moving a generic role-based enterprise application
> security model into the database rather than a primary goal.

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

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.

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)


Sean