2

On occasion our MySQL 5.6 server loses some user privileges when restarted. The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running. After a restart of MySQL, the user record still exists but the privileges are missing. In phpMyAdmin User Accounts, the user appears with global privileges of "Usage". The expected SELECT permission is missing.

Users and privileges are created using:

CREATE USER 'userName'@'hostName' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'userName'@'hostName';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `dbName` . * TO 'userName'@'hostName';

What could be causing the loss of privileges? Is there something else that should be executed in addition to these commands to force MySQL to persist the permissions?

2

This is a strange behaviour! Once user is created you can make sure of that by reviewing the grants for it.

show grants for 'username'@'hostname';

Privileges are persistent, you don't need any additional step to make them stay.

Check if my.cnf has something like --init-file or --skip-grant-tables. Also, how mysql is started? Does your root password get reset as well? Are you sure you're connected to the right mysql?

  • The root password is not reset and most of the other users are also unaffected. To the best of my knowledge I am connecting to the correct server. – Corin Nov 17 '15 at 15:06
  • Can you create a user > check show grants for it > Connect from another prompt > do show grants again > restart mysql > verify if issue repeats? > Do you see anything in error log? Can you repeat and provide steps? Does this happen if you do it outside phpmyadmin and using say mysql commandline client? I'd post bug report in this case!!! – mysql_user Nov 17 '15 at 15:26
  • Nothing in the my.cnf looks like the init-file or skip-grant-tables options. We're starting mysql using mysqld_safe. – Corin Nov 17 '15 at 17:26
  • I'll try to remember to do something with show grants before we restart the service next time. So far this only occurs sometimes on the production server and never in test environments. :( – Corin Nov 17 '15 at 17:27
  • This is strange. Have you tried above steps ? Is this happening only when you create user via phpadmin ? Can you please create user via mysqlcli and restart mysqld and share results – Ankit Kapoor Jun 25 '17 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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