8 Replies Latest reply on Jan 4, 2002 12:35 AM by guy_rouillier

    Connection Pooling, why do I have to specify username and pa

    bjavaboy

      I am using JBoss 2.4.0 and Sybase as my database

      A new connection pool is successfully created as long as I provide myuser and mypass in jboss.jcml

      If I don't provide username and password jboss hangs when trying to create a new connection pool(I thing JBoss should look into).

      Why do I have to specify user name and password in jboss.jcml for connection pooling. Is it not unsecure?

      Is there any way to create this pool dynamically as and when needed

      How do I handle the situation where I have n different users on a databse. Do I need to create n separate pools, one for every each. There is definelty something which I don't know.

        • 1. Re: Connection Pooling, why do I have to specify username an
          davidjencks

          > I am using JBoss 2.4.0 and Sybase as my database
          >
          > A new connection pool is successfully created as long
          > as I provide <attribute
          &gt; name="JDBCUser">myuser and <attribute
          &gt; name="Password">mypass in jboss.jcml
          >
          > If I don't provide username and password jboss hangs
          > when trying to create a new connection pool(I thing
          > JBoss should look into).
          >
          > Why do I have to specify user name and password in
          > jboss.jcml for connection pooling. Is it not
          > unsecure?
          There is no way to connect to a database without a password. If you think that is insecure,...

          Keep your server computer secured. Hiding the pasword on it mostly serves to produce the illustion of security by obscurity.

          You may be interested in having each user connect to the database with their own credentials. This effectively defeats connection pooling unless the db driver supports reauthentication: I don't know of any that do. This can be done using the jca (jbosscx) framework by writing your own PrincipalMapping class to return a Subject using the supplied username and password.
          >
          > Is there any way to create this pool dynamically as
          > and when needed

          Yes, but it's not that easy to do. You have to create the mbean programatically, configure it, and start it.
          >
          > How do I handle the situation where I have n
          > different users on a databse. Do I need to create n
          > separate pools, one for every each. There is
          > definelty something which I don't know.

          You can either do that, use the suggestion above for forwarding the supplied credentials (set the pooling to be per-subject rather than per-factory), or decouple the selection of what data a user may access from the database login identity. (My suggestions are based mostly on the jboss 3 jca-based code, but it may be possible to adapt them to jboss 2.4)

          IMHO if you have a few classes of user (maybe 3 or 4) but many users, and members of each class need the same data access privileges, it may be plausible to give each class its own db login, and connection pool. If you have hundreds of users, each with different data access privileges, I think it makes more sense to use a privilege scheme encoded in the data. For instance, if a user is only supposed to see their own data, include a user id in each table, and query based on userid + other conditions.

          • 2. Re: Connection Pooling, why do I have to specify username an
            guy_rouillier

            I have a version of the 2.4.x code that properly respects username and password entered with getConnection(username, password). This is the change I was talking about with Toby. Unfortunately, I never submitted it to CVS. If this still isn't working properly in the 3.0 code, I'll definitely submit it.

            • 3. Re: Connection Pooling, why do I have to specify username an
              davidjencks

              It looks to me as if the 3.0 code will correctly handle ds.getConnection(user, password) calls if the pools are set up per-user. I haven't tested this. Currently the principal mapping stuff (used in cmp and other times when the container handles authentication) will only supply one Subject, noone AFAIK has written something that will give you a Subject based on the actual JBoss login. This would be easy if you know what you want, however I'm not sure of the exact requirements and Scott has mentioned moving some of this into a SecurityDomain.

              If you find something with authentication for jca in 3.0 that doesn't work please let me know, or if you need a different PrincipalMapping.

              • 4. Re: Connection Pooling, why do I have to specify username an
                guy_rouillier

                David, I pushed off sleep for another night , and played around with a db connection in 3.0.0alpha. I strongly believe we should respect per-user credentials within a single connection pool (which is a whole other topic - I don't see where 3.0.0 is using pools at all.) As far as I can see, there is no way to set up a connection without a principal mapping - I tried it and keep getting message "Unable to instantiate principal mapping class 'null'". The way I think it should work (and the fix I came up with for 2.4.x) is this: the username and password specified for the mbean is a default. It is used with getConnection(). If the caller uses getConnection(myusername, mypassword), then myusername and mypassword should be used as the credentials. This can all be done in a single pool.

                What do you mean by "pools are set up per user"?

                • 5. Re: Connection Pooling, why do I have to specify username an
                  davidjencks

                  > David, I pushed off sleep for another night , and
                  > played around with a db connection in 3.0.0alpha. I
                  > strongly believe we should respect per-user
                  > credentials within a single connection pool (which is
                  > a whole other topic - I don't see where 3.0.0 is
                  > using pools at all.)
                  They are inside the ConnectionManagers
                  As far as I can see, there is
                  > no way to set up a connection without a principal
                  > mapping - I tried it and keep getting message "Unable
                  > to instantiate principal mapping class 'null'".
                  Correct. There should be no way to set up a connection without a principal mapping - that would mean you had decided not to tell jboss how to handle security and authentication.

                  The
                  > way I think it should work (and the fix I came up
                  > with for 2.4.x) is this: the username and password
                  > specified for the mbean is a default. It is used
                  > with getConnection(). If the caller uses
                  > getConnection(myusername, mypassword), then
                  > myusername and mypassword should be used as the
                  > credentials.

                  AKAIK, this is exactly what happens with jboss 3 and the local transaction jdbc wrapper. (Maybe the xa wrapper too, I didn't look)

                  > This can all be done in a single pool.
                  ???How??? I think you need normally to keep track of which connection has which user/other parameters. This cannot necessarily be obtained back from the connection. To me this means pool per user: I don't know how to distinguish members of a pool.

                  > What do you mean by "pools are set up per user"?


                  Most databases will not let you change the user on a connection, once it has been obtained. Therefore, if you are pooling, you have to be able to find a connection that was created with the correct user/password (and other parameters). A jca resource adapter has to be able to identify an appropriate connection in the ManagedConnectionFactory.matchManagedConnection method: jboss can try to make its job easier by keeping track of which connection has which user, that is what setting per-user does.

                  If you don't specify per-user and get connections for several users, the connection manager will fail because it is assuming it knows what the answer will be from match... and only gives it one choice. Much faster, better concurrency, but it requires you know how your adapter works. Maybe an alternate strategy of supplying all the ManagedConnections would be good to add...

                  I'm rewriting the ConnectionManager code to greatly simplify the pooling, fix some bugs, allow caching connections in ejbs, and get it under LGPL license.
                  So, if you want to make sure it does something in particular, let me know.

                  • 6. Re: Connection Pooling, why do I have to specify username an
                    bjavaboy

                    This is what I think how a connection pool should work, which is some what similar to what guy thinks. I think user name and password should be optional. Container should not try to test database at startup (which it does in private void initializePool() for XADataSourceLoader). If username and password are not provided in conf getConnection() should throw an exception. In this case only getConnection(username, password) will return a successful connection. If username and password are specified in conf it uses this username and passwod when getConnection() is called.

                    For e.g. I create a pool called MyPool which points to MyDatabase and I have not specified username and password in conf.

                    When I make the first call of getConnection("UserA", "PwdA") it adds this connection to the pool.
                    If my second call is getConnection("UserB", "PwdB") it adds another connection to the pool as the user is different.
                    If my third call is getConnection("UserA", "PwdA") it returns me the first connection(one with user UserA) and if it is getConnection("UserB", "PwdB") it returns me the second connection(one with UserB).

                    Hope it makes sense.

                    • 7. Re: Connection Pooling, why do I have to specify username an
                      davidjencks

                      The way I think about pools, your 2 connections need to be in different pools, because the pooler needs to distinguish between them (different users). I think this is arguing over small words however: you should only need one datasource, whatever may lie behind it.

                      In my rewrite I am making it a little easier to set up and configure the "many users" case that I keep calling "many pools" behind one connection factory.

                      By default the (individual user) pools will not try to fill up to their min size until you request a connection, thus telling it what the user/params for the pool are.

                      Perhaps it would be a good idea to be able to specify startup user/passwords for pools, so the min # of connections could be obtained before any user requests.

                      BTW the jca based stuff never tries to test a connection on startup, since it can have no idea how to even get one... the ConnectionFactory is just an Object as far as it is concerned.

                      In general, for jca, in container managed sign-on, the container needs to supply the Subject for the ManagedConnection. Right now the only implemented way for the container to do this is with the "constant" ManyToOnePrincipalMapping, which always supplies the same Subject. We need also ways to simply pass the logged in user into the ManagedConnectionFactory and ConnectionManager, and to map the logged in users to some other set of resource users (kind of like db roles). Anyway, just relying on username and password specified in the conf. is too limiting.

                      • 8. Re: Connection Pooling, why do I have to specify username an
                        guy_rouillier

                        > This can all be done in a single pool.
                        ???How??? I think you need normally to keep track of which connection has which user/other parameters. This cannot necessarily be obtained back from the connection. To me this means pool per user: I don't know how to distinguish members of a pool.

                        I worked all this out in the 2.4 codebase, but haven't had the chance to look through the 3.0 code yet. Basically, the problem is that XAPoolDataSource was doing this:

                        public Connection getConnection() throws java.sql.SQLException {
                        if(!initialized) initialize();
                        return ((XAConnection)pool.getObject()).getConnection();
                        }

                        public Connection getConnection(String user, String password) throws java.sql.SQLException {
                        if(!initialized) initialize();
                        factory.setUser(user);
                        factory.setPassword(password);
                        return ((XAConnection)pool.getObject()).getConnection();
                        }

                        It was always getting generic objects from the pool. Note that the second version is a total loss - it changes the factory userid and password, which should never be done, but then it gets a generic connection object from the pool, which will use the **previous** userid and password!

                        The fix is this:

                        public Connection getConnection() throws java.sql.SQLException {
                        if(!initialized) initialize();
                        return ((XAConnection)pool.getObject(new String[] { factory.getUser(), factory.getPassword() } )).getConnection();
                        }

                        public Connection getConnection(String user, String password) throws java.sql.SQLException {
                        if(!initialized) initialize();
                        return ((XAConnection)pool.getObject(new String[] { user, password } )).getConnection();
                        }

                        Since you are trying to get an object from the pool with specific characteristics, you must identify those characteristics. So instead of using the no-args getObject() method, we use the version that takes arguments. Note also that we don't change the factory userid and password, which should never be changed, as they are the default values supplied in the config files.

                        I'll email you a zip of all the changes I made. We can discuss offline if necessary how to apply this same approach to the new JCA framework.