How to deal with MySQL database access denied

This time I will show you how to deal with denial of access to the MySQL database, and what are the precautions for handling denial of access to the MySQL database. The following is a practical case, let's take a look.

Use wordpress to build a blog, and the database uses MySQL. For the convenience of debugging, create the account my_account and allow it to access the database from any host.

CREATE USER `my_account`@'%' IDENTIFIED BY 'my_password';Copy

Modify the corresponding configuration of wp-config.php, note that DB_HOST is set to 127.0.0.1.

define('DB_USER', 'my_account'); // 账号
define('DB_PASSWORD', 'my_password'); // 密码
define('DB_HOST', '127.0.0.1'); // 数据服务地址Copy

Deployed to the cloud server, the local browser accesses the blog, and prompts that the database access is denied (local connection to remote data is no problem), the following is the error log.

ERROR 1045 (28000): Access denied for user 'my_account'@'localhost' (using password: YES)`Copy

After simple troubleshooting, the problem was solved and is recorded here Below are the solutions and reasons for the error.

Solution

1. Solution 1: Delete the anonymous account whose Host field is localhost in the mysql.user table (the account name is empty).
2. Solution 2: Create the my_account@localhost account for local connection to the database.

The author adopted plan one.

First, confirm whether there is an anonymous account in the mysql.user table.

MariaDB [(none)]> SELECT User, Host from mysql.user WHERE Host = 'localhost' AND User = '';
+------+-----------+
| User | Host      |
+------+-----------+
|      | localhost |
+------+-----------+
1 row in set (0.00 sec)Copy

Then, delete the corresponding anonymous account and try to log in again, successfully.

MariaDB [(none)]> DROP USER ''@'localhost';
Query OK, 0 rows affected (0.00 sec)Copy

Problem Analysis

Why does anonymous account cause database connection failure?

Requires a certain understanding of MySQL account creation and client connection verification.

Create MySQL account

The basic syntax is as follows:

CREATE USER account name@host IDENTIFIED BY password;Copy

Notes: (The following Use User to refer to the account name and Host to refer to the host)

Host indicates which host the account is allowed to access the database from. Mainly used for security restrictions, it can be host name, IP address, % (wildcard);

User is allowed to be repeated as long as the Host is different.

When Host is set to %, it means that the database is allowed to be connected from any host.

For example, there are two xiaoming accounts, one allows connecting to the database from the local machine, and the other allows connecting to the database from 14.215.177.39.

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE User = 'xiaoming';           
+---------+---------------+
| User    | Host          |
+---------+---------------+
| xiaoming | 14.215.177.39 |
| xiaoming | localhost     |
+---------+---------------+
2 rows in set (0.00 sec)Copy

Anonymous account

is an account with empty User and can match any user name. The following command creates an anonymous account.

CREATE USER ''@'localhost' IDENTIFIED BY 'pwd3';Copy

Identity verification

When the database server receives the client connection, it will first For identity verification, compare the User, Host, and Password fields with the records in the mysql.user table to confirm whether the account is legitimate.

Here is a question: If there are multiple matching records in the mysql.user table, which record should prevail?

The answer is "priority". The general rules are as follows:

First, check the Host field. If multiple Hosts meet the criteria, the record with the highest matching score (IP address > wildcard %) is selected.

Secondly, check the User field. If multiple Users meet the criteria, the record with the highest matching degree is selected. Anonymous users can match any user and therefore have the lowest matching degree.

Priority matching example

For example, assume that the local database has the following two accounts (the Password field is actually not clear text).

+------------+-----------+-----------+
| User       | Host      | Password  |
+------------+-----------+-----------+
| my_account | %         | 123       |
|            | localhost | 456       |
+------------+-----------+-----------+Copy

Run the following command, and the account you finally logged in matches the 2nd record. (Readers can try it by themselves. If the login fails when entering the password 123, but successfully when entering the password 456)

mysql -u my_account -pCopy

Why? Review matching priorities.

First, check the Host field. localhost and % all meet the requirements. The matching degree of localhost is higher than %, so the second record is matched.

Next, check the User field. The second record is an anonymous account and can match any User value. Therefore, the second record meets the requirements.

Therefore, although the Host field of the account my_account is %, when you connect to the database locally (the host where the database is located), because of the existence of the above rules, MySQL will think that you are logging in with an anonymous account.

The password of my_account is different from that of the anonymous account, so the password verification fails and access is denied.

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the php Chinese website!

Recommended reading:

Detailed explanation of the steps to highlight the selected li in react

node.js deployment starts running in the background Detailed explanation of forever steps

The above is the detailed content of How to deal with MySQL database access denied. For more information, please follow other related articles on the PHP Chinese website!