When setting up a database for a new cloud server we were told it couldn’t be accessed by Navicat etc, so we were lumped with phpMyAdmin, and that we couldn’t have seperate users, everything had to be in root.
So we worked with the root:password they gave us, problem being that we discovered that we could actually access the database remotely, without needing a password – i.e. potential security nightmare – turns out the password had only been set for [email protected]
The following is more as reference for myself than anything:
/** When logged in to mysql in terminal as root user **/
Change Root passwords
update user set password=PASSWORD("password") where User='root';
Add a user
INSERT INTO user (Host,User,Password) VALUES('%',username,PASSWORD(password));
Give User Db Priveleges
INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','database','username','Y','Y','Y','Y','Y','N');
And now I have a protected database and separate users setup – problem solved.