Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
+1 good explanation... –  Abdul Manaf Jun 6 '12 at 4:54
    
Great! Thank you! –  TFerrell Jun 6 '12 at 13:17
add comment

You can set password for user like this:

 mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

mysql> FLUSH PRIVILEGES;

OR using mysqladmin utility like:

shell> mysqladmin -u user_name -h host_name password "newpwd"

Basically, for a user to change password you need these GRANT options

GRANT SELECT, UPDATE ON *.* TO 'abc'@'%' IDENTIFIED BY PASSWORD 'mypass' WITH GRANT OPTION
GRANT SELECT, UPDATE ON `mysql`.`user` TO 'abc'@'%'

Also, user should be able to change only his own password:

I guess this is not handled in MySQL, infact root user only should give access to other users and he only should change their password if needs to be ..IMHO technically in order to change password you need to access mysql.user table.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.