Authentication Plugin - mysql_native_password

You are viewing an old version of this article. View the current version here.

The mysql_native_password authentication plugin is the default authentication plugin that will be used for an account created when no authentication plugin is explicitly mentioned old_passwords=0 is set. It uses the password hashing algorithm introduced in MySQL 4.1, which is also used by the PASSWORD() function when old_passwords=0 is set.

Installing the Plugin

The mysql_native_password authentication plugin is statically linked into the server, so no installation is necessary.

Creating Users

The easiest way to create a user with the mysql_native_password authentication plugin is to make sure that old_passwords=0 is set, and then create a user account that does not specify an authentication plugin. For example:

SET old_passwords=0;
CREATE USER username@hostname IDENTIFIED BY 'mariadb';

If SQL_MODE does not have NO_AUTO_CREATE_USER set, then you can also create the user via GRANT. For example:

GRANT SELECT ON db.* TO username@hostname IDENTIFIED BY 'mariadb';

Similar to all other authentication plugins, you could also specify the name of the plugin in the IDENTIFIED VIA clause while providing the password hash as the USING clause. For example:

SET old_passwords=0;
Query OK, 0 rows affected (0.000 sec)

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+
1 row in set (0.000 sec)

CREATE USER username@hostname IDENTIFIED VIA mysql_native_password USING '*54958E764CE10E50764C2EECBB71D01F08549980';
Query OK, 0 rows affected (0.000 sec)

Known issues

Mismatches between Password and authentication_string Columns

For compatibility reasons,the mysql_native_password authentication plugin tries to read the password hash from both the Password and authentication_string columns in the mysql.user table. This has caused issues in the past if one of the columns had a different value than the other.

Starting with MariaDB 10.2.19 and MariaDB 10.3.11, CREATE USER, ALTER USER, GRANT, and SET PASSWORD will set both columns whenever an account's password is changed.

See MDEV-16774 for more information.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.