Setting MySQL User Passwords and Permissions Via Command Line

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.

Leave a Reply

Your email address will not be published. Required fields are marked *