LDAP Authentication in MySQL [feedly]

LDAP Authentication in MySQL
http://www.itchyninja.com/ldap-authentication-in-mysql/

-- via my feedly.com reader

We manage hundreds of servers and have a need to add and remove DBAs, application developers, and so on regularly. Doing this manually is just beyond the scope of what we can realistically manage. Since we are already using LDAP, we wanted to find a way to integrate it with MySQL.

After reading many, many articles and getting diverse opinions as to whether or not it was even possible, we decided to give it a try. From what we read, it appears that MySQL Enterprise edition does exactly what we want; unfortunately, we don't have an Enterprise support contract ruling that out as an option. Knowing that Percona tends to mimic many of the Enterprise features, we decided to pursue doing this with Percona Server. Further reading showed this could be possible with the PAM plugin which would then authenticate via LDAP. A little indirect, but seemed like our only solution.

Since MySQL does not provide a community version of its LDAP plugin, we will use the PAM plugin that is available and then have PAM do the LDAP authentication. This is super easy to setup and PAM already has LDAP support, so we are just leveraging that capability to make this work.

An added benefit of this approach is that we get a pseudo role capability for LDAP users which allows us to specify individual user permissions at a group level, which MySQL does not support natively.

It is also possible to create multiple groups, all of which, may have separate privileges. Moreover, this approach will support users who belong to both primary and secondary groups.

Below are a few prerequisites:

1. There must be an LDAP server reachable by the MySQL server
2. A group name "mysqldba" (or similar) already configured in the LDAP server
3. User account(s) already configured in the LDAP group named above
4. The auth_pam.so and dialog.so files in the MySQL plugin directory

The process requires some dynamically linked plugin files:

• auth_pam.so
• dialog.so

These are available with the standard installation of Percona Server in versions 5.5+. By default, they should be found in the /usr/lib64/mysql/plugin directory.

Once the plugins are in place you can continue by installing the auth_pam plugin as follows:

• Execute the following command:

   mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';

• To test the plugin installed properly, you can execute the following:

   mysql> SHOW PLUGINS;

Now that we have the PAM plugin loaded, we need to configure PAM to use LDAP authentication. I tried using the configuration for PAM found online but it did not work entirely. In order to set this up, all that is necessary is to create a file named /etc/pam.d/mysql and add the three lines below to the configuration:

auth required pam_warn.so  auth required /lib/security/$ISA/pam_ldap.so audit config=/etc/ldap_host.conf  account required /lib/security/$ISA/pam_ldap.so audit config=/etc/ldap_host.conf

The above was what was needed for our setup. Others may find they don't need the "config=/etc/ldap_host.conf" additions.

The remaining work is all done within MySQL. For our purposes, we will assume the LDAP group name will be "mysqldba". All authenticated users will initially connect to MySQL as ''@''.  With this being the case, we need to create a user account for this:

mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysql, mysqldba=ldap';

The above tells MySQL that any user ''@'' will authenticate via PAM (which will then authenticate via LDAP) using the 'mysql' file in /etc/pam.d, hence why the "'mysql," entry in the CREATE USER statement. That part of the entry references the filename in /etc/pam.d.

The next part of the statement "mysqldba=ldap" is in the form of ldap_group_name=local_mysql_user. This is a sort of mapping to get you from the LDAP group of the user authenticating to a local MySQL user account.

We will now need to create the local MySQL user, "ldap" and manage its privileges. The advantage of this approach is that we can essentially implement a pseudo role capability which MySQL does not support directly. For our purposes, we will only give the user CRUD (SELECT, INSERT, UPDATE, DELETE) privileges. This is done with the following command:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'ldap'@'localhost' IDENTIFIED BY "secret";

Finally, we need to map a PROXY user so that MySQL can essentially alias the ''@'' user to the 'ldap'@'localhost' user which is done with the following:

mysql> GRANT PROXY ON 'ldap'@'localhost' TO ''@'';

If all of the above was done properly, you should now have a functioning LDAP authentication system and you can now move onto testing!

Testing is simple.  Attempt to connect to MySQL using your normal LDAP username.  For this to work, you must be a member of the "mysqldba" LDAP group first.  If this is not completed, then you will need to have that done before you can move forward.

Simply attempt to connect to MySQL using your LDAP username.  You will either be authenticated or receive an error message.

To see how the PROXY user works, you can verify your user and current_user accounts with the following:

[root@localhost ~]# mysql -uninja -p  Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g.  Your MySQL connection id is 81391  Server version: 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519  Copyright (c) 2009-2013 Percona LLC and/or its affiliates  Copyright (c) 2000, 2013, 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> SELECT USER(), CURRENT_USER();  +------------------+----------------+  | USER()           | CURRENT_USER() |  +------------------+----------------+  | ninja@localhost  | ldap@localhost |  +------------------+----------------+  1 row in set (0.00 sec)

Although I logged in as the "ninja" user, MySQL proxied the user to "ldap"@"localhost" which only has the CRUD privileges I specified.  This will prevent you from having to specify specific users for each user within MySQL; instead a proxy for each to the "ldap" user will be utilized and it inherit its privileges.

To confirm what permissions the user has, execute the below statement:

mysql> SHOW GRANTS;  +--------------------------------------------------------------------------------------------------------------------------------------+  | Grants for ldap@localhost                                                                                                            |  +--------------------------------------------------------------------------------------------------------------------------------------+  | GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'ldap'@'localhost' IDENTIFIED BY PASSWORD 'hidden hash'                               |  +--------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)

You should only see the CRUD privileges if all worked as expected.

We hope this helps give you ideas on how this can be implemented.  From what we read, it should be possible to implement this same process for the community edition of MySQL using the same plugin although we have not attempted this.

My only complaint is that we have to work through the PAM plugin to do this in a round-about manner.  I wish LDAP authentication was native.  Oh well, perhaps in a later version.

For us, this will save a tremendous amount of time in managing MySQL user access and gives us the added benefit of a pseudo "role" capability via the proxy user.  It is a win/win!

PlanetMySQL Voting: Vote UP / Vote DOWN