I was wondering how DB User Authentication is handled using a Connection Pool. Since the Connections are created all at once, and the API requires a username and password for the database, how are users authenticated? When I use a single Connection, I just have the users login through a Dialog and I pass that info to the Connection object when I create the connection to establish user authentication. Can someone give me some direction on the correct protocal to follow when using a Connection Pool. Thanks
Pool usually works better if you have like an LDAP server to authenticate users. Then you don't need to authenticate users to db. But in your case, you would be better off using individual connection stored in each user's session. This would work with a few users (max 10-20) but won't work if there's hundreds or thousands of users or if there's a limited connection licence for db. However, there's always another way to do stuff. You could have the login mechanism try to open a db connection based on given credentials. If connection is opened, close it and consider the user authenticated. Then rest of the app would use a pooled connection.
The general model I've seen used is that the application is considered the "user" of the database and has a username/password/set of permissions for itself. It then effectively grants limited access to the database through itself, and worries about the users' set of roles/permissions within itself. The theory is that no one is granted access to your application who doesn't pass muster on accessing the database in the way that the application provides. Thus, all pooling is done using the app's db identity. To further protect security on the database, I've seen distinct pools set up for various levels of db access permission. For instance, if the administrative section of the program has more db access rights than the general section, it might have its own pool with a higher privilege db user id. The disadvantage of all of this is that any tracking a database might do of who touched what records is basically blown away by a connection pool strategy - all accesses to the database come through a limited set of user accounts.
Tina, I like your model you suggested with letting the application dicern the authentication. So in lue of that, here is the model I am choosing. Since my program is an Applet, I will use the Web server's security. So users will have to actually log into the Web Page, rather than the application itself. Once logged into the web page, the Applet will load and pull the correct connections accordingly. Thanks for everyone's input.