Understanding tomcat-jdbc pool behavior when password changed

Question

In following code ( changed based on example code given in tomcat-jdbc site ), I first create the connection pool using correct username and password. After sometime I change the username and password to wrong one but still connection success. I have set the testOnBorrow to true which should test the connection when it is issued. Can someone explain why the code does not check on password when it issue a connection ?

My code

PoolProperties p = new PoolProperties();
    p.setUrl("myurl");
    p.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    p.setUsername("test");
    p.setPassword("bsc");
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);
    p.setValidationQuery("SELECT 1");
    p.setTestOnReturn(true);
    p.setValidationInterval(30000);
    p.setTimeBetweenEvictionRunsMillis(30000);
    p.setMaxActive(100);
    p.setInitialSize(10);
    p.setMaxWait(1000);
    p.setRemoveAbandonedTimeout(60);
    p.setMinEvictableIdleTimeMillis(30000);
    p.setMinIdle(10);
    p.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    p.setJdbcInterceptors(
            "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" +
                    "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);
    datasource.getPoolProperties().setUsername("Correct");
    datasource.getPoolProperties().setPassword("Correct");

    for (int i = 0; i < 1000; i++) {

        if (i == 6) {
            System.out.println("Updating wrong user.");
            datasource.getPoolProperties().setUsername("Wrong");
            datasource.getPoolProperties().setPassword("Wrong");
        }
        Connection con = null;
        try {
            System.out.println("Creating connection - " + i);
            con = datasource.getConnection();
            System.out.println("Done. connection status is open " + con.isClosed());
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(select);
            int cnt = 1;
            while (rs.next()) {
                System.out.println((cnt++) + ". Host:" + rs.getString(1));
            }
            rs.close();
            st.close();
            System.out.println("Done - \n");
        } finally {
            if (con != null) try {
                con.close();
            } catch (Exception ignore) {
            }
        }

The connections are opened and cached in a pool. The connections have already been opened, changing credentials will not have any impact on the opened connections, only on the new connections (if opened as required). If you want all the connections to use new credentials you need to shutdown the pool and reinitialize it. — Ironluca, Aug 31 '20 at 06:49
When tomcat server is starting up. it uses connection properties to connect to DB and set connection pool. Changing credentials after that won't have any impact at all. For verification, you can check debug logs in tomcat-jdbc jar when it tries to authenticate.. — SauriBabu, Sep 07 '20 at 02:51

score 0 · Answer 1 · answered Nov 25 '22 at 15:27

According to Oracle developers credentials are used only to authenticate the establishing of the connection. Once the connection is established the individual packets that flow through are not checked for the validity of the credentials. This implementation can be argued from both ends but Oracle disagree's that they should validate already established connections. There are some good reasons for it but again it is arguable. So as @Ironluca pointed in the comment your pooled open connections will not be validated again.

If you have requirement to invalidate the sessions it is possible to kill those connections aka sessions on the database side. That will need DBA access - can't remember the exact sql but I believe it might be v$session table.

Understanding tomcat-jdbc pool behavior when password changed

1 Answers1