-
1. Re: How to create database connections per user database account ?
gjeudy Sep 3, 2008 8:58 PM (in response to jorg1)Hi Jorgen,
I don't think it's an anti-pattern, this is a legitimate requirement that many of us had to implement. I guess there is several ways of doing this though I haven't seen one specifically tailored for Seam yet.
I used JBossJCAPooling ByContainer PoolBySubject datasource setup.
Check out this link to get you started : http://wiki.jboss.org/wiki/JBossJCAPooling
It is a non Seam solution and only requires you to propagate the web subject using JBoss JAAS implementation. I call WebAuthentication JBoss class to perform programmatic login in JBoss JAAS world inside my Seam authentication method. See:
http://wiki.jboss.org/wiki/WebAuthentication
That solution would probably deserve a WIKI page since it's a little lengthy to write here. Let me know if you are interested in getting more details.
-
2. Re: How to create database connections per user database account ?
nickarls Sep 3, 2008 9:37 PM (in response to jorg1)Any information would be appreciated, I have the feeling that sooner or later one of our customers will demand this. They have a strong culture of security on the database level.
I wonder if it would be possible to do in-seam by having some sort of config-switch in the SMPC that would use the Identity for automagically creating/switching to a user-specific pool? Of course, there would probably be cacheing drawbacks etc.
-
3. Re: How to create database connections per user database account ?
gjeudy Sep 4, 2008 3:26 PM (in response to jorg1)Nick,
I actually posted my solution in a JIRA.
https://jira.jboss.org/jira/browse/JBSEAM-729If you look at the solution it retrieves db user/pass from a properties file but you could retrieve this info from other places, you would just need to implement your own JAAS login module or use a different JBoss built-in login module.
-
4. Re: How to create database connections per user database account ?
jorg1 Sep 5, 2008 11:54 AM (in response to jorg1)Guillaume,
thanks for your reply, it looks very promising, as I'm actually going to deploy on a JBoss server.However, it's doesn't stand crystal clear for me, if this solution solves the problem: will there be a 1:1 relation between application users and database accounts, allowing auditing, granting of individual privileges etc at db level ? If so, sure, I would be grateful for more details.
-
5. Re: How to create database connections per user database account ?
gjeudy Sep 5, 2008 2:18 PM (in response to jorg1)Jorgen, please see the link to the JIRA link I posted earlier, the solution is detailed there.
There will be a 1:1 relation between app users and database accounts. The username/passwords don't necessarily have to match as you have some leeway where you can add custom db user/pass lookup inside your Seam authenticate method.
Both app and db accounts would have to be created separately. Your DB setup might enable single sign on login with Windows domain integration if thats the case you may have to customize the solution I have a little bit.
-
6. Re: How to create database connections per user database account ?
jorg1 Oct 3, 2008 12:16 PM (in response to jorg1)Guillaume, sorry for not returning before now, but thanks for the solution. I've implemented it now and it works very well.
However I would like to get rid of the usernames/paswords in the property file of
org.jboss.security.auth.spi.UsersLoginModule
.The requirement is: let the user log in using any username/password, and then catch the exception if the credentials are not sufficient at db level.
So I have tried to plug in other kinds of LoginModules and also to subclass UsersLoginModule so its login method allways returns true. But the connections in CallerIdentityLoginModule seams only to be established calling org.jboss.web.tomcat.security.login.WebAuthentication login method when org.jboss.security.auth.spi.UsersLoginModule is configuered with a property file holding the actual used username/password. I can't find any documentation of this in JBoss' docs. Any suggestions are appreceated.
-
7. Re: How to create database connections per user database account ?
gjeudy Oct 3, 2008 2:20 PM (in response to jorg1)You should be able to interchange UsersLoginModule with any other JAAS login module of your choosing. org.jboss.web.tomcat.security.login.WebAuthentication uses whatever security-domain you have configured in jboss-web.xml. That security-domain should have its corresponding entry in login-config.xml. You are free to swap it with a different login module.
More on JBoss Login modules. If none of the provided login modules fit your needs you can always implement your own.
Lastly, and this is really optional; instead of having to manually change the login-config.xml in your jboss install you can make the login-config.xml for your project a deployable artifact. See how this can be achieved here.
-
8. Re: How to create database connections per user database account ?
gjeudy Oct 3, 2008 2:42 PM (in response to jorg1)Jorgen,
Though I have never used it personnally you may want to try ClientLoginModule, I think its designed to propagate the user credentials provided by the client app, so that might be just what you need.
-
9. Re: How to create database connections per user database account ?
jorg1 Oct 6, 2008 2:46 PM (in response to jorg1)I have now tried to use ClientLoginModule. It seems to work well. Guillaume, thanks for your thorough and prompt help.