Description:
We wanted to create a new user with our admin user as follows and got a completely confusing and misleading error message:
shell> mysql --user=dba --password=dbasecret
mysql> GRANT ALL PRIVILEGES ON *.* TO 'service_manager'@'localhost' IDENTIFIED BY 'secret';
ERROR 1045 (28000): Access denied for user 'dba'@'localhost' (using password: YES)
How to repeat:
shell> mysql --user=root
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'dbasecret';
mysql> exit
shell> mysql --user=dba --password=dbasecret
mysql> GRANT ALL PRIVILEGES ON *.* TO 'service_manager'@'localhost' IDENTIFIED BY 'secret';
ERROR 1045 (28000): Access denied for user 'dba'@'localhost' (using password: YES)
dba@localhost [(none)]> exit
shell> mysql --user=root
mysql> GRANT ALL PRIVILEGES ON *.* TO 'service_manager'@'localhost' IDENTIFIED BY 'secret';
this happened with MySQL 5.6.17 and 5.6.22
It is clear to us, that the dba user is lacking the WITH GRANT OPTION privilege which is needed to create a new user.
Suggested fix:
We expect an error message indicating that the WITH GRANT OPTION privilege is missing or at least that the user does not have the rights to create an other user...
