-
1. Re: Associate user with database connection from pool
jesper.pedersen Nov 13, 2014 8:01 AM (in response to nickarls)<connection-listener> + org.jboss.jca.adapters.jdbc.spi.listener.ConnectionListener
-
2. Re: Associate user with database connection from pool
nickarls Nov 13, 2014 8:12 AM (in response to jesper.pedersen)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 Nov 13, 2014 8:21 AM (in response to nickarls)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 Nov 18, 2014 4:32 PM (in response to jesper.pedersen)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 Nov 19, 2014 11:23 AM (in response to cchebert)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 Nov 19, 2014 11:28 AM (in response to jesper.pedersen)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 Nov 19, 2014 11:32 AM (in response to cchebert)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 Nov 19, 2014 11:44 AM (in response to jesper.pedersen)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 Nov 26, 2014 1:52 PM (in response to jesper.pedersen)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 Dec 1, 2014 9:00 AM (in response to cchebert)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 Dec 1, 2014 11:24 AM (in response to jesper.pedersen)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 Oct 28, 2015 9:16 AM (in response to cchebert)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 Dec 2, 2015 10:34 AM (in response to nickarls)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 !