MySQL Secure Deployment Guide / Enabling Authentication

Chapter 11 Enabling Authentication

When a client connects to the MySQL server, the server uses the user name provided by the client and the client host to select the account row from the mysql.user table. The server authenticates the client, determining from the account row which authentication plugin applies to the client. The server invokes that plugin to authenticate the user, and the plugin returns a status to the server indicating whether the user is permitted to connect.

This deployment uses the caching_sha2_password and auth_socket authentication plugins for user authentication.

Caching SHA-2 Authentication

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password, which was the default in MySQL 5.7.

The server-side caching_sha2_password plugin is built into the server and it does not need to be loaded explicitly. Therefore, no server-side configuration is required to use it.

The client-side plugin is built into the libmysqlclient library (MySQL 8.0.4 and higher) and is available to any program linked against libmysqlclient. For a list of compatible clients and connectors, see caching_sha2_password-Compatible Clients and Connectors.

The caching_sha2_password plugin uses a SHA-2 algorithm that provides 256-bit password encryption. Passwords are salted with random data before SHA-256 transformations are applied. The resulting hashed values are stored in the mysql.user table. Using a salt helps defend against dictionary attacks on stored password hash values.

The caching_sha2_password plugin requires a secure connection (made using TLS credentials, a Unix socket file, or shared memory) or an unencrypted connection that supports password exchange using an RSA key pair. However, the performance cost associated with a secure connection is mitigated by the caching capability of the plugin. Once a hashed password is cached in memory, authentication can be performed over an unencrypted channel using a SHA256-based challenge-response mechanism, which means faster authentication for users that have connected previously.

Note

Changing a password, renaming a user, and FLUSH PRIVILEGES operations invalidate cached password hash values. When a cached password hash value is invalidated, a secure connection is required again for password exchange.

User accounts created later in this deployment use caching_sha2_password authentication. See Chapter 13, Creating User Accounts. TLS and RSA key pair connection methods are demonstrated in Chapter 14, Connecting to the Server.

For additional information about the caching_sha2_password plugin, see Caching SHA-2 Pluggable Authentication.

Socket Peer-Credential Authentication

This section describes how to enable the server-side auth_socket authentication plugin, which authenticates clients that connect to the MySQL server from the local host through the Unix socket file. auth_socket authentication is well suited to server administration user accounts for which access must be tightly restricted.

The auth_socket plugin checks whether the socket user name matches the MySQL user name specified by the client program to the server. If the names do not match, the plugin also checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user table row. If a match is found, the plugin permits the connection.

For example, suppose that a MySQL account is created for a user named valerie who is to be authenticated by the auth_socket plugin for connections from the local host through the socket file:

CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;

If a user on the local host with a login name of stefanie invokes mysql with the option --user=valerie to connect through the socket file, the server uses auth_socket to authenticate the client. The plugin determines that the --user option value (valerie) differs from the client user's name (stephanie) and refuses the connection. If a user named valerie tries the same thing, the plugin finds that the user name and the MySQL user name are both valerie and permits the connection. However, the plugin refuses the connection even for valerie if the connection is made using a different protocol, such as TCP/IP.

Users authenticated by the auth_socket need not specify a password when connecting to the server. However, users authenticated by the auth_socket plugin are restricted from connecting remotely; they can only connect from the local host through the Unix socket file.

To install the server-side auth_socket plugin:

Add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):
```
plugin-load-add=auth_socket.so
auth_socket=FORCE_PLUS_PERMANENT
```
- plugin-load-add=auth_socket.so
  
  Loads the auth_socket.so plugin library each time the server is started.
- auth_socket=FORCE_PLUS_PERMANENT
  
  Prevents the server from running without the auth_socket plugin, and server startup fails if the plugin does not initialize successfully.

To verify plugin installation, restart the server and examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement:

$> systemctl restart mysqld

$> cd /usr/local/mysql 
$> bin/mysqladmin -u root -p version
Enter password: (enter root password here)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%socket%';
+-------------------+---------------+
| PLUGIN_NAME       | PLUGIN_STATUS |
+-------------------+---------------+
| auth_socket       | ACTIVE        |
+-------------------+---------------+

Optionally, modify the MySQL root user account to use the auth_socket plugin for authentication:
```
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
```

To verify that the root@localhost account is using the auth_socket plugin, issue this query:

mysql> SELECT user, plugin FROM mysql.user WHERE user IN ('root')\G
*************************** 1. row ***************************
  user: root
plugin: auth_socket

To verify that the auth_socket plugin works, log in to the MySQL server host as the operating system root user and then connect to the MySQL server locally as the MySQL root user. You should be able to connect without specifying a password.
```
$> cd /usr/local/mysql 
$> bin/mysql -u root
```

For more information about the auth_socket plugin, see Socket Peer-Credential Pluggable Authentication.

PREV HOME UP NEXT