Interpretation of new features | MySQL 8.0 multi-factor authentication

Author: Jin Changlong

Aikesen test engineer, responsible for the testing of DMP products

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

MySQL 8.0.27 adds the multi-factor authentication (MFA) function, which can specify multiple authentication checks for a user. A new system variable authentication_policy has also been introduced to manage the multi-factor authentication feature.

We know that before MySQL 8.0.27, an authentication plugin can be specified when creating a user, and the value of the system variable default_authentication_plugin will be taken if it is not specified explicitly. There are three valid values of default_authentication_plugin, namely mysql_native_password, sha256_password, and caching_sha2_password. These three authentication plugins are built-in plugins that do not require registration steps.

1. System variable authentication_policy

In MySQL 8.0.27, authentication_policy is used to manage user authentication, first start mysql

root@ubuntu:~# docker run --name mysql-1 -e MYSQL_ROOT_PASSWORD=123 -d --ip 172.17.0.2 mysql:8.0.27

Also check the values of authentication_policy and default_authentication_plugin

root@ubuntu:~# docker run -it --rm mysql:8.0.27 mysql -h172.17.0.2 -uroot -p123
......
  
mysql> show global variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| authentication_policy | *,,   |
+-----------------------+-------+
1 row in set (0.02 sec)
 
mysql> show global variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

We see that the default value of authentication_policy is *,,

The first element value is an asterisk ( ), indicating that it can be any plugin, and the default value is the value of default_authentication_plugin. If the element value is not an asterisk ( ), it must be set to one of mysql_native_password , sha256_password , caching_sha2_password .

The second and third element values are empty, and these two positions cannot be set to internally stored plugins. If the element value is empty, it means the plugin is optional.

Create a user and take a look. When the plugin name is not specified, the default plugin caching_sha2_password is automatically used.

mysql> create user 'wei1'@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)
  
mysql> select user,host,plugin from mysql.user where user='wei1';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| wei1  | localhost | caching_sha2_password |
+------+-----------+-----------------------+
1 row in set (0.00 sec)

When specifying a plugin name, the corresponding plugin will be used

mysql> create user 'wei2'@'localhost' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)
 
mysql> select user,host,plugin from mysql.user where user='wei2';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| wei2 | localhost | mysql_native_password |
+------+-----------+-----------------------+
1 row in set (0.01 sec)

Try changing the value of the first element of authentication_policy to sha256_password

mysql> set global authentication_policy='sha256_password,,';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'authentication_policy';
+-----------------------+-------------------+
| Variable_name         | Value             |
+-----------------------+-------------------+
| authentication_policy | sha256_password,, |
+-----------------------+-------------------+
1 row in set (0.00 sec)

Create a user again, without specifying the name of the plugin

mysql> create user 'wei3'@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)
 
mysql> select user,host,plugin from mysql.user where user='wei3';
+------+-----------+-----------------+
| user | host      | plugin          |
+------+-----------+-----------------+
| wei3 | localhost | sha256_password |
+------+-----------+-----------------+
1 row in set (0.00 sec)

You can see that the default plugin used is sha256_password, indicating that when the first element of authentication_policy specifies the plugin name, default_authentication_plugin is deprecated.

Second, multi-factor authentication users

First we restore authentication_policy to the default value

mysql> set global authentication_policy='*,,';
Query OK, 0 rows affected (0.01 sec)
 
mysql> show global variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| authentication_policy | *,,   |
+-----------------------+-------+
1 row in set (0.01 sec)

Create a two-factor authentication user. The following creation fails because 2 internal storage plugins cannot be used at the same time.

mysql> create user 'wei3'@'localhost' identified by '123' and identified with mysql_native_password by '123';
ERROR 4052 (HY000): Invalid plugin "mysql_native_password" specified as 2 factor during "CREATE USER".

Then let's install a pluggable plug-in Socket Peer-Credential

mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%socket%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| auth_socket | ACTIVE        |
+-------------+---------------+
1 row in set (0.00 sec)

Create another two-factor authentication user

mysql> create user 'wei4'@'localhost' identified by '123' and identified with auth_socket as 'root';
Query OK, 0 rows affected (0.05 sec)
 
mysql> select user,host,plugin,User_attributes from mysql.user where user='wei4';
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user | host      | plugin                | User_attributes                                                                                                                              |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| wei4 | localhost | caching_sha2_password | {"multi_factor_authentication": [{"plugin": "auth_socket", "passwordless": 0, "authentication_string": "root", "requires_registration": 0}]} |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

After the creation is successful, the user 'wei4'@'localhost' must provide the correct password and the login user of the local host is root, and the verification will pass.

Let's try it out, provide the correct password 123 as the host root user, and log in successfully.

root@ubuntu:~# docker exec -it mysql-1 bash
root@1d118873f98e:/# mysql -uwei4 --password1=123 --password2
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.27 MySQL Community Server - GPL
 
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>

Modify it, and change the host login user required by 'wei4'@'localhost' to wei4

mysql> alter user 'wei4'@'localhost' modify 2 factor identified with auth_socket as 'wei4';
Query OK, 0 rows affected (0.16 sec)
 
mysql> select user,host,plugin,User_attributes from mysql.user where user='wei4';
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user | host      | plugin                | User_attributes                                                                                                                              |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| wei4 | localhost | caching_sha2_password | {"multi_factor_authentication": [{"plugin": "auth_socket", "passwordless": 0, "authentication_string": "wei4", "requires_registration": 0}]} |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As the host root user again, provide the correct password 123, the login fails

root@ubuntu:~# docker exec -it mysql-1 bash 
root@1d118873f98e:/# mysql -uwei4 --password1=123 --password2 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Enter password: 
ERROR 1698 (28000): Access denied for user 'wei4'@'localhost'  
root@1d118873f98e:/#

Therefore, it can be determined that the two-factor authentication mechanism is effective. MySQL 8.0.27 can set up to three-factor authentication for one user, which will not be shown here.

In a nutshell, the existing password-password authentication is very suitable for website or application access, but may not be secure enough in certain situations such as online financial transactions. The introduction of the multi-factor authentication (MFA) function can improve the security of the database system to a certain extent.

References:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_authentication_policy