Mysql – How to login into thesql shell from localhost instead of ‘%’

mysql-5.5

I need to grant a permission to a newly created user. I have access to root. But, i can't grant a permission to a new user. When I run:

grant all privileges on 'newuser'.* to 'newuser'@localhost;

I got this:

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'newuser'

The part that I don't understand is why do I logged in as 'root'@'%'? Since I logged in with this command:

mysql --protocol TCP -h localhost -u root -p

But this:

select user(), current_user();

gives me this:

+----------+----------------+
| user()   | current_user() |
+----------+----------------+
| root@::1 | root@%         |
+----------+----------------+

I know only 'root'@'localhost' has grant privilege. Then I check mysql.user table which gives me output (only different columns shown here):

+-----------+---------+------------+-----------------------+
| Host      | User    | Grant_priv | authentication_string |
+-----------+---------+------------+-----------------------+
| localhost | root    | Y          |                       |
| %         | root    | N          | NULL                  |
+-----------+---------+------------+-----------------------+

Other columns that is not included above for 'root'@'localhost' and 'root'@'%' are identical.

So, back to my question: How to login into mysql shell from localhost instead of '%'?

NOTE: I run mysql version 5.5.21 on windows 10 machine.

Best Answer

user() function shows what have your client answered when server asks "Who are you?".

Query shows that your client answers using IPv6 local address.

There is no such record in your mysql.user table. But server finds the record which client's information is matched to. current_user() function shows what account was applied by server for the authentication based on the client's info.

Simply tell your client that it must connect to server using IPv4 - in command line or in configuration file, or disable IPv6 somewhere - on the client, or on the server, or on the workstation. Or add one more user with grant privileges - 'root'@'::1' (recommended).

I don't know why your server cannot convert '::1' adddress to 'localhost' name. Maybe you have no proper record ::1 localhost in your HOSTS file?

Related Question