Using LDAP To Authenticate MySQL

By Greg Keller Posted December 4, 2014

MySQL is a leading relational database system with a commercial and open source offering. Managing users for MySQL is typically a manual chore resulting in extra time and effort – and, potentially a security risk when users created in the database are independent of a business’s identity management system and user provisioning process. Starting in version 5.6.10, the commercial distribution of MySQL supports external user authentication including LDAP. LDAP is a common user directory that organizations may have. By connecting MySQL to LDAP, IT admins benefit a number of ways:

  • One central place to manage your user credentials – saving time and effort
  • Your users effectively get single sign-on for their technical applications
  • Increased security – reduced risk of users having access that shouldn’t

JumpCloud’s Directory-as-a-Service (DaaS) makes it easy to manage , authenticate and authorize MySQL users. Through DaaS’s hosted LDAP service, users can be populated in our directory and then MySQL can authenticate via a secure LDAP endpoint requiring only minimal configuration necessary on the application side.

Here’s how to manage MySQL users with JumpCloud:

BEFORE YOU BEGIN: If you haven’t done so already, please sign up for the JumpCloud service here. You can evaluate this entire process below with no commitment. We give you 10 free users forever.

Initial set-up: Preparing JumpCloud as the LDAP directory

Before MySQL can be integrated with JumpCloud’s LDAP endpoint, the following steps need to be completed to ensure it can communicate effectively via ldapsearch.

1) Turn the LDAP Service ‘On’

In JumpCloud’s ‘Settings’ enable the LDAP service…

LDAP Service ON

Once enabled, JumpCloud will unveil certain customer-specific data, aspects of which will be required in setting up ldapsearch in the steps below (e.g., Organization ID)…

ldapsearch

2) Create an LDAP Service User Account

When utilizing LDAP, JumpCloud recommends the use of a binding user service account (e.g., the “Bind DN”). This user will act as a true service account, enabling ldap to search the user directory as the Bind DN. To create this user:

  1. Go to Users and ‘Add User’
  2. Fill in the properties of this service account in the manner you wish. An example called username “ldapuser” is below.
  3. Most critically, ensure ‘LDAP binding user service account’ checkbox is enabled.
  4. Enter a password for the service account to ensure the account is ‘verified’ and active.
LDAP-binding-user-service-account

Step 2: Adding users to the JumpCloud Directory

In this next step, you will add users to the Directory. These will be the users you desire to provision to MySQL and elsewhere. Follow the steps above in Step 1, yet in this case, you do not need to elect these users as an LDAP Binding account.

Step 3: Configure MySQL to authenticate to the JumpCloud LDAP endpoint

A) Verify the LDAP authentication shared object for MySQL is present. If not, refer to the MySQL documentation here [link: http://dev.mysql.com/doc/refman/5.6/en/pam-authentication-plugin.html ].

[root@mysql ~]# ls /usr/lib64/mysql/plugin/authentication_pam.so /usr/lib64/mysql/plugin/authentication_pam.so

B) Install required packages for LDAP authentication. On CentOS, the following packages are needed

 [root@mysql ~]# yum install openldap openldap-clients nss-pam-ldapd

C) Edit the MySQL Configuration (my.cnf) to include the PAM authentication plugin. The location and contents of your file may vary.

[root@mysql ~]# vi /usr/my.cnf
mysql 1

  D) Create a PAM file “/etc/pam.d/mysql” and add the following contents:

[root@mysql ~]

# vi /etc/pam.d/mysql

#%PAM-1.0  auth required pam_ldap.so  account required pam_ldap.so

E) Edit the PAM LDAP Configuration file. On CentOS it is “/etc/pam_ldap.conf”. You will insert the following values as per below. Please note that this Knowledge Base article on generally configuring and application to communicate with JumpCloud’s LDAP endpoint will be helpful. It will explain the structure of the BindDN and search base.

base ou=Users,o=<Your Org ID>,dc=jumpcloud,dc=com
binddn uid=<Your Bind DN/service account username>,ou=Users,o=<Your Org ID>,dc=jumpcloud,dc=com
bindpw <Your Bind DN/service account password>
timelimit 120
idle_timelimit 3600
uri ldaps://ldap.jumpcloud.com:636 or ldap://ldap.jumpcloud.com:389

F) Restart the MySQL Service

[root@mysql ~]# service mysql restart

For each user account in the directory that needs access perform the following SQL commands. In this example we will use a username “janedba” as for the account and “testdb” is the database:

CREATE USER ‘janedba‘@’localhost’ IDENTIFIED WITH authentication_pam AS ‘mysql’;  GRANT ALL PRIVILEGES ON testdb.* TO ‘janedba’@’localhost’;  FLUSH PRIVILEGES;
MySQL 3

Step 3: This user can now test the MySQL to JumpCloud authentication connection.

mysql –user=janedba -p –enable-cleartext-plugin testdb

MySQL 4

Note that the requirement for the client to pass a clear text login to the server is from MySQL. This is best explained from MySQL documentation: “The client-side plugin with which the PAM plugin communicates simply sends the password to the server in clear text so it can be passed to PAM. This may be a security problem in some configurations, but is necessary to use the server-side PAM library. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using SSL. See Section 6.3.8.7, The Cleartext Client-Side Authentication Plugin” http://dev.mysql.com/doc/refman/5.6/en/cleartext-authentication-plugin.html

Instead of standing up your own LDAP system or managing it, you can easily leverage JumpCloud’s hosted LDAP service, all part of Directory-as-a-Service. Connect it to all of your critical apps, and you are good to go. Your users will also appreciate this, as their single account can now be used for technical applications as well.

If you are utilizing MySQL within your organization and want to simplify the user management for it – and increase security – give JumpCloud a try. We offer 10 free users forever.

Greg Keller

Greg is JumpCloud's Chief Product Officer, overseeing the product management team, product vision and go-to-market execution for the company's Directory-as-a-Service offering. The SaaS-based platform re-imagines Active Directory and LDAP for the cloud era, securely connecting and managing employees, their devices and IT applications.

Recent Posts