Andrej Koelewijn

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.