MySQL Permissions Necessary To Reset Password

MySQLpermissionsSecurityuser-defined-type

On my team there is a desire for some users to periodically update their MySQL password.

Our MySQL server is shared between a few projects (and certain users are assigned to certain projects). If I'm correct, changing your password requires access to the mysql.user table (which would, in turn allow the user to modify the password of any other user on the server.)

If this is all correct, is there any way to secure the database in a way that a user can update their password, but not other users?

Best Answer

A user should be able to change his/her own password strictly using SET PASSWORD.

For a connected user to set the new password to mynewpass, just run the following:

mysql> SET PASSWORD = PASSWORD('mynewpass');

According to the MySQL Documentation on SET PASSWORD, if the server is a read-only enabled server, you need SUPER privilege to do this. Otherwise, you can do this any time. There is no need for another user to set someone else's password. If you need a super user to set it you can still use SET PASSWORD.

To set the password of 'someuser'@'10.1.2.30' to hisnewpass, run this:

mysql> SET PASSWORD FOR 'someuser'@'10.1.2.30' = PASSWORD('hisnewpass');

According to the MySQL Documentation on SET PASSWORD, this is the equivalent of:

UPDATE mysql.user SET Password=PASSWORD('hisnewpass')
WHERE User='someuser' AND Host='10.1.2.30';
FLUSH PRIVILEGES;

Using SET PASSWORD does not warrant manipulating mysql.user.

Related Question