You are missing something - very much I assume: Reasonable system design. ;) The whole idea of managing users with their database credentials is good for exactly one use case imho - managing databases. For everything else, it imposes more problems than it does good... So let the shitstorm begin... ;)
BOT: There is a dilemma. You basically have to create a connection pool for each user, simply because otherwise you would have to create a connection to the database each time your datasource runs in a connection timeout. And a three way handshake plus authentication isn't exactly a cheap thing to do - latencies will kill your performance. While some drivers can be configured accordingly, this is a bad idea in general and most drivers aren't good at managing connections themselves because of what is called "separations of concerns". On the other hand, each connection "pool" needs only some 5 connections, since it is very unlikely that the same user will do many operations in parallel.
Note: The only driver I am aware of which manages connections and pools decently is MongoDB's driver.
Now here is the problem: You can't attach a connection pool to a session. Not without walking quite a few miles, and I doubt that it is possible altogether.
One idea to get around this is to create a connection pool for a user when he or she logs in and register it dynamically with JNDI. The problem is that this is not very scalable (assume you have a couple of hundreds of users). So you have to make sure that the pool is removed when a session terminates (either by logout or timeout). Plus, the code has to be maintained.
Another idea is to use Apache Shiro and write a custom Realm which simply tries to log in into the database to check the credentials, throwing an AuthenticationExcepion if this fails. The tradeoff here is that you'd have to initialize a connection each time, which has some latency. Your realm might even use an application wide connection pool and inspect the databases metadata for authentication and authorization data. Of course that would make it necessary to access the database as an privileged user - which is a horrible idea.
Bottom line: No matter which way you look at it, administering an applications authentication and authorization needs via the database may eliminate the authentication and authorization layer in the application, but in turn requires an additional layer of abstraction (you don't want to change your service's code in case some database mechanisms change, do you?), imposes scalability problems as we saw and ties your code very much to one database (except of course you don't want fine grained permissions and can live with "worked, didn't work" results).
DataSource.getConnection(username, password)are pooled at all (I have seen pools that do and pools that don't).