2 Replies Latest reply on May 5, 2013 7:30 AM by James Olsen

    Mapping Security Principal to JDBC Connection

    James Olsen Newbie

       

      "jim_b_o" wrote:
      Several JDBC drivers (including Oracle and DB2) have proprietary API for informing the database who the real user of a pooled connection is.

      Can anyone provide any guidance on how within JBoss (pref 3.2.3) I could determine when a pooled connection is being allocated to a particular user/thread so that I can call the extra API before the connection is used? The entry point would also need to be aware of the current EJB Security Principal so that can be used to determine who the real user is.


        • 1. Re: Mapping Security Principal to JDBC Connection
          James Olsen Newbie

           

          "jim_b_o" wrote:
          OK, I've made some progress on this myself.

          Getting the Principal is easy thanks to org.jboss.security.SecurityAssociation.getPrincipal(), so there is no need to have access to the EJBContext.

          In my case trying to set this on the Connection when it is allocated from the pool is no good due to the behaviour of the persistence mechanism (TopLink in this case). I tried providing my own org.jboss.resource.adapter.jdbc.ValidConnectionChecker implementation because it looked like it would be called at the right time but TopLink appears to be re-allocating connections from the pool immediately after using them in the scope of the last user rather than immediately before using them in the scope the new user. So plan B...

          I've hacked org.jboss.resource.adapter.jdbc.WrappedStatement to add the following method:
          private void setUser(Connection c) throws SQLException {
           WrappedConnection wc = (WrappedConnection)c;
           if (wc.getUnderlyingConnection() instanceof DB2Connection) {
           DB2Connection db2c = (DB2Connection)wc.getUnderlyingConnection();
           if (db2c != null) {
           Principal p = SecurityAssociation.getPrincipal();
           if (p != null) {
           db2c.setDB2ClientUser(p.getName());
           }
           else {
           db2c.setDB2ClientUser("<null>");
           }
           }
           }
           }

          which I call from setQueryTimeout() as this is in turn called from all the execute() methods. That way the necessary info is set on the Connection prior to any/all statements being executed.

          This is working with basic tests but I need to check it with XA and under high load to see if there are any issues.

          I'm not sure if this is the best approach and I guess it should be turned into something pluggable rather than a hack. Any comments welcomed.