"Access denied" Displayed During Database Connection

Scenario

A client failed to connect to a database, and the error message "Error 1045: Access denied for user username" was displayed.

Handling Methods

1. An invalid host is connected.

   Cause: An invalid database host is connected, and the user or client IP address does not have the access permission.

   Solution: Ensure that the host name of the database to be connected is correctly specified.

2. The user does not exist.

   Cause: The user account used for connecting to the database does not exist.

   Solution:

   • Log in to the database as an administrator and run the following command to check whether the user exists:

     SELECT User FROM mysql.user WHERE User='username'; 

   • If the user does not exist, create the user.

     CREATE USER 'xxx'@'xxxxxx' IDENTIFIED BY 'xxxx'; 

3. The client IP address does not have the access permission.

   Cause: The user used by the client exists, but the client IP address is not allowed to access the database.

   Solution:

   • Log in to the database as an administrator and run the following command to check which client IP addresses are allowed to connect to the database for the user:

     SELECT Host, User FROM mysql.user WHERE User='username'; 

   • If the client IP address is not within the allowed network segment, assign the access permission to the client IP address. For example, run the following command to grant the test user the permission to access the 192.168.0 network segment:

     GRANT ALL PRIVILEGES ON *.* TO'root'@'192.168.0.%' IDENTIFIED BY 'password' WITH GRANT OPTION; 
     FLUSH PRIVILEGES; 

4. The password is incorrect.

   Cause: The password of the user is incorrect.

   Solution:

   • Check whether the password is correct. Because the password is used for identity authentication, the user password cannot be read from RDS for MySQL in plain text. However, you can compare the hash string with the PASSWORD function value of the password to check whether the password is correct. The following is an example of SQL statements:

     mysql> SELECT Host, User, authentication_string, PASSWORD('12345') FROM mysql.user WHERE User='test'; 
     +-----------+------+-------------------------------------------+-------------------------------------------+
     | Host      | User | authentication_string                     | PASSWORD('12345')                         | 
     +-----------+------+-------------------------------------------+-------------------------------------------+
     | %         | test | *6A23DC5E7446019DC9C1778554ED87BE6BA61041 | *00A51F3F48415C7D4E8908980D443C29C69B60C9 | 
     +-----------+------+-------------------------------------------+-------------------------------------------+
     2 rows in set, 1 warning (0.00 sec) 

     The preceding example shows that the hash value of PASSWORD('12345') does not match the authentication_string field, indicating that the password 12345 is incorrect.

   • To reset the user password, run the following SQL statement:

     set password for 'test'@'%' = 'new_password'; 

5. The password contains special characters, which are treated as escape characters by Bash.

   Cause: In the default Linux Bash shell, when connecting to a database, special characters in the password will be interpreted as escape characters. As a result, the password becomes invalid.

   For example, in a Bash shell, if the password test$123 is used, when you run the mysql -hxxx -u test -ptest$123 command to connect to a database, the error "ERROR 1045 (28000): Access denied" will be displayed.

   Solution: Enclose the password in single quotation marks to prevent Bash from interpreting special characters.

   mysql -hxxx -u test -p'test$123'

6. REQUIRE SSL is configured for the user, but the client uses a non-SSL connection.

   Troubleshooting:

   • Run the show create user 'xxx' command to check whether the user must use the SSL connection. If the REQUIRE SSL attribute is displayed, the user must use the SSL connection.
   • Check whether statements similar to the following have been used to grant permissions to the user:

     GRANT ALL PRIVILEGES ON . TO 'ssluser'@'localhost' IDENTIFIED BY 'zdh1234' REQUIRE SSL; 

   • Check the ssl_type value of the user. If the value is not empty, the user must use SSL.

     SELECT User, Host, ssl_type FROM mysql.user WHERE User='xxx'; 

   Solution:

   • Connect the client to the database in SSL mode. For details, see Connecting to a DB Instance Using a MySQL Client.
   • Run the ALTER USER 'test'@'xxxxx' REQUIRE NONE; command to remove the SSL permission from the user.