How can I ensure that database security is applied to the current user when I use a connection pool since the connections are created with a default user ID and password?

Joe Sam Shirah

In general, you can't. Nearly all optimizations involve trade-offs and a large part of the developer's job is to select those that make the most sense under a given set of circumstamces. This is really a security question, which can easily evolve into a can of worms. I'll start with a quote from JDBC Performance Tips that connection pooling "may also be best suited to apps in which there is a certain class of users that can share connections... This would not be well-suited to apps in which each user had to have their own individual authority verified and used for the connection, since this would conflict with the concept of saving away connections for use by any number of users." While you may find some database engine that allows a temporary user, it certainly isn't a common or standard feature.

Most workable security involves groups or levels of users, so that a security conscious connection pool would probably set up connections based on group level passwords. If security is a concern, one should get or create a pool that works as hinted by the ConnectionPoolDataSource class JDBC 2.0 Optional Package. It has the method getPooledConnection(String user, String password). Clearly the intent is that classes of connections are created and when one is requested, the user and password is compared to those of current connections. If there is a request that doesn't match the group levels, a new connection is created and passed back. Of course, this raises the spector of a program having knowledge of passwords and potentially passing them around over the internet, bringing up another yet another security issue, which is probably best resolved by having the pool manager resident on the server.