Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12

    Unanswered: Cannot give root remote access

    Hi,

    I'm trying to enable remote access for the root user in MySQL (this is a test-DB that is only accessible from the internal network so it's not a security issue).

    On the server itselft I ran the following:

    Code:
    mysql> grant all on *.* to root@'computer.domain.com';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select User, Host from user;
    +-----------+----------------------+
    | User      | Host                 |
    +-----------+----------------------+
    | appuser   | %                    |
    | root      | 127.0.0.1            |
    | appuser   | 127.0.0.1            |
    | root      | localhost            |
    | root      | computer.domain.com  |
    +-----------+----------------------+
    5 rows in set (0.00 sec)
    appuser is the user our web application is using, and I can connect remotely without problems (either through the mysql commandline client or through a JDBC client)

    However when I try to connect as the root user from that computer I get the message:

    Code:
    c:\>mysql --host=mysqlserver --user=root --password=******
    ERROR 1045 (28000): Access denied for user 'root'@'computer.domain.com' (using password: YES)
    The password is definitely correct. I checked that multiple times.

    I also tried
    Code:
    grant all on *.* to 'root'@'%';
    to be able to use the root account from any computer, but with no luck either.

    There is an entry for that (root) user and I granted all privileges to that root user. I should be able to connect remotely as root, shouldn't I?

    So what am I missing here?

    The MySQL server version is 5.1.69 and it's running on CentOS 6.4
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    daft question
    were privileges FLUSHed afterwards?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by healdem View Post
    daft question
    were privileges FLUSHed afterwards?
    Yes. Sorry I should have mentioned that.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If you use GRANT you do not need to flush privileges. This is only needed when you INSERT/UPDATE or DELETE from the User table.

    The way things work in MySQL is that upon connection it uses the client IP address to determine the machine name from the DNS. If it cannot find it you will get the access failure you are getting there.

    In order to validate that the DNS is the issue GRANT ALL ON *.* TO 'root'@ip_address_of_client; If this works then you will need add your computer name and IP address into the DNS to make it work.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by it-iss.com View Post
    The way things work in MySQL is that upon connection it uses the client IP address to determine the machine name from the DNS. If it cannot find it you will get the access failure you are getting there.

    In order to validate that the DNS is the issue GRANT ALL ON *.* TO 'root'@ip_address_of_client; If this works then you will need add your computer name and IP address into the DNS to make it work.
    Thanks for the tip. Unfortunately adding root with the IP address of my computer didn't help.

    When I connect as the regular user ("appuser") from my computer select user() shows appuser@computer.domain.com

    From the commandline of the MySQL server, a ping to the name of my computer (with and without domain) is successful and shows my computer's name.

    So to me it seems the DNS resolution works (although I don't really know that much about networking).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    OK, I found the reason.

    Apparently when I run
    Code:
    grant all on *.* to 'root'@'computer.domain.com';
    then this user does not use the same password as the existing "root" user.


    After I executed:
    Code:
    set password for 'root'@'computer.domain.com' = password('my_new_password');
    I could log on remotely.
    Last edited by gvee; 07-11-13 at 11:03. Reason: Removed sensitive information
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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