Validating user/password for database connection

[Jaikiran Pai]

Hi,
We have a J2EE application deployed on JBoss-3.2.3. We have deployed a datasource for the same. We have a requirement that given a username/password, we have to validate that this is a valid database user/password. Currently, the way we have implemented this is:
- Lookup the DataSource object
- Invoke the getConnection(user,password) method on the DataSource object
- If SQLException is thrown then display error message. Else, validation succeeds.

Issues that the current implementation has:
- getConnection is a heavy operation.
- SQLException is caught, and irrespective of whether the exception is because of invalid user/password(exception might have occured for some other reason), its assumed that user/password is invalid.

Is there any light weight operation through which, given a username and password, it can be validated that this user/password is valid database user/password?

[Paul Sturrock]

Not really. We had a simmilar requirement and that is pretty much the way we did it.

[Jaikiran Pai]

Thanks Paul, for the quick reply.

[Roger Chung-Wee]

Are you using connection pooling? In such a case, the getConnection method returns a logical connection from the pool, so this is not a heavy operation. The main point of connection pooling is to move such heavy operations to server startup or deployment, at which point the JDBC connections are created and connected to the DB.

[Jaikiran Pai]

Are you using connection pooling?

Yes we are using connection pooling. Following the configuration:

<datasources>
   <local-tx-datasource>
      <jndi-name>MyDS</jndi-name>
      <connection-url>jdbc:teradata://10.147.12.4/DATABASE=myDB</connection-url>
      <driver-class>com.teradata.ioc.jdbc.EnhancedDriver</driver-class>
      <user-name>test</user-name>
      <password>test</password>
      <min-pool-size>2</min-pool-size>
      <max-pool-size>25</max-pool-size>
      <idle-timeout-minutes>10</idle-timeout-minutes>
   </local-tx-datasource>
</datasources>

In such a case, the getConnection method returns a logical connection from the pool, so this is not a heavy operation

Just to test this out, i tried out invoking the getConnection method passing it the user name and password mentioned in the datasource file.
The getConnection method successfully returned in just 16 milli sec. So this looks fine.
But, if i specify an invalid user/password, the getConnection method returned with the SQLException in 12 seconds. I believe, when the getConnection is invoked with some user/password other than the one specified in the datasource(connection pool) then the app server bypasses the connection pool and hits the database server to get the connection, which makes it a heavy operation.

Is this understanding right?

[Roger Chung-Wee]

I am not familiar with JBoss, so I can't comment on what happens if you supply a username/password to the getConnection method. My suspicion is that you would still get a logical connection if the username/password combination matches.

On physical connections ... I won't have thought that this is a good thing to do. In almost every case, it makes sense to get a logical connection from the pool and let the server instance manage the connection for the connection pool, enable connection pool features, and maintain the quality of connections provided to applications. When you use a physical connection, you lose all of the connection management benefits that the server offers, such as error handling and statement caching.