6.4.1.7 LDAP Pluggable Authentication

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

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:

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

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

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_sasl and authentication_ldap_simple. 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_sasl.so
authentication_ldap_sasl_server_host=127.0.0.1
authentication_ldap_sasl_bind_base_dn="dc=example,dc=com"
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"

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

Alternatively, to register the plugins at runtime, use these statements (adjust the .so suffix as necessary):

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.

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_sasl_server_host=127.0.0.1
authentication_ldap_sasl_bind_base_dn="dc=example,dc=com"
authentication_ldap_simple_server_host=127.0.0.1
authentication_ldap_simple_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 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.5.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:

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.

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

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.

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:

Overall requirements for LDAP authentication of MySQL users:

The instructions here assume the following scenario:

The instructions for setting up an account that uses LDAP authentication depend on which server-side LDAP plugin is used.

Contents Haut

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):

Suppose that a 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:

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)

The authentication process occurs as follows:

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.

Contents Haut

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):

Suppose that a 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:

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.

Contents Haut

LDAP Authentication User DN Suffixes

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

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

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:

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

Contents Haut

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 user authenticated with LDAP, such that all the external logins are mapped to the MySQL accounts that hold the privileges. Any user who connects 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:

Create the default proxy MySQL account:

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

Create the proxied accounts and grant their privileges:

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

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:

This demonstrates that basha uses the privileges granted to the accounting MySQL account, and that proxying occurred 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:

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

Contents Haut

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 names, or 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:

Consider the following MySQL proxy account definition:

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