25

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?

Improve this question
3
  • Are you looking for a strictly MySQL solution or can you approach this problem from the application side (as in program your way out of the problem) Commented Aug 25, 2009 at 18:50
  • I'd rather not program my way out unless I have to. I'd prefer to have authentication handled natively. I've thought about simply "authenticating" via the app in LDAP and creating users in the MySQL users DB and linking them... but this requires the app to have big credentials with the MySQL server. Commented Aug 25, 2009 at 19:25
  • MySQL proxy (as mentioned by someone below) would be my first choice Commented Aug 25, 2009 at 22:15

11 Answers 11

Reset to default
10

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

9

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.

Improve this answer

Comments

6

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

Improve this answer

2 Comments

Right, but while there is support now in MySQL allowing pluggable authentication modules, there aren't any actual modules, right? So I can't set this up to authenticate against Windows or LDAP or NIS until someone writes those, as far as I understand it...
It's still a bit roundabout, but if you have PAM-LDAP setup you could use the already written PAM plugin.
5

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.

Improve this answer

Comments

1

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

Improve this answer

1 Comment

PostgreSQL may be an option. I haven't deployed yet and am currently in the planning stages... good thing I found out before implementing MySQL for the solution and having to worry about data migration. Thanks for this.
1

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

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.

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

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

I've been pretty much in the same situation. That's why I came here! usually I don't post if I can find it with the Google machine ;)
0

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)

Improve this answer

1 Comment

Instead of just posting that link, you could post the relevant parts in your answer. This creates a more complete answer, and would ensure your answer is still relevant if that link ever breaks.
0

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.
Improve this answer

Comments

0

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 mysqlnd Loaded plugins 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.

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:

The server requested authentication method unknown to the client

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

Improve this answer

Comments

0

Maybe it could be helpful for someone - utility that automatically sinchronize users between LDAP and MySQL.

PyPI page - https://pypi.org/project/myldapsync/

GitHub page - https://github.com/6eh01der/myldapsync

MySQL accounts with specified auth plugin will be created if they were found in LDAP and dropped if they'll disappear from LDAP. Some information about configuration options can be found in the config example github.com/6eh01der/myldapsync/blob/main/myldapsync/… . Supported auth plugins are listed in readme.

Improve this answer

1 Comment

Thanks for the update! It would be helpful to briefly explain how myldapsync works — does it sync users as MySQL accounts, or is it more of a shadow-sync tool for external auth layers? A quick example or usage case would make this answer even more useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.