Using MySQL 8.0.27 Multi‑Factor Authentication with the authentication_policy Variable

MySQL 8.0.27 introduced multi‑factor authentication (MFA) and a new system variable authentication_policy to manage user authentication methods.

Prior to 8.0.27, CREATE USER could specify only one authentication plugin, and if omitted the server used the value of default_authentication_plugin , which could be mysql_native_password , sha256_password , or caching_sha2_password .

The authentication_policy variable holds three comma‑separated elements. The first element may be * (allow any plugin) or a specific built‑in plugin; the second and third elements must be empty, indicating optional plugins.

Example of checking default values in a Docker container:

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

Then:

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 | *,, | +-----------------------+-------+ mysql> show global variables like 'default_authentication_plugin'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+

Creating a user without specifying a plugin automatically uses the default plugin:

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 | +------+-----------+-----------------------+

Specifying a plugin explicitly works as expected:

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 | +------+-----------+-----------------------+

Changing authentication_policy so the first element is sha256_password makes that plugin the default for new users:

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,, | +-----------------------+-------------------+

Creating another user after the change:

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 | +------+-----------+-----------------+

Restoring the policy to its default *,, and attempting to create a user with two internal plugins fails:

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".

Installing an external plugin auth_socket enables a true two‑factor setup:

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 |

Creating a user with password plus auth_socket as the second factor stores the configuration in User_attributes :

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}]} |

Login as the host root user with the correct password succeeds, confirming the MFA works.

After altering the second factor to require the host user wei4 instead of root , the same login attempt fails, demonstrating that both factors are enforced:

mysql> alter user 'wei4'@'localhost' modify 2 factor identified with auth_socket as 'wei4'; Query OK, 0 rows affected (0.16 sec)

MySQL 8.0.27 can support up to three authentication factors per account, and the MFA feature significantly enhances database security, especially for sensitive workloads such as online financial transactions.

Reference: MySQL Documentation – authentication_policy