MySQL supports a number of authentication mechanisms that are available through pluggable authentication. MySQL uses a number of algorithms to encrypt passwords stored in the user table:
- The mysql_native_password plugin implements the standard password format, a 41-byte-wide hash.
- The mysql_old_password plugin implements an older format that is less secure, being 16 bytes wide.
- The sha256_password plugin implements the SHA-256 hashing algorithm widely used in secure computing.
The value of the old_passwords system variable specifies the algorithm that the PASSWORD() function uses to create passwords, as follows:
- 0: The standard algorithm, as used since MySQL 4.1.1
- 1: The old algorithm, as used before MySQL 4.1.1
- 2: The SHA-256 algorithm
Start the server with the default-authentication-plugin option set to sha256_password to use SHA-256 passwords for all new users, or use CREATE USER with the IDENTIFIED WITH sha256_password clause to specify SHA-256 passwords for a specific user.
Client-Side Cleartext Authentication Plugin
Some authentication methods, such as PAM (Pluggable Authentication Modules) authentication, require the client to send a plain-text password to the server so that the server can process the password in its normal form. The mysql_clear_password plugin enables this behavior.
The MySQL client library includes a built-in Cleartext Authentication plugin, mysql_clear_password. The plugin is used to send a plain text password to the server – The password is usually hashed. The plugin is Enabled by the LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN environment variable and specifying –enable-cleartext-plugin when running MySQL client applications such as mysql and mysqladmin. The MYSQL_ENABLE_CLEARTEXT_PLUGIN option of the mysql_options() C API function can also be used to enable the plugin.
Loadable Authentication Plugins
In addition to default built-in plugins, MySQL provides several loadable plugins:
- The Test Authentication plugin (test_plugin_server) authenticates using native or old password authentication, and is intended for testing and development purposes.
- The Socket Peer-Credential (auth_socket) plugin allows users to connect via the UNIX socket file only if their Linux username matches their MySQL account.
- The PAM authentication plugin (authentication_pam) is an Enterprise Edition plugin that allows you to log in using an external authentication mechanism. MySQL does not store your password, but uses the UNIX PAM (Pluggable Authentication Modules) mechanism to transmit the client’s provided username and password for authentication by the operating system.
You can develop your own authentication plugins. The Test Authentication plugin is intended for use by developers to create their own plugins; its source code is available as part of the MySQL source code distribution. Load a loadable authentication plugin by starting the server with the plugin-load option at the command line or in the my.cnf file, as in the following example:
[mysqld] plugin-load=authentication_pam.so
PAM Authentication Plugin
The PAM Authentication plugin is an Enterprise Edition plugin that authenticates MySQL accounts against the operating system. PAM defines services that configure authentication. These are stored in /etc/pam.d. PAM looks in /etc/pam.d for services that it authenticates. For example, to create a PAM service called mysql-pam, create the file /etc/pam.d/mysql-pam with the following content:
#%PAM-1.0 auth include password-auth account include password-auth
In addition to MySQL authentication, PAM integrates with other authentication methods including LDAP and Active Directory, so you can use PAM to authenticate many services (including MySQL) against a single store in your network. To create a MySQL user that maps directly to an operating system user, use a statement such as the following:
CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS 'mysql-pam';
When bob logs in, MySQL passes the username and password that it receives from the client to PAM, which authenticates against the operating system. The client must send the password in clear text. Enable the client-side Cleartext Authentication plugin to serve this purpose:
shell> mysql --enable-cleartext-plugin -ubob -p Enter password: bob’s_OS_password
To enable group-based logins with the PAM Authentication plugin, create a PAM-enabled anonymous proxy account that matches no users, but specifies a set of mappings from operating system group to MySQL user:
CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql-pam, sales=m_sales, finance=m_finance';
The preceding example assumes that you have sales and finance operating system groups and m_sales and m_finance MySQL users. You must then grant the PROXY privilege to the anonymous proxy account, giving it rights to log in as the m_sales and m_finance MySQL users:
GRANT PROXY ON m_sales@localhost TO ''@''; GRANT PROXY ON m_finance@localhost TO ''@'';
Users who are members of the sales and finance groups can now provide their operating system credentials at the mysql command-line prompt, which logs them in as the m_sales or m_finance MySQL users, respectively, giving them all of the privileges granted to those accounts. For example, if peter is a member of the sales group, he could log in as follows:
shell> mysql --enable-cleartext-plugin -upeter -p Enter password: peter’s_OS_password Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql> SELECT CURRENT_USER(); +-------------------+ | CURRENT_USER() | +-------------------+ | m_sales@localhost | +-------------------+ 1 row in set (0.01 sec)