13 Replies Latest reply on Dec 2, 2015 10:34 AM by cchebert

    Associate user with database connection from pool

    nickarls

      Possibly something for Jesper Pedersen...

       

      We are using an Oracle database and application contexts and would need a way for associating a user token with a connection checked out from a datasource, what would be the most natural integration point? I'm not talking about user specific connection pools, more of a place where I could associate the token with the connection once it has been checked out. I would probably have to execute some "alter session"-command or calling an oracle-specific package automatically before JPA etc uses it. Our views return user specific data from views depending on user-specific data and therefore the "session" needs to be propagated between connection checkouts.

       

      Any pointers appreciated,

      Nik

        • 1. Re: Associate user with database connection from pool
          jesper.pedersen

          <connection-listener> + org.jboss.jca.adapters.jdbc.spi.listener.ConnectionListener

          • 2. Re: Associate user with database connection from pool
            nickarls

            Cool, thanks, I'll give it a try. Tried looking for the listener but didn't see it in the schema docs. I guess the listener is execute in "module classloader space", is the any way of accessing "application space" from there so I could read application user info etc?

            • 3. Re: Associate user with database connection from pool
              jesper.pedersen

              wildfly-datasources_2_0.xsd+ ?

               

              The order of loading the class is module, TCCL and container.

               

              Re: application space, depends on what you can pass in using the <config-property>'s, and data in your ThreadLocal...

              • 4. Re: Associate user with database connection from pool
                cchebert

                Hi !

                 

                We have a similar requirement and we are currently evaluating JBoss EAP 6.3 as a replacement for Glassfish. Those listeners that you were suggesting are not available in eap 6.3 right ? Any tips on how we can achieve the same goal ? We need to open database connections with a generic account and then set a context variable (alter session) which be used later by the "virtual private database" mechanism of Oracle. With Glassfish we managed to do that through a connector project but I'm looking for an easier (ligther) way.

                 

                Thanks in advance,

                 

                Christian

                • 5. Re: Associate user with database connection from pool
                  jesper.pedersen

                  You will have to set the information in your application whenever you use a connection - it also depends on the tech used, f.ex. JPA uses a connection too behind the scenes...

                  • 6. Re: Associate user with database connection from pool
                    cchebert

                    Thanks Jesper, we are using JPA everywhere so I guess I'll stick to JCA and use a custom resource adapter.

                     

                    Thanks !

                    • 7. Re: Associate user with database connection from pool
                      jesper.pedersen

                      I hope you mean some sort of wrapper for the DataSource bound in JNDI, and not a replacement for :datasources:

                      • 8. Re: Associate user with database connection from pool
                        cchebert

                        yes, I will not try to implement my own datasource , I'm not that mad !

                        • 9. Re: Associate user with database connection from pool
                          cchebert

                          Hi Jesper,

                           

                          Do you know if there is any plan of integrating those connection listeners you mentioned above in JBoss EAP any time soon ? I can't get my head around this problem ! From where I stand, I can't figure a way to achieve my goal without having my own connection factory, my own datasource and other stuff like that.

                           

                          Any help would be much appreciated !

                           

                          Christian

                          • 10. Re: Associate user with database connection from pool
                            jesper.pedersen

                            Contact Red Hat for product roadmaps.

                             

                            Yes, it does require some work to do the same in EAP atm, but doable -- delegator of DataSource and Connection with override in DataSource::getConnection(..) and Connection::close(). Then bind delegator in JNDI, and use that.

                            • 11. Re: Associate user with database connection from pool
                              cchebert

                              Thanks Jesper, it's working now.

                               

                              If anybody need the same thing, here's how I managed to create my wrapper. It's quite simple 

                               

                              (I'm new around here so if this is not place to publish that kind of stuff, let me know!)

                               

                              1) Create a custom Datasource class :

                               

                              public class RunAsWrapperDatasource implements DataSource {

                                   private DataSource delegate;


                                   public RunAsWrapperDatasource(String name) {

                                        try {

                                         InitialContext context = new InitialContext();

                                         this.delegate = (DataSource)context.lookup(name);

                                       }

                                       catch (Exception ex) {

                                         // exception handling stuff here

                                       }

                                  }

                               

                                public Connection getConnection() throws SQLException {

                                  Connection connection = getDelegate().getConnection();

                               

                                   //do whatever you need to do before returning the connection

                               

                                  return connection;

                                }

                               

                              [...]

                              }

                               

                              2) Create an ObjectFactory

                               

                              public class RunAsWrapperObjectFactory implements ObjectFactory {

                                public Object getObjectInstance(Object obj, Name name, Context nameCtx, Hashtable<?, ?> environment) throws Exception {

                                  return new RunAsWrapperDatasource((String)environment.get("delegate"));

                                }

                              }

                               

                              3) Create the datasource as usual

                               

                              4) Create the binding for the wrapper

                               

                                      <subsystem xmlns="urn:jboss:domain:naming:1.4">

                                          <bindings>

                                              <object-factory name="java:global/jdbc/MyDataSourceWrapper" module="mymodule" class="mypackage.RunAsWrapperObjectFactory">

                                                  <environment>

                                                      <property name="delegate" value="the jndi name of the datasource"/>

                                                  </environment>

                                              </object-factory>

                                          </bindings>

                                          <remote-naming/>

                                      </subsystem>

                              • 12. Re: Associate user with database connection from pool
                                nickarls

                                Thanks, I managed to get my wrapper up and running with your instructions. I notice that during a normal JSF page render, the getConnection gets *a lot* of hits so the connection->user association should be really quick (or a quick way to check if the information has already been set). Do you call something over the session to associate the information or have you found some attribute on the connection itself that could be used for holding information (and read from the DB side)?

                                 

                                Also, are you using ThreadLocals to pass the information between your application and the wrapped datasource?

                                 

                                Thanks in advance,

                                Nik

                                • 13. Re: Associate user with database connection from pool
                                  cchebert

                                  We use the security context to retrieve the username of the logged user. The username is then passed to the Oracle session (using  DBMS_SESSION.set_context). Finally, this session variable can be used in triggers on anything running in the database.

                                   

                                  The Subject (who contains the username) can be retrieved using this line of code : (Subject) PolicyContext.getContext("javax.security.auth.Subject.container");

                                   

                                  ThreadLocal variables can be tricky. Threads can and will be reused of other servlet/ejb calls so you have to make sure that you clean up your variables before releasing the threads. I haven't tested it on JBoss (since we currently moving from Glassfish to JBoss) but on Glassfish, instead of using ThreadLocal variables to pass information between layers I used the TransactionSynchronizationRegistry.

                                   

                                  Hope this can be helpful to you !