convert password hash?

[bccamp]

I'm the administrator for a website that began with a pre mysql 4.1. The passwords for the users are stored in 16 bit format. The mysql has been upgraded to a version 5, but the passwords in the table remained in a 16 bit format, instead of being converted to the 41 bit format that is now standard from what I've read. My question is can the 16 bit hash be converted to the 41 bit hash without each user loging in and resetting their password?

I want to move the site to another host, but when I sync the database tables to my local testing machine for transfer, the passwords are no longer recognized. Is there a way to maintain the 16bit hash when upgrading? setting the password field size to 16?

If anyone know the answer to this one, I can stop sweating.

 

[hvass]

My understanding is no - there is no way to read the password back into plain text.

You can however get v5 to recognize old passwords with --old-password option old-passwords in my.cnf and either not bother to get them to change passwords but everything then stays as 16bit or get the users to create a new password that will created as a 41bit

http://dev.mysql.com/doc/refman/5.0/en/old-client.html

 

[ingresman]

or you could change them on the fly e.g
alter the table to have an INTEGER column called password_version and a column called password41. Set password_version to 16 initialy.
When the user logs, check password_version to see if it's equal to 16, if it is , authenticate the user in the normal way. If he does authenticate, hash the password into the new 41 bit format (as you know it's valid) and store it in password41 and also update password_version to 41. you might want to clear down the 16 bit password value.
So next time he loggs in his password_version will be set to 41 so check against password41 for the hash.
I've never done anything like this so you might have to have a look at the link hvass has posted to understand the ins-and-out.
A bit fiddly but often these things are.
Hope this helps !

 

[ingresman]

Shuld have read the link first.
If the password_version is 16 then use old_password() to get the hash and when you upgrade them to 41 use password().
e.g.

mysql> select password("fred"),old_password("fred");
+-------------------------------------------+----------------------+
| password("fred")                          | old_password("fred") |
+-------------------------------------------+----------------------+
| *6C69D17939B2C1D04E17A96F9B29B284832979B7 | 0569ef75321b8fed     |
+-------------------------------------------+----------------------+
1 row in set (0.00 sec)

mysql>

 

[bccamp]

You guys are great. Problem solved "on the fly." Here's the final code:

$query="SELECT num, user, password FROM volunteers WHERE user = '$_POST[username]' AND password = old_password('$_POST[password]')" or die(mysql_error());
$result=mysql_query($query) or die(mysql_error());
$num=mysql_num_rows($result);
if ($num==1){
	$query="UPDATE volunteers SET password41= password('$_POST[password]'), password=NULL WHERE user = '$_POST[username]' AND password = old_password('$_POST[password]')" or die("cannot insert");
	$result=mysql_query($query) or die(mysql_error());
}
if ($num==NULL){
	$query="SELECT num, user, password41 FROM volunteers WHERE user = '$_POST[username]' AND password41 = password('$_POST[password]')" or die(mysql_error());	
	$result=mysql_query($query) or die(mysql_error());
	$num=mysql_num_rows($result);
}
while ($row=mysql_fetch_array($result)){
	$id=$row['num'];
}
mysql_close();

I just hope 'old_password' is not phased out any time soon b/c many of these people only login occasionally. Thanks again guys.

 

[bccamp]

Ok, spoke too soon. My code works great on my testing server, but saves the password in 'password41' in the same 16bit format that 'password' was saved in. Is this hardwired into the mysql version (5.0.67) or php (5.2.6)? Can I force a 41bit save in the code? In the end, I guess if I can still read the old_password, everything is ok, but I would like everything to be up to date.

Ideas?

 

[ingresman]

sounds odd, don't think php will be bothered, might be worth a google for the mysql though.
What versions of mysql and php did you test on ?

 

[hvass]

Good idea ingresman
But hang on I think you might be making this a bit complicated I dont think you even need old-passwords now.
Instead make sure you have not specified old-passwords in the my.cnf

Even though there are old passwords in the mysql.user table they will still be able to log on with their old passwords.
IF you use grant statement rather than update the table directly it should overwrite the password with the 41 bit version.

So if user is userman and they log in successfully you would just go

grant all on mydb.* to usereman@localhost identified by 'newpassword';

and newpassword should end up encrypted 41

 

[bccamp]

Let me make sure we're talking about the same thing. The 'users' in the table only have access to their information within the table, not the entire DB. I'm not talking about the mysql user tables, only 1 table in the db that has passwords stored by individual users within the table 'volunteers'. Will the 'GRANT' function work within the one table without giving them access to the entire table or database? The way I have the table set up is the mysql_connect function uses a username and password I set up and it has rights to update, delete, add to the table, but the individual people with information within the table have no rights to do so. Those are the passwords I'm trying to update.

Forgive me if I've been redundant in my explanation, but sometimes I've found I'm not very clear in getting my thoughts across clearly in this format. I didn't know the GRANT function dealt with data within the tables, only the mysql user table.

 

[hvass]

grant is the mechanism for setting the various values in all the permissions tables

if you are using the mysql permissions system to control access to the database then this is what you should use

if you only wanted users to be able to select from one table (thetable) and not insert update drop or have any other permissions then you would go

grant select on mydb.thetable to username@localhost identified by 'newpassword'

This would update the mysql.user table and also the mysql.db table etc.. you can go much further to control particular columns within the table, the machine they connect from etc.. worth a read of the manual to try and get your head around the permissions system ...

 

[ingresman]

If you can already write to the volunteers table I don't think you need to go any where near the permissions system. you are implemeting the secutity at this level yourself.
Also I don't think you need to make any changes to my.cnf as both password() and old_password() are avaiable.
If you are trying to change the actual mysql users (and I belive you are not) then that requires eomw thought.
Take comfort from it working in test and go from there.