Authentication Plugin - mysql_native_password

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 and 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. This hashing algorithm is based on SHA-1.

It is not recommended to use the mysql_native_password authentication plugin for new installations that require high password security. If someone is able to both listen to the connection protocol and get a copy of the mysql.user table, then the person would be able to use this information to connect to the MariaDB server. The ed25519 authentication plugin is a more modern authentication plugin that provides simple password authentication using a more secure algorithm.

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 account with the mysql_native_password authentication plugin is to make sure that old_passwords=0 is set, and then create a user account via CREATE USER that does not specify an authentication plugin, but does specify a password via the IDENTIFIED BY clause:

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 account via GRANT:

SET old_passwords=0;
GRANT SELECT ON db.* TO username@hostname IDENTIFIED BY 'mariadb';

You can also create the user account by providing a password hash via the IDENTIFIED BY PASSWORD clause, and MariaDB will validate whether the password hash is one that is compatible with mysql_native_password:

SET old_passwords=0;

SELECT PASSWORD('mariadb');
+-------------------------------------------+
| PASSWORD('mariadb')                       |
+-------------------------------------------+
| *54958E764CE10E50764C2EECBB71D01F08549980 |
+-------------------------------------------+

CREATE USER username@hostname
  IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';

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:

CREATE USER username@hostname
  IDENTIFIED VIA mysql_native_password USING '*54958E764CE10E50764C2EECBB71D01F08549980';

Changing User Passwords

You can change a user account's password with the SET PASSWORD statement while providing the plain-text password as an argument to the PASSWORD() function:

SET PASSWORD =  PASSWORD('new_secret')

You can also change the user account's password with the ALTER USER statement. You would have to make sure that old_passwords=0 is set, and then you would have to specify a password via the IDENTIFIED BY clause:

SET old_passwords=0;
ALTER USER username@hostname IDENTIFIED BY 'new_secret';

Client Authentication Plugins

For clients that use the libmysqlclient or MariaDB Connector/C libraries, MariaDB provides one client authentication plugin that is compatible with the mysql_native_password authentication plugin:

mysql_native_password

When connecting with a client or utility to a server as a user account that authenticates with the mysql_native_password authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:

mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=alice

However, the mysql_native_password client authentication plugin is generally statically linked into client libraries like libmysqlclient or MariaDB Connector/C, so this is not usually necessary.

`mysql_native_password`

The mysql_native_password client authentication plugin hashes the password before sending it to the server.

Support in Client Libraries

The mysql_native_password authentication plugin is one of the conventional authentication plugins, so all client libraries should support it.

Known Old Issues (Only Relevant for Old Installations)

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.

CREATE USER, ALTER USER, GRANT, and SET PASSWORD set the Password and authentication_string columns in the mysql.user table whenever an account's password is changed.

CREATE USER, ALTER USER, GRANT, and SET PASSWORD do not set the Password and authentication_string columns in the mysql.user table whenever an account's password is changed.