Issue: MySQL5.7 Server on Ubuntu 18.04 Machine allow root user without password, even when root password is set through mysqladmin and mysql_secure_installation

Recently i found on one new Ubuntu 18.04 MySQL 5.7 database allow root user from loalhost to login without password even when password was set through mysqladmin and mysql_secure_installation

Setup

For this post we are using Ubuntu 18.04 Machine with MySQL Server 5.7 version

root@srv2:~# lsb_release -d
Description:    Ubuntu 18.04.4 LTS

root@srv2:~# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Working without password

In terms of authentication things get bit changed in MySQL 5.7 and in way it working is bit different. It will not reflect password password even we try to set it through mysqladmin command

Let’s try what happened when we try to set password through mysqladmin command.

root@srv2:~# mysqladmin password
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

In first impression, it sees that it set MySQL password properly, but when we try to login on MySQL, it login through without any password and when we check password hash for root user, nothing saved for it.

mysql> select User, Host, HEX(authentication_string) FROM mysql.user where user like '%root%';
+------+-----------+----------------------------+
| User | Host      | HEX(authentication_string) |
+------+-----------+----------------------------+
| root | localhost |                            |
+------+-----------+----------------------------+
1 row in set (0.01 sec)

So what exactly happening here, actually In MySQL 5.7 login is by-default happen through auth_socket.

mysql> SELECT plugin from mysql.user where User="root";
+-------------+
| plugin      |
+-------------+
| auth_socket |
+-------------+
1 row in set (0.00 sec)

So it login because of Unix socket though which we already connected, it just detect root initiated Unix socket and connect to MySQL Server.

Even if we try to change password it still empty in password hash table for root, because it comes with warning and no row affected.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('passw0rd');
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> select User, Host, HEX(authentication_string) FROM mysql.user where user like '%root%';
+------+-----------+----------------------------+
| User | Host      | HEX(authentication_string) |
+------+-----------+----------------------------+
| root | localhost |                            |
+------+-----------+----------------------------+
1 row in set (0.00 sec)

If we check warning after changing password we can see some information regarding it.

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SET PASSWORD FOR  = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR  = '' instead |
| Note    | 1699 | SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.                                                                              |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Set Password

Now we need to understand and change password reset command like below.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'passw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> select User, Host, HEX(authentication_string) FROM mysql.user where user like '%root%';
+------+-----------+------------------------------------------------------------------------------------+
| User | Host      | HEX(authentication_string)                                                         |
+------+-----------+------------------------------------------------------------------------------------+
| root | localhost | 2A37344231433231414345304332443642303637384135453530334432413630453846393635314133 |
+------+-----------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This time it change password policy as well in below command.

mysql> SELECT plugin from mysql.user where User="root";
+-----------------------+
| plugin                |
+-----------------------+
| mysql_native_password |
+-----------------------+
1 row in set (0.00 sec)

I hope that would be clear how MySQL 5.7 login without password and how can we change its password to make it secure.