Changing Database Passwords Via Jdbc
JDBC handles standard sql statements well, but some DB intertactions aren’t covered by standard SQL. Changing a user’s password is such a case, with each vendor offering their own way of doing it. Here’s a few recipes for Databases I have done this for.
Oracle
Fairly straightforward - execute the sql statement
ALTER user IDENTIFIED BY password
The previous password needn’t be supplied. The only gotcha here is that neither argument should be quoted. If you try doing this using a java.sql.Prepared Statement?, it will quote the arguments, and Oracle will complain that it can’t identify the user. So, use an old-fashioned java.sql.Statement and create the sql string by hand - it’s not a very big one, after all!
MS SQL Server
This has a stored proc shipped with it, with signature
sp_password( oldpwd , newpwd [, userid] )
Use a java.sql.CallableStatement to invoke it. If userid is not supplied, it will use the user id that the current Connection is logged on as.
Curiously, this proc will refuse to operate within a transaction. This can be a headache under J2EE environments, where the appserver supplies ready-rolled connections, usually embedded within a broader JTA transaction. To switch off transactions on a particular method call, you will need to edit the deployment descriptor, and set the trans-attribute tag inside the assembly-descriptor to ‘Not Supported’ for that bean, method, etc. allowing the app server to hand you a non-transactional Connection.
Tested on WebSphere 5.0 using their default MS Sql drivers against Sql Server 2000.