Setting a user password in mysql (Why do I get 'Access denied')

Databases
InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)

Setting a user password in mysql (Why do I get 'Access denied')

This article has not been rated yet. After reading, feel free to leave comments and rate it.

Question:

I updated a user's password in the mysql system table mysql.user and the user gets an error message 'Access denied'. What am I doing wrong?

Answer:

Did you update the password field directly as shown in the example below? That won't work. The password field has to be updated using the password() function which encrypts the string.

Don't forget to reload with mysqladmin afterwards to make the authentication take effect.

/* switching to system db */
use mysql;

/* trying to change all passwords to 'default': */

/* this does not work */
update user set Password = 'default';

/* this is how it is done */
update user set Password = PASSWORD('default');
flush privileges;

/* from the command line: */
mysqladmin -uroot -pdefault reload

Send a message to
Subject

Comments:

NEW: Optional: Register Login Email address (not necessary): Rate as	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option, or post under a registered account.

	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material. Add a picture:


	Search: