Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Should I use a connection pool for this? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Should I use a connection pool for this?" Watch "Should I use a connection pool for this?" New topic
Author

Should I use a connection pool for this?

Rob Micah
Ranch Hand

Joined: Aug 30, 2011
Posts: 94
I have a web application that needs to make a JDBC connection to an oracle database when accessed. The account used to make the connection can change depending on the request. The problem is that the connection pool is using the last known account to try and keep the connection pool alive but the password for the account was changed and the constant login attempts lock the account.

What is the best approach for this?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42274
    
  64
How many DBs are there? And how many concurrent requests do you expect? You could use one connection pool for each DB.


Ping & DNS - my free Android networking tools app
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

That sounds weird. Why do you need to use different logins in your application? Aren't you actually looking for the ALTER SESSION SET CURRENT_SCHEMA command?

Oracle also supports proxy authentication, which sounds like something you might want to use too, but I've zero experience with that.

Your current setup is probably not workable. If you tell us why you need to use different users, perhaps someone here will be able to give a better advice.
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

Pooling implies that the connection has already been established and is "shared" by application across requests.

If you are stopping and starting again with new credentials you may have to custom build your own "pool" management.

WP
Rob Micah
Ranch Hand

Joined: Aug 30, 2011
Posts: 94
The reason my application is setup this way is that it shares a database with another application. This other application is where the user logs in. When the logged in user wants to run a report they are handed off to my application along with their credentials. Each user has specific tables and rows in the database they can and can't see so I am unable to use a generic login for my application.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

As I've mentioned earlier, I don't have direct experience with proxy connections, but it seems they would fit your needs nicely. You would keep a single connection pool and use the proxy connection to impersonate a different user to execute that user's operations in his security context.

You would need to do some setup for each user that would be used this way (ie. grant him the right to connect through the proxy connection), but you wouldn't need to hassle with passwords in the connection pool.

To me it looks like the proxy authentication was designed exactly for scenarios like yours, I'd strongly suggest exploring it.
Rob Micah
Ranch Hand

Joined: Aug 30, 2011
Posts: 94
I'll check into it. For the sake of discussion though, what's the best way to do a JDBC connection without using a pool inside something like a servlet?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Should I use a connection pool for this?