Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Should I use a connection pool for this?

 
Rob Micah
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How many DBs are there? And how many concurrent requests do you expect? You could use one connection pool for each DB.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 103
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 103
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic