proxy user revisited

A new 10.2 feature I mentioned in su in sqlplus – Laurent Schneider is the ability to use a proxy user in a connection string.

This was documented in What’s New in Oracle Call Interface?

Proxy access for a single client can be set using various connect strings.

Today I got one user complaining about ORA-01045: user USER lacks CREATE SESSION

Proxy was introduced in Oracle 8 (OCI only) and enhanced in numerous versions.

In JDBC, we used openProxySession as defined in  OracleConnection (Oracle Database JDBC Java API Reference)

In C, we used OCILogon2 and OCIConnectionPoolCreate with an empty password for the USER2 as documented in Connect, Authorize, and Initialize Functions (oracle.com)

In both case, the CREATE SESSION was required.

For the C code, check for cdemocpproxy.c in the demo directory or on google and compile it with

cc -lclntsh -I$ORACLE_HOME/rdbms/public -L$ORACLE_HOME/lib cdemocpproxy.c -o cdemocpproxy && ./cdemocpproxy

For Java, here is a sample code

import java.util.Properties;

import java.sql.*;

import oracle.jdbc.OracleConnection;




public class Proxy2 {

  public static void main(String argv[]) throws SQLException {

    String url = "jdbc:oracle:thin:@srv01:1521/DB01.example.com";

    Properties props = new Properties();

    props.setProperty("user", "user1");

    props.setProperty("password", "Secret_Passw0rd");

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Connection conn = DriverManager.getConnection(url, props);

    OracleConnection oc = (OracleConnection)conn;

    props.setProperty(OracleConnection.PROXY_USER_NAME, "user2");

    oc.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, props);

    ResultSet res = conn.

      prepareCall("select user from dual").

    executeQuery();

    res.next();

    System.out.println(res.getString("USER"));

  }

}

create user USER1 identified by Secret_Passw0rd;

create user USER2 identified by Secret_Pr0xYYY;

grant create session to user2;

alter user user2 grant connect through user1;

In multiple examples found on the Internet including OracleBase, AskTom, MyOracleSupport, the user1 receives CREATE SESSION.

Without CREATE SESSION, both my C and Java examples fail with :

Error - ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied

However, if you don’t use openProxySession but simply use USER1[USER2] as a username, it succeed

As it does in SQLPLUS

SQL> connect USER1[USER2]/Secret_Passw0rd@DB01
Connected.

Or also in java :

import java.util.Properties;

import java.sql.*;


public class Proxy1 {

  public static void main(String argv[]) throws SQLException {

    String url = "jdbc:oracle:thin:@srv01:1521/DB01.example.com";

    Properties props = new Properties();

    props.setProperty("user", "user1[user2]");

    props.setProperty("password", "Secret_Passw0rd");

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    Connection conn = DriverManager.getConnection(url, props);

    ResultSet res = conn.

      prepareCall("select user from dual").

    executeQuery();

    res.next();

    System.out.println(res.getString("USER"));

  }

}

Or in C

OCIConnectionPoolCreate(... "USER1[USER2]", "Secret_Passw0rd" ...)

This is a major behavior change that I blogged 16 years ago and realized today. I am glad I blogged all my memories 🙂