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?

Improve this question
1

1 Answer 1

Reset to default
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?

Improve this answer
5
  • 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, 2015 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, 2015 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, 2015 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, 2015 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
    – simplifiedDB
    Jun 25, 2017 at 20:09

Your Answer

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

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