MySQL 5.7 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 appropriate account row from the mysql.user table. The server then 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 these authentication plugins:

  • sha256_password authentication plugin: Performs authentication using SHA-256 password hashing. This encryption is stronger than that available with MySQL native authentication.

  • auth_socket authentication plugin: Authenticates clients that connect from the local host through the Unix socket file.

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. 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.

The auth_socket plugin uses the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can only be used on systems that support the SO_PEERCRED option, such as Linux.

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.

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.

The features of auth_socket plugin authentication are well suited to server administration user accounts for which access must be tightly restricted.

To install the server-side auth_socket plugin:

  1. 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.

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

    shell> systemctl restart mysqld
    shell> cd /usr/local/mysql 
    shell> 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        |
    +-------------------+---------------+
  3. Optionally, modify the MySQL root user account to use the auth_socket plugin for authentication:

    mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
  4. 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
  5. To verify that the auth_socket plugin works, login 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.

    shell> cd /usr/local/mysql 
    shell> bin/mysql -u root

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


User Comments
Sign Up Login You must be logged in to post a comment.