9 Replies Latest reply on Oct 6, 2008 2:46 PM by jorg1

    How to create database connections per user database account ?

    jorg1

      I have an application, with the requirement, that each user should log on using their own oracle database account, i.e. using a seaparate connection with their own username/pasword.


      I'm using the Seam Application Framework generated by seamgen, and I've tried to override
      getPersistenceContext() in EntityHome to first check if there is a new user, and if so,  create a new connection doing something like this:
         


          Session entityManagerDelegate = (Session) entityManager.getDelegate();
          Class.forName(driver); 
          Connection conn = DriverManager.getConnection(url, username, password);
          entityManagerDelegate.reconnect(conn);
      



      However, I got following error:




      java.sql.SQLException: Listener refused the connection with the following error:
      ORA-12519, TNS:no appropriate service handler found.
      





      I haven't been able to find any dokumentation/examples on how to do this in Seam.


      Is there or solution ?


      Or, if it's an antipattern I would very much appreciate an explanation.


      Thanks.

        • 1. Re: How to create database connections per user database account ?
          gjeudy

          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

            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

              Nick,


              I actually posted my solution in a JIRA.
              https://jira.jboss.org/jira/browse/JBSEAM-729


              If 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

                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

                  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

                    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

                      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

                        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

                          I have now tried to use ClientLoginModule. It seems to work well. Guillaume, thanks for your thorough and prompt help.