How do I grant access to mysql server from all IP Addresses

baronobeefdip · 11-15-2014, 11:51 AM

I am hosting a mysql database server that I want accessible from every computer on the network (meaning from every ip address that it receives a request from). Google searches and references manuals don't show how to do this and I have no other choice other than to ask someone on here. I was hoping that there would be a command or a string of code to insert into the my.cnf file in order to go about doing this, if not then how do I tell mysql to grant access to the server from every ip address, every user, and every database. I have limited knowledge of mysql when it comes to network configurations. I need an answer pronto.

astrogeek · 11-15-2014, 12:37 PM

Make sure the server is not started with --skip-networking.

Then the GRANTs for each accessible database must include a suitable condition, something like '%'@'%'.

See MySQL manual for GRANT usage.

baronobeefdip · 11-17-2014, 07:55 PM

here is the string that I used

Code:

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

I just tried to connect to it via the command line with this command

Code:

#mysql -u root@192.168.1.81 -p

Then I entered the password and I keep getting this message

Code:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

astrogeek · 11-17-2014, 08:32 PM

You did not specify use of password in the GRANT, so trying to use one will produce an error when you get to that point (but that is not the error you are seeing).

The login shown is trying to connect to the MySQL server on the local machine (with the keyboard attached) not the remote machine. Apparently the local machine does not have a MySQL server running resulting in the error that you posted.

To connect to a remote MySQL instance you must specify the target host with the -h option, and do not append the address to the user name unless it was specified that way in the GRANT.

It would be helpful if you would be explicit about which machine is which so that we don't make incorrect assumptions. Also, if you could post the my.cnf from the target machine.

astrogeek · 11-17-2014, 09:20 PM

I am a heavy MySQL user, but have never tried to run it wide open to the network as you want to do, so I just set up a test instance to test that with.

(I am running MariaDB on Slackware, so there may be differences with Debian, probably not with MySQL).

I found that it will not allow me to specify a wide open GRANT - it accepts it but it will not honor it.

It will work with EITHER a user name, OR an IP address, either of which may include wildcards, but must include at least one non-wildcard character or single-character wildcard.

So for your case I would suggest something like...

Code:

GRANT ALL ON *.* TO 'user'@'_%';

OR

GRANT ALL ON *.* TO '_%'@'xxx.yyy.zzz.000'

...which work on my test instance.

But...

Code:

GRANT ALL ON *.* TO '_%'@'_%'

... does not work...

You will likely still need a password to satisfy the local client, or possibly add a local GRANT to cover the network connection.

Hope this helps.

(All of the above said, I whould highly recommend that you restrict the connections by password, address and database or it will surely come back to haunt you!)

Habitual · 11-18-2014, 08:27 AM

Quote:

Originally Posted by baronobeefdip

how do I tell mysql to grant access to the server from every ip address, every user, and every database.

You need to clarify this "access" and for what purpose. I have to ask "why"?
User access to databases? Do you know what mistakes can happen? On purpose, or by accident? This is a bad idea, IMO.

Quote:

Originally Posted by baronobeefdip

here is the string that I used

Code:

mysql> GRANT ALL PRIVILEGES ON db_name.* TO '%'@'%';

That's poor form even with a missing password option. Do you really want your dbs open to the nefarious bots/scripts/and other assorted wickedness that is out there?

Good Security deems that you should create mysql "users" from known hosts and stay away from wide open grants such as the above.

Code:

GRANT ALL PRIVILEGES ON db_name.* TO 'fred'@'freds_IP' identified by 'freds_password'; flush privileges; exit;

If fred connects from 2 IPs, then fred get two grants, not "any ip".

IMO, you are courting disaster doing such wide open grants, unless it is your intent to 'trash' mysql for some 'reason'?

What if fred trashed barney's database accidentally?
What if barney's deletes the mysql database?
Again, if this is your intent, then by all means carry on, else RUN FAST AND RUN FAR.

michaelk · 11-18-2014, 09:03 AM

In addition, mysql is probably configured to only listen on localhost. Before you can access it from remote computers you need to change bind-address in my.cnf to your IP address.

bind-address=192.168.1.81

Be sure to restart mysql. If running a firewall you also need to allow mysql service or TCP port 3306. Once you grant access as stated above, using a remote PC should be as easy as:

mysql -u username –h 192.168.1.81 –p