Working With MySQL User Privileges

Allow non localhost db user to connect

  1. allow binding from other hosts other than localhost

     vi /etc/mysql/my.cnf
   remove bind-address = 127.0.0.1

  2. check and see the current privileges on hosts

     mysql> use mysql;
 mysql> select * from user;
   see if there is host = localhost and user = root which will not allow non localhost to access

  3. grant the user with % (all hosts)

     mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '[password same as root@local e.g. 5678
 # e.g. GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '5678';

 mysql> (optional) delete from user where host = 'localhost' and user = 'root';
 mysql> flush privileges;

Create a new user

  1. create a user

     mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
 mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

  2. grant all privileges

     mysql> GRANT ALL ON *.* TO 'myuser'@'localhost';
 mysql> GRANT ALL ON *.* TO 'myuser'@'%';

or grant just enough

    mysql> GRANT DELETE,INSERT,SELECT,UPDATE on mydatabase.* TO 'myuser'@'localhost';       
    mysql> flush privileges;

  1. to see the privileges

     mysql> SHOW GRANTS FOR mysql;