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.
Table of Contents
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)
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)
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>
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)
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:
4 comments
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
Looks like the quotation mark is wrong.There is a double quotes at the end of the password. Use single quote.
Hello everybody;
Thank you for this short and so useful tutorial ,it works well
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.