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.

By default, MySQL uses the built-in mysql_native_password authentication plugin, which performs authentication using the native password hashing method. For greater security, this deployment uses the sha256_password and auth_socket authentication plugins for user authentication.

SHA-256 Authentication

When a user account is configured to authenticate using the sha256_password plugin, the server uses the sha256_password plugin to encrypt the user password using SHA-256 password hashing. This encryption is more secure than that available with MySQL native authentication. The password hash is stored in the plugin and in the authentication_string column of the mysql.user system table.

The server-side sha256_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 the sha256_password plugin.

To use the sha256_password plugin for new user accounts, you can specify the sha256_password plugin when creating new user accounts or you can configure the sha256_password plugin as the default authentication plugin using the default_authentication_plugin configuration option. Later in this deployment, the sha256_password plugin is specified when creating a user account. See Chapter 13, Creating User Accounts.

For more information about the sha256_password plugin, see SHA-256 Pluggable Authentication. For a discussion of the advantages and disadvantages of the sha256_password plugin, see MySQL Server Blog: Protecting MySQL Passwords With the sha256_password Plugin.

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