Authentication Plugin - mysql_native_password
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
mysql_native_password authentication plugin is statically linked into the server, so no installation is necessary.
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. For example:
SET old_passwords=0; CREATE USER username@hostname IDENTIFIED BY 'mariadb';
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. For example:
SET old_passwords=0; SELECT PASSWORD('mariadb'); +-------------------------------------------+ | PASSWORD('mariadb') | +-------------------------------------------+ | *54958E764CE10E50764C2EECBB71D01F08549980 | +-------------------------------------------+ CREATE USER username@hostname IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980';
CREATE USER username@hostname IDENTIFIED VIA mysql_native_password USING '*54958E764CE10E50764C2EECBB71D01F08549980';
Changing User Passwords
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. For example:
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:
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. For example:
mysql --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=alice
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 client authentication plugin hashes the password before sending it to the server.
Support in Client Libraries
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
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.
See MDEV-16774 for more information.
- ed25519 secure connection plugin
- History of MySQL and MariaDB authentication protocols