Allow all remote connections, MySQL

Question

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.

Answer 1

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”.)

Answer 2

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

[mysqld]
skip-grant-tables

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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