The following example illustrates the use of Oracle proxy users. If you wonder what proxy users are, have a look at my previous posting: Oracle Proxy Users.
First we’ll create two database users. One normal application user, pu_user_1, and a Big Application User, pu_pool_user. The big application user will be used by a web application to connect to the database. Usually the application user, in this case pu_user_1, will be authenticated by the web application.
create user pu_pool_user identified by pu_pool_user; grant create session to pu_pool_user; create user pu_user_1 identified by pu_user_1; alter user pu_user_1 grant connect through pu_pool_user; grant create session to pu_user_1;
By granting pu_user_1 the connect through privilege, you allow pu_pool_user to proxy for pu_user_1, i.e., pu_pool_user connects to the database, but it will be as if pu_user_1 is connected.
First a normal connection. The following example connects to oracle using the user pu_pool_user:
private void normalConnection() throws Exception {
Properties properties = new Properties();
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:pu_pool_user/pu_pool_user@localhost:1521:ora1012",
properties);
printUserInfo(conn);
conn.close();
}
If you ask oracle who is connected, oracle will identify the connected user as pu_pool_user. This is displayed using the following code:
private void printUserInfo(Connection conn) throws Exception {
System.out.println(
"Is proxy session: " + ((OracleConnection) conn).isProxySession());
PreparedStatement stmt = conn.prepareStatement(
" select user, username "
+", sys_context('USERENV','PROXY_USER') "
+", sys_context('USERENV','CURRENT_USER') "
+", sys_context('USERENV','SESSION_USER') "
+" from sys.v_$session"
);
ResultSet rset = stmt.executeQuery();
if (rset.next()) {
System.out.println("user : " + rset.getString(1));
System.out.println("username : " + rset.getString(2));
System.out.println("userenv proxy_user : " + rset.getString(3));
System.out.println("userenv current_user : " + rset.getString(4));
System.out.println("userenv session_user : " + rset.getString(5));
}
stmt.close();
}
This method prints the following info for the normal connection:
Is proxy session: false user : PU_POOL_USER username : null userenv proxy_user : null userenv current_user : PU_POOL_USER userenv session_user : PU_POOL_USER
The following code connects to the database as pu_pool_user, but then we tell the connection that we’re actually proxying for pu_user_1.
private void proxyConnection() throws Exception {
Properties properties = new Properties();
properties.put("PROXY_USER_NAME", "pu_user_1");
OracleConnection conn = (OracleConnection) DriverManager.getConnection(
"jdbc:oracle:thin:pu_pool_user/pu_pool_user@localhost:1521:ora1012",
properties);
conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, properties);
printUserInfo(conn);
conn.close();
}
The result of calling printUserInfo:
Is proxy session: true user : PU_USER_1 username : PU_USER_1 userenv proxy_user : PU_POOL_USER userenv current_user : PU_USER_1 userenv session_user : PU_USER_1
As you can see, USER now returns PU_USER_1, eventhough we connected to the database as pu_pool_user.
This is very convenient, you do not have to rewrite your security code and your auditing code.
August 12th, 2006 at 08:08:52
Hi,
Wanted to know if there are any drawbacks in using the proxy user approach, especially in the following areas:
- effects on connection pooling (as connections would be pooled using the Big Application User)
- overhead in authenticating every time a database connection is acquired
Regards,
Venky
August 14th, 2006 at 09:28:01
I’m not aware of any drawbacks.
- Most web applications have a big application user, even if they don’t use proxy users. Not much difference.
- Are you sure authentication is performed every time a database connection is acquired? I would expect the oracle jdbc drivers to cache authentication information.
November 10th, 2006 at 17:57:37
One question I have, looking at your example, there’s no password sent for the individual user. Doesn’t that sort of defeat the point of proxying the user, if you can’t authenticate that it is that user? Or am I missing something? Was a line or two left out of the example?
November 10th, 2006 at 18:02:41
A proxy user is authenticated by the application server. The database trusts the application to have authenticated the user.
February 9th, 2007 at 02:28:23
What about roles does the biguser need to be granted a union of all the roles of the users it proxies or can it have none and not be priviledged beyon create session?
March 30th, 2007 at 17:51:13
Hi,
Suppose we have a “owner schema” that owns all the objects of the application.
How are defined the users pu_user_1 ? They must have synonyms to the owner schema ?
What would you recommand ?
April 10th, 2007 at 08:20:23
I think that there’s no difference when using proxy users or not in this respect. A user needs to be able to access objects in another schema, so you need either public synonyms, private synonyms or a fully qualified naming and the required privileges.
April 10th, 2007 at 21:29:25
Responding to dfc:
In the oracle 10.2 sql doc, there is an “authentication required” clause on the “alter user pu_user_1″ which forces a password to be supplied when the proxy user switches over to the other user
I haven’t ever done it but that is what is in the docs
November 20th, 2007 at 18:06:07
Just an update
The authentication required clause works fine
December 20th, 2007 at 14:53:39
I just tried out your code, and I get an error saying that
OracleConnection.PROXYTYPE_USER_NAME cannot be resolved
and
The method isProxySession() is undefined for the type OracleConnection
i am using ojdbc14.jar in my classpath. please help
January 12th, 2008 at 00:37:42
Hi,
I want to ask one thing,that’s implement VPD using proxy_user to grant access for user.My trouble is that user from remote connect to database using database link,in which the user in database link is different from this current user,(certainly on server,this user have role to access data).I want to ask:how can we paypass this?how to implementation authentication that transparent to location of the user(because i’m using database link).
Thanks for all answer!
December 11th, 2008 at 17:20:55
We are implementing Proxy authentication in our project.We are using Oracle Clinet9.0.4.0.1, I am using OracleOCIConnecionPooling to establish proxy connection,However I am getting some ecxeption that i dont understand. Here is the code
OracleOCIConnectionPool cpool=new OracleOCIConnectionPool(“username”,”password”,”jdbc:oracle:oci:@(description=(address=(host=hostname)(protocol=tcp)(port=1529))(connect_data=(sid=databasename)))”,poolConfig);
Properties prop=new Properties();
prop.out(OracleOCIConnectionPool.PROXY_USER_NAME,username);
prop.put9ORACLEOCIConnectionPool.PROXY_PASSWORD,password);
Connection conn=pool.getProxyConnection(OracleOCIConnectionPool.PROXY_USER_NAME,prop);
Intially I got some erros while establishing a OracleOCIConection and I resoved those. But I am getting
java.sql.SQLException: invalid proxy type specified : null properties
Any Help??
May 15th, 2009 at 01:03:37
[...] http://www.it-eye.nl/weblog/2005/09/12/oracle-proxy-users-by-example/ [...]
June 23rd, 2010 at 17:34:42
[...] a way is needed to set a username for each (JDBC) database connection retrieved. It-eye weblog explains how to open a connection to a database using java, and then switch to another [...]