1

In MySQL 5.6.27, user 'bmcrae' has create user privileges and is able to create a new user. However, when trying to change another user's password:

 SQL: SET PASSWORD FOR 'newuser' = PASSWORD('***************');

I am getting an "access denied" error:

Access denied for user 'bmcrae'@'%' to database 'mysql'

I have re-read the relevant MySQL documentation and not found anyone else having such issues. Any advice?

Improve this question

1 Answer 1

Reset to default
1

According to MySQL documentation, in order to change a password of a user, bmcrae must have UPDATE privilege for the mysql database.

To check the privileges of the user bmcrae, run the following command:

show grants for 'bmcrae'@'hostname';

OR

Show grants for bmcrae;

And check whether, the user bmcrae has UPDATE privilege on mysql database.

If the bmcrae doesn't have that privileges, grant update privilege to bmcrae user.

Login as a root user and run the following statement to grant update statement to bmcrae user.

grant update on mysql.* to 'bmcrae'@'hostname';

Warning: If you grant update privileges on mysql database, the bmcrae user can update all tables of mysql database.

Improve this answer
1
  • Indeed; after looking further I found that requirement as well. (I wish it were more clear in the CREATE_USER privilege documentation.) I was concerned about giving such broad privileges, and even tried limiting to the Password column, but that is insufficient: GRANT UPDATE (Password) ON mysql.user TO 'bmcrae'; What was also insufficient was to limit to the user table: GRANT UPDATE ON mysql.user TO 'bmcrae'; So, I guess I will need to allow UPDATE privileges to the mysql database. Thanks for the speedy & accurate reply.
    – user2795934
    Commented May 25, 2016 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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