Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Grant access to MySQL server

  1. #1
    Join Date
    Jun 2008
    Location
    /earth/europe/uk/england/west-yorkshire/leeds/mystreet/myhouse
    Posts
    248

    Default Grant access to MySQL server

    Hello

    - Machine A 10.0.0.5
    -- running Suse 11.0 and MySQL 5.0.51a

    - Machine B 10.0.0.10
    -- running Windows XP SP3

    When I install some software onto Machine B it wants me to install MySQL onto Machine B, I don't want to as I already have MySQL running on Machine A. However, if I tell Machine B to use the MySQL server on Machine A, I get told that "host can not connect".

    I have done a search on the MySQL pages and Google and can find how to GRANT permissions for users to databases, but I can't seem to figure out how to allow a machine to connect in (so it can create databases etc)

    Example:-

    GRANT ALL PRIVILEGES ON databasename.* TO username@10.0.0.10 IDENTIFIED BY 'password'

    Although this works, in that username can now do all actions on the DATABASE from 10.0.0.10, I wonder how can I grant permission to the server to create the databases required by the application?

    I hope this makes sense but if you need any clarification, please ask!!

    Thanks!

  2. #2
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: Grant access to MySQL server

    Hello badger_fruit

    I have never used Windows and will not be able to give you any useful advice regarding machine B. But I can add some thoughts in the hope to get you airborne.

    1. Make sure that machineA has a proper my.cnf i.e. you have not enabled 'skip-networking'.

    2. You can't tell machineB as such to use the MySQL server on machineA. Instead, the application using MySQL on machineB must be configured to contact someuser@machineA. In Linux style this would mean to execute this on machineB:

    Code:
    mysql -h 10.0.0.5 -u someuser -pPassword mydatabase
    The grant on machineA (where MySQL resides) must allow access for someuser@10.0.0.10 including CREATE. Check for Create_priv=Y in table 'user' database 'mysql' on machineA.

    Can you try with the command line client if you can connect from machineB to machineA?

    3. In contradiction with 2. there is a way for an application on machineB to connect to machineB locally and forward this to machineA. Create an ssh tunnel forwarding port 3306 from machineB to machineA. If machineB were running Linux it would look like:

    Code:
    ssh -4 -f -N -L3306:10.0.0.5:3306 tunneluser@machineA
    I have no idea if this is possible in Windows, sorry. Then a final remark: MySQL is often in troubles when DNS is not working properly. I see that you are using IP numbers and that should be ok, but you better check this too.

  3. #3
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: Grant access to MySQL server

    Just re-read your post and saw this:

    GRANT ALL PRIVILEGES ON databasename.* TO username@10.0.0.10 IDENTIFIED BY 'password'
    To enable the creation of new databases you have to grant privileges on a global level (and not just for one database):

    Code:
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'10.0.0.10' IDENTIFIED BY 'password';

  4. #4
    Join Date
    Jun 2008
    Location
    Netherlands
    Posts
    25,384

    Default Re: Grant access to MySQL server

    OTOH "host can not connect" seems rather basic to me. When no connection, then no login, let alone that it comes to the differences in intepreting commands. So when this error is anything near the truth (I can not say, having no MS experience):

    I a should check if MySQL is listening
    Code:
    netstat -tlp
    You could check by connecting to the port from outside with
    Code:
    telnet A <MySQL-port>
    (or some other tool available on B), when refused there is something blocking things (Firewall, ...). When allowed, just disconnect, but you know more.
    Henk van Velden

  5. #5
    Join Date
    Jun 2008
    Location
    /earth/europe/uk/england/west-yorkshire/leeds/mystreet/myhouse
    Posts
    248

    Default Re: Grant access to MySQL server

    Quote Originally Posted by vodoo View Post
    Hello badger_fruit

    I have never used Windows and will not be able to give you any useful advice regarding machine B. But I can add some thoughts in the hope to get you airborne.

    1. Make sure that machineA has a proper my.cnf i.e. you have not enabled 'skip-networking'.
    This is commented out, so it is "off".
    I appreciate this is a linux forum - as an expert user in Windows, it's OK, I can handle THAT side of things!

    Quote Originally Posted by vodoo View Post
    2. You can't tell machineB as such to use the MySQL server on machineA. Instead, the application using MySQL on machineB must be configured to contact someuser@machineA.

    The grant on machineA (where MySQL resides) must allow access for someuser@10.0.0.10 including CREATE. Check for Create_priv=Y in table 'user' database 'mysql' on machineA.
    I used the same user on MachineB as I did for my AutoMySQLBackup.sh script (that you also helped with on another thread) so I know it works locally but I do not think I have done a GRANT for the user@MachineB - maybe this is all that is wrong ...

    Quote Originally Posted by vodoo View Post
    Then a final remark: MySQL is often in troubles when DNS is not working properly. I see that you are using IP numbers and that should be ok, but you better check this too.
    Hmm, i did notice on MachineB's application it had a default "servername" which was localhost .. I think I only tried the IP address instead of the computer name. Maybe it is as simple as that ...

    OK, so thank you Voodoo, I will attempt to:-

    1. Create a new user on MachineA and give this user CREATE access to *.* using your modification of my (almost correct!) original attempt:-

    GRANT ALL PRIVILEGES ON *.* TO 'machineBuser'@'10.0.0.10' IDENTIFIED BY 'password';

    2. When prompted by the application on MachineB, I will attempt to use DNS instead of IP address ...

    please, wish me luck!!!

  6. #6
    Join Date
    Jun 2008
    Location
    /earth/europe/uk/england/west-yorkshire/leeds/mystreet/myhouse
    Posts
    248

    Default Re: Grant access to MySQL server

    Quote Originally Posted by hcvv View Post
    OTOH "host can not connect" seems rather basic to me. When no connection, then no login, let alone that it comes to the differences in intepreting commands. So when this error is anything near the truth (I can not say, having no MS experience):

    I a should check if MySQL is listening
    Code:
    netstat -tlp
    You could check by connecting to the port from outside with
    Code:
    telnet A <MySQL-port>
    (or some other tool available on B), when refused there is something blocking things (Firewall, ...). When allowed, just disconnect, but you know more.
    HCVV, I am sorry, you posted while I was writing my reply above.

    FYI:-


    Code:
    # netstat -tlp | grep mysql
    tcp        0      0 *:mysql                 *:*                     LISTEN      2473/mysqld
    So yes, it would appear to be listening ...

    However, on machineB ...
    Code:
    telnet 10.0.0.5 3306
    Host 10.0.0.10 is not allowed to connect to this MySQL server
    I will try to create a new user as per my previous email and report back ...

  7. #7
    Join Date
    Jun 2008
    Location
    Netherlands
    Posts
    25,384

    Default Re: Grant access to MySQL server

    Aha, but again reading the message and trying to interprete, it says this host is not allowed, nothing about a user (which would e impossible because you only tried to make a TCP/IP connection and not to login).

    So it seemss it is listening, but only allowing cetain hosts. This is analogue to other programs like e.g. xinetd, where you can conigure that only certain hosts (or networks) are allowed. Now I am not good enough MySQL guru, but I should look for a place where you can define allowed hosts.
    Henk van Velden

  8. #8
    Join Date
    Jun 2008
    Location
    /earth/europe/uk/england/west-yorkshire/leeds/mystreet/myhouse
    Posts
    248

    Default Re: Grant access to MySQL server

    Quote Originally Posted by hcvv View Post
    *snip* ... but I should look for a place where you can define allowed hosts.
    hence my original post
    interestingly enough, after creating the user I COULD telnet into the MYSQL server from another host BUT silly me - I turned off machineB last night, so for my test today I used MachineC (Win Server 2003) 10.0.0.20

    Of course, I modified the GRANT statement to the right IP address but when I tried to install the application, it did not like Win Server and so would not install as far as the DB connection

    I've looked all over for such a configuration entry, I would expect it to be in my.cnf but alas, it is not

  9. #9
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: Grant access to MySQL server

    Hi again,

    When things don't work let's start verifying the basics:

    1. You can ping host A from host B (I'm sure you already checked this).

    2. On host A: prompt> mysqladmin ping
    to verify that mysql is running.

    3. On host A: let's re-check the grants you have:

    Code:
    echo "SELECT * FROM user WHERE Host = '10.0.0.10';" | mysql -E mysql
    4. Check that MySQL is listening on port 3306. Do on host A:

    Code:
    echo "SHOW variables LIKE 'port';" | mysql -N
    5. Check the firewall. FW_SERVICES_INT_TCP or FW_SERVICES_EXT_TCP (depending on your setup) should include port 3306.

    Then you should be able to connect using the command line client on host B

    Code:
    mysql -h 10.0.0.5 -u someuser -pPassword database
    Most or all of points 1. to 5. should arready be ok, because you reported previously:

    telnet 10.0.0.5 3306
    Host 10.0.0.10 is not allowed to connect to this MySQL server
    The response is coming from the MySQL server on Host A. This is not perfect but still very good, because it shows that the server is responding, but is refusing the connection. I guess that there is still a problem with the grants. Probably telnet from host B does not provide for a proper username in accordance with the name in MySQL's user table. You should really try the mysql command line tool and report what the error message is.

    Usernames must match. And in case there is some DNS (or for any Windows-specific reason), host B may identify itself as 'hostname.domain.tld' (you name your own) or even just as 'hostname' to the MySQL server. In this case you need another grant (not using IP numbers)

    Code:
    GRANT ALL ON *.* TO 'user'@'hostB.domain.tld' IDENTIFIED BY 'Password';
    GRANT ALL ON *.* TO 'user'@'hostB' IDENTIFIED BY 'Password';
    The two (or three) scenarios are completely different for MySQL.

  10. #10
    Join Date
    Jan 2009
    Location
    Switzerland
    Posts
    1,529

    Default Re: Grant access to MySQL server

    interestingly enough, after creating the user I COULD telnet into the MYSQL server from another host BUT silly me - I turned off machineB last night, so for my test today I used MachineC (Win Server 2003) 10.0.0.20
    ... and keep in mind that joe@hostB is not the same user as joe@hostC for mysql. They need individual grants.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •