9

I have a mysql server running on x.x.x.x, and can access it internally no problem (of course). However when attempting to connect externally, ie using mysql workbench, or even from an external server, I get the error message "Host 'bla.bla.bla' is not allowed to connect to this MySQL server".

I have done:

  • GRANT ALL PRIVILEGES ON *.* TO mysql@x.x.x.x IDENTIFIED BY "somepass";
  • And I have opened port 3306 in iptables.

Is there another fundamental security issue I am missing?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jon
  • 115
  • 1
  • 1
  • 4

8 Answers8

14

You need to do

GRANT ALL PRIVILEGES ON *.* TO mysql@'bla.bla.bla' ...

The part after the @ is the host from which the connection is coming, so you have allowed only connections coming from localhost. You need to allow access from each remote host necessary (or all hosts - ... mysql@'%' ... - if applicable).

nobody
  • 19,814
  • 17
  • 56
  • 77
  • 1
    Ahhhhhhhh... thank you! "%" was the fix that was needed. I knew there was a way to grant global access. I am assuming this is not a good idea for a production server though? – Jon May 06 '11 at 18:40
  • 1
    Dont forget to use FLUSH PRIVILEGES; – jonazu May 15 '14 at 12:50
8

To solve this you needed to perform the following commands:

mysql -u root -p
[enter in your password]
CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'aC0MPL3XPa33W0RD';
GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%' WITH GRANT OPTION;
Ahmad
  • 507
  • 1
  • 11
  • 22
4

I had the exactly similar situation.my MYSQL is installed on a centOS. The path to Nirvana is as below.

  1. bind-address: DID NOT WORK
  2. grant permission: DID NOT WORK
  3. Iptables when turned off: DID work.

SOLUTION:I went fishing into the iptables and made following changes:

  1. Access the iptables using the command : vim /etc/sysconfig/iptables
  2. If you find the below statements COMMENT them out by adding a '#' at the beginning of the line.

    -A INPUT -s 123.123.123.123/32 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

    -A INPUT -j REJECT --reject-with icmp-host-prohibited

    -A OUTPUT -p tcp -m tcp --dport 3306 -j ACCEPT

  3. Restart the iptables using the command: service iptables restart

Yep, that worked for me. Hope it is useful to someone.

Yoosaf Abdulla
  • 3,722
  • 4
  • 31
  • 34
  • I'm not sure if this helps anybody except You but catch my upvote for Your effort and will to go back to stackoverflow and post this here in case! :) – Mr.TK May 08 '15 at 11:39
3

When I Got my server,even I had the same problem accessing the mysql from MySQL client application, Then I granted the Mysql permission, with following query.

it worked Great

**GRANT ALL PRIVILEGES ON db_base.* TO db_user @'%' IDENTIFIED BY 'db_passwd';** 

db_base is the database Name
db_user is database User
db_passwd is the database password 

Once you execute this flush it, by the following command FLUSH PRIVILEGES;

Suppose if you are looking to give privileges to access certain tables in the Database you can use the following command

GRANT ALL PRIVILEGES ON db_base.HELLOWORLD TO db_user @'%' IDENTIFIED BY 'db_passwd';

Where HELLOWORLD is the table Name

Tisho
  • 8,320
  • 6
  • 44
  • 52
gmhk
  • 15,598
  • 27
  • 89
  • 112
0

I dont know the ins and outs of security behind this bind-addressthing, just learning by installing a debian server on a virtual-box machine. This guest has a virtual network card set up as a bridge, so the rest of the house can see it. Its IP is 192.168.1.4. From another computer (192.168.1.3), connection failed with bind-address = 127.0.0.1. Set up bind-address = 192.168.1.4 works fine. (its own address, litterally) It must be the interpretation of 127.0.0.1 inside a virtual config, not sure...

oli
  • 1
  • 1
0

Are you connecting as user mysql? You might try running the GRANT query as : GRANT ALL PRIVILEGES ON *.* TO mysql@x.x.x.x IDENTIFIED BY "somepass";

jesse reiss
  • 4,509
  • 1
  • 20
  • 19
  • just saw your edit. Ignore my query suggestion. What does your /etc/my.cnf look like? – jesse reiss May 06 '11 at 18:31
  • Yea, I did that, and also created a secondary user and granted all to that user, to no avail. I do realize the problem is that i'm accessing a remote mysql server, i'm just wondering how to grant access to a remote user? (Ie, connecting to my remote server using mysql workbench on my laptop) – Jon May 06 '11 at 18:34
  • could be something to do with your my.cnf file? Do you have `bind-address=x.x.x.x` in there? – jesse reiss May 06 '11 at 18:39
0

Have you verified that mysql workbench is trying the connect using the appropriate username? Have you flushed the privileges after running the grant command?

eykanal
  • 26,437
  • 19
  • 82
  • 113
-2

Comment out the line:

bind-address = localhost
#bind-address = localhost < this is what it should look like.

in your MySQL my.conf file. It is normally located in /etc/mysql/my.conf.

Charles Ray
  • 470
  • 1
  • 5
  • 16
  • 1
    No. If that were the case, he'd be getting a "connection refused" error from the host (because there would be no socket listening), not an "access denied" error from the MySQL server. – nobody May 06 '11 at 18:36