Why MySQL cannot log on locally after a user is created

Why MySQL cannot log on locally after a user is created

After installing MySQL, we usually add common users with corresponding permissions to access the database. When you log on to the database locally, you can log on from other mysql clients.

[Root @ mysql01 ~] # Mysql-userver-p123456
ERROR 1045 (28000): Access denied for user 'server' @ 'localhost' (using password: YES)

Local login failed;

[Root @ mysql02 ~] # Mysql-h192.168.47.166-userver-p123456
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 7
Server version: 5.5.36-log Source distribution

Remote Login successful;

1. view the mysql. user table after Logon

Image

As you can see, my database contains server users and anonymous users localhost;
• The password of the server user is 'server'
• The password of an anonymous user is blank.

2. log on to the server user on the machine and find that you can log on without a password;

[Root @ mysql01 ~] # Mysql-userver-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 3
Server version: 5.5.36-log Source distribution

Copyright (c) 2000,201 4, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> select user (), CURRENT_USER ();
+ -------------------------- + ----------------------- +
| USER () | CURRENT_USER () |
+ ------------------------- + ------------------------ +
| Server @ localhost |
+ ------------------------- +
1 row in set (0.00 sec)

After successful logon, use the USER () and CURRENT_USER () functions to view the users used.
• The USER () function returns the USER name and host name you specified when logging on to the client.
• The CURRENT_USER () function returns the user in the MySQL authorization table to authenticate your login request.

I found that I used the 'server' @ 'localhost' account to log on to the database (because the host is not specified during local login, the default logon is localhost ), however, the database uses the account ''@ 'localhost' for logon authentication, while the anonymous user'' @ 'localhost' does not have a password, therefore, I entered an empty password to log on successfully. But the anonymous user of the corresponding user after logon.

Generally, after MySQL is installed, we use the script mysql_install_db to generate an authorization table. By default, the anonymous user ''@ 'localhost' is created. It is precisely because this anonymous user affects the authentication of other users logging on from the local device.

So how does MySQL perform user identity authentication?

1. When a user requests a login from the client, MySQL compares the entries in the authorization table with the entries provided by the client, including the user name, password, and host. The Host field in the authorization table can be matched using wildcards as the mode. For example, test.example.com, % .example.com, %. com, and % can both match the Host test.example.com. The User field in the authorization table does not allow pattern matching. However, a username with null characters can represent anonymous users, and a null string can match all usernames, just like wildcards. When the Host and user values in the User table match the Host and user names provided by the client, MySQL reads the user table into the memory and sorts them according to certain rules, the first entry that matches the client user name and host name read by the sorting rule is used to authenticate the client.

Ii. sorting rules: for the Host field, sort by matching precision. The more accurate the field is, the more accurate the field is. For example, when the Host test.example.com is matched, the ratio of % .example.com to %. com is more accurate, while test.example.com is more accurate than % .example.com. For the User field, the User names of non-empty strings are sorted before the User names matching null strings. The User and Host fields have multiple matching values. MySQL uses the Host name to sort the first entry, and selects the username to sort the previous entry at the same time as the Host name field. Therefore, if the User and Host fields both have multiple matching values, the entries with the most precise matching Host name are authenticated by the User.

After learning about the authentication process, you can see why the server logon fails.

When using GaMe to log on to the local machine, if you do not specify the-h parameter, the default value is localhost. However, there are two matching entries in MySQL: 'server' @ '%' and ''@ 'localhost'

As mentioned above, an empty string can match all user names, just like wildcards.

According to the sorting rules during MySQL authentication, the first user name is sorted before, the second host name is more accurate, and the sorting is performed before.

MySQL uses the first entry in the list of hostnames for identity authentication, so ''@ 'localhost' is used to authenticate the client. Therefore, you can only log on to the database using an anonymous user's empty password. The following situations will occur.

Solution: delete anonymous users (only for security purposes)

Why can't root users be affected, but normal users cannot log on from the local device?

Because the mysql_install_db script will generate the 'root' @ 'localhost' account in the authorization table. Similarly, when using root to log on to MySQL, both 'root' @ 'localhost' and '@ 'localhost' can match the Logon account, but according to the sorting rules, the host name is the same, the username is not a Null String, so 'root' @ 'localhost' is sorted first. Root local logon is not overwritten by anonymous users.

This article permanently updates the link address: