Home MySQL Change Authentication Method For MySQL Root User In Ubuntu

Change Authentication Method For MySQL Root User In Ubuntu

By sk
Published: Updated: 36.1K views

This guide explains why and how to change authentication method for MySQL root user to Caching SHA-2 Pluggable Authentication or Native Pluggable Authentication method in Ubuntu.

Introduction

Even if you have setup password for MySQL root user, you still can't authenticate with the database server as root user with a password. Because, the MySQL root user is set to authenticate using the auth_socket plugin by default instead of the password in Ubuntu systems running MySQL 5.7 and newer versions. Hence, you can can't access the MySQL server with root user and its password.

Even if you try to authenticate as root user using command:

$ mysql -u root -p

You will get the following error message:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The auth_socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server.

This plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.

If the plugin finds that the operating system user name and the MySQL user name are same, it permits the connection without further authentication.

For example, if there is a user called ostechnix on the operating system, he/she will be able to login to the 'ostechnix'@'localhost' account within MySQL if Unix socket authentication is the defined authentication mechanism. If the operating system username differs from the MySQL user name, the auth_socket plugin simply refuses the connection.

The auth_socket plugin improves the security, but also complicates things when we use external programs like phpMyAdmin to access the database server.

Also, the users authenticated by the auth_socket plugin can only connect from the local host through the Unix socket file. They are restricted from connecting remotely. This is why we need to change the authentication method for MySQL root user in Ubuntu.

1. Change Authentication Method For MySQL Root User In Ubuntu

MySQL provides many authentication methods and the plugins that implement those methods. In this guide, we only discuss two plugins called caching_sha2_password and mysql_native_password that implements the methods called Caching SHA-2 Pluggable Authentication and Native Pluggable Authentication respectively.

First, we will see how to change authentication method for MySQL root user to caching_sha2_password.

1.1. Change authentication plugin to caching_sha2_password

The caching_sha2_password plugin performs authentication using SHA-256 password hashing. In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. It provides more secure password encryption, faster authentication, and better performance.

To change the authentication plugin, login to MySQL server using command:

$ sudo mysql

Since the MySQL 8 uses auth_socket plugin in Ubuntu, the above command will let you to login to the MySQL server as root user. Enter your sudo password to authenticate.

Next, find the current authentication method for all mysql user accounts by running the following command at the MySQL prompt:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Sample output:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 | user             | authentication_string                                                  | plugin                | host      |
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 | debian-sys-maint | $A$005$||#WR:(W'NSP>|b2
                                             yXI9EfAeI6vnIj8I.Pwvw6Gx6V9bzVce9oTbN212V12 | caching_sha2_password | localhost |
 | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | root             |                                                                        | auth_socket           | localhost |
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 5 rows in set (0.01 sec)
Check current authentication method for mysql root user
Check current authentication method for mysql root user

As you can see, MySQL root user uses auth_socket plugin for authentication. Let us change the auth_socket plugin to caching_sha2_password plugin by using ALTER USER command like below.

Please note that if you've enabled VALIDATE PASSWORD plugin while setting MySQL root user password, you must enter a strong password based on the current password policy. A strong password should consists of at least 8 characters including an uppercase letter, a lowercase letter, a number and a special character.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password123#@!';

Replace Password123#@! in the above command with a strong and unique password of your choice and note it down as we need it later.

Next, update the changes using command:

mysql> FLUSH PRIVILEGES;

Now check if the current authentication plugin is changed or not using command:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Sample output:

+------------------+------------------------------------------------------------------------+-----------------------+-----------+
 | user             | authentication_string                                                  | plugin                | host      |
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 | debian-sys-maint | $A$005$||#WR:(W'NSP>|b2
                                             yXI9EfAeI6vnIj8I.Pwvw6Gx6V9bzVce9oTbN212V12 | caching_sha2_password | localhost |
 | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | root             | $A$005$z9Lq/%(r l
 +DDI9wJbql8DWZNRoet2BSwUrIgjq.l7FxRSyMb/OWk4 | caching_sha2_password | localhost |
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 5 rows in set (0.00 sec)
Change authentication plugin to caching_sha2_password for MySQL root user
Change authentication plugin to caching_sha2_password for MySQL root user

The authentication method for MySQL root user has been set to Caching SHA-2 Pluggable Authentication. From now on, the MySQL root user can authenticate using a password.

Exit from the mysql prompt:

mysql> exit

To verify if the MySQL root user can login with a password, enter the following command:

$ mysql -u root -p

Enter the MySQL root user password:

 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 11
 Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

 Copyright (c) 2000, 2021, Oracle and/or its affiliates.

 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> 
Login to MySQL server with root user and password
Login to MySQL server with root user and password

Even though caching_sha2_password plugin provides enhanced secure encryption and better performance, it has some compatibility issues with existing MySQL installations and causes problems with some PHP versions. For details, refer this link.

If you encountered with any compatibility issues or if the client or connector doesn't support caching_sha2_password authentication plugin, revert back to native authentication method i.e. mysql_native_password as described in the following section.

1.2. Change authentication plugin to mysql_native_password

MySQL includes a mysql_native_password plugin that implements native pluggable authentication method. It provides authentication based on the password hashing method.

The mysql_native_password was the default authentication method in the older MySQL versions.

To change to mysql_native_password plugin, login to MySQL as root user:

$ mysql -u root -p

If you are using auth_socket plugin, you should enter the following command to login into MySQL server as root user:

$ sudo mysql

To set mysql_native_password plugin as default authentication for MySQL root user, run the following command at MySQL prompt.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';

Update the changes using command:

mysql> FLUSH PRIVILEGES;

Now check if the authentication method is changed or not using command:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

Sample output:

+------------------+------------------------------------------------------------------------+-----------------------+-----------+
 | user             | authentication_string                                                  | plugin                | host      |
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 | debian-sys-maint | $A$005$||#WR:(W'NSP>|b2
                                             yXI9EfAeI6vnIj8I.Pwvw6Gx6V9bzVce9oTbN212V12 | caching_sha2_password | localhost |
 | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
 | root             | *B6032809F2C8CE37BDF05DEFE5BA30D6DC74DC68                              | mysql_native_password | localhost |
 +------------------+------------------------------------------------------------------------+-----------------------+-----------+
 5 rows in set (0.00 sec)
Change authentication plugin to mysql_native_password for MySQL root user
Change authentication plugin to mysql_native_password for MySQL root user

Done! We have changed the authentication method for MySQL root user to Native Pluggable Authentication.

Conclusion

In this guide, we looked at why we need to change authentication method for MySQL root user in Ubuntu systems running MySQL 5.7 and newer versions. We also have seen how to change auth_socket plugin to caching_sha2_password or mysql_native_password plugin for MySQL root user in Ubuntu operating system.

Related read:

You May Also Like

4 comments

id027102 May 29, 2021 - 4:38 pm

No possible to implement the change , with :
MariaDB [(none)]> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘Password123#@!’;
I get :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘BY ‘Password123#@!” at line 1
I cannot find what is bad . Can you help ?
Thanks

Reply
sk May 29, 2021 - 4:58 pm

Looks like the quotation mark is wrong.There is a double quotes at the end of the password. Use single quote.

Reply
Arsene December 13, 2021 - 7:19 am

Hello everybody;
Thank you for this short and so useful tutorial ,it works well

Reply
Fighting Ubuntu February 23, 2022 - 12:54 am

There is some mechanism in Ubuntu that will eventually revert any changes you make this way back to the default. So far I’ve been unable to figure out how Ubuntu does this, and nobody who knows will tell me. The secret script seems to run every week or month.

Reply

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More