在JDBC连接上切换用户 [英] Switching users on a JDBC Connection

问题描述

我正在编写一个连接到Oracle 11g数据库并使用c3p0连接池的Java JDBC数据库应用程序。出于示例的目的，我有3个数据库用户DEFAULT，TOM和BILL。 c3p0使用DEFAULT数据库用户打开所有池化连接。我想从c3p0中检索一个池连接，并将Connection的用户更改为BILL而不是DEFAULT。是否可以在JDBC中执行此操作而不与数据库建立新连接？

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?

谢谢！

推荐答案

昨天研究后，我发现解决方案是使用Oracle代理身份验证。此解决方案不在JDBC规范中。但是，Oracle提供了实现此类解决方案的钩子。打开代理连接如下所示：

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!

这篇关于在JDBC连接上切换用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！