Login Failed After ssl_type of root Is Changed to ANY

Scenario

When user root was used to log in to a DB instance through DAS on the console, the error message "Access denied" was displayed.

Possible Causes

1. View the root account information in the mysql.user table to check whether the client IP address is within the allowed range and whether SSL is enabled.

   SELECT * FROM mysql.user WHERE User='root'; 

   If ssl_type of the root account is set to ANY, the root account needs to use SSL.

2. Check whether SSL is enabled.

   show variables like '%ssl%';

   SSL was not enabled for the instance.

   

   The cause is that ssl_type of the root account was changed to ANY. As a result, the login failed.

Solution

Run the following command to change the value of ssl_type to be empty for the root account:

update mysql.user set ssl_type='' where user = 'root';

To change the ssl_type of all other user accounts to be empty, run the following command:

update mysql.user set ssl_type='' where user not like 'mysql%';