MySQL Enterprise 5.5 (trial version available here) includes MySQL PAM authentication plugin. In this post I will show how to configure it with the OpenLDAP and Active Directory.
MySQL PAM authentication uses Linux pam_ldap library to send the calls. To configure MySQL LDAP authentication we will need to configure pam_ldap on linux.
OpenLDAP Linux configuration
- Make sure that libpam-ldap/openldap is installed. If not, on RedHat/CentOS use commands:
# yum install openldap openldap-clients
- Configure /etc/ldap.conf. Sample configuration:
debug 10 # set debug level only during the initial
configuration
base dc=corp,dc=company_name,dc=com
binddn cn=service_account,OU=Service Accounts,OU=US
Security,DC=corp,DC=company_name,DC=com
bindpw <password>
timelimit 120
idle_timelimit 3600
uri ldaps://<LDAP URL>:<LDAP PORT>
# The following lines are needed only for Active
Directory
pam_login_attribute samaccountname
pam_member_attribute member
nss_map_objectclass posixAccount user
nss_map_objectclass shadowAccount user
nss_map_attribute uid sAMAccountName
nss_map_attribute homeDirectory unixHomeDirectory
nss_map_attribute shadowLastChange pwdLastSet
nss_map_objectclass posixGroup group
nss_map_attribute uniqueMember member
pam_login_attribute sAMAccountName
pam_filter objectclass=User
pam_password ad
Make sure you can connect to ldap
# telnet <ldap server> <ldap password> (should
show “connected”)
Make sure you can search ldap with ldapsearch
# ldapsearch –w <password for bind user> -x –D
'cn=USER,OU=People …'
“(&(ObjectClass=user)(cn=USERNAME))”
MySQL configuration
To setup mysql follow the instructions from http://dev.mysql.com/doc/refman/5.5/en/pam-authentication-plugin.html
- Make sure you use MySQL 5.5.16+ enterprise version
- Add line:
plugin-load=authentication_pam.so
into my.cnf and restart mysql server
- Put the following in /etc/pam.d/mysql:
#%PAM-1.0
auth
required pam_ldap.so
account required
pam_ldap.so
Individual User Authentication
- Create user for group substitution
CREATE USER 'user_name'@'localhost'
IDENTIFIED WITH authentication_pam
AS 'mysql';
Where mysql = name of the /etc/pam.d/mysql file above, user_name should match the LDAP’s uid (samaccountname for AD).
- Login to mysql with your LDAP username and password
mysql --user=user_name --password=verysecret mydb
Group Authentication
- Create user for group substitution
CREATE USER ''@''
IDENTIFIED WITH authentication_pam
AS 'mysql, ldap_group1=mysql_user1,
ldap_group2=mysql_user2';
Where mysql = name of the /etc/pam.d/mysql file above, ldap_group1 is the ldap group, mysql_user1 is the user to substitute (we need to create this user)
- Create user for each LDAP group
CREATE USER 'mysql_user1'@'localhost' IDENTIFIED BY
'very secret password';
GRANT ALL PRIVILEGES ON mydevdb.* TO
'mysql_user1'@'localhost';
GRANT PROXY ON 'mysql_user1'@'localhost' TO ''@'';
- Login to mysql with your LDAP username and passwordand make sure the user was substituted correctly:
mysql --user=user_name --password=verysecret mydb
mysql> SELECT USER(), CURRENT_USER(),
@@proxy_user;
+-------------------+-----------------------+--------------+
|
USER()
| CURRENT_USER() |
@@proxy_user |
+-------------------+-----------------------+--------------+
| user_name@localhost | mysql_user1@localhost |
''@'%' |
+-------------------+-----------------------+--------------+
Debugging
- Add “debug 16” (or other level, 0 to 256) into /etc/ldap.conf
- Add “export AUTHENTICATION_PAM_LOG=1” to /etc/init.d/mysql and restart mysql
- Then for troubleshoot look into mysql error log (i.e. /var/log/mysqld.log).
Current issues
Due to some PAM library’s limitations the group authentication may not work with Active Directory (individual authentication works fine), probably because the “posix_group” is not fully supported by Active Directory.
Links and more info
MySQL PAM Module documentation
Download
MySQL Enterprise Trial from Oracle E-delivery
We plan a session on MySQL LDAP Auth at MySQL Connect (Sept 29-30, San Francisco)