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 root@localhost.
The following is more as reference for myself than anything:
/** When logged in to mysql in terminal as root user **/
Change Root passwords
use mysql;
update user set password=PASSWORD("password") where User='root';
Add a user
INSERT INTO user (Host,User,Password) VALUES('%',username,PASSWORD(password));
flush privileges;
Give User Db Priveleges
use mysql;
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');
flush privileges;
And now I have a protected database and separate users setup – problem solved.