MariaDB Server supports different authentication methods just like MySQL.
Depending on your installation, the number of available and active authentication plugins can vary.
But you should always have those 3 by default:
MariaDB > select plugin_name from information_schema.plugins
where plugin_type like 'authentication';
+-----------------------+
| plugin_name |
+-----------------------+
| mysql_native_password |
| mysql_old_password |
| unix_socket |
+-----------------------+
3 rows in set (0.002 sec)If we compare with MySQL 9.7, we can see only 2 default authentication plugins:
mysql > select plugin_name from information_schema.plugins
where plugin_type like 'authentication';
+-----------------------+
| plugin_name |
+-----------------------+
| sha256_password |
| caching_sha2_password |
+-----------------------+
2 rows in set (0.005 sec)About caching_sha2_password module
In some packaging, the caching_sha2_password auhentication plugin is statically built with the server, in some others, it’s a module you need to load.
For example, if we use the MariaDB Server from a binary tar.gz like mariadb-13.0.0-linux-systemd-x86_64.tar.gz, the module is present:
MariaDB > select * from information_schema.plugins
where plugin_name like 'caching_sha2_password'\G
*************************** 1. row ***************************
PLUGIN_NAME: caching_sha2_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 2.3
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Oracle Corporation, Sergei Golubchik
PLUGIN_DESCRIPTION: MySQL-compatible SHA2 authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
PLUGIN_MATURITY: Gamma
PLUGIN_AUTH_VERSION: 1.0
1 row in set (0.006 sec)As you can see, the plugin is loaded, but the plugin_library is NULL. This means it’s statically built with the server.
On the other hand, when using MariaDB on Fedora, for example, the module needs to be loaded. It doesn’t appear on the list of installed plugins:
MariaDB > select plugin_name, load_option from information_schema.plugins
where plugin_type like 'authentication';
+-----------------------+-------------+
| plugin_name | load_option |
+-----------------------+-------------+
| mysql_native_password | FORCE |
| mysql_old_password | FORCE |
| unix_socket | ON |
+-----------------------+-------------+
3 rows in set (0.000 sec)This means that we first need to load the plugin. Pay attention, the plugin to load is called auth_mysql_sha2 but its name when loaded is indeed caching_sha2_password.
MariaDB [(none)]> INSTALL SONAME 'auth_mysql_sha2';
Query OK, 0 rows affected (0.003 sec)
MariaDB > select * from information_schema.plugins
where plugin_name like 'caching_sha2_password'\G
*************************** 1. row ***************************
PLUGIN_NAME: caching_sha2_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 2.3
PLUGIN_LIBRARY: auth_mysql_sha2.so
PLUGIN_LIBRARY_VERSION: 1.15
PLUGIN_AUTHOR: Oracle Corporation, Sergei Golubchik
PLUGIN_DESCRIPTION: MySQL-compatible SHA2 authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
PLUGIN_MATURITY: Gamma
PLUGIN_AUTH_VERSION: 1.0
1 row in set (0.002 sec)Now, we can see that the module is indeed loaded.
You can find information directly from the dedicated documentation page.
Create a user authenticating with caching_sha2_password
In MariaDB, to create a user who will authenticate with the caching_sha2_password, you need to use a different syntax than in MySQL.
If we want to create a user test_user@% using password as password, in MySQL we would create it like this:
mysql > create user test_user identified
with 'caching_sha2_password' by 'password';
Query OK, 0 rows affected (0.010 sec)
mysql> show create user test_user\G
*************************** 1. row ***************************
CREATE USER for test_user@%: CREATE USER `test_user`@`%` IDENTIFIED WITH
'caching_sha2_password' AS '$A$00A$\ZV7v]4bw l Jmc0G1aWVtSLvZBSjOBadZOn/
fogYBzGktDZYKgrY2Q7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE
CURRENT DEFAULTIn MariaDB, if we want to create the same user with the same password, the syntax is a bit different:
MariaDB > create user test_user identified
with 'caching_sha2_password' using password('fred');
Query OK, 0 rows affected (0.011 sec)
MariaDB > show create user test_user\G
*************************** 1. row ***************************
CREATE USER for test_user@%: CREATE USER `test_user`@`%` IDENTIFIED VIA
caching_sha2_password USING '$A$005$?
EF.hb68E)SOQF2<,5NCWY8icdW1TAdhZN0QgDxaGWwHAFWSroBGVcipF8SPBg5'
1 row in set (0.001 sec)Changing the authentication method of an existing user
This is also not clear from the documentation on how to modify the authentication method of an existing user.
So let’s consider this user:
MariaDB > show create user existing_user\G
*************************** 1. row ***************************
CREATE USER for existing_user@%: CREATE USER `existing_user`@`%`
IDENTIFIED BY PASSWORD '*6C69D17939B2C1D04E17A96F9B29B284832979B7'
1 row in set (0.001 sec)
MariaDB > select host,user, plugin from mysql.user
where user like 'existing_user';
+------+---------------+-----------------------+
| Host | User | plugin |
+------+---------------+-----------------------+
| % | existing_user | mysql_native_password |
+------+---------------+-----------------------+
1 row in set (0.002 sec)Our existing user is using mysql_native_password as authentication method. If we want to change it to caching_sha2_password we use the following statement:
Please keep in mind that you need to provide a password; otherwise, the statement won’t fail, and the user will be able to connect without one!
MariaDB > alter user existing_user identified
with 'caching_sha2_password' using password('fred');
Query OK, 0 rows affected (0.007 sec)Changing the password
Once the authentication method is changed, password changes always use the same syntax.
Changing the password for the current user:
MariaDB > set password=password('donenow');
Query OK, 0 rows affected (0.010 sec)And for another user, this is the syntax:
MariaDB > set password for test_user = password('mynewpass');
Query OK, 0 rows affected (0.006 sec)This is documented in the reference manual.
Transfer a user from MySQL to MariaDB
Now, let’s imagine that after migrating to MariaDB, we want to copy a user and their credentials from MySQL.
mysql > show create user mysql_user\G
*************************** 1. row ***************************
CREATE USER for mysql_user@%: CREATE USER `mysql_user`@`%`
IDENTIFIED WITH 'caching_sha2_password' AS
'$A$00A$\"z3Ex=\'RdXk;ta#\nHhTOTKS3p2kmNQ0qDxkpBAPcixeV.gST0v7AgWVQkl5'
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY
DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULTOf course, we cannot copy this statement directly into MariaDB; it will fail:
MariaDB > CREATE USER `mysql_user`@`%` IDENTIFIED WITH
'caching_sha2_password' AS
'$A$00A$\"z3Ex=\'RdXk;ta#\nHhTOTKS3p2kmNQ0qDxkpBAPcixeV.gST0v7AgWVQkl5';
ERROR 1372 (HY000): Password hash should be 70 characters longIn fact, there is no easy or recommended way other than manually replacing the password.
If you really need to use the same password and you don’t know it, this is the workaround, but use it with caution.
These are the steps:
- get the authentication string as hexadecimal in MySQL
- unhex is into a variable in MariaDB
- create the user without a password or with a temporary one
- manually modify a table you should never touch
Step1
In the MySQL client, we will display the authentication string in hexadecimal like this:
mysql > set print_identified_with_as_hex=on;
Query OK, 0 rows affected (0.000 sec)
mysql > show create user mysql_user\G
*************************** 1. row ***************************
CREATE USER for mysql_user@%: CREATE USER `mysql_user`@`%` IDENTIFIED WITH
'caching_sha2_password' AS
0x24412430304124227A334578013D27520F64586B063B746113230A4868544F544B533370
326B6D4E51307144786B7042415063697865562E67535430763741675756516B6C35
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY
DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.012 sec)Step 2
Now select the hexadecimal value just after the first two characters (0x) and paste it in the MariaDB client as the parameter of the UNHEX() function like this:
MariaDB > select unhex('24412430304124227A334578013D27520F64586B063B746113230A4868544F544B5
33370326B6D4E51307144786B7042415063697865562E67535430763741675756516B6C35'
) into @a;
Query OK, 1 row affected (0.000 sec)Step 3
We create the user in MariaDB using the caching_sha2_password and we don’t set a password or we use a temporary one:
MariaDB > CREATE USER `mysql_user`@`%` IDENTIFIED
WITH 'caching_sha2_password';
Query OK, 0 rows affected (0.003 sec)Step 4
This is the sensitive part of the process; please be careful not to destroy everything.
We will modify the authentication_string attribute of the Priv column in the mysql.global_priv table:
MariaDB > UPDATE mysql.global_priv
SET Priv = JSON_SET(Priv, '$.authentication_string', @a)
WHERE User = 'mysql_user';
Query OK, 1 row affected (0.013 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)We need to flush the privileges for it to take effect.
And now it’s done. We can test connecting to MariaDB using the same password as in MySQL, and it will work!
Conclusion
You should now be more familiar with the caching_sha2_password support in MariaDB and how to handle basic operations such as creating a user and modifying their password or authentication method.
Enjoy using MariaDB Server!