"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 xxx" was displayed.

Handling Methods

1. An incorrect host is connected.

   Cause: An incorrect 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 target user exists:

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

   • 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 target user:

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

   • 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 target password is correct. Because the password is used for identity authentication, the user password cannot be read from GaussDB(for MySQL) in plain text. However, you can compare the hash string with the PASSWORD function value of the target password to check whether the target 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 target 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 and is escaped by Bash.

   Cause: In the default Bash environment of Linux, when the CLI is used to connect to a database, special characters in the password will be escaped by the environment. As a result, the password becomes invalid.

   For example, in the Bash environment, the password of user test is test$123. When you run the mysql -hxxx -u test -ptest$123 command to connect to a database, the error message "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 'password' REQUIRE SSL; 

   • Check the ssl_type value of the target 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 SSL Connection.
   • Run the ALTER USER 'username'@'host' REQUIRE NONE; command to remove the SSL permission from the user.