Allow all remote connections, MySQL

Question

I used SQL Server and now use MySQL for the project. With SQL Server, our developers can connect to a remote database on their local computers if they know the host, username, password. However, with MySQL, to give the developer access from their local computers, I had to log into MySQL and execute:

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

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

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

Answer 1

As indicated by Ryan above, you need a team

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

However, note that the documentation indicates that for this you need to create another user account from localhost for the same user; otherwise, an anonymous account created automatically with mysql_install_db takes precedence because it has a more specific host column.

In other words; so that user user can connect from any server; 2 accounts must 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 is the corresponding snippet for reference:

After connecting to the server with administrator rights, you can add new accounts. The following statements use GRANT to create 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 operators have the following Properties:

Two accounts have a monty username and some_pass password. Both accounts are superusers with full privileges to do anything. The "monty" @localhost account can only be used when connecting to the local host. The account 'monty' @ '%' uses the '%' wildcard for the main part, so it can be used to connect from any host.

Both accounts for monty must be able to connect from anywhere as monty . Without a localhost account, the anonymous account for localhost created by mysql_install_db will take precedence when monty connects to the local host. As a result, monty will be treated as an anonymous user. The reason for this is that the anonymous user account has more specific host column value than the "monty" @ '%' account and thus earlier in the sort order of the user table. (Sorting the user table in section 6.2.4 "Access control, step 1: connection verification".)

It seems silly to me if I do not understand this.

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'; 

Allows a specific user to log in from anywhere.

This is bad because it removes some security control, that is, if the account is compromised.

Answer 4

You also need to disable the line below in the configuration file: bind-address = 127.0.0.1

Answer 5

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

Answer 6

Install and configure 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 )

In Ubuntu, install mysql using:

 sudo apt-get install mysql-server 

Enter just something 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 

Logging into the database from the server using

mysql -u root -p

Create a database user using the statement below

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

Open firewall:

 sudo ufw allow 3306 

Restart mysql

 sudo service mysql restart