MySQL Bugs: #22118: Access Denied for user root@localhost with "skip-name-resolve"

Bug #22118	Access Denied for user root@localhost with "skip-name-resolve"
Submitted:	8 Sep 2006 13:26	Modified:	26 Sep 2013 15:55
Reporter:	REY Benoît	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	4.1.14-4.1.21,5.0.44sp1	OS:	Windows (Windows 2003,XP)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
After upgrading from release 4.1.14 to 4.1.21, we can't connect to the server from localhost as root.

-The MySQL server is started with the option "skip-name-resolve".
-In the user table we define that the user root can connect only from host "localhost".

When attempting to connect as root we get an "access denied for user root @127.0.0.1".

If we restart the server without "skip-name-resolve" there is no issue... But in the documentation it's specified that you can use localhost with the option "skip-name-resolve":

--skip-name-resolve 
Do not resolve hostnames when checking client connections. Use only IP numbers. If you use this option, all Host column values in the grant tables must be IP numbers or localhost.

How to repeat:
Create a user root who can connect only from host "localhost" and start the server with "skip-name-resolve".

Try to connect from localhost as root...

Thank you for a problem report. Please, send the results of:

SELECT host, use FROM mysql.user;

statement from your machine.

Here are the result of "select host, user from mysql.user" :
'localhost', 'root'
'172.18.210.1', 'root'
'172.18.210.%', 'statisticiens'
'localhost', 'partenaires'
'172.18.200.%', 'partenaires'

I was not able to repeat the behaviour described with 4.1.22-BK on Linux:

openxs@suse:~/dbs/4.1> bin/mysqld_safe --skip-name-resolve &
[1] 9552
openxs@suse:~/dbs/4.1> Starting mysqld daemon with databases from /home/openxs/d
bs/4.1/var

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT host, user FROM mysql.user;
+-----------+--------+
| host      | user   |
+-----------+--------+
| localhost |        |
| localhost | root   |
| localhost | sample |
| suse      |        |
| suse      | root   |
+-----------+--------+
5 rows in set (0.01 sec)

mysql> drop user ''@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT host, user FROM mysql.user;
+-----------+--------+
| host      | user   |
+-----------+--------+
| localhost | root   |
| localhost | sample |
| suse      |        |
| suse      | root   |
+-----------+--------+
4 rows in set (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT host, user FROM mysql.user;
+-----------+--------+
| host      | user   |
+-----------+--------+
| localhost | root   |
| localhost | sample |
| suse      |        |
| suse      | root   |
+-----------+--------+
4 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT host, user FROM mysql.user;
+-----------+--------+
| host      | user   |
+-----------+--------+
| localhost | root   |
| localhost | sample |
| suse      |        |
| suse      | root   |
+-----------+--------+
4 rows in set (0.00 sec)

While on Windows (4.1.18 and 4.1.21), with skip_name_resolve added to my.ini I had got:

C:\Documents and Settings\openxs>mysql -uroot -proot
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL ser
ver

This explicitely contradicts our manual (http://dev.mysql.com/doc/refman/4.1/en/server-options.html):

"--skip-name-resolve

Do not resolve hostnames when checking client connections. Use only IP numbers. If you use this option, all Host column values in the grant tables must be IP numbers or localhost. See Section 7.5.5, “How MySQL Uses DNS”."

So, this is either a bug (Windows-specific!) or a request for proper documentation.

I wonder if it's only Windows.
Does mysqld resolve '127.0.0.1' back to 'localhost' internally, in case skip-name-resolve is set? Because that's what's really needed.
Can a dev please check this in the code? Thanks.

Bump.  It would be nice if this worked correctly. I see the bug in 5.0.51a.

I just came across the same issue using Windows.  Will this ever be resolved?  I'm attempting to use this line to resolve issues with slow remote connections.

Bump

Problem is not solved  - this time on Linux with MySql 5.1.30, and with J/connector 5.1.7.  Does not appear to be Linux os specific.

Bump.

Same problem reported here. I am running MySql 5.1.30 community server on a Windows Server 2003 box. When I experienced long delays connecting from a linux machine I added skip-name-resolve to the my.ini file, and then I could not login as root.

Potential workaround:

1) Make sure that skip-name-resolve is NOT in my.ini
2) Log into mysql as root
3) Execute the queries:

UPDATE information_schema.USER_PRIVILEGES SET grantee = '\'root\'@\'%\'' WHERE grantee = '\'root\'@\'localhost\'';
UPDATE mysql.user SET Host = '%' WHERE User = 'root'

4) Add skip-name-resolve to my.ini
5) Restart mysql service
6) You should be able to login as root now

BUT, I don't know how badly that hurts security. I think that allows root to login from anywhere. Maybe try changing the permissions to 127.0.0.1 instead of %.

I, too, cannot use MySQL Administrator after adding the skip-name-resolve line to my.ini.  Any info here on this one yet 3 years later?

I had the same problem. The problem is that the default root account is created as @localhost. When you turn off name resolution, it logically will not work since 'localhost' is a name. What you need to do is re-enable name resolution, then modify the root user to root@127.0.0.1. Now disable name resolution again, and try logging in... you should have no problem.

Ben's suggestion worked perfectly!  Thank you very much!  No issues yet :)

Ah, correction.  One issue.  While I can now log into MySQL Administrator, I can't see any databases under Catalogs.  Only information_schema.  Is there something I need to do to enable me to see my existing databases?

Also appears I don't have access to any databases from Root.  I also can't view any users.  Did I just screw something up by adding the host 127.0.0.1 to Root?  I switched the skip-name-resolve back off and tried going in again and I have the same issue.

Problem fixed.  Fixed permissions for Root.

Step 1: Added skip-grant-tables to my.ini to disable authentication.
Step 2: After restarting the MySQL service, open MySQL Command Line Client and login as root
Step 3: Issue the following commands:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

FLUSH PRIVILEGES;

GRANT ALL ON *.* TO 'root'@'localhost';

Step 4: Restart the MySQL service after removing skip-grant-tables from my.ini or commenting it out.

Done deal!

Bug #62714 was marked as duplicate of this one.

Their workarounds involve using 127.0.0.1, however I already stated that does not work.

I must instead use ::1 instead of 127.0.0.1 in the user table.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to --skip-name-resolve description:

Depending on the network configuration of your system and the Host
values for your accounts, clients may need to connect using an
explicit --host option, such as --host=localhost, --host=127.0.0.1,
or --host=::1.

i get reference from [11 Sep 2006 15:29] Valeriy Kravchuk 
after using this:
SELECT host, use FROM mysql.user;
i am getting this error:

mysql> SELECT host, use FROM mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'use F
ROM mysql.user' at line 1

kindly solve my problem.
i am using mysql v.5.6.17.
thnx