• Mysql：Authentication Plugins：插件式（权限）认证

    6.4 Security Components and Plugins

    6.4.1 Authentication Plugins
    6.4.2 The Connection-Control Plugins
    6.4.3 The Password Validation Component
    6.4.4 The MySQL Keyring
    6.4.5 MySQL Enterprise Audit
    6.4.6 The Audit Message Component
    6.4.7 MySQL Enterprise Firewall
    6.4.8 MySQL Enterprise Data Masking and De-Identification

    MySQL includes several components and plugins that implement security features:

    • Plugins for authenticating attempts by clients to connect to MySQL Server. Plugins are available for several authentication protocols. For general discussion of the authentication process, see Section 6.2.17, “Pluggable Authentication”. For characteristics of specific authentication plugins, see Section 6.4.1, “Authentication Plugins”.

    • A password-validation component for implementing password strength policies and assessing the strength of potential passwords. See Section 6.4.3, “The Password Validation Component”.

    • Keyring plugins that provide secure storage for sensitive information. See Section 6.4.4, “The MySQL Keyring”.

    • (MySQL Enterprise Edition only) MySQL Enterprise Audit, implemented using a server plugin, uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines. See Section 6.4.5, “MySQL Enterprise Audit”.

    • A user-defined function enables applications to add their own message events to the audit log. See Section 6.4.6, “The Audit Message Component”.

    • (MySQL Enterprise Edition only) MySQL Enterprise Firewall, an application-level firewall that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics. See Section 6.4.7, “MySQL Enterprise Firewall”.

    • (MySQL Enterprise Edition only) MySQL Enterprise Data Masking and De-Identification, implemented as a plugin library containing a plugin and a set of user-defined functions. Data masking hides sensitive information by replacing real values with substitutes. MySQL Enterprise Data Masking and De-Identification functions enable masking existing data using several methods such as obfuscation (removing identifying characteristics), generation of formatted random data, and data replacement or substitution. See Section 6.4.8, “MySQL Enterprise Data Masking and De-Identification”.

    6.4.1 Authentication Plugins

    6.4.1.1 Native Pluggable Authentication
    6.4.1.2 Caching SHA-2 Pluggable Authentication
    6.4.1.3 SHA-256 Pluggable Authentication
    6.4.1.4 Client-Side Cleartext Pluggable Authentication
    6.4.1.5 PAM Pluggable Authentication
    6.4.1.6 Windows Pluggable Authentication
    6.4.1.7 LDAP Pluggable Authentication
    6.4.1.8 No-Login Pluggable Authentication
    6.4.1.9 Socket Peer-Credential Pluggable Authentication
    6.4.1.10 Test Pluggable Authentication
    6.4.1.11 Pluggable Authentication System Variables

    The following sections describe pluggable authentication methods available in MySQL and the plugins that implement these methods. For general discussion of the authentication process, see Section 6.2.17, “Pluggable Authentication”.

    The default plugin is indicated by the value of the default_authentication_plugin system variable.

    6.4.1.1 Native Pluggable Authentication

    MySQL includes a mysql_native_password plugin that implements native authentication; that is, authentication based on the password hashing method in use from before the introduction of pluggable authentication.

    The following table shows the plugin names on the server and client sides.

    Table 6.12 Plugin and Library Names for Native Password Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin mysql_native_password
    Client-side plugin mysql_native_password
    Library file None (plugins are built in)

    The following sections provide installation and usage information specific to native pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

    Installing Native Pluggable Authentication

    The mysql_native_password plugin exists in server and client forms:

    • The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.

    • The client-side plugin is built into the libmysqlclient client library and is available to any program linked against libmysqlclient.

    Using Native Pluggable Authentication

    MySQL client programs use mysql_native_password by default. The --default-auth option can be used as a hint about which client-side plugin the program can expect to use: 

    shell> mysql --default-auth=mysql_native_password ...

    6.4.1.2 Caching SHA-2 Pluggable Authentication

    MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

    • sha256_password: Implements basic SHA-256 authentication.

    • caching_sha2_password: Implements SHA-256 authentication (like sha256_password), but uses caching on the server side for better performance and has additional features for wider applicability.

    This section describes the caching SHA-2 authentication plugin. For information about the original basic (noncaching) plugin, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”.

    Important

    In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

    Important

    To connect to the server using an account that authenticates with the caching_sha2_password plugin, you must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, the caching_sha2_password plugin uses MySQL's encryption capabilities. See Section 6.3, “Using Encrypted Connections”.

    Note

    In the name sha256_password, “sha256” refers to the 256-bit digest length the plugin uses for encryption. In the name caching_sha2_password, “sha2” refers more generally to the SHA-2 class of encryption algorithms, of which 256-bit encryption is one instance. The latter name choice leaves room for future expansion of possible digest lengths without changing the plugin name.

    The caching_sha2_password plugin has these advantages, compared to sha256_password:

    • On the server side, an in-memory cache enables faster reauthentication of users who have connected previously when they connect again.

    • RSA-based password exchange is available regardless of the SSL library against which MySQL is linked.

    • Support is provided for client connections that use the Unix socket-file and shared-memory protocols.

    The following table shows the plugin names on the server and client sides.

    Table 6.13 Plugin and Library Names for SHA-2 Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin caching_sha2_password
    Client-side plugin caching_sha2_password
    Library file None (plugins are built in)

    The following sections provide installation and usage information specific to caching SHA-2 pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

    Installing SHA-2 Pluggable Authentication

    The caching_sha2_password plugin exists in server and client forms:

    • The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.

    • The client-side plugin is built into the libmysqlclient client library and is available to any program linked against libmysqlclient.

    The server-side plugin uses the sha2_cache_cleaner audit plugin as a helper to perform password cache management. sha2_cache_cleaner, like caching_sha2_password, is built in and need not be installed.

    Using SHA-2 Pluggable Authentication

    To set up an account that uses the caching_sha2_password plugin for SHA-256 password hashing, use the following statement, where password is the desired account password: 

    CREATE USER 'sha2user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'password';

    The server assigns the caching_sha2_password plugin to the account and uses it to encrypt the password using SHA-256, storing those values in the plugin and authentication_string columns of the mysql.user system table.

    The preceding instructions do not assume that caching_sha2_password is the default authentication plugin. If caching_sha2_password is the default authentication plugin, a simpler CREATE USER syntax can be used.

    To start the server with the default authentication plugin set to caching_sha2_password, put these lines in the server option file:

    [mysqld]
default_authentication_plugin=caching_sha2_password

    That causes the caching_sha2_password plugin to be used by default for new accounts. As a result, it is possible to create the account and set its password without naming the plugin explicitly:

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

    Another consequence of setting default_authentication_plugin to caching_sha2_password is that, to use some other plugin for account creation, you must specify that plugin explicitly. For example, to use the mysql_native_password plugin, use this statement:

    CREATE USER 'nativeuser'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password';

    caching_sha2_password supports connections over secure transport. If you follow the RSA configuration procedure given later in this section, it also supports encrypted password exchange using RSA over unencrypted connections. RSA support has these characteristics:

    • On the server side, two system variables name the RSA private and public key-pair files: caching_sha2_password_private_key_path and caching_sha2_password_public_key_path. The database administrator must set these variables at server startup if the key files to use have names that differ from the system variable default values.

    • The server uses the caching_sha2_password_auto_generate_rsa_keys system variable to determine whether to automatically generate the RSA key-pair files. See Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.

    • The Caching_sha2_password_rsa_public_key status variable displays the RSA public key value used by the caching_sha2_password authentication plugin.

    • Clients that are in possession of the RSA public key can perform RSA key pair-based password exchange with the server during the connection process, as described later.

    • For connections by accounts that authenticate with caching_sha2_password and RSA key pair-based password exchange, the server does not send the RSA public key to clients by default. Clients can use a client-side copy of the required public key, or request the public key from the server.

      Use of a trusted local copy of the public key enables the client to avoid a round trip in the client/server protocol, and is more secure than requesting the public key from the server. On the other hand, requesting the public key from the server is more convenient (it requires no management of a client-side file) and may be acceptable in secure network environments.

      In all cases, if the option is given to specify a valid public key file, it takes precedence over the option to request the public key from the server.

    For clients that use the caching_sha2_password plugin, passwords are never exposed as cleartext when connecting to the server. How password transmission occurs depends on whether a secure connection or RSA encryption is used:

    • If the connection is secure, an RSA key pair is unnecessary and is not used. This applies to encrypted TCP connections that use TLS, as well as Unix socket-file and shared-memory connections. The password is sent as cleartext but cannot be snooped because the connection is secure.

    • If the connection is not secure, an RSA key pair is used. This applies to unencrypted TCP connections without TLS and named-pipe connections. RSA is used only for password exchange between client and server, to prevent password snooping. When the server receives the encrypted password, it decrypts it. A scramble is used in the encryption to prevent repeat attacks.

    To enable use of an RSA key pair for password exchange during the client connection process, use the following procedure:

    1. Create the RSA private and public key-pair files using the instructions in Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.

    2. If the private and public key files are located in the data directory and are named private_key.pem and public_key.pem (the default values of the caching_sha2_password_private_key_path and caching_sha2_password_public_key_path system variables), the server uses them automatically at startup.

      Otherwise, to name the key files explicitly, set the system variables to the key file names in the server option file. If the files are located in the server data directory, you need not specify their full path names:

      [mysqld]
caching_sha2_password_private_key_path=myprivkey.pem
caching_sha2_password_public_key_path=mypubkey.pem

      If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names:

      [mysqld]
caching_sha2_password_private_key_path=/usr/local/mysql/myprivkey.pem
caching_sha2_password_public_key_path=/usr/local/mysql/mypubkey.pem

    3. Restart the server, then connect to it and check the Caching_sha2_password_rsa_public_key status variable value. The value will differ from that shown here, but should be nonempty:

      mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'G
*************************** 1. row ***************************
Variable_name: Caching_sha2_password_rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----

      If the value is empty, the server found some problem with the key files. Check the error log for diagnostic information.

    After the server has been configured with the RSA key files, accounts that authenticate with the caching_sha2_password plugin have the option of using those key files to connect to the server. As mentioned previously, such accounts can use either a secure connection (in which case RSA is not used) or an unencrypted connection that performs password exchange using RSA. Suppose that an unencrypted connection is used. For example:

    shell> mysql --ssl-mode=DISABLED -u sha2user -p
Enter password: password

    For this connection attempt by sha2user, the server determines that caching_sha2_password is the appropriate authentication plugin and invokes it (because that was the plugin specified at CREATE USER time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. However, the server does not send the public key to the client, and the client provided no public key, so it cannot encrypt the password and the connection fails:

    ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password'
reported error: Authentication requires secure connection.

    To request the RSA public key from the server, specify the --get-server-public-key option:

    shell> mysql --ssl-mode=DISABLED -u sha2user -p --get-server-public-key
Enter password: password

    In this case, the server sends the RSA public key to the client, which uses it to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

    Alternatively, if the client has a file containing a local copy of the RSA public key required by the server, it can specify the file using the --server-public-key-path option:

    shell> mysql --ssl-mode=DISABLED -u sha2user -p --server-public-key-path=file_name
Enter password: password

    In this case, the client uses the public key to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

    The public key value in the file named by the --server-public-key-path option should be the same as the key value in the server-side file named by the caching_sha2_password_public_key_path system variable. If the key file contains a valid public key value but the value is incorrect, an access-denied error occurs. If the key file does not contain a valid public key, the client program cannot use it.

    Client users can obtain the RSA public key two ways:

    • The database administrator can provide a copy of the public key file.

    • A client user who can connect to the server some other way can use a SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key' statement and save the returned key value in a file.

    Cache Operation for SHA-2 Pluggable Authentication

    On the server side, the caching_sha2_password plugin uses an in-memory cache for faster authentication of clients who have connected previously. Entries consist of account-name/password-hash pairs. The cache works like this:

    1. When a client connects, caching_sha2_password checks whether the client and password match some cache entry. If so, authentication succeeds.

    2. If there is no matching cache entry, the plugin attempts to verify the client against the credentials in the mysql.user system table. If this succeeds, caching_sha2_password adds an entry for the client to the hash. Otherwise, authentication fails and the connection is rejected.

    In this way, when a client first connects, authentication against the mysql.user system table occurs. When the client connects subsequently, faster authentication against the cache occurs.

    Password cache operations other than adding entries are handled by the sha2_cache_cleaner audit plugin, which performs these actions on behalf of caching_sha2_password:

    • It clears the cache entry for any account that is renamed or dropped, or any account for which the credentials or authentication plugin are changed.

    • It empties the cache when the FLUSH PRIVILEGES statement is executed.

    • It empties the cache at server shutdown. (This means the cache is not persistent across server restarts.)

    Cache clearing operations affect the authentication requirements for subsequent client connections. For each user account, the first client connection for the user after any of the following operations must use a secure connection (made using TCP using TLS credentials, a Unix socket file, or shared memory) or RSA key pair-based password exchange:

    FLUSH PRIVILEGES clears the entire cache and affects all accounts that use the caching_sha2_password plugin. The other operations clear specific cache entries and affect only accounts that are part of the operation.

    Once the user authenticates successfully, the account is entered into the cache and subsequent connections do not require a secure connection or the RSA key pair, until another cache clearing event occurs that affects the account. (When the cache can be used, the server uses a challenge-response mechanism that does not use cleartext password transmission and does not require a secure connection.)

    6.4.1.3 SHA-256 Pluggable Authentication

    MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:

    • sha256_password: Implements basic SHA-256 authentication.

    • caching_sha2_password: Implements SHA-256 authentication (like sha256_password), but uses caching on the server side for better performance and has additional features for wider applicability.

    This section describes the original noncaching SHA-2 authentication plugin. For information about the caching plugin, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.

    Important

    In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

    Because caching_sha2_password is the default authentication plugin in MySQL 8.0 and provides a superset of the capabilities of the sha256_password authentication plugin, sha256_password is deprecated and will be removed in a future MySQL version. MySQL accounts that authenticate using sha256_password should be migrated to use caching_sha2_password instead.

    Important

    To connect to the server using an account that authenticates with the sha256_password plugin, you must use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, the sha256_password plugin uses MySQL's encryption capabilities. See Section 6.3, “Using Encrypted Connections”.

    Note

    In the name sha256_password, “sha256” refers to the 256-bit digest length the plugin uses for encryption. In the name caching_sha2_password, “sha2” refers more generally to the SHA-2 class of encryption algorithms, of which 256-bit encryption is one instance. The latter name choice leaves room for future expansion of possible digest lengths without changing the plugin name.

    The following table shows the plugin names on the server and client sides.

    Table 6.14 Plugin and Library Names for SHA-256 Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin sha256_password
    Client-side plugin sha256_password
    Library file None (plugins are built in)

    The following sections provide installation and usage information specific to SHA-256 pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

    Installing SHA-256 Pluggable Authentication

    The sha256_password plugin exists in server and client forms:

    • The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.

    • The client-side plugin is built into the libmysqlclient client library and is available to any program linked against libmysqlclient.

    Using SHA-256 Pluggable Authentication

    To set up an account that uses the sha256_password plugin for SHA-256 password hashing, use the following statement, where password is the desired account password: 

    CREATE USER 'sha256user'@'localhost'
IDENTIFIED WITH sha256_password BY 'password';

    The server assigns the sha256_password plugin to the account and uses it to encrypt the password using SHA-256, storing those values in the plugin and authentication_string columns of the mysql.user system table.

    The preceding instructions do not assume that sha256_password is the default authentication plugin. If sha256_password is the default authentication plugin, a simpler CREATE USER syntax can be used.

    To start the server with the default authentication plugin set to sha256_password, put these lines in the server option file:

    [mysqld]
default_authentication_plugin=sha256_password

    That causes the sha256_password plugin to be used by default for new accounts. As a result, it is possible to create the account and set its password without naming the plugin explicitly:

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

    Another consequence of setting default_authentication_plugin to sha256_password is that, to use some other plugin for account creation, you must specify that plugin explicitly. For example, to use the mysql_native_password plugin, use this statement:

    CREATE USER 'nativeuser'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'password';

    sha256_password supports connections over secure transport. sha256_password also supports encrypted password exchange using RSA over unencrypted connections if MySQL is compiled using OpenSSL, and the MySQL server to which you wish to connect is configured to support RSA (using the RSA configuration procedure given later in this section).

    RSA support has these characteristics:

    For clients that use the sha256_password plugin, passwords are never exposed as cleartext when connecting to the server. How password transmission occurs depends on whether a secure connection or RSA encryption is used:

    • If the connection is secure, an RSA key pair is unnecessary and is not used. This applies to encrypted connections that use TLS. The password is sent as cleartext but cannot be snooped because the connection is secure.

    • If the connection is not secure, and an RSA key pair is available, the connection remains unencrypted. This applies to unencrypted connections without TLS. RSA is used only for password exchange between client and server, to prevent password snooping. When the server receives the encrypted password, it decrypts it. A scramble is used in the encryption to prevent repeat attacks.

    • If a secure connection is not used and RSA encryption is not available, the connection attempt fails because the password cannot be sent without being exposed as cleartext.

    Note

    To use RSA password encryption with sha256_password, the client and server both must be compiled using OpenSSL, not just one of them.

    Assuming that MySQL has been compiled using OpenSSL, use the following procedure to enable use of an RSA key pair for password exchange during the client connection process:

    1. Create the RSA private and public key-pair files using the instructions in Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.

    2. If the private and public key files are located in the data directory and are named private_key.pem and public_key.pem (the default values of the sha256_password_private_key_path and sha256_password_public_key_path system variables), the server uses them automatically at startup.

      Otherwise, to name the key files explicitly, set the system variables to the key file names in the server option file. If the files are located in the server data directory, you need not specify their full path names:

      [mysqld]
sha256_password_private_key_path=myprivkey.pem
sha256_password_public_key_path=mypubkey.pem

      If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names:

      [mysqld]
sha256_password_private_key_path=/usr/local/mysql/myprivkey.pem
sha256_password_public_key_path=/usr/local/mysql/mypubkey.pem

    3. Restart the server, then connect to it and check the Rsa_public_key status variable value. The value will differ from that shown here, but should be nonempty:

      mysql> SHOW STATUS LIKE 'Rsa_public_key'G
*************************** 1. row ***************************
Variable_name: Rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----

      If the value is empty, the server found some problem with the key files. Check the error log for diagnostic information.

    After the server has been configured with the RSA key files, accounts that authenticate with the sha256_password plugin have the option of using those key files to connect to the server. As mentioned previously, such accounts can use either a secure connection (in which case RSA is not used) or an unencrypted connection that performs password exchange using RSA. Suppose that an unencrypted connection is used. For example:

    shell> mysql --ssl-mode=DISABLED -u sha256user -p
Enter password: password

    For this connection attempt by sha256user, the server determines that sha256_password is the appropriate authentication plugin and invokes it (because that was the plugin specified at CREATE USER time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. In this case, the plugin sends the RSA public key to the client, which uses it to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct.

    The server sends the RSA public key to the client as needed. However, if the client has a file containing a local copy of the RSA public key required by the server, it can specify the file using the --server-public-key-path option:

    shell> mysql --ssl-mode=DISABLED -u sha256user -p --server-public-key-path=file_name
Enter password: password

    The public key value in the file named by the --server-public-key-path option should be the same as the key value in the server-side file named by the sha256_password_public_key_path system variable. If the key file contains a valid public key value but the value is incorrect, an access-denied error occurs. If the key file does not contain a valid public key, the client program cannot use it. In this case, the sha256_password plugin sends the public key to the client as if no --server-public-key-path option had been specified.

    Client users can obtain the RSA public key two ways:

    • The database administrator can provide a copy of the public key file.

    • A client user who can connect to the server some other way can use a SHOW STATUS LIKE 'Rsa_public_key' statement and save the returned key value in a file.

    6.4.1.4 Client-Side Cleartext Pluggable Authentication

    A client-side authentication plugin is available that enables clients to send passwords to the server as cleartext, without hashing or encryption. This plugin is built into the MySQL client library.

    The following table shows the plugin name.

    Table 6.15 Plugin and Library Names for Cleartext Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin None, see discussion
    Client-side plugin mysql_clear_password
    Library file None (plugin is built in)

    Many client-side authentication plugins perform hashing or encryption of a password before the client sends it to the server. This enables clients to avoid sending passwords as cleartext.

    Hashing or encryption cannot be done for authentication schemes that require the server to receive the password as entered on the client side. In such cases, the client-side mysql_clear_password plugin is used, which enables the client to send the password to the server as cleartext. There is no corresponding server-side plugin. Rather, mysql_clear_password can be used on the client side in concert with any server-side plugin that needs a cleartext password. (Examples are the PAM and simple LDAP authentication plugins; see Section 6.4.1.5, “PAM Pluggable Authentication”, and Section 6.4.1.7, “LDAP Pluggable Authentication”.)

    The following discussion provides usage information specific to cleartext pluggable authentication. For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

    Note

    Sending passwords as cleartext may be a security problem in some configurations. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using a method that protects the password. Possibilities include SSL (see Section 6.3, “Using Encrypted Connections”), IPsec, or a private network.

    To make inadvertent use of the mysql_clear_password plugin less likely, MySQL clients must explicitly enable it. This can be done in several ways:

    • Set the LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN environment variable to a value that begins with 1, Y, or y. This enables the plugin for all client connections.

    • The mysql, mysqladmin, mysqlcheck, mysqldump, mysqlshow, and mysqlslap client programs support an --enable-cleartext-plugin option that enables the plugin on a per-invocation basis.

    • The mysql_options() C API function supports a MYSQL_ENABLE_CLEARTEXT_PLUGIN option that enables the plugin on a per-connection basis. Also, any program that uses libmysqlclient and reads option files can enable the plugin by including an enable-cleartext-plugin option in an option group read by the client library.

    6.4.1.5 PAM Pluggable Authentication

    Note

    PAM pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

    MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use PAM (Pluggable Authentication Modules) to authenticate MySQL users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as traditional Unix passwords or an LDAP directory.

    PAM pluggable authentication provides these capabilities:

    • External authentication: PAM authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables and that authenticate using methods supported by PAM.

    • Proxy user support: PAM authentication can return to MySQL a user name different from the external user name passed by the client program, based on the PAM groups the external user is a member of and the authentication string provided. This means that the plugin can return the MySQL user that defines the privileges the external PAM-authenticated user should have. For example, an operating sytem user named joe can connect and have the privileges of a MySQL user named developer.

    PAM pluggable authentication has been tested on Linux and macOS.

    The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by the plugin_dir system variable. For installation information, see Installing PAM Pluggable Authentication.

    Table 6.16 Plugin and Library Names for PAM Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin authentication_pam
    Client-side plugin mysql_clear_password
    Library file authentication_pam.so

    The client-side mysql_clear_password cleartext plugin that communicates with the server-side PAM plugin is built into the libmysqlclient client library and is included in all distributions, including community distributions. Inclusion of the client-side cleartext plugin in all MySQL distributions enables clients from any distribution to connect to a server that has the server-side PAM plugin loaded.

    The following sections provide installation and usage information specific to PAM pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”. For information about the mysql_clear_password plugin, see Section 6.4.1.4, “Client-Side Cleartext Pluggable Authentication”. For proxy user information, see Section 6.2.18, “Proxy Users”.

    How PAM Authentication of MySQL Users Works

    This section provides a general overview of how MySQL and PAM work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use specific PAM services, see Using PAM Pluggable Authentication.

    1. The client program and the server communicate, with the client sending to the server the client user name (the operating system user name by default) and password:

      • The client user name is the external user name.

      • For accounts that use the PAM server-side authentication plugin, the corresponding client-side plugin is mysql_clear_password. This client-side plugin performs no password hashing, with the result that the client sends the password to the server as cleartext.

    2. The server finds a matching MySQL account based on the external user name and the host from which the client connects. The PAM plugin uses the information passed to it by MySQL Server (such as user name, host name, password, and authentication string). When you define a MySQL account that authenticates using PAM, the authentication string contains:

      • A PAM service name, which is a name that the system administrator can use to refer to an authentication method for a particular application. There can be multiple applications associated with a single database server instance, so the choice of service name is left to the SQL application developer.

      • Optionally, if proxying is to be used, a mapping from PAM groups to MySQL user names.

    3. The plugin uses the PAM service named in the authentication string to check the user credentials and returns 'Authentication succeeded, Username is user_name' or 'Authentication failed'. The password must be appropriate for the password store used by the PAM service. Examples:

      • For traditional Unix passwords, the service looks up passwords stored in the /etc/shadow file.

      • For LDAP, the service looks up passwords stored in an LDAP directory.

      If the credentials check fails, the server refuses the connection.

    4. Otherwise, the authentication string indicates whether proxying occurs. If the string contains no PAM group mapping, proxying does not occur. In this case, the MySQL user name is the same as the external user name.

    5. Otherwise, proxying is indicated based on the PAM group mapping, with the MySQL user name determined based on the first matching group in the mapping list. The meaning of “PAM group” depends on the PAM service. Examples:

      • For traditional Unix passwords, groups are Unix groups defined in the /etc/group file, possibly supplemented with additional PAM information in a file such as /etc/security/group.conf.

      • For LDAP, groups are LDAP groups defined in an LDAP directory.

      If the proxy user (the external user) has the PROXY privilege for the proxied MySQL user name, proxying occurs, with the proxy user assuming the privileges of the proxied user.

    Installing PAM Pluggable Authentication

    This section describes how to install the PAM authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

    To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

    The plugin library file base name is authentication_pam. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

    To load the plugin at server startup, use the --plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary): 

    [mysqld]
plugin-load-add=authentication_pam.so

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to load the plugin at runtime, use this statement (adjust the .so suffix for your platform as necessary):

    INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';

    INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

    To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

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

    If the plugin fails to initialize, check the server error log for diagnostic messages.

    To associate MySQL accounts with the PAM plugin, see Using PAM Pluggable Authentication.

    Uninstalling PAM Pluggable Authentication

    The method used to uninstall the PAM authentication plugin depends on how you installed it:

    • If you installed the plugin at server startup using a --plugin-load-add option, restart the server without the option.

    • If you installed the plugin at runtime using an INSTALL PLUGIN statement, it remains installed across server restarts. To uninstall it, use UNINSTALL PLUGIN:

      UNINSTALL PLUGIN authentication_pam;
    Using PAM Pluggable Authentication

    This section describes in general terms how to use the PAM authentication plugin to connect from MySQL client programs to the server. The following sections provide instructions for using PAM authentication in specific ways. It is assumed that the server is running with the server-side PAM plugin enabled, as described in Installing PAM Pluggable Authentication.

    To refer to the PAM authentication plugin in the IDENTIFIED WITH clause of a CREATE USER statement, use the name authentication_pam. For example:

    CREATE USER user
  IDENTIFIED WITH authentication_pam
  AS 'auth_string';

    The authentication string specifies the following types of information:

    • The PAM service name (see How PAM Authentication of MySQL Users Works). Examples in the following discussion use a service name of mysql-unix for authentication using traditional Unix passwords, and mysql-ldap for authentication using LDAP.

    • For proxy support, PAM provides a way for a PAM module to return to the server a MySQL user name other than the external user name passed by the client program when it connects to the server. Use the authentication string to control the mapping from external user names to MySQL user names. If you want to take advantage of proxy user capabilities, the authentication string must include this kind of mapping.

    For example, if an account uses the mysql-unix PAM service name and should map operating system users in the root and users PAM groups to the developer and data_entry MySQL users, respectively, use a statement like this:

    CREATE USER user
  IDENTIFIED WITH authentication_pam
  AS 'mysql-unix, root=developer, users=data_entry';

    Authentication string syntax for the PAM authentication plugin follows these rules:

    • The string consists of a PAM service name, optionally followed by a PAM group mapping list consisting of one or more keyword/value pairs each specifying a PAM group name and a MySQL user name:

      pam_service_name[,pam_group_name=mysql_user_name]...

      The plugin parses the authentication string for each connection attempt that uses the account. To minimize overhead, keep the string as short as possible.

    • Each pam_group_name=mysql_user_name pair must be preceded by a comma.

    • Leading and trailing spaces not inside double quotation marks are ignored.

    • Unquoted pam_service_name, pam_group_name, and mysql_user_name values can contain anything except equal sign, comma, or space.

    • If a pam_service_name, pam_group_name, or mysql_user_name value is quoted with double quotation marks, everything between the quotation marks is part of the value. This is necessary, for example, if the value contains space characters. All characters are legal except double quotation mark and backslash (). To include either character, escape it with a backslash.

    If the plugin successfully authenticates the external user name (the name passed by the client), it looks for a PAM group mapping list in the authentication string and, if present, uses it to return a different MySQL user name to the MySQL server based on which PAM groups the external user is a member of:

    • If the authentication string contains no PAM group mapping list, the plugin returns the external name.

    • If the authentication string does contain a PAM group mapping list, the plugin examines each pam_group_name=mysql_user_name pair in the list from left to right and tries to find a match for the pam_group_name value in a non-MySQL directory of the groups assigned to the authenticated user and returns mysql_user_name for the first match it finds. If the plugin finds no match for any PAM group, it returns the external name. If the plugin is not capable of looking up a group in a directory, it ignores the PAM group mapping list and returns the external name.

    The following sections describe how to set up several authentication scenarios that use the PAM authentication plugin:

    • No proxy users. This uses PAM only to check login names and passwords. Every external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication. (For a MySQL account of 'user_name'@'host_name' to match the external user, user_name must be the external user name and host_name must match the host from which the client connects.) Authentication can be performed by various PAM-supported methods. Later discussion shows how to authenticate client credentials using traditional Unix passwords, and passwords in LDAP.

      PAM authentication, when not done through proxy users or PAM groups, requires the MySQL user name to be same as the operating system user name. MySQL user names are limited to 32 characters (see Section 6.2.3, “Grant Tables”), which limits PAM nonproxy authentication to Unix accounts with names of at most 32 characters.

    • Proxy users only, with PAM group mapping. For this scenario, create one or more MySQL accounts that define different sets of privileges. (Ideally, nobody should connect using those accounts directly.) Then define a default user authenticating through PAM that uses some mapping scheme (usually based on the external PAM groups the users are members of) to map all the external user names to the few MySQL accounts holding the privilege sets. Any client who connects and specifies an external user name as the client user name is mapped to one of the MySQL accounts and uses its privileges. The discussion shows how to set this up using traditional Unix passwords, but other PAM methods such as LDAP could be used instead.

    Variations on these scenarios are possible:

    • You can permit some users to log in directly (without proxying) but require others to connect through proxy accounts.

    • You can use one PAM authentication method for some users, and another method for other users, by using differing PAM service names among your PAM-authenticated accounts. For example, you can use the mysql-unix PAM service for some users, and mysql-ldap for others.

    The examples make the following assumptions. You might need to make some adjustments if your system is set up differently.

    • The login name and password are antonio and antonio_password, respectively. Change these to correspond to the user you want to authenticate.

    • The PAM configuration directory is /etc/pam.d.

    • The PAM service name corresponds to the authentication method (mysql-unix or mysql-ldap in this discussion). To use a given PAM service, you must set up a PAM file with the same name in the PAM configuration directory (creating the file if it does not exist). In addition, you must name the PAM service in the authentication string of the CREATE USER statement for any account that authenticates using that PAM service.

    The PAM authentication plugin checks at initialization time whether the AUTHENTICATION_PAM_LOG environment value is set in the server's startup environment. If so, the plugin enables logging of diagnostic messages to the standard output. Depending on how your server is started, the message might appear on the console or in the error log. These messages can be helpful for debugging PAM-related issues that occur when the plugin performs authentication. For more information, see PAM Authentication Debugging.

    PAM Unix Password Authentication without Proxy Users

    This authentication scenario uses PAM to check external users defined in terms of operating system user names and Unix passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through traditional Unix password store.

    Note

    Traditional Unix passwords are checked using the /etc/shadow file. For information regarding possible issues related to this file, see PAM Authentication Access to Unix Password Store.

    1. Verify that Unix authentication permits logins to the operating system with the user name antonio and password antonio_password.

    2. Set up PAM to authenticate MySQL connections using traditional Unix passwords by creating a mysql-unix PAM service file named /etc/pam.d/mysql-unix. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, the mysql-unix file might look like this:

      #%PAM-1.0
auth            include         password-auth
account         include         password-auth

      For macOS, use login rather than password-auth.

      The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead:

      @include common-auth
@include common-account
@include common-session-noninteractive

    3. Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the mysql-unix PAM service:

      CREATE USER 'antonio'@'localhost'
  IDENTIFIED WITH authentication_pam
  AS 'mysql-unix';
GRANT ALL PRIVILEGES
  ON mydb.*
  TO 'antonio'@'localhost';

      Here, the authentication string contains only the PAM service name, mysql-unix, which authenticates Unix passwords.

    4. Use the mysql command-line client to connect to the MySQL server as antonio. For example:

      shell> mysql --user=antonio --password --enable-cleartext-plugin
Enter password: antonio_password

      The server should permit the connection and the following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+-------------------+--------------+
| USER()            | CURRENT_USER()    | @@proxy_user |
+-------------------+-------------------+--------------+
| antonio@localhost | antonio@localhost | NULL         |
+-------------------+-------------------+--------------+

      This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the antonio MySQL user, and that no proxying has occurred.

    Note

    The client-side mysql_clear_password authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to PAM. A cleartext password is necessary to use the server-side PAM library, but may be a security problem in some configurations. These measures minimize the risk:

    PAM LDAP Authentication without Proxy Users

    This authentication scenario uses PAM to check external users defined in terms of operating system user names and LDAP passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through LDAP.

    To use PAM LDAP pluggable authentication for MySQL, these prerequisites must be satisfied:

    • An LDAP server must be available for the PAM LDAP service to communicate with.

    • LDAP users to be authenticated by MySQL must be present in the directory managed by the LDAP server.

    Note

    Another way to use LDAP for MySQL user authentication is to use the LDAP-specific authentication plugins. See Section 6.4.1.7, “LDAP Pluggable Authentication”.

    Configure MySQL for PAM LDAP authentication as follows:

    1. Verify that Unix authentication permits logins to the operating system with the user name antonio and password antonio_password.

    2. Set up PAM to authenticate MySQL connections using LDAP by creating a mysql-ldap PAM service file named /etc/pam.d/mysql-ldap. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, the mysql-ldap file might look like this:

      #%PAM-1.0
auth        required    pam_ldap.so
account     required    pam_ldap.so

      If PAM object files have a suffix different from .so on your system, substitute the correct suffix.

      The PAM file format might differ on some systems.

    3. Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the mysql-ldap PAM service:

      CREATE USER 'antonio'@'localhost'
  IDENTIFIED WITH authentication_pam
  AS 'mysql-ldap';
GRANT ALL PRIVILEGES
  ON mydb.*
  TO 'antonio'@'localhost';

      Here, the authentication string contains only the PAM service name, mysql-ldap, which authenticates using LDAP.

    4. Connecting to the server is the same as described in PAM Unix Password Authentication without Proxy Users.

    PAM Unix Password Authentication with Proxy Users and Group Mapping

    The authentication scheme described here uses proxying and PAM group mapping to map connecting MySQL users who authenticate using PAM onto other MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy account authenticated using PAM, such that all the external users are mapped to the MySQL accounts that hold the privileges. Any user who connects using the proxy account is mapped to one of those MySQL accounts, the privileges for which determine the database operations permitted to the external user.

    The procedure shown here uses Unix password authentication. To use LDAP instead, see the early steps of PAM LDAP Authentication without Proxy Users.

    Note

    Traditional Unix passwords are checked using the /etc/shadow file. For information regarding possible issues related to this file, see PAM Authentication Access to Unix Password Store.

    1. Verify that Unix authentication permits logins to the operating system with the user name antonio and password antonio_password.

    2. Verify that antonio is a member of the root or users PAM group.

    3. Set up PAM to authenticate the mysql-unix PAM service through operating system users by creating a file named /etc/pam.d/mysql-unix. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, the mysql-unix file might look like this:

      #%PAM-1.0
auth            include         password-auth
account         include         password-auth

      For macOS, use login rather than password-auth.

      The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead:

      @include common-auth
@include common-account
@include common-session-noninteractive

    4. Create a default proxy user (''@'') that maps external PAM users to the proxied accounts:

      CREATE USER ''@''
  IDENTIFIED WITH authentication_pam
  AS 'mysql-unix, root=developer, users=data_entry';

      Here, the authentication string contains the PAM service name, mysql-unix, which authenticates Unix passwords. The authentication string also maps external users in the root and users PAM groups to the developer and data_entry MySQL user names, respectively.

      The PAM group mapping list following the PAM service name is required when you set up proxy users. Otherwise, the plugin cannot tell how to perform mapping from external user names to the proper proxied MySQL user names.

      Note

      If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see Default Proxy User and Anonymous User Conflicts.

    5. Create the proxied accounts and grant to them the privileges required for MySQL access:

      CREATE USER 'developer'@'localhost'
  IDENTIFIED WITH mysql_no_login;
CREATE USER 'data_entry'@'localhost'
  IDENTIFIED WITH mysql_no_login;

GRANT ALL PRIVILEGES
  ON mydevdb.*
  TO 'developer'@'localhost';
GRANT ALL PRIVILEGES
  ON mydb.*
  TO 'data_entry'@'localhost';

      The proxied accounts use the mysql_no_login authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, it is expected that users who authenticate using PAM will use the developer or data_entry account by proxy based on their PAM group. (This assumes that the plugin is installed. For instructions, see Section 6.4.1.8, “No-Login Pluggable Authentication”.) For alternative methods of protecting proxied accounts against direct use, see Preventing Direct Login to Proxied Accounts.

    6. Grant to the proxy account the PROXY privilege for each proxied account:

      GRANT PROXY
  ON 'developer'@'localhost'
  TO ''@'';
GRANT PROXY
  ON 'data_entry'@'localhost'
  TO ''@'';

    7. Use the mysql command-line client to connect to the MySQL server as antonio.

      shell> mysql --user=antonio --password --enable-cleartext-plugin
Enter password: antonio_password

      The server authenticates the connection using the default ''@'' proxy account. The resulting privileges for antonio depend on which PAM groups antonio is a member of. If antonio is a member of the root PAM group, the PAM plugin maps root to the developer MySQL user name and returns that name to the server. The server verifies that ''@'' has the PROXY privilege for developer and permits the connection. The following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+---------------------+--------------+
| USER()            | CURRENT_USER()      | @@proxy_user |
+-------------------+---------------------+--------------+
| antonio@localhost | developer@localhost | ''@''        |
+-------------------+---------------------+--------------+

      This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the developer MySQL user, and that proxying occurs through the default proxy account.

      If antonio is not a member of the root PAM group but is a member of the users PAM group, a similar process occurs, but the plugin maps user PAM group membership to the data_entry MySQL user name and returns that name to the server:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+----------------------+--------------+
| USER()            | CURRENT_USER()       | @@proxy_user |
+-------------------+----------------------+--------------+
| antonio@localhost | data_entry@localhost | ''@''        |
+-------------------+----------------------+--------------+

      This demonstrates that the antonio operating system user is authenticated to have the privileges of the data_entry MySQL user, and that proxying occurs through the default proxy account.

    Note

    The client-side mysql_clear_password authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to PAM. A cleartext password is necessary to use the server-side PAM library, but may be a security problem in some configurations. These measures minimize the risk:

    PAM Authentication Access to Unix Password Store

    On some systems, Unix authentication uses a password store such as /etc/shadow, a file that typically has restricted access permissions. This can cause MySQL PAM-based authentication to fail. Unfortunately, the PAM implementation does not permit distinguishing “password could not be checked” (due, for example, to inability to read /etc/shadow) from “password does not match.” If you are using Unix password store for PAM authentication, you may be able to enable access to it from MySQL using one of the following methods:

    • Assuming that the MySQL server is run from the mysql operating system account, put that account in the shadow group that has /etc/shadow access:

      1. Create a shadow group in /etc/group.

      2. Add the mysql operating system user to the shadow group in /etc/group.

      3. Assign /etc/group to the shadow group and enable the group read permission:

        chgrp shadow /etc/shadow
chmod g+r /etc/shadow

      4. Restart the MySQL server.

    • If you are using the pam_unix module and the unix_chkpwd utility, enable password store access as follows: 

      chmod u-s /usr/sbin/unix_chkpwd
setcap cap_dac_read_search+ep /usr/sbin/unix_chkpwd

      Adjust the path to unix_chkpwd as necessary for your platform.

    PAM Authentication Debugging

    The PAM authentication plugin checks at initialization time whether the AUTHENTICATION_PAM_LOG environment value is set (the value does not matter). If so, the plugin enables logging of diagnostic messages to the standard output. These messages may be helpful for debugging PAM-related issues that occur when the plugin performs authentication.

    Some messages include reference to PAM plugin source files and line numbers, which enables plugin actions to be tied more closely to the location in the code where they occur.

    Another technique for debugging connection failures and determining what is happening during connection attempts is to configure PAM authentication to permit all connections, then check the system log files. This technique should be used only on a temporary basis, and not on a production server.

    Configure a PAM service file named /etc/pam.d/mysql-any-password with these contents (the format may differ on some systems):

    #%PAM-1.0
auth        required    pam_permit.so
account     required    pam_permit.so

    Create an account that uses the PAM plugin and names the mysql-any-password PAM service:

    CREATE USER 'testuser'@'localhost'
  IDENTIFIED WITH authentication_pam
  AS 'mysql-any-password';

    The mysql-any-password service file causes any authentication attempt to return true, even for incorrect passwords. If an authentication attempt fails, that tells you the configuration problem is on the MySQL side. Otherwise, the problem is on the operating system/PAM side. To see what might be happening, check system log files such as /var/log/secure, /var/log/audit.log, /var/log/syslog, or /var/log/messages.

    After determining what the problem is, remove the mysql-any-password PAM service file to disable any-password access.

    6.4.1.6 Windows Pluggable Authentication

    Note

    Windows pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

    MySQL Enterprise Edition for Windows supports an authentication method that performs external authentication on Windows, enabling MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password.

    The client and server exchange data packets in the authentication handshake. As a result of this exchange, the server creates a security context object that represents the identity of the client in the Windows OS. This identity includes the name of the client account. Windows pluggable authentication uses the identity of the client to check whether it is a given account or a member of a group. By default, negotiation uses Kerberos to authenticate, then NTLM if Kerberos is unavailable.

    Windows pluggable authentication provides these capabilities:

    • External authentication: Windows authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables who have logged in to Windows.

    • Proxy user support: Windows authentication can return to MySQL a user name different from the external user name passed by the client program. This means that the plugin can return the MySQL user that defines the privileges the external Windows-authenticated user should have. For example, a Windows user named joe can connect and have the privileges of a MySQL user named developer.

    The following table shows the plugin and library file names. The file must be located in the directory named by the plugin_dir system variable.

    Table 6.17 Plugin and Library Names for Windows Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin authentication_windows
    Client-side plugin authentication_windows_client
    Library file authentication_windows.dll

    The library file includes only the server-side plugin. The client-side plugin is built into the libmysqlclient client library.

    The server-side Windows authentication plugin is included only in MySQL Enterprise Edition. It is not included in MySQL community distributions. The client-side plugin is included in all distributions, including community distributions. This permits clients from any distribution to connect to a server that has the server-side plugin loaded.

    The Windows authentication plugin is supported on any version of Windows supported by MySQL 8.0 (see https://www.mysql.com/support/supportedplatforms/database.html).

    The following sections provide installation and usage information specific to Windows pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”. For proxy user information, see Section 6.2.18, “Proxy Users”.

    Installing Windows Pluggable Authentication

    This section describes how to install the Windows authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

    To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

    To load the plugin at server startup, use the --plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file: 

    [mysqld]
plugin-load-add=authentication_windows.dll

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to load the plugin at runtime, use this statement:

    INSTALL PLUGIN authentication_windows SONAME 'authentication_windows.dll';

    INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

    To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

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

    If the plugin fails to initialize, check the server error log for diagnostic messages.

    To associate MySQL accounts with the Windows authentication plugin, see Using Windows Pluggable Authentication. Additional plugin control is provided by the authentication_windows_use_principal_name and authentication_windows_log_level system variables. See Section 5.1.8, “Server System Variables”.

    Uninstalling Windows Pluggable Authentication

    The method used to uninstall the Windows authentication plugin depends on how you installed it:

    • If you installed the plugin at server startup using a --plugin-load-add option, restart the server without the option.

    • If you installed the plugin at runtime using an INSTALL PLUGIN statement, it remains installed across server restarts. To uninstall it, use UNINSTALL PLUGIN:

      UNINSTALL PLUGIN authentication_windows;

    In addition, remove any startup options that set Windows plugin-related system variables.

    Using Windows Pluggable Authentication

    The Windows authentication plugin supports the use of MySQL accounts such that users who have logged in to Windows can connect to the MySQL server without having to specify an additional password. It is assumed that the server is running with the server-side plugin enabled, as described in Installing Windows Pluggable Authentication. Once the DBA has enabled the server-side plugin and set up accounts to use it, clients can connect using those accounts with no other setup required on their part.

    To refer to the Windows authentication plugin in the IDENTIFIED WITH clause of a CREATE USER statement, use the name authentication_windows. Suppose that the Windows users Rafal and Tasha should be permitted to connect to MySQL, as well as any users in the Administrators or Power Users group. To set this up, create a MySQL account named sql_admin that uses the Windows plugin for authentication:

    CREATE USER sql_admin
  IDENTIFIED WITH authentication_windows
  AS 'Rafal, Tasha, Administrators, "Power Users"';

    The plugin name is authentication_windows. The string following the AS keyword is the authentication string. It specifies that the Windows users named Rafal or Tasha are permitted to authenticate to the server as the MySQL user sql_admin, as are any Windows users in the Administrators or Power Users group. The latter group name contains a space, so it must be quoted with double quote characters.

    After you create the sql_admin account, a user who has logged in to Windows can attempt to connect to the server using that account:

    C:> mysql --user=sql_admin

    No password is required here. The authentication_windows plugin uses the Windows security API to check which Windows user is connecting. If that user is named Rafal or Tasha, or is a member of the Administrators or Power Users group, the server grants access and the client is authenticated as sql_admin and has whatever privileges are granted to the sql_admin account. Otherwise, the server denies access.

    Authentication string syntax for the Windows authentication plugin follows these rules:

    • The string consists of one or more user mappings separated by commas.

    • Each user mapping associates a Windows user or group name with a MySQL user name:

      win_user_or_group_name=mysql_user_name
win_user_or_group_name

      For the latter syntax, with no mysql_user_name value given, the implicit value is the MySQL user created by the CREATE USER statement. Thus, these statements are equivalent:

      CREATE USER sql_admin
  IDENTIFIED WITH authentication_windows
  AS 'Rafal, Tasha, Administrators, "Power Users"';

CREATE USER sql_admin
  IDENTIFIED WITH authentication_windows
  AS 'Rafal=sql_admin, Tasha=sql_admin, Administrators=sql_admin,
      "Power Users"=sql_admin';

    • Each backslash character () in a value must be doubled because backslash is the escape character in MySQL strings.

    • Leading and trailing spaces not inside double quotation marks are ignored.

    • Unquoted win_user_or_group_name and mysql_user_name values can contain anything except equal sign, comma, or space.

    • If a win_user_or_group_name and or mysql_user_name value is quoted with double quotation marks, everything between the quotation marks is part of the value. This is necessary, for example, if the name contains space characters. All characters within double quotes are legal except double quotation mark and backslash. To include either character, escape it with a backslash.

    • win_user_or_group_name values use conventional syntax for Windows principals, either local or in a domain. Examples (note the doubling of backslashes):

      domain\user
.\user
domain\group
.\group
BUILTIN\WellKnownGroup

    When invoked by the server to authenticate a client, the plugin scans the authentication string left to right for a user or group match to the Windows user. If there is a match, the plugin returns the corresponding mysql_user_name to the MySQL server. If there is no match, authentication fails.

    A user name match takes preference over a group name match. Suppose that the Windows user named win_user is a member of win_group and the authentication string looks like this:

    'win_group = sql_user1, win_user = sql_user2'

    When win_user connects to the MySQL server, there is a match both to win_group and to win_user. The plugin authenticates the user as sql_user2 because the more-specific user match takes precedence over the group match, even though the group is listed first in the authentication string.

    Windows authentication always works for connections from the same computer on which the server is running. For cross-computer connections, both computers must be registered with Windows Active Directory. If they are in the same Windows domain, it is unnecessary to specify a domain name. It is also possible to permit connections from a different domain, as in this example:

    CREATE USER sql_accounting
  IDENTIFIED WITH authentication_windows
  AS 'SomeDomain\Accounting';

    Here SomeDomain is the name of the other domain. The backslash character is doubled because it is the MySQL escape character within strings.

    MySQL supports the concept of proxy users whereby a client can connect and authenticate to the MySQL server using one account but while connected has the privileges of another account (see Section 6.2.18, “Proxy Users”). Suppose that you want Windows users to connect using a single user name but be mapped based on their Windows user and group names onto specific MySQL accounts as follows:

    • The local_user and MyDomaindomain_user local and domain Windows users should map to the local_wlad MySQL account.

    • Users in the MyDomainDevelopers domain group should map to the local_dev MySQL account.

    • Local machine administrators should map to the local_admin MySQL account.

    To set this up, create a proxy account for Windows users to connect to, and configure this account so that users and groups map to the appropriate MySQL accounts (local_wlad, local_dev, local_admin). In addition, grant the MySQL accounts the privileges appropriate to the operations they need to perform. The following instructions use win_proxy as the proxy account, and local_wlad, local_dev, and local_admin as the proxied accounts.

    1. Create the proxy MySQL account:

      CREATE USER win_proxy
  IDENTIFIED WITH  authentication_windows
  AS 'local_user = local_wlad,
      MyDomain\domain_user = local_wlad,
      MyDomain\Developers = local_dev,
      BUILTIN\Administrators = local_admin';

    2. For proxying to work, the proxied accounts must exist, so create them:

      CREATE USER local_wlad
  IDENTIFIED WITH mysql_no_login;
CREATE USER local_dev
  IDENTIFIED WITH mysql_no_login;
CREATE USER local_admin
  IDENTIFIED WITH mysql_no_login;

      The proxied accounts use the mysql_no_login authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, it is expected that users who authenticate using Windows will use the win_proxy proxy account. (This assumes that the plugin is installed. For instructions, see Section 6.4.1.8, “No-Login Pluggable Authentication”.) For alternative methods of protecting proxied accounts against direct use, see Preventing Direct Login to Proxied Accounts.

      You should also execute GRANT statements (not shown) that grant each proxied account the privileges required for MySQL access.

    3. Grant to the proxy account the PROXY privilege for each proxied account:

      GRANT PROXY ON local_wlad TO win_proxy;
GRANT PROXY ON local_dev TO win_proxy;
GRANT PROXY ON local_admin TO win_proxy;

    Now the Windows users local_user and MyDomaindomain_user can connect to the MySQL server as win_proxy and when authenticated have the privileges of the account given in the authentication string (in this case, local_wlad). A user in the MyDomainDevelopers group who connects as win_proxy has the privileges of the local_dev account. A user in the BUILTINAdministrators group has the privileges of the local_admin account.

    To configure authentication so that all Windows users who do not have their own MySQL account go through a proxy account, substitute the default proxy account (''@'') for win_proxy in the preceding instructions. For information about default proxy accounts, see Section 6.2.18, “Proxy Users”.

    Note

    If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see Default Proxy User and Anonymous User Conflicts.

    To use the Windows authentication plugin with Connector/NET connection strings in Connector/NET 6.4.4 and higher, see Using the Windows Native Authentication Plugin.

    6.4.1.7 LDAP Pluggable Authentication

    Note

    LDAP pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

    MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use LDAP (Lightweight Directory Access Protocol) to authenticate MySQL users by accessing directory services such as X.500. MySQL uses LDAP to fetch user, credential, and group information.

    LDAP pluggable authentication provides these capabilities:

    • External authentication: LDAP authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables in LDAP directories.

    • Proxy user support: LDAP authentication can return to MySQL a user name different from the external user name passed by the client program, based on the LDAP groups the external user is a member of. This means that an LDAP plugin can return the MySQL user that defines the privileges the external LDAP-authenticated user should have. For example, an LDAP user named joe can connect and have the privileges of a MySQL user named developer, if the LDAP group for joe is developer.

    • Security: Using TLS, connections to the LDAP server can be secure.

    The following tables show the plugin and library file names for simple and SASL-based LDAP authentication. The file name suffix might differ on your system. The files must be located in the directory named by the plugin_dir system variable.

    Table 6.18 Plugin and Library Names for Simple LDAP Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin name authentication_ldap_simple
    Client-side plugin name mysql_clear_password
    Library file name authentication_ldap_simple.so

    Table 6.19 Plugin and Library Names for SASL-Based LDAP Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin name authentication_ldap_sasl
    Client-side plugin name authentication_ldap_sasl_client
    Library file names authentication_ldap_sasl.so, authentication_ldap_sasl_client.so

    The library files include only the authentication_ldap_XXX authentication plugins. The client-side mysql_clear_password plugin is built into the libmysqlclient client library.

    Each server-side LDAP plugin works with a specific client-side plugin:

    • The server-side authentication_ldap_simple plugin performs simple LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side mysql_clear_password plugin, which sends the password to the server as cleartext. No password hashing or encryption is used, so a secure connection between the MySQL client and server is recommended to prevent password exposure.

    • The server-side authentication_ldap_sasl plugin performs SASL-based LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side authentication_ldap_sasl_client plugin. The client-side and server-side SASL LDAP plugins use SASL messages for secure transmission of credentials within the LDAP protocol, to avoid sending the cleartext password between the MySQL client and server.

    The following sections provide installation and usage information specific to LDAP pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”. For information about the mysql_clear_password plugin, see Section 6.4.1.4, “Client-Side Cleartext Pluggable Authentication”. For proxy user information, see Section 6.2.18, “Proxy Users”.

    Note

    If your system supports PAM and permits LDAP as a PAM authentication method, another way to use LDAP for MySQL user authentication is to use the server-side authentication_pam plugin. See Section 6.4.1.5, “PAM Pluggable Authentication”.

    Prerequisites for LDAP Pluggable Authentication

    To use LDAP pluggable authentication for MySQL, these prerequisites must be satisfied:

    • An LDAP server must be available for the LDAP authentication plugins to communicate with.

    • LDAP users to be authenticated by MySQL must be present in the directory managed by the LDAP server.

    • An LDAP client library must be available on systems where the server-side authentication_ldap_sasl or authentication_ldap_simple plugin is used. Currently, supported libraries are the Windows native LDAP library, or the OpenLDAP library on non-Windows systems.

    • To use SASL-based LDAP authentication:

      • The LDAP server must be configured to communicate with a SASL server.

      • A SASL client library must be is available on systems where the client-side authentication_ldap_sasl_client plugin is used. Currently, the only supported library is the Cyrus SASL library.

    How LDAP Authentication of MySQL Users Works

    This section provides a general overview of how MySQL and LDAP work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use specific LDAP authentication plugins, see Using LDAP Pluggable Authentication.

    The client connects to the MySQL server, providing the MySQL client user name and the LDAP password:

    • For simple LDAP authentication, the client-side and server-side plugins communicate the password as cleartext.

    • For SASL-based LDAP authentication, the client-side and server-side plugins use SASL messages for secure transmission of credentials within the LDAP protocol, to avoid sending the cleartext password between the MySQL client and server.

    If the client user name and host name match no MySQL account, the connection is rejected.

    If there is a matching MySQL account, authentication against LDAP occurs. The LDAP server looks for an entry matching the user and authenticates the entry against the LDAP password:

    • If the MySQL account names an the LDAP user distinguished name (DN), LDAP authentication uses that value and the LDAP password provided by the client. (To associate an LDAP user DN with a MySQL account, include a BY clause that specifies an authentication string in the CREATE USER statement that creates the account.)

    • If the MySQL account names no LDAP user DN, LDAP authentication uses the user name and LDAP password provided by the client. In this case, the authentication plugin first binds to the LDAP server using the root DN and password as credentials to find the user DN based on the client user name, then authenticates that user DN against the LDAP password. This bind using the root credentials fails if the root DN and password are set to incorrect values, or are empty (not set) and the LDAP server does not permit anonymous connections.

    If the LDAP server finds no match or multiple matches, authentication fails and the client connection is rejected.

    If the LDAP server finds a single match, LDAP authentication succeeds (assuming that the password is correct), the LDAP server returns the LDAP entry, and the authentication plugin determines the name of the authenticated user based on that entry:

    • If the LDAP entry has a group attribute (by default, the cn attribute), the plugin returns its value as the authenticated user name.

    • If the LDAP entry has no group attribute, the authentication plugin returns the client user name as the authenticated user name.

    The MySQL server compares the client user name with the authenticated user name to determine whether proxying occurs for the client session:

    • If the names are the same, no proxying occurs: The MySQL account matching the client user name is used for privilege checking.

    • If the names differ, proxying occurs: MySQL looks for an account matching the authenticated user name. That account becomes the proxied user, which is used for privilege checking. The MySQL account that matched the client user name is treated as the external proxy user.

    Installing LDAP Pluggable Authentication

    This section describes how to install the LDAP authentication plugins. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

    To be usable by the server, the plugin library files must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

    The server-side plugin library file base names are authentication_ldap_simple and authentication_ldap_sasl. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

    To load the plugins at server startup, use --plugin-load-add options to name the library files that contain them. With this plugin-loading method, the options must be given each time the server starts. Also, specify values for any plugin-provided system variables you wish to configure.

    Each server-side LDAP plugin exposes a set of system variables that enable its operation to be configured. Setting most of these is optional, but you must set the variables that specify the LDAP server host (so the plugin knows where to connect) and base distinguished name for LDAP bind operations (to limit the scope of searches and obtain faster searches). For details about all LDAP system variables, see Section 6.4.1.11, “Pluggable Authentication System Variables”.

    To load the plugins and set the LDAP server host and base distinguished name for LDAP bind operations, put lines such as these in your my.cnf file (adjust the .so suffix for your platform as necessary): 

    [mysqld]
plugin-load-add=authentication_ldap_simple.so
authentication_ldap_simple_server_host=127.0.0.1
authentication_ldap_simple_bind_base_dn="dc=example,dc=com"
plugin-load-add=authentication_ldap_sasl.so
authentication_ldap_sasl_server_host=127.0.0.1
authentication_ldap_sasl_bind_base_dn="dc=example,dc=com"

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to load the plugins at runtime, use these statements (adjust the .so suffix for your platform as necessary):

    INSTALL PLUGIN authentication_ldap_simple
  SONAME 'authentication_ldap_simple.so';
INSTALL PLUGIN authentication_ldap_sasl
  SONAME 'authentication_ldap_sasl.so';

    INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

    After installing the plugins at runtime, their system variables become available and you can add settings for them to your my.cnf file to configure the plugins for subsequent restarts. For example:

    [mysqld]
authentication_ldap_simple_server_host=127.0.0.1
authentication_ldap_simple_bind_base_dn="dc=example,dc=com"
authentication_ldap_sasl_server_host=127.0.0.1
authentication_ldap_sasl_bind_base_dn="dc=example,dc=com"

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to set and persist the values at runtime, use these statements:

    SET PERSIST authentication_ldap_simple_server_host='127.0.0.1';
SET PERSIST authentication_ldap_simple_bind_base_dn='dc=example,dc=com';
SET PERSIST authentication_ldap_sasl_server_host='127.0.0.1';
SET PERSIST authentication_ldap_sasl_bind_base_dn='dc=example,dc=com';

    SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to be used for subsequent server restarts. To change a value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 13.7.6.1, “SET Syntax for Variable Assignment”.

    To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

    mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%ldap%';
+----------------------------+---------------+
| PLUGIN_NAME                | PLUGIN_STATUS |
+----------------------------+---------------+
| authentication_ldap_sasl   | ACTIVE        |
| authentication_ldap_simple | ACTIVE        |
+----------------------------+---------------+

    If a plugin fails to initialize, check the server error log for diagnostic messages.

    To associate MySQL accounts with an LDAP plugin, see Using LDAP Pluggable Authentication.

    Additional Notes for SELinux

    On systems running EL6 or EL that have SELinux enabled, changes to the SELinux policy are required to enable the MySQL LDAP plugins to communicate with the LDAP service:

    1. Create a file mysqlldap.te with these contents:

      module mysqlldap 1.0;

require {
        type ldap_port_t;
        type mysqld_t;
        class tcp_socket name_connect;
}

#============= mysqld_t ==============

allow mysqld_t ldap_port_t:tcp_socket name_connect;

    2. Compile the security policy module into a binary representation:

      checkmodule -M -m mysqlldap.te -o mysqlldap.mod

    3. Create an SELinux policy module package:

      semodule_package -m mysqlldap.mod  -o mysqlldap.pp

    4. Install the module package:

      semodule -i mysqlldap.pp

    5. When the SELinux policy changes have been made, restart the MySQL server:

      service mysqld restart
    Uninstalling LDAP Pluggable Authentication

    The method used to uninstall the LDAP authentication plugins depends on how you installed them:

    • If you installed the plugins at server startup using --plugin-load-add options, restart the server without those options.

    • If you installed the plugins at runtime using INSTALL PLUGIN, they remain installed across server restarts. To uninstall them, use UNINSTALL PLUGIN:

      UNINSTALL PLUGIN authentication_ldap_simple;
UNINSTALL PLUGIN authentication_ldap_sasl;

    In addition, remove from your my.cnf file any startup options that set LDAP plugin-related system variables. If you used SET PERSIST to persist LDAP system variables, use RESET PERSIST to remove the settings.

    Using LDAP Pluggable Authentication

    This section describes how to enable MySQL accounts to connect to the MySQL server using LDAP pluggable authentication. It is assumed that the server is running with the appropriate server-side plugins enabled, as described in Installing LDAP Pluggable Authentication, and that the appropriate client-side plugins are available on the client host.

    This section does not describe LDAP configuration or administration. It is assumed that you are familiar with those topics.

    The two server-side LDAP plugins each work with a specific client-side plugin:

    • The server-side authentication_ldap_simple plugin performs simple LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side mysql_clear_password plugin, which sends the password to the server as cleartext. No password hashing or encryption is used, so a secure connection between the MySQL client and server is recommended to prevent password exposure.

    • The server-side authentication_ldap_sasl plugin performs SASL-based LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side authentication_ldap_sasl_client plugin. The client-side and server-side SASL LDAP plugins use SASL messages for secure transmission of credentials within the LDAP protocol, to avoid sending the cleartext password between the MySQL client and server.

    Overall requirements for LDAP authentication of MySQL users:

    • There must be an LDAP directory entry for each user to be authenticated.

    • There must be a MySQL user account that specifies a server-side LDAP authentication plugin and optionally names the associated LDAP user distinguished name (DN). (To associate an LDAP user DN with a MySQL account, include a BY clause in the CREATE USER statement that creates the account.) If an account names no LDAP string, LDAP authentication uses the user name specified by the client to find the LDAP entry.

    • Client programs connect using the connection method appropriate for the server-side authentication plugin the MySQL account uses. For LDAP authentication, connections require the MySQL user name and LDAP password. In addition, for accounts that use the server-side authentication_ldap_simple plugin, invoke client programs with the --enable-cleartext-plugin option to enable the client-side mysql_clear_password plugin.

    The instructions here assume the following scenario:

    • MySQL users betsy and boris authenticate to the LDAP entries for betsy_ldap and boris_ldap, respectively. (It is not necessary that the MySQL and LDAP user names differ. The use of different names in this discussion helps clarify whether an operation context is MySQL or LDAP.)

    • LDAP entries use the uid attribute to specify user names. This may vary depending on LDAP server. Some LDAP servers use the cn attribute for user names rather than uid. To change the attribute, modify the authentication_ldap_simple_user_search_attr or authentication_ldap_sasl_user_search_attr system variable appropriately.

    • These LDAP entries are available in the directory managed by the LDAP server, to provide distinguished name values that uniquely identify each user:

      uid=betsy_ldap,ou=People,dc=example,dc=com
uid=boris_ldap,ou=People,dc=example,dc=com

    • CREATE USER statements that create MySQL accounts name an LDAP user in the BY clause, to indicate which LDAP entry the MySQL account authenticates against.

    The instructions for setting up an account that uses LDAP authentication depend on which server-side LDAP plugin is used. The following sections describe several usage scenarios.

    Simple LDAP Authentication

    To configure a MySQL account for simple LDAP authentication, the CREATE USER statement specifies the authentication_ldap_simple plugin, and optionally names the LDAP user distinguished name (DN):

    CREATE USER user
  IDENTIFIED WITH authentication_ldap_simple
  [BY 'LDAP user DN'];

    Suppose that MySQL user betsy has this entry in the LDAP directory:

    uid=betsy_ldap,ou=People,dc=example,dc=com

    Then the statement to create the MySQL account for betsy looks like this:

    CREATE USER 'betsy'@'localhost'
  IDENTIFIED WITH authentication_ldap_simple
  AS 'uid=betsy_ldap,ou=People,dc=example,dc=com';

    The authentication string specified in the BY clause does not include the LDAP password. That must be provided by the client user at connect time.

    Clients connect to the MySQL server by providing the MySQL user name and LDAP password, and by enabling the client-side mysql_clear_password plugin:

    shell> mysql --user=betsy --password --enable-cleartext-plugin
Enter password: betsy_password (betsy_ldap LDAP password)
    Note

    The client-side mysql_clear_password authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to the LDAP server. A cleartext password is necessary to use the server-side LDAP library without SASL, but may be a security problem in some configurations. These measures minimize the risk:

    The authentication process occurs as follows:

    1. The client-side plugin sends betsy and betsy_password as the client user name and LDAP password to the MySQL server.

    2. The connection attempt matches the 'betsy'@'localhost' account. The server-side LDAP plugin finds that this account has an authentication string of 'uid=betsy_ldap,ou=People,dc=example,dc=com' to name the LDAP user DN. The plugin sends this string and the LDAP password to the LDAP server.

    3. The LDAP server finds the LDAP entry for betsy_ldap and the password matches, so LDAP authentication succeeds.

    4. The LDAP entry has no group attribute, so the server-side plugin returns the client user name (betsy) as the authenticated user. This is the same user name supplied by the client, so no proxying occurs and the client session uses the 'betsy'@'localhost' account for privilege checking.

    Had the matching LDAP entry contained a group attribute, that attribute value would have been the authenticated user name and, if the value differed from betsy, proxying would have occurred. For examples that use the group attribute, see LDAP Authentication with Proxying.

    Had the CREATE USER statement contained no BY clause to specify the betsy_ldap LDAP distinguished name, authentication attempts would use the user name provided by the client (in this case, betsy). In the absence of an LDAP entry for betsy, authentication would fail.

    SASL-Based LDAP Authentication

    To configure a MySQL account for SASL LDAP authentication, the CREATE USER statement specifies the authentication_ldap_sasl plugin, and optionally names the LDAP user distinguished name (DN):

    CREATE USER user
  IDENTIFIED WITH authentication_ldap_sasl
  [BY 'LDAP user DN'];

    Suppose that MySQL user boris has this entry in the LDAP directory:

    uid=boris_ldap,ou=People,dc=example,dc=com

    Then the statement to create the MySQL account for boris looks like this:

    CREATE USER 'boris'@'localhost'
  IDENTIFIED WITH authentication_ldap_sasl
  AS 'uid=boris_ldap,ou=People,dc=example,dc=com';

    The authentication string specified in the BY clause does not include the LDAP password. That must be provided by the client user at connect time.

    Clients connect to the MySQL server by providing the MySQL user name and LDAP password:

    shell> mysql --user=boris --password
Enter password: boris_password (boris_ldap LDAP password)

    For the server-side authentication_ldap_sasl plugin, clients use the client-side authentication_ldap_sasl_client plugin. If a client program does not find the client-side plugin, specify a --plugin-dir option that names the directory where the plugin library file is installed.

    The authentication process for boris is similar to that previously described for betsy with simple LDAP authentication, except that the client-side and server-side SASL LDAP plugins use SASL messages for secure transmission of credentials within the LDAP protocol, to avoid sending the cleartext password between the MySQL client and server.

    LDAP Authentication User DN Suffixes

    LDAP authentication plugins permit the authentication string that provides user DN information to begin with a + prefix character:

    • In the absence of a + character, the authentication string value is treated as is without modification.

    • If the authentication string begins with +, the plugin constructs the full user DN value from the user name sent by the client, together with the DN specified in the authentication string (with the + removed). In the constructed DN, the client user name becomes the value of the attribute that specifies LDAP user names. This is uid by default; to change the attribute, modify the authentication_ldap_simple_user_search_attr or authentication_ldap_sasl_user_search_attr system variable appropriately. The authentication string is stored as given in the mysql.user system table, with the full user DN constructed on the fly before authentication.

    This account authentication string does not have + at the beginning, so it is taken as the full user DN:

    CREATE USER 'baldwin'
  IDENTIFIED WITH authentication_ldap_simple
  AS 'uid=admin,ou=People,dc=example,dc=com';

    The client connects with the user name specified in the account (baldwin). In this case, that name is not used because the authentication string has no prefix and thus fully specifies the user DN.

    This account authentication string does have + at the beginning, so it is taken as just part of the user DN:

    CREATE USER 'accounting'
  IDENTIFIED WITH authentication_ldap_simple
  AS '+ou=People,dc=example,dc=com';

    The client connects with the user name specified in the account (accounting), which in this case is used as the uid attribute together with the authentication string to construct the user DN: uid=accounting,ou=People,dc=example,dc=com

    The accounts in the preceding examples have a nonempty user name, so the client always connects to the MySQL server using the same name as specified in the account definition. If an account has an empty user name, such as the default anonymous ''@'%' proxy account described in LDAP Authentication with Proxying, clients might connect to the MySQL server with varying user names. But the principle is the same: If the authentication string begins with +, the plugin uses the user name sent by the client together with the authentication string to construct the user DN.

    LDAP Authentication with Proxying

    LDAP authentication plugins support proxying, enabling a user to connect to the MySQL server as one user but assume the privileges of a different user. This section describes basic LDAP plugin proxy support. The LDAP plugins also support specification of group preference and proxy user mapping; see LDAP Authentication Group Preference and Mapping Specification.

    The authentication scheme described here uses proxying based on mapping LDAP group attribute values to connecting MySQL users who authenticate using LDAP onto other MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy account authenticated with LDAP, such that all the external logins are mapped to the MySQL accounts that hold the privileges. Any user who connects using the proxy account is mapped to one of those MySQL accounts, the privileges for which determine the database operations permitted to the external user.

    The instructions here assume the following scenario:

    • LDAP entries use the uid and cn attributes to specify user name and group values, respectively. To use different user and group attribute names, set the appropriate system variables to configure the plugin:

    • These LDAP entries are available in the directory managed by the LDAP server, to provide distinguished name values that uniquely identify each user:

      uid=basha,ou=People,dc=example,dc=com,cn=accounting
uid=basil,ou=People,dc=example,dc=com,cn=front_office

      The group attribute values will become the authenticated user names, so they name the accounting and front_office proxied accounts.

    • The examples assume use of SASL LDAP authentication. Make the appropriate adjustments for simple LDAP authentication.

    Create the default proxy MySQL account:

    CREATE USER ''@'%'
  IDENTIFIED WITH authentication_ldap_simple;

    The proxy account definition has no AS 'auth_string' clause to name an LDAP user DN. Thus:

    • When a client connects, the client user name is used as the LDAP user name to search for.

    • The matching LDAP entry is expected to include a group attribute naming the proxied MySQL account that defines the privileges the client should have.

    Note

    If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see Default Proxy User and Anonymous User Conflicts.

    Create the proxied accounts and grant to them the privileges required for MySQL access:

    CREATE USER 'accounting'@'localhost'
  IDENTIFIED WITH mysql_no_login;
CREATE USER 'front_office'@'localhost'
  IDENTIFIED WITH mysql_no_login;

GRANT ALL PRIVILEGES
  ON accountingdb.*
  TO 'accounting'@'localhost';
GRANT ALL PRIVILEGES
  ON frontdb.*
  TO 'front_office'@'localhost';

    The proxied accounts use the mysql_no_login authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, it is expected that users who authenticate using LDAP will use the default ''@'%' proxy account. (This assumes that the mysql_no_login plugin is installed. For instructions, see Section 6.4.1.8, “No-Login Pluggable Authentication”.) For alternative methods of protecting proxied accounts against direct use, see Preventing Direct Login to Proxied Accounts.

    Grant to the proxy account the PROXY privilege for each proxied account:

    GRANT PROXY
  ON 'accounting'@'localhost'
  TO ''@'%';
GRANT PROXY
  ON 'front_office'@'localhost'
  TO ''@'%';

    Use the mysql command-line client to connect to the MySQL server as basha.

    shell> mysql --user=basha --password
Enter password: basha_password (basha LDAP password)

    Authentication occurs as follows:

    1. The server authenticates the connection using the default ''@'%' proxy account, for client user basha.

    2. The matching LDAP entry is:

      uid=basha,ou=People,dc=example,dc=com,cn=accounting

    3. The matching LDAP entry has group attribute cn=accounting, so accounting becomes the authenticated user.

    4. The authenticated user differs from the client user name basha, with the result that basha is treated as a proxy for accounting, and basha assumes the privileges of the accounting account. The following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-----------------+----------------------+--------------+
| USER()          | CURRENT_USER()       | @@proxy_user |
+-----------------+----------------------+--------------+
| basha@localhost | accounting@localhost | ''@'%'       |
+-----------------+----------------------+--------------+

    This demonstrates that basha uses the privileges granted to the accounting MySQL account, and that proxying occurs through the default proxy user account.

    Now connect as basil instead:

    shell> mysql --user=basil --password
Enter password: basil_password (basil LDAP password)

    The authentication process for basil is similar to that previously described for basha:

    1. The server authenticates the connection using the default ''@'%' proxy account, for client user basil.

    2. The matching LDAP entry is:

      uid=basil,ou=People,dc=example,dc=com,cn=front_office

    3. The matching LDAP entry has group attribute cn=front_office, so front_office becomes the authenticated user.

    4. The authenticated user differs from the client user name basil, with the result that basil is treated as a proxy for front_office, and basil assumes the privileges of the front_office account. The following query returns output as shown:

      mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-----------------+------------------------+--------------+
| USER()          | CURRENT_USER()         | @@proxy_user |
+-----------------+------------------------+--------------+
| basil@localhost | front_office@localhost | ''@'%'       |
+-----------------+------------------------+--------------+

    This demonstrates that basil uses the privileges granted to the front_office MySQL account, and that proxying occurs through the default proxy user account.

    LDAP Authentication Group Preference and Mapping Specification

    As described in LDAP Authentication with Proxying, basic LDAP authentication proxying works by the principle that the plugin uses the first group name returned by the LDAP server as the MySQL proxy user account name. This simple capability does not enable specifying any preference about which group name to use if the LDAP server returns multiple group names, or specifying any name other than the group name as the proxy user name.

    As of MySQL 8.0.14, for MySQL accounts that use LDAP authentication, the authentication string can specify the following information to enable greater proxying flexibility:

    • A list of groups in preference order, such that the plugin uses the first group name in the list that matches a group returned by the LDAP server.

    • A mapping from group names to proxy user names, such that a group name when matched can provide a specified name to use as the proxy user. This provides an alternative to using the group name as the proxy user.

    Consider the following MySQL proxy account definition:

    CREATE USER ''@'%'
  IDENTIFIED WITH authentication_ldap_sasl
  AS '+ou=People,dc=example,dc=com#grp1=usera,grp2,grp3=userc';

    The authentication string has a user DN suffix ou=People,dc=example,dc=com prefixed by the + character. Thus, as described in LDAP Authentication User DN Suffixes, the full user DN is constructed from the user DN suffix as specified, plus the client user name as the uid attribute.

    The remaining part of the authentication string begins with #, which signifies the beginning of group preference and mapping information. This part of the authentication string lists group names in the order grp1, grp2, grp3. The LDAP plugin compares that list with the set of group names returned by the LDAP server, looking in list order for a match against the returned names. The plugin uses the first match, or if there is no match, authentication fails.

    Suppose that the LDAP server returns groups grp3, grp2, and grp7. The LDAP plugin uses grp2 because it is the first group in the authentication string that matches, even though it is not the first group returned by the LDAP server. If the LDAP server returns grp4, grp2, and grp1, the plugin uses grp1 even though grp2 also matches. grp1 has a precedence higher than grp2 because it is listed earlier in the authentication string.

    Assuming that the plugin finds a group name match, it performs mapping from that group name to the MySQL proxy user name, if there is one. For the example proxy account, mapping occurs as follows:

    • If the matching group name is grp1 or grp3, those are associated in the authentication string with user names usera and userc, respectively. The plugin uses the corresponding associated user name as the proxy user name.

    • If the matching group name is grp2, there is no associated user name in the authentication string. The plugin uses grp2 as the proxy user name.

    If the LDAP server returns a group in DN format, the LDAP plugin parses the group DN to extract the group name from it.

    To specify LDAP group preference and mapping information, these principles apply:

    • Begin the group preference and mapping part of the authentication string with a # prefix character.

    • The group preference and mapping specification is a list of one or more items, separated by commas. Each item has the form group_name=user_name or group_name. Items should be listed in group name preference order. For a group name selected by the plugin as a match from set of group names returned by the LDAP server, the two syntaxes differ in effect as follows:

      • For an item specified as group_name=user_name (with a user name), the group name maps to the user name, which is used as the MySQL proxy user name.

      • For an item specified as group_name (with no user name), the group name is used as the MySQL proxy user name.

    • To quote a group or user name that contains special characters such as space, surround it by double quote (") characters. For example, if an item has group and user names of my group name and my user name, it must be written in a group mapping using quotes:

      "my group name"="my user name"

      If an item has group and user names of my_group_name and my_user_name (which contain no special characters), it may but need not be written using quotes. Any of the following are valid:

      my_group_name=my_user_name
my_group_name="my_user_name"
"my_group_name"=my_user_name
"my_group_name"="my_user_name"

    • To escape a character, precede it by a backslash (). This is useful particularly to include a literal double quote or backslash, which are otherwise not included literally.

    • A user DN need not be present in the authentication string, but if present, it must precede the group preference and mapping part. A user DN can be given as a full user DN, or as a user DN suffix with a + prefix character.

    6.4.1.8 No-Login Pluggable Authentication

    The mysql_no_login server-side authentication plugin prevents all client connections to any account that uses it. Use cases for this plugin include:

    • Accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users.

    • Proxied accounts that should never permit direct login but are intended to be accessed only through proxy accounts.

    The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by the plugin_dir system variable.

    Table 6.20 Plugin and Library Names for No-Login Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin mysql_no_login
    Client-side plugin None
    Library file mysql_no_login.so

    The following sections provide installation and usage information specific to no-login pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”. For proxy user information, see Section 6.2.18, “Proxy Users”.

    Installing No-Login Pluggable Authentication

    This section describes how to install the no-login authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

    To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

    The plugin library file base name is mysql_no_login. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

    To load the plugin at server startup, use the --plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary): 

    [mysqld]
plugin-load-add=mysql_no_login.so

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to load the plugin at runtime, use this statement (adjust the .so suffix for your platform as necessary):

    INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';

    INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

    To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

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

    If the plugin fails to initialize, check the server error log for diagnostic messages.

    To associate MySQL accounts with the no-login plugin, see Using No-Login Pluggable Authentication.

    Uninstalling No-Login Pluggable Authentication

    The method used to uninstall the no-login authentication plugin depends on how you installed it:

    • If you installed the plugin at server startup using a --plugin-load-add option, restart the server without the option.

    • If you installed the plugin at runtime using an INSTALL PLUGIN statement, it remains installed across server restarts. To uninstall it, use UNINSTALL PLUGIN:

      UNINSTALL PLUGIN mysql_no_login;
    Using No-Login Pluggable Authentication

    This section describes how to use the no-login authentication plugin to prevent accounts from being used for connecting from MySQL client programs to the server. It is assumed that the server is running with the no-login plugin enabled, as described in Installing No-Login Pluggable Authentication.

    To refer to the no-login authentication plugin in the IDENTIFIED WITH clause of a CREATE USER statement, use the name mysql_no_login.

    An account that authenticates using mysql_no_login may be used as the DEFINER for stored program and view objects. If such an object definition also includes SQL SECURITY DEFINER, it executes with that account's privileges. DBAs can use this behavior to provide access to confidential or sensitive data that is exposed only through well-controlled interfaces.

    The following example illustrates these principles. It defines an account that does not permit client connections, and associates with it a view that exposes only certain columns of the mysql.user system table:

    CREATE DATABASE nologindb;
CREATE USER 'nologin'@'localhost'
  IDENTIFIED WITH mysql_no_login;
GRANT ALL ON nologindb.*
  TO 'nologin'@'localhost';
GRANT SELECT ON mysql.user
  TO 'nologin'@'localhost';
CREATE DEFINER = 'nologin'@'localhost'
  SQL SECURITY DEFINER
  VIEW nologindb.myview
  AS SELECT User, Host FROM mysql.user;

    To provide protected access to the view to an ordinary user, do this:

    GRANT SELECT ON nologindb.myview
  TO 'ordinaryuser'@'localhost';

    Now the ordinary user can use the view to access the limited information it presents:

    SELECT * FROM nologindb.myview;

    Attempts by the user to access columns other than those exposed by the view result in an error, as do attempts to select from the view by users not granted access to it.

    Note

    Because the nologin account cannot be used directly, the operations required to set up objects that it uses must be performed by root or similar account that has the privileges required to create the objects and set DEFINER values.

    The mysql_no_login plugin is also useful in proxying scenarios. (For a discussion of concepts involved in proxying, see Section 6.2.18, “Proxy Users”.) An account that authenticates using mysql_no_login may be used as a proxied user for proxy accounts:

    -- create proxied account
CREATE USER 'proxied_user'@'localhost'
  IDENTIFIED WITH mysql_no_login;
-- grant privileges to proxied account
GRANT ...
  ON ...
  TO 'proxied_user'@'localhost';
-- permit proxy_user to be a proxy account for proxied account
GRANT PROXY
  ON 'proxied_user'@'localhost'
  TO 'proxy_user'@'localhost';

    This enables clients to access MySQL through the proxy account (proxy_user) but not to bypass the proxy mechanism by connecting directly as the proxied user (proxied_user). A client who connects using the proxy_user account has the privileges of the proxied_user account, but proxied_user itself cannot be used to connect.

    For alternative methods of protecting proxied accounts against direct use, see Preventing Direct Login to Proxied Accounts.

    6.4.1.9 Socket Peer-Credential Pluggable Authentication

    The server-side auth_socket authentication plugin authenticates clients that connect from the local host through the Unix socket file. The plugin uses the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux.

    The source code for this plugin can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.

    The following table shows the plugin and library file names. The file must be located in the directory named by the plugin_dir system variable.

    Table 6.21 Plugin and Library Names for Socket Peer-Credential Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin auth_socket
    Client-side plugin None, see discussion
    Library file auth_socket.so

    The following sections provide installation and usage information specific to socket pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

    Installing Socket Pluggable Authentication

    This section describes how to install the socket authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

    To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

    To load the plugin at server startup, use the --plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file: 

    [mysqld]
plugin-load-add=auth_socket.so

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to load the plugin at runtime, use this statement:

    INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

    INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

    To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

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

    If the plugin fails to initialize, check the server error log for diagnostic messages.

    To associate MySQL accounts with the socket plugin, see Using Socket Pluggable Authentication.

    Uninstalling Socket Pluggable Authentication

    The method used to uninstall the socket authentication plugin depends on how you installed it:

    • If you installed the plugin at server startup using a --plugin-load-add option, restart the server without the option.

    • If you installed the plugin at runtime using an INSTALL PLUGIN statement, it remains installed across server restarts. To uninstall it, use UNINSTALL PLUGIN:

      UNINSTALL PLUGIN auth_socket;
    Using Socket Pluggable Authentication

    The socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server. If the names do not match, the plugin checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user system table row. If a match is found, the plugin permits the connection. The authentication_string value can be specified using an IDENTIFIED ...AS clause with CREATE USER or ALTER USER.

    Suppose that a MySQL account is created for an operating system 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.

    To permit both the valerie and stephanie operating system users to access MySQL through socket file connections that use the account, this can be done two ways:

    • Name both users at account-creation time, one following CREATE USER, and the other in the authentication string:

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

    • If you have already used CREATE USER to create the account for a single user, use ALTER USER to add the second user:

      CREATE USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket;
ALTER USER 'valerie'@'localhost' IDENTIFIED WITH auth_socket AS 'stephanie';

    To access the account, both valerie and stephanie specify --user=valerie at connect time.

    6.4.1.10 Test Pluggable Authentication

    MySQL includes a test plugin that checks account credentials and logs success or failure to the server error log. This is a loadable plugin (not built in) and must be installed prior to use.

    The test plugin source code is separate from the server source, unlike the built-in native plugin, so it can be examined as a relatively simple example demonstrating how to write a loadable authentication plugin.

    Note

    This plugin is intended for testing and development purposes, and is not for use in production environments or on servers that are exposed to public networks.

    The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by the plugin_dir system variable.

    Table 6.22 Plugin and Library Names for Test Authentication

    Plugin or FilePlugin or File Name
    Server-side plugin test_plugin_server
    Client-side plugin auth_test_plugin
    Library file auth_test_plugin.so

    The following sections provide installation and usage information specific to test pluggable authentication:

    For general information about pluggable authentication in MySQL, see Section 6.2.17, “Pluggable Authentication”.

    Installing Test Pluggable Authentication

    This section describes how to install the test authentication plugin. For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.

    To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.

    To load the plugin at server startup, use the --plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the server my.cnf file (adjust the .so suffix for your platform as necessary): 

    [mysqld]
plugin-load-add=auth_test_plugin.so

    After modifying my.cnf, restart the server to cause the new settings to take effect.

    Alternatively, to load the plugin at runtime, use this statement (adjust the .so suffix for your platform as necessary):

    INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';

    INSTALL PLUGIN loads the plugin immediately, and also registers it in the mysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

    To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:

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

    If the plugin fails to initialize, check the server error log for diagnostic messages.

    To associate MySQL accounts with the test plugin, see Using Test Pluggable Authentication.

    Uninstalling Test Pluggable Authentication

    The method used to uninstall the test authentication plugin depends on how you installed it:

    • If you installed the plugin at server startup using a --plugin-load-add option, restart the server without the option.

    • If you installed the plugin at runtime using an INSTALL PLUGIN statement, it remains installed across server restarts. To uninstall it, use UNINSTALL PLUGIN:

      UNINSTALL PLUGIN test_plugin_server;
    Using Test Pluggable Authentication

    To use the test authentication plugin, create an account and name that plugin in the IDENTIFIED WITH clause:

    CREATE USER 'testuser'@'localhost'
IDENTIFIED WITH test_plugin_server
BY 'testpassword';

    Then provide the --user and --password options for that account when you connect to the server. For example:

    shell> mysql --user=testuser --password
Enter password: testpassword

    The plugin fetches the password as received from the client and compares it with the value stored in the authentication_string column of the account row in the mysql.user system table. If the two values match, the plugin returns the authentication_string value as the new effective user ID.

    You can look in the server error log for a message indicating whether authentication succeeded (notice that the password is reported as the “user”): 

    [Note] Plugin test_plugin_server reported:
'successfully authenticated user testpassword'

    6.4.1.11 Pluggable Authentication System Variables

    These variables are unavailable unless the appropriate server-side plugin is installed:

    • authentication_ldap_sasl for system variables with names of the form authentication_ldap_sasl_xxx

    • authentication_ldap_simple for system variables with names of the form authentication_ldap_simple_xxx

    Table 6.23 Authentication Plugin System Variable Summary

    NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
    authentication_ldap_sasl_auth_method_name Yes Yes Yes   Global Yes
    authentication_ldap_sasl_bind_base_dn Yes Yes Yes   Global Yes
    authentication_ldap_sasl_bind_root_dn Yes Yes Yes   Global Yes
    authentication_ldap_sasl_bind_root_pwd Yes Yes Yes   Global Yes
    authentication_ldap_sasl_ca_path Yes Yes Yes   Global Yes
    authentication_ldap_sasl_group_search_attr Yes Yes Yes   Global Yes
    authentication_ldap_sasl_group_search_filter Yes Yes Yes   Global Yes
    authentication_ldap_sasl_init_pool_size Yes Yes Yes   Global Yes
    authentication_ldap_sasl_log_status Yes Yes Yes   Global Yes
    authentication_ldap_sasl_max_pool_size Yes Yes Yes   Global Yes
    authentication_ldap_sasl_server_host Yes Yes Yes   Global Yes
    authentication_ldap_sasl_server_port Yes Yes Yes   Global Yes
    authentication_ldap_sasl_tls Yes Yes Yes   Global Yes
    authentication_ldap_sasl_user_search_attr Yes Yes Yes   Global Yes
    authentication_ldap_simple_auth_method_name Yes Yes Yes   Global Yes
    authentication_ldap_simple_bind_base_dn Yes Yes Yes   Global Yes
    authentication_ldap_simple_bind_root_dn Yes Yes Yes   Global Yes
    authentication_ldap_simple_bind_root_pwd Yes Yes Yes   Global Yes
    authentication_ldap_simple_ca_path Yes Yes Yes   Global Yes
    authentication_ldap_simple_group_search_attr Yes Yes Yes   Global Yes
    authentication_ldap_simple_group_search_filter Yes Yes Yes   Global Yes
    authentication_ldap_simple_init_pool_size Yes Yes Yes   Global Yes
    authentication_ldap_simple_log_status Yes Yes Yes   Global Yes
    authentication_ldap_simple_max_pool_size Yes Yes Yes   Global Yes
    authentication_ldap_simple_server_host Yes Yes Yes   Global Yes
    authentication_ldap_simple_server_port Yes Yes Yes   Global Yes
    authentication_ldap_simple_tls Yes Yes Yes   Global Yes
    authentication_ldap_simple_user_search_attr Yes Yes Yes   Global Yes
    authentication_windows_log_level Yes Yes Yes   Global No
    authentication_windows_use_principal_name Yes Yes Yes   Global No

    • authentication_ldap_sasl_auth_method_name

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-auth-method-name=value
      System Variable authentication_ldap_sasl_auth_method_name
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value SCRAM-SHA-1

      For SASL LDAP authentication, the authentication method name. Communication between the authentication plugin and the LDAP server occurs according to this authentication method. These authentication method values are permitted:

      • SCRAM-SHA-1: Authentication uses a SASL challenge-response mechanism to ensure password security.

        The client-side authentication_ldap_sasl_client plugin communicates with the SASL server, using the password to create a challenge and obtain a SASL request buffer, then passes this buffer to the server-side authentication_ldap_sasl plugin. The client-side and server-side SASL LDAP plugins use SASL messages for secure transmission of credentials within the LDAP protocol, to avoid sending the cleartext password between the MySQL client and server.

    • authentication_ldap_sasl_bind_base_dn

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-bind-base-dn=value
      System Variable authentication_ldap_sasl_bind_base_dn
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For SASL LDAP authentication, the base distinguished name (DN). This variable can be used to limit the scope of searches by anchoring them at a certain location (the “base”) within the search tree.

      Suppose that members of one set of LDAP user entries each have this form: 

      uid=user_name,ou=People,dc=example,dc=com

      And that members of another set of LDAP user entries each have this form:

      uid=user_name,ou=Admin,dc=example,dc=com

      Then searches work like this for different base DN values:

      • If the base DN is ou=People,dc=example,dc=com: Searches find user entries only in the first set.

      • If the base DN is ou=Admin,dc=example,dc=com: Searches find user entries only in the second set.

      • If the base DN is ou=dc=example,dc=com: Searches find user entries in the first or second set.

      In general, more specific base DN values result in faster searches because they limit the search scope more.

    • authentication_ldap_sasl_bind_root_dn

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-bind-root-dn=value
      System Variable authentication_ldap_sasl_bind_root_dn
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For SASL LDAP authentication, the root distinguished name (DN). This variable is used in conjunction with authentication_ldap_sasl_bind_root_pwd as the credentials for authenticating to the LDAP server for the purpose of performing searches. Authentication uses either one or two LDAP bind operations, depending on whether the MySQL account names an LDAP user DN:

      • If the account does not name a user DN: authentication_ldap_sasl performs an initial LDAP binding using authentication_ldap_sasl_bind_root_dn and authentication_ldap_sasl_bind_root_pwd. (These are both empty by default, so if they are not set, the LDAP server must permit anonymous connections.) The resulting bind LDAP handle is used to search for the user DN, based on the client user name. authentication_ldap_sasl performs a second bind using the user DN and client-supplied password.

      • If the account does name a user DN: The first bind operation is unnecessary in this case. authentication_ldap_sasl performs a single bind using the user DN and client-supplied password. This is faster than if the MySQL account does not specify an LDAP user DN.

    • authentication_ldap_sasl_bind_root_pwd

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-bind-root-pwd=value
      System Variable authentication_ldap_sasl_bind_root_pwd
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For SASL LDAP authentication, the password for the root distinguished name. This variable is used in conjunction with authentication_ldap_sasl_bind_root_dn. See the description of that variable.

    • authentication_ldap_sasl_ca_path

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-ca-path=value
      System Variable authentication_ldap_sasl_ca_path
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For SASL LDAP authentication, the absolute path of the certificate authority file. Specify this file if it is desired that the authentication plugin perform verification of the LDAP server certificate.

      Note

      In addition to setting the authentication_ldap_sasl_ca_path variable to the file name, you must add the appropriate certificate authority certificates to the file and enable the authentication_ldap_sasl_tls system variable.

    • authentication_ldap_sasl_group_search_attr

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-group-search-attr=value
      System Variable authentication_ldap_sasl_group_search_attr
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value cn

      For SASL LDAP authentication, the name of the attribute that specifies group names in LDAP directory entries. If authentication_ldap_sasl_group_search_attr has its default value of cn, searches return the cn value as the group name. For example, if an LDAP entry with a uid value of user1 has a cn attribute of mygroup, searches for user1 return mygroup as the group name.

      This variable should be the empty string if you want no group or proxy authentication.

      If the group search attribute is isMemberOf, LDAP authentication directly retrieves the user attribute isMemberOf value and assigns it as group information. If the group search attribute is not isMemberOf, LDAP authentication searches for all groups where the user is a member. (The latter is the default behavior.) This behavior is based on how LDAP group information can be stored two ways: 1) A group entry can have an attribute named memberUid or member with a value that is a user name; 2) A user entry can have an attribute named isMemberOf with values that are group names.

    • authentication_ldap_sasl_group_search_filter

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-group-search-filter=value
      System Variable authentication_ldap_sasl_group_search_filter
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value (|(&(objectClass=posixGroup)(memberUid=%s))(&(objectClass=group)(member=%s)))

      For SASL LDAP authentication, the custom group search filter.

      The search filter value can contain {UA} and {UD} notation to represent the user name and the full user DN. For example, {UA} is replaced with a user name such as "admin", whereas {UD} is replaced with a use full DN such as "uid=admin,ou=People,dc=example,dc=com". The following value is the default, which supports both OpenLDAP and Active Directory:

      (|(&(objectClass=posixGroup)(memberUid={UA}))
  (&(objectClass=group)(member={UD})))

      In some cases for the user scenario, memberOf is a simple user attribute that holds no group information. For additional flexibility, an optional {GA} prefix can be used with the group search attribute. Any group attribute with a {GA} prefix is treated as a user attribute having group names. For example, with a value of {GA}MemberOf, if the group value is the DN, the first attribute value from the group DN is returned as the group name.

    • authentication_ldap_sasl_init_pool_size

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-init-pool-size=#
      System Variable authentication_ldap_sasl_init_pool_size
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 10
      Minimum Value 0
      Maximum Value 32767

      For SASL LDAP authentication, the initial size of the pool of connections to the LDAP server. Choose the value for this variable based on the average number of concurrent authentication requests to the LDAP server.

      The plugin uses authentication_ldap_sasl_init_pool_size and authentication_ldap_sasl_max_pool_size together for connection-pool management:

      • When the authentication plugin initializes, it creates authentication_ldap_sasl_init_pool_size connections, unless authentication_ldap_sasl_max_pool_size=0 to disable pooling.

      • If the plugin receives an anthentication request when there are no free connections in the current connection pool, the plugin can create a new connection, up to the maximum connection pool size given by authentication_ldap_sasl_max_pool_size.

      • If the plugin receives a request when the pool size is already at its maximum and there are no free connections, authentication fails.

      • When the plugin unloads, it closes all pooled connections.

      Changes to plugin system variable settings may have no effect on connections already in the pool. For example, modifying the LDAP server host, port, or TLS settings does not affect existing connections. However, if the original variable values were invalid and the connection pool could not be initialized, the plugin attempts to reinitialize the pool for the next LDAP request. In this case, the new system variable values are used for the reinitialization attempt.

      If authentication_ldap_sasl_max_pool_size=0 to disable pooling, each LDAP connection opened by the plugin uses the values the system variables have at that time.

    • authentication_ldap_sasl_log_status

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-log-status=#
      System Variable authentication_ldap_sasl_log_status
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 1
      Minimum Value 1
      Maximum Value (>= 8.0.18) 6
      Maximum Value (<= 8.0.17) 5

      For SASL LDAP authentication, the logging level for messages written to the error log. The following table shows the permitted level values and their meanings.

      Table 6.24 Log Levels for authentication_ldap_sasl_log_status

      Option ValueTypes of Messages Logged
      1 No messages
      2 Error messages
      3 Error and warning messages
      4 Error, warning, and information messages
      5 Same as previous level plus debugging messages from MySQL
      6 Same as previous level plus debugging messages from LDAP library

      Log level 6 is available as of MySQL 8.0.18.

      On the client side, messages can be logged to the standard output by setting the AUTHENTICATION_LDAP_CLIENT_LOG environment variable. The permitted and default values are the same as for authentication_ldap_sasl_log_status.

      The AUTHENTICATION_LDAP_CLIENT_LOG environment variable applies only to SASL LDAP authentication. It has no effect for simple LDAP authentication because the client plugin in that case is mysql_clear_password, which knows nothing about LDAP operations.

    • authentication_ldap_sasl_max_pool_size

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-max-pool-size=#
      System Variable authentication_ldap_sasl_max_pool_size
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 1000
      Minimum Value 0
      Maximum Value 32767

      For SASL LDAP authentication, the maximum size of the pool of connections to the LDAP server. To disable connection pooling, set this variable to 0.

      This variable is used in conjunction with authentication_ldap_sasl_init_pool_size. See the description of that variable.

    • authentication_ldap_sasl_server_host

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-server-host=host_name
      System Variable authentication_ldap_sasl_server_host
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String

      For SASL LDAP authentication, the LDAP server host. The permitted values for this variable depend on the authentication method:

    • authentication_ldap_sasl_server_port

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-server-port=port_num
      System Variable authentication_ldap_sasl_server_port
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 389
      Minimum Value 1
      Maximum Value 32376

      For SASL LDAP authentication, the LDAP server TCP/IP port number.

      As of MySQL 8.0.14, if the LDAP port number is configured as 636 or 3269, the plugin uses LDAPS (LDAP over SSL) instead of LDAP. (LDAPS differs from startTLS.)

    • authentication_ldap_sasl_tls

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-tls[={OFF|ON}]
      System Variable authentication_ldap_sasl_tls
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Boolean
      Default Value OFF

      For SASL LDAP authentication, whether connections by the plugin to the LDAP server are secure. If this variable is enabled, the plugin uses TLS to connect securely to the LDAP server. If you enable this variable, you may also wish to set the authentication_ldap_sasl_ca_path variable.

      MySQL LDAP plugins support the StartTLS method, which initializes TLS on top of a plain LDAP connection. The ldaps method is deprecated and MySQL does not support it.

    • authentication_ldap_sasl_user_search_attr

      PropertyValue
      Command-Line Format --authentication-ldap-sasl-user-search-attr=value
      System Variable authentication_ldap_sasl_user_search_attr
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value uid

      For SASL LDAP authentication, the name of the attribute that specifies user names in LDAP directory entries. If a user distinguished name is not provided, the authentication plugin searches for the name using this attribute. For example, if the authentication_ldap_sasl_user_search_attr value is uid, a search for the user name user1 finds entries with a uid value of user1.

    • authentication_ldap_simple_auth_method_name

      PropertyValue
      Command-Line Format --authentication-ldap-simple-auth-method-name=value
      System Variable authentication_ldap_simple_auth_method_name
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value SIMPLE

      For simple LDAP authentication, the authentication method name. Communication between the authentication plugin and the LDAP server occurs according to this authentication method. These authentication method values are permitted:

      • SIMPLE: This authentication method uses either one or two LDAP bind operations, depending on whether the MySQL account names an LDAP user distinguished name. See the description of authentication_ldap_simple_bind_root_dn.

      • AD-FOREST: authentication_ldap_simple searches all the domains in the Active Directory forest, performing an LDAP bind to each Active Directory domain until the user is found in some domain.

      Note

      For simple LDAP authentication, it is recommended to also set TLS parameters to require that communication with the LDAP server take place over secure connections.

    • authentication_ldap_simple_bind_base_dn

      PropertyValue
      Command-Line Format --authentication-ldap-simple-bind-base-dn=value
      System Variable authentication_ldap_simple_bind_base_dn
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For simple LDAP authentication, the base distinguished name (DN). This variable can be used to limit the scope of searches by anchoring them at a certain location (the “base”) within the search tree.

      Suppose that members of one set of LDAP user entries each have this form: 

      uid=user_name,ou=People,dc=example,dc=com

      And that members of another set of LDAP user entries each have this form:

      uid=user_name,ou=Admin,dc=example,dc=com

      Then searches work like this for different base DN values:

      • If the base DN is ou=People,dc=example,dc=com: Searches find user entries only in the first set.

      • If the base DN is ou=Admin,dc=example,dc=com: Searches find user entries only in the second set.

      • If the base DN is ou=dc=example,dc=com: Searches find user entries in the first or second set.

      In general, more specific base DN values result in faster searches because they limit the search scope more.

    • authentication_ldap_simple_bind_root_dn

      PropertyValue
      Command-Line Format --authentication-ldap-simple-bind-root-dn=value
      System Variable authentication_ldap_simple_bind_root_dn
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For simple LDAP authentication, the root distinguished name (DN). This variable is used in conjunction with authentication_ldap_simple_bind_root_pwd as the credentials for authenticating to the LDAP server for the purpose of performing searches. Authentication uses either one or two LDAP bind operations, depending on whether the MySQL account names an LDAP user DN:

      • If the account does not name a user DN: authentication_ldap_simple performs an initial LDAP binding using authentication_ldap_simple_bind_root_dn and authentication_ldap_simple_bind_root_pwd. (These are both empty by default, so if they are not set, the LDAP server must permit anonymous connections.) The resulting bind LDAP handle is used to search for the user DN, based on the client user name. authentication_ldap_simple performs a second bind using the user DN and client-supplied password.

      • If the account does name a user DN: The first bind operation is unnecessary in this case. authentication_ldap_simple performs a single bind using the user DN and client-supplied password. This is faster than if the MySQL account does not specify an LDAP user DN.

    • authentication_ldap_simple_bind_root_pwd

      PropertyValue
      Command-Line Format --authentication-ldap-simple-bind-root-pwd=value
      System Variable authentication_ldap_simple_bind_root_pwd
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For simple LDAP authentication, the password for the root distinguished name. This variable is used in conjunction with authentication_ldap_simple_bind_root_dn. See the description of that variable.

    • authentication_ldap_simple_ca_path

      PropertyValue
      Command-Line Format --authentication-ldap-simple-ca-path=value
      System Variable authentication_ldap_simple_ca_path
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value NULL

      For simple LDAP authentication, the absolute path of the certificate authority file. Specify this file if it is desired that the authentication plugin perform verification of the LDAP server certificate.

      Note

      In addition to setting the authentication_ldap_simple_ca_path variable to the file name, you must add the appropriate certificate authority certificates to the file and enable the authentication_ldap_simple_tls system variable.

    • authentication_ldap_simple_group_search_attr

      PropertyValue
      Command-Line Format --authentication-ldap-simple-group-search-attr=value
      System Variable authentication_ldap_simple_group_search_attr
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value cn

      For simple LDAP authentication, the name of the attribute that specifies group names in LDAP directory entries. If authentication_ldap_simple_group_search_attr has its default value of cn, searches return the cn value as the group name. For example, if an LDAP entry with a uid value of user1 has a cn attribute of mygroup, searches for user1 return mygroup as the group name.

      If the group search attribute is isMemberOf, LDAP authentication directly retrieves the user attribute isMemberOf value and assigns it as group information. If the group search attribute is not isMemberOf, LDAP authentication searches for all groups where the user is a member. (The latter is the default behavior.) This behavior is based on how LDAP group information can be stored two ways: 1) A group entry can have an attribute named memberUid or member with a value that is a user name; 2) A user entry can have an attribute named isMemberOf with values that are group names.

    • authentication_ldap_simple_group_search_filter

      PropertyValue
      Command-Line Format --authentication-ldap-simple-group-search-filter=value
      System Variable authentication_ldap_simple_group_search_filter
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value (|(&(objectClass=posixGroup)(memberUid=%s))(&(objectClass=group)(member=%s)))

      For simple LDAP authentication, the custom group search filter.

      The search filter value can contain {UA} and {UD} notation to represent the user name and the full user DN. For example, {UA} is replaced with a user name such as "admin", whereas {UD} is replaced with a use full DN such as "uid=admin,ou=People,dc=example,dc=com". The following value is the default, which supports both OpenLDAP and Active Directory:

      (|(&(objectClass=posixGroup)(memberUid={UA}))
  (&(objectClass=group)(member={UD})))

      In some cases for the user scenario, memberOf is a simple user attribute that holds no group information. For additional flexibility, an optional {GA} prefix can be used with the group search attribute. Any group attribute with a {GA} prefix is treated as a user attribute having group names. For example, with a value of {GA}MemberOf, if the group value is the DN, the first attribute value from the group DN is returned as the group name.

    • authentication_ldap_simple_init_pool_size

      PropertyValue
      Command-Line Format --authentication-ldap-simple-init-pool-size=#
      System Variable authentication_ldap_simple_init_pool_size
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 10
      Minimum Value 0
      Maximum Value 32767

      For simple LDAP authentication, the initial size of the pool of connections to the LDAP server. Choose the value for this variable based on the average number of concurrent authentication requests to the LDAP server.

      The plugin uses authentication_ldap_simple_init_pool_size and authentication_ldap_simple_max_pool_size together for connection-pool management:

      • When the authentication plugin initializes, it creates authentication_ldap_simple_init_pool_size connections, unless authentication_ldap_simple_max_pool_size=0 to disable pooling.

      • If the plugin receives an anthentication request when there are no free connections in the current connection pool, the plugin can create a new connection, up to the maximum connection pool size given by authentication_ldap_simple_max_pool_size.

      • If the plugin receives a request when the pool size is already at its maximum and there are no free connections, authentication fails.

      • When the plugin unloads, it closes all pooled connections.

      Changes to plugin system variable settings may have no effect on connections already in the pool. For example, modifying the LDAP server host, port, or TLS settings does not affect existing connections. However, if the original variable values were invalid and the connection pool could not be initialized, the plugin attempts to reinitialize the pool for the next LDAP request. In this case, the new system variable values are used for the reinitialization attempt.

      If authentication_ldap_simple_max_pool_size=0 to disable pooling, each LDAP connection opened by the plugin uses the values the system variables have at that time.

    • authentication_ldap_simple_log_status

      PropertyValue
      Command-Line Format --authentication-ldap-simple-log-status=#
      System Variable authentication_ldap_simple_log_status
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 1
      Minimum Value 1
      Maximum Value (>= 8.0.18) 6
      Maximum Value (<= 8.0.17) 5

      For simple LDAP authentication, the logging level for messages written to the error log. The following table shows the permitted level values and their meanings.

      Table 6.25 Log Levels for authentication_ldap_simple_log_status

      Option ValueTypes of Messages Logged
      1 No messages
      2 Error messages
      3 Error and warning messages
      4 Error, warning, and information messages
      5 Same as previous level plus debugging messages from MySQL
      6 Same as previous level plus debugging messages from LDAP library

      Log level 6 is available as of MySQL 8.0.18.

    • authentication_ldap_simple_max_pool_size

      PropertyValue
      Command-Line Format --authentication-ldap-simple-max-pool-size=#
      System Variable authentication_ldap_simple_max_pool_size
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 1000
      Minimum Value 0
      Maximum Value 32767

      For simple LDAP authentication, the maximum size of the pool of connections to the LDAP server. To disable connection pooling, set this variable to 0.

      This variable is used in conjunction with authentication_ldap_simple_init_pool_size. See the description of that variable.

    • authentication_ldap_simple_server_host

      PropertyValue
      Command-Line Format --authentication-ldap-simple-server-host=host_name
      System Variable authentication_ldap_simple_server_host
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String

      For simple LDAP authentication, the LDAP server host. The permitted values for this variable depend on the authentication method:

      • For authentication_ldap_simple_auth_method_name=SIMPLE: The LDAP server host can be a host name or IP address.

      • For authentication_ldap_simple_auth_method_name=AD-FOREST. The LDAP server host can be an Active Directory domain name. For example, for an LDAP server URL of ldap://example.mem.local:389, the server name can be mem.local.

        An Active Directory forest setup can have multiple domains (LDAP server IPs), which can be discovered using DNS. On Unix and Unix-like systems, some additional setup may be required to configure your DNS server with SRV records that specify the LDAP servers for the Active Directory domain. Suppose that your configuration has these properties:

        • The name server that provides information about Active Directory domains has IP address 10.172.166.100.

        • The LDAP servers have names ldap1.mem.local through ldap3.mem.local and IP addresses 10.172.166.101 through 10.172.166.103.

        You want the LDAP servers to be discoverable using SRV searches. For example, at the command line, a command like this should list the LDAP servers: 

        host -t SRV _ldap._tcp.mem.local

        Perform the DNS configuration as follows:

        1. Add a line to /etc/resolv.conf to specify the name server that provides information about Active Directory domains:

          nameserver 10.172.166.100

        2. Configure the appropriate zone file for the name server with SRV records for the LDAP servers:

          _ldap._tcp.mem.local. 86400 IN SRV 0 100 389 ldap1.mem.local.
_ldap._tcp.mem.local. 86400 IN SRV 0 100 389 ldap2.mem.local.
_ldap._tcp.mem.local. 86400 IN SRV 0 100 389 ldap3.mem.local.

        3. It may also be necessary to specify the IP address for the LDAP servers in /etc/hosts if the server host cannot be resolved. For example, add lines like this to the file:

          10.172.166.101 ldap1.mem.local
10.172.166.102 ldap2.mem.local
10.172.166.103 ldap3.mem.local

        With the DNS configured as just described, the server-side LDAP plugin can discover the LDAP servers and will try to authenticate in all domains until authentication succeeds or there are no more servers.

        Windows needs no such settings as just described. Given the LDAP server host in the authentication_ldap_simple_server_host value, the Windows LDAP library searches all domains and attempts to authenticate.

    • authentication_ldap_simple_server_port

      PropertyValue
      Command-Line Format --authentication-ldap-simple-server-port=port_num
      System Variable authentication_ldap_simple_server_port
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Integer
      Default Value 389
      Minimum Value 1
      Maximum Value 32376

      For simple LDAP authentication, the LDAP server TCP/IP port number.

      As of MySQL 8.0.14, if the LDAP port number is configured as 636 or 3269, the plugin uses LDAPS (LDAP over SSL) instead of LDAP. (LDAPS differs from startTLS.)

    • authentication_ldap_simple_tls

      PropertyValue
      Command-Line Format --authentication-ldap-simple-tls[={OFF|ON}]
      System Variable authentication_ldap_simple_tls
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type Boolean
      Default Value OFF

      For simple LDAP authentication, whether connections by the plugin to the LDAP server are secure. If this variable is enabled, the plugin uses TLS to connect securely to the LDAP server. If you enable this variable, you may also wish to set the authentication_ldap_simple_ca_path variable.

      MySQL LDAP plugins support the StartTLS method, which initializes TLS on top of a plain LDAP connection. The ldaps method is deprecated and MySQL does not support it.

    • authentication_ldap_simple_user_search_attr

      PropertyValue
      Command-Line Format --authentication-ldap-simple-user-search-attr=value
      System Variable authentication_ldap_simple_user_search_attr
      Scope Global
      Dynamic Yes
      SET_VAR Hint Applies No
      Type String
      Default Value uid

      For simple LDAP authentication, the name of the attribute that specifies user names in LDAP directory entries. If a user distinguished name is not provided, the authentication plugin searches for the name using this attribute. For example, if the authentication_ldap_simple_user_search_attr value is uid, a search for the user name user1 finds entries with a uid value of user1.
  • 相关阅读:
    C语言实现mq收发数据的函数
    4G通信模块在ARM平台下的应用
    4G 通信模块在ARM 平台下的应用
    修改web前端访问端口
    从零开始用 Flask 搭建一个网站（一）
    Python实现Windows定时关机
    前端和后端的数据交互(jquery ajax+python flask+mysql)
    python开源项目及示例代码
    Django读取Mysql数据并显示在前端
    C++关于string的一些用法
  • 原文地址：https://www.cnblogs.com/jinzhenshui/p/12488124.html
Copyright © 2020-2023  润新知