使用 LDAP (AD) 进行 MySQL 身份验证

Using LDAP (AD) for MySQL authentication

提问by snicker

I'm trying to come up with a plan to allow users to auth with a MySQL database (many, actually) using LDAP. More specifically, ActiveDirectory. Database will likely be accessed through applications, not web. What are my options?

我正在尝试制定一个计划，允许用户使用 LDAP 对 MySQL 数据库（实际上很多）进行身份验证。更具体地说，ActiveDirectory。数据库可能会通过应用程序访问，而不是网络。我有哪些选择？

EDIT:

编辑：

Okay. It seems that there is no "official" way to allow authentication on MySQL using LDAP. What other options exist? Can we synchronize LDAP users and passwords to the MySQL user table?

好的。似乎没有“官方”方式允许使用 LDAP 对 MySQL 进行身份验证。还有哪些其他选择？我们可以将 LDAP 用户和密码同步到 MySQL 用户表吗？

采纳答案by longneck

this is possible with mysql proxy. there's a few things you need to know to make this work:

这可以通过 mysql 代理实现。要完成这项工作，您需要了解以下几点：

• mysql proxy can execute shell commands
• mysql proxy can intercept and rewrite authentication

• mysql代理可以执行shell命令
• mysql代理可以拦截和重写认证

these two pages will help you get started:

这两个页面将帮助您入门：

• example of running a shell command: http://forge.mysql.com/tools/tool.php?id=79
• example of intercepting and rewriting authentication: http://web.archive.org/web/20150329071023/http://jan.kneschke.de/2009/6/25/mysql-proxy-roles/

• 运行 shell 命令的示例：http: //forge.mysql.com/tools/tool.php?id=79
• 拦截和重写认证示例：http://web.archive.org/web/20150329071023/http: //jan.kneschke.de/2009/6/25/mysql-proxy-roles/

回答by Charalampos Serenis

You can use the auth_ldap plugin provided by Infoscope Hellas L.P. under GPL.

您可以在 GPL 下使用 Infoscope Hellas LP 提供的 auth_ldap 插件。

It can be downloaded from sourceforge at: http://sourceforge.net/projects/mysqlauthldap/

它可以从 sourceforge 下载：http: //sourceforge.net/projects/mysqlauthldap/

Homepage: http://infoscope.gr/mysqlauthldap

主页：http: //infoscope.gr/mysqlauthldap

The plugin is still a Beta and works only for UNIX installations.

该插件仍为 Beta 版，仅适用于 UNIX 安装。

回答by Dave Stokes

Please looks at https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-7.html

请看https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-7.html

This is in the 5.5.7 release candidate

这是在 5.5.7 候选版本中

回答by alastori

This is possible now with commercial extensions, e.g. with MySQL External Authentication for Windows:

这现在可以通过商业扩展实现，例如使用MySQL 外部身份验证 Windows：

This enables you to configure MySQL 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 token information in their environment without specifying an additional password.

这使您能够将 MySQL 配置为使用本机 Windows 服务来验证客户端连接。已登录 Windows 的用户可以根据其环境中的令牌信息从 MySQL 客户端程序连接到服务器，而无需指定额外的密码。

回答by Mohammed Noureldin

Now by the end of 2017, I can suggest this:

现在到 2017 年底，我可以提出以下建议：

https://www.percona.com/doc/percona-server/LATEST/security/pam_plugin.html#pam-plugin

https://www.percona.com/doc/percona-server/LATEST/security/pam_plugin.html#pam-plugin

Percona PAM Authentication Plugin is a free and Open Source implementation of the MySQL‘s authentication plugin. This plugin acts as a mediator between the MySQL server, the MySQL client, and the PAM stack. The server plugin requests authentication from the PAM stack, forwards any requests and messages from the PAM stack over the wire to the client (in cleartext) and reads back any replies for the PAM stack.

Percona PAM Authentication Plugin 是 MySQL 身份验证插件的免费开源实现。该插件充当 MySQL 服务器、MySQL 客户端和 PAM 堆栈之间的中介。服务器插件从 PAM 堆栈请求身份验证，将来自 PAM 堆栈的任何请求和消息通过线路转发到客户端（以明文形式）并读回 PAM 堆栈的任何回复。

It is NOTtested, I do not know yet how good it is.

它没有经过测试，我还不知道它有多好。

回答by Esteban Küber

Seems like you areout of luck:(

好像你是幸运的了:(

Could you use PostgreSQL?

你能用PostgreSQL吗？

This authentication method operates similarly to password except that it uses LDAP as the authentication method. LDAP is used only to validate the user name/password pairs. Therefore the user must already exist in the database before LDAP can be used for authentication. The server and parameters used are specified after the ldap key word in the file pg_hba.conf. The format of this parameter is:

ldap[s]://servername[:port]/base dn[;prefix[;suffix]]

此身份验证方法的操作与密码类似，不同之处在于它使用 LDAP 作为身份验证方法。LDAP 仅用于验证用户名/密码对。因此，在使用 LDAP 进行身份验证之前，用户必须已经存在于数据库中。使用的服务器和参数在文件pg_hba.conf 中的ldap 关键字之后指定。该参数的格式为：

ldap[s]://servername[:port]/base dn[;prefix[;suffix]]

回答by z0lo

This is definitely possible. See here: https://www.percona.com/blog/2017/04/21/how-to-setup-and-troubleshoot-percona-pam-with-ldap-for-external-authentication/

这绝对是可能的。请参阅此处：https: //www.percona.com/blog/2017/04/21/how-to-setup-and-troubleshoot-percona-pam-with-ldap-for-external-authentication/

In my environment, I did not set up Samba or NSS/SSS and I do not join the windows domain. I just treat the AD server as an LDAP endpoint. So I started from Step 9 in the above directions.

在我的环境中，我没有设置 Samba 或 NSS/SSS，也没有加入 windows 域。我只是将 AD 服务器视为 LDAP 端点。所以我从上述方向的第 9 步开始。

EDIT:Add instructions from above link as suggested by AfroThundr

编辑：按照 AfroThundr 的建议从上面的链接添加说明

Install the Percona PAM plugin:

安装 Percona PAM 插件：

mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
Query OK, 0 rows affected (0.00 sec)

Configure Percona PAM to authenticate to LDAP by creating /etc/pam.d/mysqld with this content:

通过使用以下内容创建 /etc/pam.d/mysqld 来配置 Percona PAM 以对 LDAP 进行身份验证：

auth required pam_ldap.so
account required pam_ldap.so

Create a MySQL user that will authenticate via auth_pam:

创建将通过 auth_pam 进行身份验证的 MySQL 用户：

mysql> CREATE USER user@'%' IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON testdb.* TO user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Login as this user and check grants:

以该用户身份登录并检查授权：

[root@ps-20 ~]# mysql -u user
Password: <your LDAP/AD password>
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 22
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> SHOW GRANTS;
+-----------------------------------------------------+
| Grants for user@%                                 |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%'                  |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'user'@'%' |
+---------------------------------------------------

Also beware of AppArmor - it will block the auth attempt. You may see misleading error messages in /var/log/auth.log:

还要注意 AppArmor - 它会阻止身份验证尝试。您可能会在/var/log/auth.log以下位置看到误导性错误消息：

Feb 12 13:37:36 mysqld[15164]: PAM _pam_init_handlers: no default config /etc/pam.d/other
Feb 12 13:37:36 mysqld[15164]: PAM error reading PAM configuration file
Feb 12 13:37:36 mysqld[15164]: PAM pam_start: failed to initialize handlers

You need to add the following to /etc/apparmor.d/local/usr.sbin.mysqld:

您需要将以下内容添加到/etc/apparmor.d/local/usr.sbin.mysqld：

#include <abstractions/authentication>

#include <abstractions/authentication>

and reload apparmor:

并重新加载 apparmor：

service apparmor restart

service apparmor restart

(Thanks to https://bugs.launchpad.net/ubuntu/+source/squid/+bug/1608984for leading me to the AppArmor part)

（感谢https://bugs.launchpad.net/ubuntu/+source/squid/+bug/1608984引导我进入 AppArmor 部分）

回答by brentil

Now that's it has been a decade since the original post the answer is finally yes to there being an official LDAP connection method for MySQL. It however does require you be running the MySQL Enterprise Edition or MySQL Cluster CGE versions.

现在距最初的帖子已经过去十年了，答案最终是肯定的，因为 MySQL 有一个官方的 LDAP 连接方法。但是，它确实需要您运行 MySQL Enterprise Edition 或 MySQL Cluster CGE 版本。

MySQL Enterprise Authentication

MySQL 企业身份验证

Only available in select Commercial Editions

MySQL Enterprise Edition provides ready to use external authentication modules to easily integrate existing security infrastructures, including Linux Pluggable Authentication Modules (PAM) and Windows Active Directory. By authenticating MySQL users from centralized directories, organizations can implement Single Sign On. The same user names, passwords and permissions can be used. This makes MySQL DBAs more productive by eliminating the need to manage credentials in individual systems. It also makes IT infrastructures more secure by leveraging existing security rules and processes (e.g. identifying weak passwords and managing password expiration).

MySQL users can be authenticated using PAM or native Windows OS services.

• MySQL External Authentication for PAM- Enables you to configure MySQL to use Linux PAMs (Pluggable Authentication Modules) to authenticate users via PAMs for various authentication methods, such as Linux passwords or an LDAP directory.
• MySQL External Authentication for Windows- Enables you to configure MySQL 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 token information in their environment without specifying an additional password.
• New!MySQL External Authentication for LDAP- Enables you to configure MySQL to authenticate users via LDAP (Lightweight Directory Access Protocol) servers. Users or groups of users can be specified
  in detail via LDAP specifications. Username/Password and SASL
  authentication are supported.

仅在部分商业版中可用

MySQL 企业版提供即用型外部身份验证模块，可轻松集成现有安全基础架构，包括 Linux 可插拔身份验证模块 (PAM) 和 Windows Active Directory。通过从集中目录对 MySQL 用户进行身份验证，组织可以实施单点登录。可以使用相同的用户名、密码和权限。通过消除在单个系统中管理凭据的需要，这使 MySQL DBA 的工作效率更高。它还通过利用现有的安全规则和流程（例如识别弱密码和管理密码过期）使 IT 基础设施更加安全。

MySQL 用户可以使用 PAM 或本机 Windows 操作系统服务进行身份验证。

• 用于 PAM 的 MySQL 外部身份验证- 使您能够将 MySQL 配置为使用 Linux PAM（可插拔身份验证模块）通过 PAM 对用户进行身份验证以获取各种身份验证方法，例如 Linux 密码或 LDAP 目录。
• 适用于 Windows 的 MySQL 外部身份验证- 使您能够将 MySQL 配置为使用本机 Windows 服务来验证客户端连接。已登录 Windows 的用户可以根据其环境中的令牌信息从 MySQL 客户端程序连接到服务器，而无需指定额外的密码。
• 新的！LDAP 的 MySQL 外部身份验证- 使您能够配置 MySQL 以通过 LDAP（轻量级目录访问协议）服务器对用户进行身份验证。可以
  通过 LDAP 规范详细指定用户或用户组。支持用户名/密码和 SASL
  身份验证。

回答by Miburi

For those still having issues on this like I was or trying to get LDAP authentication with PHP, it seems with php 7+ libmysqlclient is not supported anymore so you'll need to use the mysql client library mysqlnd with your php configuration and verify it contains the cleartext password plugin as below.

对于像我这样仍然有问题的人或尝试使用 PHP 进行 LDAP 身份验证，似乎不再支持 php 7+ libmysqlclient，因此您需要将 mysql 客户端库 mysqlnd 与您的 php 配置一起使用并验证它包含明文密码插件如下。

php mysqlnd Loaded plugins mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_sha256_password

php mysqlnd 加载插件 mysqlnd,debug_trace,auth_plugin_mysql_native_password, auth_plugin_mysql_clear_password,auth_plugin_sha256_password

Then use the auth_pam_compat by running the following below to install plugin and create each of the users you want authenticated.

然后通过运行以下命令来使用 auth_pam_compat 来安装插件并创建您要进行身份验证的每个用户。

mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
mysql> CREATE USER testaduser IDENTIFIED WITH auth_pam_compat;

Note you cannot use pam_auth with php 7 currently, there's a bug in it that returns:

请注意，您目前无法在 php 7 中使用 pam_auth，其中有一个错误会返回：

The server requested authentication method unknown to the client

The server requested authentication method unknown to the client

https://bugs.php.net/bug.php?id=75645

https://bugs.php.net/bug.php?id=75645

回答by Robert Rouse

I personally can't find any information that suggests this is possible. All I see is using MySQL as the data store for the LDAP directory.

我个人找不到任何表明这是可能的信息。我所看到的只是使用 MySQL 作为 LDAP 目录的数据存储。