MySQL :: MySQL 8.4 Reference Manual :: 8.2.17 Pluggable Authentication (original) (raw)

8.2.17 Pluggable 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 system table. The server then authenticates the client, determining from the account row which authentication plugin applies to the client:

Pluggable authentication enables these important capabilities:

Note

If you start the server with the--skip-grant-tables option, authentication plugins are not used even if loaded because the server performs no client authentication and permits any client to connect. Because this is insecure, if the server is started with the --skip-grant-tables option, it also disables remote connections by enablingskip_networking.

Available Authentication Plugins

MySQL 8.4 provides these authentication plugins:

Note

For information about current restrictions on the use of pluggable authentication, including which connectors support which plugins, seeRestrictions on Pluggable Authentication.

Third-party connector developers should read that section to determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.

If you are interested in writing your own authentication plugins, see Writing Authentication Plugins.

The Default Authentication Plugin

The CREATE USER andALTER USER statements have syntax for specifying how an account authenticates. Some forms of this syntax do not explicitly name an authentication plugin (there is no IDENTIFIED WITH clause). For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

In such cases, the server assigns the default authentication plugin to the account. MySQL 8.4 supports multifactor authentication and up to three clauses that specify how an account authenticates. The rules that determine the default authentication plugin for authentication methods that name no plugin are factor-specific:

CREATE USER 'wei'@'localhost' IDENTIFIED BY 'password'  
  AND IDENTIFIED WITH authentication_ldap_simple;

In the same way, this example creates a three-factor authentication account: 

CREATE USER 'mateo'@'localhost' IDENTIFIED BY 'password'  
  AND IDENTIFIED WITH authentication_ldap_simple  
  AND IDENTIFIED WITH authentication_fido;

You can use SHOW CREATE USER to view the applied authentication methods.

mysql> CREATE USER 'sofia'@'localhost' IDENTIFIED WITH authentication_ldap_simple  
       AND IDENTIFIED BY 'abc';  
ERROR 1524 (HY000): Plugin '' is not loaded  
mysql> CREATE USER 'sofia'@'localhost' IDENTIFIED WITH authentication_ldap_simple  
       AND IDENTIFIED BY 'abc';  
ERROR 1524 (HY000): Plugin '*' is not loaded

Authentication Plugin Usage

This section provides general instructions for installing and using authentication plugins. For instructions specific to a given plugin, see the section that describes that plugin underSection 8.4.1, “Authentication Plugins”.

In general, pluggable authentication uses a pair of corresponding plugins on the server and client sides, so you use a given authentication method like this:

In the case that an account uses an authentication method that is the default for both the server and the client program, the server need not communicate to the client which client-side plugin to use, and a round trip in client/server negotiation can be avoided.

For standard MySQL clients such as mysql andmysqladmin, the--default-auth=plugin_name option can be specified on the command line as a hint about which client-side plugin the program can expect to use, although the server overrides this if the server-side plugin associated with the user account requires a different client-side plugin.

If the client program does not find the client-side plugin library file, specify a--plugin-dir=dir_name option to indicate the plugin library directory location.

Authentication Plugin Client/Server Compatibility

Pluggable authentication enables flexibility in the choice of authentication methods for MySQL accounts, but in some cases client connections cannot be established due to authentication plugin incompatibility between the client and server.

The general compatibility principle for a successful client connection to a given account on a given server is that the client and server both must support the authentication_method_ required by the account. Because authentication methods are implemented by authentication plugins, the client and server both must support the authentication plugin required by the account.

Authentication plugin incompatibilities can arise in various ways. Examples:

In general, these compatibility issues do not arise when connections are made between a client and server from the same MySQL distribution. When connections are made between a client and server from different MySQL series, issues can arise. These issues are inherent in the development process when MySQL introduces new authentication plugins or removes old ones. To minimize the potential for incompatibilities, regularly upgrade the server, clients, and connectors on a timely basis.

Authentication Plugin Connector-Writing Considerations

Various implementations of the MySQL client/server protocol exist. The libmysqlclient C API client library is one implementation. Some MySQL connectors (typically those not written in C) provide their own implementation. However, not all protocol implementations handle plugin authentication the same way. This section describes an authentication issue that protocol implementors should take into account.

In the client/server protocol, the server tells connecting clients which authentication plugin it considers the default. If the protocol implementation used by the client tries to load the default plugin and that plugin does not exist on the client side, the load operation fails. This is an unnecessary failure if the default plugin is not the plugin actually required by the account to which the client is trying to connect.

If a client/server protocol implementation does not have its own notion of default authentication plugin and always tries to load the default plugin specified by the server, it fails with an error if that plugin is not available.

To avoid this problem, the protocol implementation used by the client should have its own default plugin and should use it as its first choice (or, alternatively, fall back to this default in case of failure to load the default plugin specified by the server). Example:

Restrictions on Pluggable Authentication

The first part of this section describes general restrictions on the applicability of the pluggable authentication framework described at Section 8.2.17, “Pluggable Authentication”. The second part describes how third-party connector developers can determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.

The term “native authentication” used here refers to authentication against passwords stored in themysql.user system table. This is the same authentication method provided by older MySQL servers, before pluggable authentication was implemented. “Windows native authentication” refers to authentication using the credentials of a user who has already logged in to Windows, as implemented by the Windows Native Authentication plugin (“Windows plugin” for short).

General Pluggable Authentication Restrictions
Pluggable Authentication and Third-Party Connectors

Third-party connector developers can use the following guidelines to determine readiness of a connector to take advantage of pluggable authentication capabilities and what steps to take to become more compliant: