Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.7Kb
Man Pages (Zip) - 329.5Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  LDAP Pluggable Authentication

6.5.1.8 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 http://www.mysql.com/products/.

As of MySQL 5.7.19, 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 login user, based on the LDAP group of the external user. 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 the 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 table shows the plugin and library file names. 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.15 Plugin and Library Names for LDAP Authentication

Server-side plugin namesauthentication_ldap_sasl, authentication_ldap_simple
Client-side plugin namesauthentication_ldap_sasl_client, mysql_clear_password
Library file namesauthentication_ldap_sasl.so, authentication_ldap_sasl_client.so, authentication_ldap_simple.so

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

There are two server-side LDAP plugins, each of which 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 in clear text. 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 clear-text 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.3.9, “Pluggable Authentication”. For information about the mysql_clear_password plugin, see Section 6.5.1.5, “Client-Side Cleartext Pluggable Authentication”. For proxy user information, see Section 6.3.10, “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.5.1.6, “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 plugin in clear text.

  • 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 clear-text 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 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 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 the user DN against the LDAP password. This bind using the root credentials fails if the root DN and password are set but 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.5.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, set the value of plugin_dir at server startup to tell the server the plugin directory location.

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.5.1.12, “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 authentication_ldap_sasl SONAME 'authentication_ldap_sasl.so';
INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so';

INSTALL PLUGIN loads a plugin, and also registers it in the mysql.plugins system table to cause the plugin to be loaded for each subsequent normal server 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.

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.5.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.

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_sasl;
    UNINSTALL PLUGIN authentication_ldap_simple;

In addition, remove from your my.cnf file any startup options that set LDAP plugin-related system variables.

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.

There are two server-side LDAP plugins, each of which 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 in clear text. 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 clear-text 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, but using different names here helps clarify whether an operation context is MySQL or LDAP.)

  • LDAP entries use the uid attribute to specify user names.

  • 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,pwd=pwd1,ou=People,dc=example,dc=com
    uid=boris_ldap,pwd=pwd2,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.

Simple LDAP Authentication

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

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

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

uid=betsy_ldap,pwd=pwd1,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
  BY '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: pwd1 (betsy_ldap LDAP password)
Note

The client-side mysql_clear_password plugin with which the server-side authentication_ldap_simple plugin communicates sends the password to the MySQL server in clear text so it can be passed as is to the LDAP server. This 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:

  • To make inadvertent use of the mysql_clear_password plugin less likely, MySQL clients must explicitly enable it; for example, with the --enable-cleartext-plugin option.

  • To avoid password exposure with the mysql_clear_password plugin enabled, MySQL clients should connect to the MySQL server using a secure connection.

For additional information, see Section 6.5.1.5, “Client-Side Cleartext Pluggable Authentication”, and Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.

The authentication process occurs as follows:

  1. The client-side plugin sends betsy and pwd1 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 should specify the authentication_ldap_sasl plugin, and optionally name the LDAP user distinguished name (DN):

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

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

uid=boris_ldap,pwd=pwd2,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
  BY '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: pwd2 (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 clear-text password between the MySQL client and server.

LDAP Authentication with Proxying

The authentication scheme described here uses proxying based on LDAP group attribute values to map 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:

  • 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,pwd=pwd3,ou=People,dc=example,dc=com,cn=accounting
    uid=basil,pwd=pwd4,ou=People,dc=example,dc=com,cn=front_office

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

  • 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_sasl;

The proxy account definition has no BY 'auth_string' clause to name an LDSP user DN, so that when clients connect, 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 problem, and ways of dealing with it, see Default Proxy User and Anonymous User Conflicts.

Create the proxied accounts and grant their privileges:

CREATE USER 'accounting'@'localhost' ACCOUNT LOCK;
CREATE USER 'front_office'@'localhost' ACCOUNT LOCK;

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

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

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

Connect to the MySQL server as basha using the mysql command-line client:

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

The server authenticates the connection using the ''@'%' account, for client user basha. The matching LDAP entry has group attribute cn=accounting, so accounting becomes the authenticated user. This 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 should return 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 occurred through the default proxy user account.

Now connect as basil instead:

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

The authentication process for basil is similar to that previously described for basha. In this case, the matching LDAP entry has group attribute cn=front_office, so front_office becomes the authenticated user. This 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 should return 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 occurred through the default proxy user account.


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