MySQL Authentication – What is mysql_native_password?

authenticationmysql-5.7plugins

I was trying to set password for root. When I run:

mysql> SELECT * from mysql.user where User="root";

It shows:

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password |                       | N                | 2018-06-13 15:11:59   |              NULL | N              |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+

This Document says that,

The mysql_native_password native authentication plugin is backward
compatible. Older clients that do not support authentication plugins
do use the native authentication protocol, so they can connect to
servers that support pluggable authentication.

But technically I'm not getting much. Does it have to do anything with root user password?

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

and

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';

Does it make any difference?

Best Answer

For a long time, MySQL has supported different authentication plugins, basically programable pieces of code to demonstrate that a mysql accounts is onwed by whever claims so.

The original way to do that is to setup a password, hash it in a particular way, and store it on the mysql.user table. However, it is not the only way you can authenticate, for example:

  • The unix socket authentication allows login to uses on the local machine with the same unix name than the mysql account. That is commonly used for admin accounts for things like monitoring or other tasks without needing to maintain a password. It has that name because it only works with socket connections (not remotelly)
  • A PAM autentication plugin allows to set up, for example, an LDAP backed system and use that to authenticate (nice to integrate it into an existing organization)
  • The latest versions of mysql (8.0) use a less trivial authentication method (caching_sha2_password), which in theory is more secure (I am not saying it is or it is not, but certainly the default "native" one was quite bad), but may require updates of client drivers and applications, so you can always revert to the older one for compatibility reasons.

On the "Enterprise" world, there is many times special needs for very special authentication methods, beyond a user and password.

Basically, mysql_native_password is the traditional method to authenticate- it is not very secure (it uses just a hash of the password), but it is compatible with older drivers. If you are going to start a new mysql service, you probably want to use the new plugin from the start (and TLS). If you have special needs, you can use other method- you can even program one if you have certain special needs).

You can chose a different method for each individual user- for example, your normal applications can use mysql_native_password or the new sha2 one, but you can make sure your admin accounts use a 2-factor authentication token, and unix_socket for a monitoring user gathering statistics on the mysql server. Those other authentication methods may or may not use the password field on the mysql.user table, like the native one does (they may store the password elswhere, or they may not even have a concept of a password!).

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

and

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';

Are essentially the same, mysql_native_password is normally the default authentication method. With WITH you can decide which method to use. For example, if you use GRANT USAGE ON *.* TO root@localhost IDENTIFIED WITH socket_auth, you are setting that user to use unix socket authentication. MariaDB uses a slightly different syntax: VIA unix_socket. Running those command mainly results in an update of the mysql.user table.

Note ALTER / GRANT works automatically on next user login, while UPDATEing directly the mysql.user table may require a FLUSH PRIVILEGES, and has some issues on certain scenarios (Galera, etc.).

Related Question