7 Replies Latest reply on Jan 9, 2013 1:03 PM by Jesper Pedersen

    Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error

    Yaguo Zhou Newbie

      hi:

      When an application can't get a database connection through jca layer due to database password error, it's better to add an extension point in IronJacamar's datasource implementation to allow user's program to "implements". user can get the correct password from another server(our own server which stores the correct password), and then get and return a normal database connection to IronJacamar. This solution's advantage is that we can avoid restarting jboss application server when database password is changed.

        • 1. Re: Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error
          Jesper Pedersen Master

          Sounds to me that you need a security domain that has this functionality.

           

          Then you can do a :flush-idle-connection-in-pool, and all new connections will automatically use the new password.

          • 3. Re: Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error
            Jeff Zhang Newbie

            Yaguo

            it seems JDBC API hasn't listener to know when DB change the password. Some vendor provide API in their JDBC driver, like http://docs.oracle.com/cd/E14072_01/java.112/e10589/dbchgnf.htm.

            Use them and try to evict old connection and get new connection by new password. Do you think it is an approach?

            • 5. Re: Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error
              Jesper Pedersen Master

              It is a matter of separating the connections in the pool. That can only be done through the security domain.

               

              Related is reauthentication of connections, where the credentials are changed on existing connections, but only IronJacamar supports that. However, we don't have a plugin for Oracle yet.

              • 6. Re: Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error
                Yaguo Zhou Newbie

                Appreciate for your reply and sorry for my late response because of my busy work.

                Let me describe  my new feature's scenario more clearly:

                 

                For financial security reason, DBA in our company must change our database's password every 2 or 3 months, but unfortunately, every time after passwods are changed, we have to modify datasource password in java application server(we are using JBoss 5.1) and restart it, meanwhile, we sometimes forgot to change and restart one of our jboss instances, because  we have dozens of web application located on many jboss instance.so what we want is that after DBA changed passwords,  jboss can get the new password when receive a new http request and store the password in memory(in mbean or one property of class).

                 

                so we think about a solution and have finished it already:

                step1. we developed a socket server program which is used to receive command message to save password and query password of databases(socket message format is design by our own and we call the socket server as DBPM server)

                step2. DBA change the password through IBM Tivoli that is extended by our programmer to send password to DBPM server meanwhile, and DBPM server will store it centrally.

                step3. I hack the code org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.java as follows:

                 

                private LocalManagedConnection getLocalManagedConnection(Properties props,Properties copy)

                                       throws JBossResourceException

                   {

                             boolean debug = log.isDebugEnabled();

                       Connection con = null;

                             try

                             {

                                       String url = getConnectionURL();

                                       Driver d = getDriver(url);

                                      

                                       // alreay got the new password

                                       if(this.newPasswordFromDbpm != null){

                                                 copy.setProperty("password", newPasswordFromDbpm);

                                       }

                                      

                                       try{

                                                 con = d.connect(url, copy);

                                       }catch(SQLException e){

                                                 if(debug){

                                                           log.debug("ErrorCode:"+e.getErrorCode() + " " + "SQLState:"+e.getSQLState());

                                                 }

                                                

                                                 // for mysql and db2

                                                 if("28000".equals(e.getSQLState())){

                                   

                                                           if(debug){

                                                                     log.debug("we catch a SQLException(SQLState=28000), so try to get new password from DBPM server, and then reconnect the database");

                                                           }

                                                         

                                                          String dbName = url.split(":")[3].split("/")[1]; // get database name from url

                                                          String dbUserNm = copy.getProperty("user");

                                                          //try to get password!

                                                          String dbpmPassword = DsPasswdGetter.doGetPasswd(dbName, dbUserNm, false);

                                                          if(dbpmPassword != null){

                                                                    if(debug){

                                                                              log.debug("get dbpmPassword ok.. begin to reconnect the database");

                                                                    }

                                                                    copy.setProperty("password", dbpmPassword);

                                                                    this.newPasswordFromDbpm = dbpmPassword;

                                                                    //write it to xml configuration file

                                                                    if(securityDomain != null){

                                                                              Properties prop = new Properties();

                                                                              prop.setProperty(securityDomain, dbpmPassword);

                                                                              if(debug){

                                                                                        log.debug("begin to write dbpmPassword to login-config.xml");

                                                                              }

                                                                              UpdateJdbcPassword.doModifyPasswd(prop);

                                                                    }else{

                                                                              throw new JBossResourceException("securityDomain is null");

                                                                    }

                                                                   

                                                                    //begin to reconnect

                                                                    con = d.connect(url, copy);

                                                          }else{

                                                                    //error to get password from DBPM server

                                                                    throw new JBossResourceException("Can not get password from dbmp server for [dbName:"+dbName+", dbUserNm:"+dbUserNm+"]");

                                                          }

                                                }

                                               

                                                if(con==null)

                                                  throw new JBossResourceException("Wrong driver class for this connection URL");

                                       }

                                      

                                       return new LocalManagedConnection(this, con, props, transactionIsolation, preparedStatementCacheSize);

                             }

                             catch (Throwable e)

                             {

                                 if (con != null)

                                 {

                                    try

                                    {

                                       con.close();

                                    }

                                    catch (Throwable ignored)

                                    {

                                    }

                                 }

                                       throw new JBossResourceException("Could not create connection", e);

                             }

                   }

                step4. now suppose a http request arrived and all existing connected connections in pool are busy, so jca try to establish a new connection , and then flow into my code above.

                step5. my code above will fetch the correct password back and reconnect successfully.

                step6. jca return a correct connection to web application. i am happy to see that web application don't even know jca has helped him to get the correct password.

                step7. so now we don't need to modify and restart jboss instance any more.

                 

                The problem is that my hacked code doesn't merge into upstream trunk , and now it's in my own branch. so i will very happy to *implements* a interface to do things above to avoid hacking the jca source code. many thanks!

                 

                Best Regards,

                Yaguo

                • 7. Re: Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error
                  Jesper Pedersen Master

                  I understand the use-case, but it should be handled by a security domain which always gives the correct password.

                   

                  You can find the security domain types for AS 7 here - https://community.jboss.org/wiki/JBossAS7SecurityDomainModel - or write a custom one - https://community.jboss.org/wiki/JBossAS7SecurityCustomLoginModules.

                   

                  That functionality is also available for JBoss 5.1 - check with the PicketBox people for that.