Passwords in Mysql

KeithWor

My problem Should be simple but it won't work as I thought. I'm using PHP 4, Mysql, and Apache. My users need to be able to change their MYsql Password at the same time they change their application password . All Appears to perform normaly. both databases are updated and encrypted. However the user can no longer connect to Mysql. Below is the suspect code any Ideas would be welcomed.
thanks , Keith

If ($t_pswd==$t_pswd2){
@("UPDATE users set password=\"$t_pswd\" where userid=\"$userid2\"") ;
$tmp_t_hostname = "localhost"; //
$tmp_t_username = $username; //
$tmp_t_password = $password; //
$tmp_t_database = "mysql"; //
$tmp_o_link = mysql_connect($tmp_t_hostname,$tmp_t_username,$tmp_t_password)
or die("Sorry, Could Not Connect Database at this time");
mysql_select_db($tmp_t_database)
or die("Database does not exist or is invalid");

	@mysql_query("UPDATE user set password=PASSWORD('$t_pswd') where user=\"$username2\"") ;
Echo "Password Changed !!!";

}else{
Echo "Change Failed";
}

mrmufin

This is happening because MySQL reads the privileges once at startup of the MySQL daemon and stores that information in memory. Hence, when changes are made to the mysql.user table, they are not immediately recognized.

Probably the easiest way to get around this is to connect to the database as 'root' when changing MySQL passwords and use GRANT and REVOKE statements (rather than UPDATE) when changing MySQL user passwords. This (at least according to the MySQL manual) will allow the changes to become effective immediately.

Then re-send the header, and you should be able to connect to the MySQL server upon refresh with the username and new password.

Alternatively, you should be able to run:

mysqladmin -u root -proot password reload

inside a system() or exec() call and acheive the same effect.

Either way, you must close the MySQL connection and re-connect to the server using the new password.

But the question that just begs to be asked is, why are you using multiple user names and passwords to start with...? I've always found it easier to use a single database user name and password (with strictly limited permissions) for an entire application, and then create a user table which the application uses, to avoid this type of scenario...

thom

You just have to run the following as a root user to refresh the user tables:

flush privileges;