Proxy Authentication in Oracle using JDBC

Hi All,

Oracle Java Database Connectivity (JDBC) provides proxy authentication, also called N-tier authentication. This feature is supported through both the JDBC Oracle Call Interface (OCI) driver and the JDBC Thin driver. Proxy authentication is the process of using a middle-tier for user authentication. In all cases, an administrator must authorize the middle-tier server to proxy a client, that is, to act on behalf of the client. Operations done on behalf of a client by a middle-tier server can be audited. To grant a proxy connectivity first of all we have to run the below query in DB.

ALTER USER userA GRANT CONNECT THROUGH connection_pool_user;

Creating a Proxy Connection in Java is very simple. All we have to do is to create a connection object from the connection pool first.

For example, lets assume you have weblogic server with connection pool configured with UserZ(Connection pool user)

Step 1.
Get a connection from the pool first.

Connection con= dataSource.getConnection() // getting connection from the pool over Connection pool user{UserZ}

Step 2
Create a proxy connection by invoking the openProxySession method of OracleConnection class.

For the first argument pass type as a standard value(OracleConnection.PROXYTYPE_USER_NAME),

And then pass the properties for  proxy connection in second argument.
Properties properties = new Properties();
properties.put(“PROXY_USER_NAME”, userName); // This property value should be used with the type OracleConnection.PROXYTYPE_USER_NAME

<<Code snippet>>
java.util.Properties prop = new java.util.Properties();
prop.put(OracleConnection.PROXY_USER_NAME, “userABC”);
conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME, prop); // Now the proxy connection has been created

Step 3 Closing the proxy Connection.
It is always a best practise to check the proxy session with isProxySession() method of OracleConnection class before closing that.
And to close simple invoke the closeMethod().
OracleConnection.close(OracleConnection.PROXY_SESSION);

To close a proxy session on a non-cached connection simple invoke the same method with different argument.
OracleConnection.close(OracleConnection.INVALID_CONNECTION);

Be careful while using the proxy connections on Oracle Database. I will post the issues in my next blog for the same.

Thanks
R Vashi

Advertisements

8 thoughts on “Proxy Authentication in Oracle using JDBC

  1. Ramzi

    Hi,

    Thank you for this entry. I followed your advises but I get more sessions in the database than it should be.
    “Be careful while using the proxy connections on Oracle Database. I will post the issues in my next blog for the same.” Please can tell me about this issues.

    Thanks in advance,
    Ramzi

    Reply
  2. ®V Post author

    Hi Ramzi,
    Could you please detailed more about problem, if you are able to see multiple sessions for the same user in database, then please make sure you are closing the proxy connection in code.

    e.g
    if(connection!=null){
    if (connection.isProxySession()){
    connection.close(Oracleconnectionection.PROXY_SESSION);
    /*
    If you are caching the proxy connections,invoke the same method with different argument.
    OracleConnection.close(OracleConnection.INVALID_CONNECTION);
    */
    }
    connection.close();
    connection=null;
    }

    Please try this and let me know if you are still facing the issue,

    And about being careful the proxy connections. You can read this article if you are using proxy connections with connection pool on Weblogic. https://rocksolutions.wordpress.com/2010/06/04/connection-pool-issue-on-weblogic/


    Thanks
    R Vashi

    Reply
  3. Ramzi

    Hi,

    I will explain more in detail my problem.
    I configured a connection pool in Weblogic with a proxy account.
    Then, I developed a loginModule and an auditModule.
    When the user log in to the application, the loginModule tries to open a proxy session with the provided username and password. This module is working fine (I have seen that when the proxy authentication failed the only way to close session is to use connection.abort(), this works for me).
    Now returning to the auditModule, this one is using the proxy account, so it doesn’t open a proxy session in order to save record into the database and this my problem, whereas I’m closing the connection the session still exists in the database. I tried connection.close(), connection.close(INVALID_CONNECTION) but it doesn’t work for me unless I’m misleading something.

    What do you mean by caching connection ? I’m using only the connection pooling on weblogic side (I get the datasource from the JNDI and then I get a connection)

    Thanks,
    Ramzi

    Reply
    1. ®V Post author

      Hi Ramzi,

      First of all check few things.
      1. All the users which are trying to open a proxy connection, should have connect grant from the Proxy Account.
      e.g alter user UserA grant connect through PROXY_ACCOUNT_NAME

      Also Proxy connections, like standard connections, can be cached. Caching proxy connections enhances the performance. But as per your details seems like you are not using(which is ok).

      Did you try by closing the connection
      OracleConnection.close(OracleConnection.PROXY_SESSION)

      you can refer to the below Oracle documentation: http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/proxya.htm

      try this and let me know if you still facing issues.


      Thanks
      R Vashi

      Reply
  4. Ramzi

    Hi Vashi,

    Yes I did, this drive me crazy, the connection is closed but the session remains active on the database, when I do a select on v_$session I got 6 sessions instead of 5, and in the pool weblogic indicate that there are only 5 active connections.

    Here is the code:

    try {
    cs.close();
    /*1*/ printUserInfo(“createLoginTrace#79”, oracleConnection);

    oracleConnection.close(oracleConnection.PROXY_SESSION);
    /*2*/ printUserInfo(“createLoginTrace#79”, oracleConnection);
    oracleConnection.close();
    /*3*/ printUserInfo(“createLoginTrace#74”, oracleConnection);
    } catch (Exception ignore) {
    ignore.printStackTrace();
    }
    oracleConnection = null;

    the /*1*/ indicates that it’s a proxy connection
    /*2*/ indicates that it’s a non proxy session
    /*3*/ fires a exception since the connection is closed but the session remains active on the database.

    This the problem I’m facing.

    Thanks in advance!

    Reply
  5. ®V Post author

    Hi Ramzi,

    Can you paste the code how you getting the connection from pool. and setting the proxy settings over that connection object.
    the way you are closing the connection seems fine. It usually never happens with proxy connections.

    Reply
  6. Ramzi

    Hi Vashi,

    Finally I found the problem, it’s the driver I choosed in weblogic. I changed the driver I everything is working fine.

    Thank you for your help !
    Ramzi

    Reply
  7. Team Roster

    Best you should edit the webpage name title Proxy Authentication in Oracle using JDBC Rocksolutions's Blog to something more generic for your content you make. I loved the post yet.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s