Switching users on a JDBC Connection

Question

I am writing a Java JDBC database application that connects to an Oracle 11g database and am using a c3p0 connection pool. For the purposes of an example, I have 3 database users DEFAULT, TOM, and BILL. c3p0 opens all of the pooled Connections with the DEFAULT database user. I would like to retrieve one of the pooled Connections from c3p0 and change the user for the Connection to be BILL instead of DEFAULT. Is it possible to do this in JDBC without establishing a new connection with the database?

I have already tried doing the following:

connect BILL/password;

But this does not work. I get an error saying

java.sql.SQLException: ORA-00900: invalid SQL statement

Are there any other options? Is there something having to do with context set or switching that can facilitate what I'm trying to do?

Thanks!

Answer 1

If these users do not login interactively to the database via your application, is it unreasonable to just have three separate pools, one for each user? Then use some connection manager to retrieve the appropriate connection?

Answer 2

You can use DataSource.getConnection(String user, String password). c3p0 internally maintains a separate pool for each user.

Answer 3

c3p0 creates physical connections with the credential you told him to use and you can't change the credentials of a connection obtained from a pool after the facts. If you want to use connections with different database users, you need to create and use different pools.

Answer 4

Have you tried issuing this statement via jbdc:

alter session set current_schema=BILL.

If I remember correctly the oracle structure, the username with which you connect is the same as the schema you are working on.

I did use the above statement successfully in the past with Oracle 10 via jdbc. My user was the root/admin user, it had permissions to various database schemas and I had a need to switch between them in the same connection. Notice that I didn't need to supply a password again.

This doesn't sound like a very security-conscious model, so I don't know if it is suitable for your use-case.

Answer 5

After researching yesterday, I found that the solution is to use Oracle Proxy Authentication. This solution is outside of the JDBC specification. However, Oracle provides a hook to implement such a solution. Opening a proxy connection would look like as follows:

import oracle.jdbc.OracleConnection;    

//Declare variables
String url = "...";
String username = "...";
String password = "...";

//Create the Connection
Connection conn = DriverManager.getConnection(url, username, password);

//Set the proxy properties
java.util.Properties prop = new java.util.Properties();
prop.put(OracleConnection.PROXY_USER_NAME, "BILL");
prop.put(OracleConnection.PROXY_USER_PASSWORD, "password");

//Cast the Connection to an OracleConnection and create the proxy session
((OracleConnection)conn).openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop);

/* The Connection credentials have now been changed */

I wouldn't be surprised if there are other nuances associated with this, but this is a good start. Thanks for your help, everyone!