drop user neutral;
drop user one_gets_in;
drop user one_wants_in;
 
create user neutral identified by neutral;
grant create session to neutral;
 
create user one_gets_in identified by gets;
grant create session to one_gets_in;
 
 
create user one_wants_in identified by wants;
alter user one_wants_in grant connect through one_gets_in;
grant create session to one_wants_in;
 
 
 
set heading off
 
 
connect neutral/neutral@ora11b
select 'AUTHENTICATED_IDENTITY: '|| sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'CURRENT_SCHEMA........: '|| sys_context('USERENV','CURRENT_SCHEMA'),
       'CURRENT_SCHEMAID......: '|| sys_context('USERENV','CURRENT_SCHEMAID'),	
       'ENTERPRISE_IDENTITY...: '|| sys_context('USERENV','ENTERPRISE_IDENTITY'), 	
       'IDENTIFICATION_TYPE...: '|| sys_context('USERENV','IDENTIFICATION_TYPE'), 	
       'OS_USER...............: '|| sys_context('USERENV','OS_USER'), 		
       'PROXY_USER............: '|| sys_context('USERENV','PROXY_USER'), 		
       'PROXY_USERID..........: '|| sys_context('USERENV','PROXY_USERID'), 		
       'SESSION_USER..........: '|| sys_context('USERENV','SESSION_USER'), 		
       'SESSION_USERID........: '|| sys_context('USERENV','SESSION_USERID'), 		
       'SESSIONID.............: '|| sys_context('USERENV','SESSIONID'), 		
       'SID...................: '|| sys_context('USERENV','SID')
   from dual;
 
rem Standard connect for 'Gets'
connect one_gets_in/gets@ora11b
/ 
 
connect neutral/neutral@ora11b
 
rem standard connect for 'Wants'
connect one_wants_in/wants@ora11b
/ 
 
rem connect proxy[user]/proxy_password
rem these should not work:
rem 
rem User wants using wants password
connect one_wants_in[one_gets_in]/wants@ora11b
/ 
 
rem User wants using gets passworf
connect one_wants_in[one_gets_in]/gets@ora11b
/ 
 
rem User gets using gets password
connect one_gets_in[one_wants_in]/wants@ora11b
/ 
 
rem this should work
connect one_gets_in[one_wants_in]/gets@ora11b
/ 
 
 
rem and now switch the schema we a reusing
alter session set current_schema=neutral;
 
select 'AUTHENTICATED_IDENTITY: '|| sys_context('USERENV','AUTHENTICATED_IDENTITY'),
       'CURRENT_SCHEMA........: '|| sys_context('USERENV','CURRENT_SCHEMA'),
       'CURRENT_SCHEMAID......: '|| sys_context('USERENV','CURRENT_SCHEMAID'),	
       'ENTERPRISE_IDENTITY...: '|| sys_context('USERENV','ENTERPRISE_IDENTITY'), 	
       'IDENTIFICATION_TYPE...: '|| sys_context('USERENV','IDENTIFICATION_TYPE'), 	
       'OS_USER...............: '|| sys_context('USERENV','OS_USER'), 		
       'PROXY_USER............: '|| sys_context('USERENV','PROXY_USER'), 		
       'PROXY_USERID..........: '|| sys_context('USERENV','PROXY_USERID'), 		
       'SESSION_USER..........: '|| sys_context('USERENV','SESSION_USER'), 		
       'SESSION_USERID........: '|| sys_context('USERENV','SESSION_USERID'), 		
       'SESSIONID.............: '|| sys_context('USERENV','SESSIONID'), 		
       'SID...................: '|| sys_context('USERENV','SID')
   from dual;

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.