Allow all remote connections, MySQL

I had been using SQL Server and am now using MySQL for a project. With SQL Server, our developers can connect to the remote database on their local machines if they know the host, username, password. With MySQL, though, to give a developer access from their local machines, I have been having to log in to MySQL and execute:

GRANT ALL ON *.* to user@address IDENTIFIED BY 'password'; 
flush privileges;

Where address is the IP address of the developer's machine. Of course, if they change networks, I have to execute it again. Is there a way to allow all remote connections like I have experienced with SQL Server, or is this a bad idea for some reason? We have username and password still.. I'm obviously a little confused.

Also: this is a development database and is only accessible from our internal network. I understand why it is a bad idea to give everyone access to a production database.

As pointed out by Ryan above, the command you need is

GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

However, note that the documentation indicates that in order for this to work, another user account from localhost must be created for the same user; otherwise, the anonymous account created automatically by mysql_install_db takes precedence because it has a more specific host column.

In other words; in order for user user to be able to connect from any server; 2 accounts need to be created as follows:

GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'password'; 
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

Read the full documentation here.

And here's the relevant piece for reference:

> After connecting to the server as root, you can add new accounts. The > following statements use GRANT to set up four new accounts:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

> The accounts created by these statements have the following > properties: > > Two of the accounts have a user name of monty and a password of > some_pass. Both accounts are superuser accounts with full privileges > to do anything. The 'monty'@'localhost' account can be used only when > connecting from the local host. The 'monty'@'%' account uses the '%' > wildcard for the host part, so it can be used to connect from any > host. > > It is necessary to have both accounts for monty to be able to connect > from anywhere as monty. Without the localhost account, the > anonymous-user account for localhost that is created by > mysql_install_db would take precedence when monty connects from the > local host. As a result, monty would be treated as an anonymous user. > The reason for this is that the anonymous-user account has a more > specific Host column value than the 'monty'@'%' account and thus comes > earlier in the user table sort order. (user table sorting is discussed > in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

You can disable all security by editing /etc/my.cnf:

[mysqld]
skip-grant-tables

GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'password'; 

Will allow a specific user to log on from anywhere.

It's bad because it removes some security control, i.e. if an account is compromised.

> Install and setup mysql to connect from anywhere remotely DOES NOT > WORK WITH mysql_secure_installation ! > (https://dev.mysql.com/doc/refman/5.5/en/mysql-secure-installation.html)

#On Ubuntu, Install mysql using:

sudo apt-get install mysql-server

#Have just the below in /etc/mysql/my.cnf

[mysqld]
#### Unix socket settings (making localhost work)
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock

#### TCP Socket settings (making all remote logins work)
port            = 3306
bind-address = 0.0.0.0

#Login into DB from server using mysql -u root -p

#Create DB user using the below statement

grant all privileges on *.* to ‘username’@‘%’ identified by ‘password’;

#Open firewall: sudo ufw allow 3306

#Restart mysql

sudo service mysql restart

Also you need to disable below line in configuration file: bind-address = 127.0.0.1

mysql> CREATE USER 'monty'@'192.168.%.%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'192.168.%.%'

Mabey you only need:

Step one:

grant all privileges on *.* to 'user'@'IP' identified by 'password';

or

grant all privileges on *.* to 'user'@'%' identified by 'password';

Step two:

sudo ufw allow 3306

Step three:

sudo service mysql restart