MySQL PAM/LDAP authentication module configuration

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

53 comments to MySQL PAM/LDAP authentication module configuration

  • You might want to note that passwords are sent in the clear over the wire when external authentication (like pam) is used.

    You should be able to use SSL to avoid that.

  • Note that in order for the group mapping to work you’ll need the UNIX apis getgrnam(3)/getpwnam(3) to work against your LDAP server.
    And this means changing more PAM configuration profiles than just the /etc/pam.d/mysql file you can use for 1-to-1 authentication.

  • I need to extract data from 2 MySQL databases located at two different servers. How is it possible from one single PHP page?

  • I use the iPhone Configuration Utility available on the Apple web site to build an iPhone user prloife with the Pulse client preconfigured and install/apply a user digital certificate. Easy to do under VPN and the Credentials sections. Then either install the prloife directly on the phone or export then email it.I too have found that you cannot seem to use a digital certificate *and* password authentication like Joel mentioned. With a standard web browser on a computer (not iPhone) one can enter their login/password and then select their certificate, but the iPhone Pulse client seems to be one or the other, so I had to create a new Realm with more restrictions. Not something I want to roll out for production yet.

  • I just posted this qusieton on the JNet community forum. If this cold be answered, I would greatly appreciate it.Hello,I’m new to all of this VPN stuff, so please bare with me.I would like to let our iPad users connect to our network via Junos Pulse, be we have a policy that states:1) Only our computers can connect2) the suer needs to authenticate with a password to conenctSo, I see how we can use a certificate or a password, but that doesn’t help. For Mac/Linux/Windows we use host checker to looks for various stuff that only our computers have. Is there a way we can do anything to validate the iPad and then have the user submit a U/P to connect?Thanks,Allan

  • Keith, I’ve successfully used IPCU to inatsll a skeleton Pulse-only configuration profile, in both cases when the app was already inatslled. In one case, the app had been enabled and manually configured already, in the other case, the app had been freshly inatslled from the app store but not enabled. Both cases behaved as expected with no issues. That’s probably not entirely helpful as it doesn’t move you closer to a solution, but at least you know how it ought to work.

  • We have it working with MS certs tniestg at this stage. They are very difficult to manage automatically. It works on Ipad 4.2 (beta) without a problem for us. Iphone works without a problem. Yes, you can put certs on the phone. But if you have 600+ phones already in the wild it is difficult to get the cert out to them. Juniper SA cannot read the SCEP / NDES machine cert on the phone. Can only read personal certs. ( MS Server 2008 R2 set up ) at this stage, I am told.

  • Using the latest IPCU on Snow Leopard: if I try inlislatng a profile that contains nothing but VPN settings *after* Junos Pulse is already installed, the profile installer on the phone itslef hangs indefinitely. If the profile is installed first, and then the Pulse app, all is well (and the VPN configurations show up properly in pulse as they should). Hmmm Anyone else using the IPCU having this issue? Thanks.

  • Thanks Kevin, I was able to get Junos Pulse working on the iPad last week. I cheekcd the App Store again today and it still looks like the only version of Pulse available on the iPad is the iPhone version (the iPad runs iPhone apps, just at a different resolution) but this version is working just fine.Now time to see if I can get it working on my 6.5 system with RSA integration.

  • Allan,Your best option at this point is going to use ciefeticatrs for the VPN connection, whith authentication to the backend resources. We are looking into the best way to handle both at the same time, but as of right now I don’t have anything that can be shared. In the meantime, looking into supporting ciefeticatrs on your devices would probably be time well spent.

  • A white paper like that would help. I am sure there are many interesting scanerios where this can make account management much simpler. Especially when you want to immediately remove access for a user who might be crafty enough to restore a database backup. And if the MySQL privilege tables are used, then the stale backup has stale privs.

  • I’ve noticed that since using a Cert on the clneit for the initial SSL (RADIUS two factor for secondary auth) the pulse clneit needs a manual kick to bring the VPN back to life ? It doesn’t need to fully re-auth, but I have to het connect again within Pulse.Annoying when flipping between 3G and Wi/Fi and back.

  • Jing,Good point. I’m not creating new VMs as much as I’m mainkg copies of existing VMs, tweaking them (ie: installing OpenLDAP), and then zipping them up for safe keeping. In a sense I’m creating a new one, but I’m not creating it in the sense that VMware means. That is, I can’t install a brand new OS, such as Windows XP, but I can certainly mutate existing snapshots.

  • VMWare rocks!Last weekend, my bhertor finally showed me VMWare. I had heard about it some time ago, but never had the time to actually try this out. After downloading evaluated one of the pre-built virtual machines, using the VMPlayer I decided to download the Wor

    • I am using Visual Studio 2010 and MVC2. I created a MySql dsaabtae with a sql script in MySql Workbench. I have Connector/Net 6.3.3 beta 2. I added a new item to create an ADO.Net Entity Model from the for the MySql dsaabtae. The resulting model had lower case names for all of the tables in the diagram. I looked an saw that this probably comes from the fact that the Table names in the MySql dsaabtae are also lower case. Note from my previous comments I am generating with the MySql tt file from Visual studio all use upper case names also. Somewhere in the process, the table names which begin with an upper case first letter are going to all lower case names. This is more a pain in the neck than anything else since I have to modify the names in the edmx diagram in oder for compatability with existing code. However, I am reporting this as a problem because it might make it hard for some people to migrate from Sql server to MySql. I’d think that is one of your goals.

      • Ali

        Very helpful, didn’t want to cnonitue using my own admin account in our sh backup script anymore, but for anyone else interested, you should also give a user the SHOW DATABASES perm so the user can do all at once.

  • Keith Hope this helps but I’ve got 4.2 installed and was hanivg the same problem as you with the Failed to start the VPN service message. I rolled back to 4.1 and restored from a backup and had the same.What I’ve discovered though is that if you delete the app and reinstall it, it works on both 4.1 and 4.2 however.

  • Boy

    I have been using Roboform for a year now. I love the program, and don’t know what I would do wtuhoit it. I would recommend it. I made a quick video about the program and posted it on squidoo

  • Finding this post solves a porblem for me. Thanks!

  • Great article! thank you

    • Agree with Melvin but will also add that the fix above also works to resolve erorrs adding users when using the MySQL Admin GUI Tools. When adding a user via the MySQL Admin tool, it would throw an error stating that the user may have been deleted from the database and to refresh the user pane. This fixes that.

    • Sun’s acquirement by Oracle doesn’t mean that MySQL will die. This is FUD spaerd either by stupid people, either by people against the merge. Even if Oracle itself won’t develop MySQL anymore (although this is not sure), other people / companies could.I really don’t understand the purpose of this post.

  • Hey!Thanks for the info!You have an interesting blog.

    • Must have been quite the task, but good for you. Last year I moved all our websites at work from .NET to myql and it was an anynoing task, and they aren’t nearly the tool this is. Would like to see how you did it. There are likely some good learning points.

    • 1、修改主从参数主库:relay-log=/opt/mysql/mysqldata/mysqllog/replicaterelay-log-index=/opt/mysql/mysqldata/mysqllog/replicate.indexread-only=1skip-slave-start=1innodb_flush_log_at_trx_commit = 1sync-binlog=1备库:#read-only=12、查看主备库状态show slave status \Gshow matser status \G3、操作备库STOP SLAVE IO_THREAD;SHOW PROCESSLIST;看到Has read all relay logshow slave status \GSTOP SLAVE;RESET MASTER;RESET SLAVE;show matser status \G4、操作主库show matser status \GRESET MASTER;RESET SLAVE;CHANGE MASTER TOMASTER_HOST=’192.168.1.4 ,MASTER_USER=’repl’,MASTER_PASSWORD=’xifenfei’,MASTER_LOG_FILE=’mysq_lbin.000001 ,MASTER_LOG_POS=107;5、重启新主从库,开启slave进程,检查是否正常主库SHOW PROCESSLIST;show matser status \G从库start slave;SHOW PROCESSLIST;show slave status \G

  • Great blog! Thanks for the info!

  • Thanks for this great blog!

    • article will add to Rajeev Karamchedu’s excellent post, Integrating Mac OS X into Unix LDAP Environment with NFS Home Directories , only with Leopard Server intaesd of Tiger. My goals are a bit different from

    • Ryan:MacOS X can be conadfigadured to auth users using the “Directory Access” utiladity (it’s pretty slick, and supadports the RFC way, the Apple way, and the ActiveDirectory way), and setadting up Samba to use OpenLDAP as a SAM backadend for NT doiman authenadtiadcaadtion was pretty simadple – just a matadter of using their schemas inteladliadgently and keepading your users under a paradticaduadlar DN. We didn’t bother with the OS 9 boxen because they were being phased out anways. Linux requires libnss-ldap, of course (which is pretty well-documented, IMO). One thing that ended up bitading us in the ass was putting the “ldap” entry in nsswitch.conf first on the list — it ends up makading INIT go out-to-lunch in a bizadzare way, which forced us to boot the PDC (not someadthing that can be offline for a few days while you debug it) off of a LiveCD and run the seradvices when we restarted it for a new keradnel until someadone else found the same probadlem and lazyadweb caught up with thea0issue.Most useadful was the offiadcial Samba3 HOWTO, and the . Of course, had I to do it over again, I’d just have setup OpenLDAP as a fake OS X server, so as my boss slowly conadverted the office to woradshipading at , he could’ve used the variadety OS X admin tools he touted. If you don’t have to migrate an existading doiman, that’s a bonusa0too.iain:One hopes, but it still reminds me of HR peoadple comading to Netscape and assumading that the thing that made the proadgramadmers at Netscape willadingly put in 80a0hour weeks while on salary was the free pizza and Mt. Dew and winking-and-nodding at goofading off (as opposed to, e.g., everyadone being comadpeadtent at their job and recadogadnizading that the work actuadally meant someadthing in the fuzzy-kitty, big-picture sense), so if only every comadpany proadvided free pizza and Mt. Dew and let employadees occaadsionadally goof off, everyadone would want to put in 80a0hour weeks while on salary.Really I’m just pissed at spamadmers, paradticaduadlarly those extremadists who call it “maradketading” and are busily proadmotading the idea of altoadgether replacading conadtent with advertising.

    • There is an obvious conlcift with competing for business andworking together on improving the MySQL software. It’s fairlynew for the MySQL community to have this situation. I viewthis event as a learning experience. Personally I don’t thinkit’s possible to close anyone out from conferences. Just lookat Intel/AMD, AMD always had an alternative conference thehotel next to the Intel Developer’s conference.Personally I’ve always thrived in a competitive situation. It usuallyincreases the value of the community since more innovation isrequired to stay ahead of your competitor and this increases thevalue of the MySQL community and enables us to increase theimpact of MySQL in the IT world. The great thing about open sourceis that you will have to share your results with the community soeven though you’re competing for business there will always becooperation on the development of the source code.My personal trait on behaving as a good community member inthis situation is to focus on displaying your own positivecharacteristics and showing what you’ve done to innovate.Comparisons to other happens through benchmarks of varioussorts, both company-specific and open source benchmarks andstandard benchmarks.Lastly I would like to give special credit to the work done byMark Callaghan and his team at Google. I’ve had theopportunity to play around with their patches and really foundthem to be easy to understand and really stable. So if I hadan award to give out on the MySQL Community Contributionof the year I’d certainly give it to them. Disclaimer: I don’t havesuch an award to give out :) So finally much looking forward to meeting at the MySQL UsersConference week and seeing what everyone is up to andsharing our ideas.

  • First off, Leopard’s autofs DOES work with LDAP. Second, we are also lokniog at a little bit of hardcoded application from Apple, ergo not very flexible. Third, the integration of Mac OS X into LDAP is not covered in this particular post, as it was quite heavily covered in this comprehensive article titled Integrating Mac OS X into Unix LDAP Environment with NFS Home Directories .

  • If you set the port for OpenDS to 389, it means that you must run OpenDS as root. Or your regular user must have the plegirevis to open ports under 1024, which requires afaik specific configuration on Linux. May be you could explain how you do configure the regular user ?RegardsLudo

  • when i wanne create the table, i get the fwnoolilg error:Warning: mysql_query() [function.mysql-query]: Access denied for user nobody’@'localhost’ (using password: NO) in /home/a4690156/public_html/Scripts/create_Table.php on line 34PHP Error MessageWarning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/a4690156/public_html/Scripts/create_Table.php on line 34no TABLE created. You have problems in the system already, backtrack and debug!

  • about integrating Apple into your etnxiisg Unix/NFS environment, please read the article Integrating Mac OS X into Unix LDAP Environment with NFS Home Directories. Rant: Even with Leopard, there is no support for Microsoft DFS.

  • Ludo,I hadn’t gotten that far yet :) . Currently, since this for tetnsig. My initial idea was to use setuid root. After you posted I thought I’d poke around, but I can’t figure out what I’d need to actually make setuid root since making the OpenDS.jar or the start-ds script won’t really work.If someone has an idea, I’d like to hear it.So after some hacking around with the other admins here, we have decided to try some iptables rules.We made the Directory server owned by a regular user, and let it attach to an unprivileged port, and then tossed this into iptables:iptables -t nat -A PREROUTING -p tcp –dport 389 -j REDIRECT –to-port 1389iptables -t nat -A OUTPUT -p tcp –dport 389 -j REDIRECT –to-ports 1389I am not sure how much I like that, but it’s a fix for now.

  • 21ayour the man dude these tutorials are epic. i have one liltte problem with this and my products page though when i try to create a dynamic table with 2 columns so i can display more products it just duplicates the products so ile have two black tops next to each other and 2 black hats next to each other etc etc etc i have tryed editing the php code to like ID++ or +1 to no prevail and also as tanib10 said how can i implement a next button?? please help or anyone who has got this workingef