Mysql connection error ERROR 1045 (28000): solution to the Access denied for user problem 02/25 Update SLTechnology News&Howtos

Mysql connection error ERROR 1045 (28000): solution to the Access denied for user problem

2025-02-25 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article focuses on "Mysql connection error ERROR 1045 (28000): the solution to the Access denied for user problem". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "Mysql connection error ERROR 1045 (28000): the solution to the Access denied for user problem"!

An error occurred while connecting remote data from 192.168.111.99:

[mysql@LVS01 mysql_5621] $mysql-uroot-p123-h292.168.111.10-P 5621

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'root'@'192.168.111.99' (using password: YES)

View remote host permissions:

Mysql > use mysql

Database changed

Mysql > select user,host,password from user

+-- +

+-- +

| | root | mysql-svr1 |

| | root | 127.0.0.1 |

| | root |:: 1 |

| | root | 192.168.111.1% |

| | root | 192.168.111.99 |

+-- +

16 rows in set (0.00 sec)

It is found that 192.168.111.99 has been authorized, but why is it still not connected?

The permissions associated with 192.168.111.99 are the following lines:

| | root | 192.168.111.1% |

| | root | 192.168.111.99 |

Attempt to log in with an empty password:

[mysql@LVS01 mysql_5621] $mysql-uroot-h292.168.111.10-P 5621

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 20216

Server version: 5.6.21-log Source distribution

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 >

Delete a user with an empty password:

Mysql > drop user root@'192.168.111.%'

Query OK, 0 rows affected (0.31 sec)

Mysql > drop user root@'192.168.111.99'

Query OK, 0 rows affected (0.00 sec)

Mysql > select user,host,password from user

+-- +

+-- +

| | root | mysql-svr1 |

| | root | 127.0.0.1 |

| | root |:: 1 |

+-- +

14 rows in set (0.00 sec)

Mysql >

[mysql@LVS01 mysql_5621] $mysql-uroot-p123-h292.168.111.10-P 5621

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 20312

Server version: 5.6.21-log Source distribution

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 >

Summary:

The reason for this error is that when the same user has different host scope permissions, verify the permissions by the most matching host.

That is, when logging in to the server from 192.168.111.99, the verification order is:

Root@'192.168.111.99'

Root@'192.168.111.%'

Root@'%'

Reference documentation:

Http://dev.mysql.com/doc/refman/5.7/en/connection-access.html

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

Whenever the server reads the user table into memory, it sorts the rows.

When a client attempts to connect, the server looks through the rows in sorted order.

The server uses the first row that matches the client host name and user name.

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0 to 255.255.255.0 are considered equally specific.) The pattern'% 'means "any host" and is least specific. The empty string''also means "any host" but sorts after'%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific) For rows with equally-specific Host and User values, the order is indeterminate.

Recommendations:

(1) when the user is authorized, it is authorized according to the minimum host range, and the password is given, only once.

(2) if the host scope changes in the future, the old license can be deleted when re-authorization, otherwise it may cause similar problems due to the different passwords of the two authorization.

At this point, I believe you have a deeper understanding of "Mysql connection error ERROR 1045 (28000): the solution to the Access denied for user problem". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.