Using LDAP (AD) for MySQL authentication

问题:

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?

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?

回答1:

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

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

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/

回答2:

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

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

Homepage: http://infoscope.gr/mysqlauthldap

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

回答3:

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

This is in the 5.5.7 release candidate

回答4:

This is possible now with commercial extensions, e.g. with MySQL External Authentication for 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.

回答5:

Seems like you are out of luck :(

Could you use 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]]

回答6:

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

https://www.percona.com/doc/percona-server/LATEST/management/pam_plugin.html

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.

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

回答7:

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.

回答8:

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/

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.

EDIT: Add instructions from above link as suggested by AfroThundr

Install the Percona PAM plugin:

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:

auth required pam_ldap.so
account required pam_ldap.so

Create a MySQL user that will authenticate via auth_pam:

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:

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:

#include <abstractions/authentication>

and reload apparmor:

service apparmor restart

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

回答9:

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 Enterprise Authentication

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.