This document was written using Microsoft Windows Active Directory 2012, Mysql 5.6 and CentOS 6.5. MySQL is running on CentOS 6.5. This document describes how to map an Active Directory Group to a MySQL User and authenticate against Active Directory. The completion of these steps allows Active Directory groups to be utilized within MySQL.
In this demonstration the following groups will be referenced.
mySQL_AD – This is the MySQL user which will be mapped/associated to the Active Directory Group.
AD_mysql_users – This is the Active Directory user which will be mapped to mySQL_AD.
STEP 1. Prior to getting started you need the following pam module installed.
/usr/lib64/mysql/plugin/authentication_pam.so
STEP 2. After installing the module update the my.cnf file, my.cnf is normally located in /etc/my.cnf
[mysqld]
plugin-load=authentication_pam.so
STEP 3. Restart mysql and verify that the PAM modules is installed.
service mysql restart
mysql -u root
select * from information_schema.PLUGINS\G
The following output should be returned from the PLUGIN query
*************************** 47. row ***************************
PLUGIN_NAME: authentication_pam
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: authentication_pam.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR: Georgi Kodinov
PLUGIN_DESCRIPTION: PAM authentication plugin
PLUGIN_LICENSE: PROPRIETARY
LOAD_OPTION: ON
47 rows in set (0.00 sec)
STEP 4.
Create the following file: vim /etc/pam.d/mysql with the content below. This assumes you already have the MySQL Linux system joined to the Microsoft Active Directory Domain. The pam_winbind.so is a PAM module that can authenticate users against the local domain by talking to the Winbind daemon.
auth required pam_winbind.so
account required pam_winbind.so
STEP 5. Create a MySQL user with a password. The password is not important because it will never be used.
mySQL_AD is the MySQL user which will be mapped to the Active Directory group.
CREATE USER 'mySQL_AD'@'%' IDENTIFIED BY 'SomePassw0rd';
STEP 6. Run the CREATE USER statement to add the MySQL user to Active Directory mapping. In this example the Active Directory group AD_mysql_users is being mapped to the MySQL user: mySQL_AD
The “mysql” in front of “AD_mysql_users=mySQL_AD” of should match the name of pam.d file e.g. (/etc/pamd./mysql)
AD_mysql_users=mySQL_AD
CREATE USER ''@''
IDENTIFIED WITH authentication_pam
AS 'mysql, AD_mysql_users=mySQL_AD';
STEP 7. Grant mySQL_AD PROXY access. This gives users in Active Directory the ability to impersonate the user: mySQL_AD. To run this command you must be logged in as MySQL root: (e.g. mysql -u root -p). Even if you are logged in with DBA access this step will not work, must be logged in under MySQL root user. I tired logging into MySQL with a user which had equivalent privileges to MySQL root and the GRANT failed. Only when I logged in with MySQL root did the GRANT succeed.
mysql -u root
GRANT PROXY ON 'mySQL_AD'@'%' TO ''@'';
STEP 8.
Do a SELECT on the table and see which MySQL users are mapped to Active Directory Groups
SELECT * FROM mysql.user;
Fig 1.
STEP 9.
Set the appropriate user access on the MySQL server for the MySQL user mySQL_AD. For example, add the appropriate permissions for the user to do a select on a MySQL database. Remember that mySQL_AD is the MySQL user which we created earlier and is mapped to the Active Directory Group AD_mysql_users.
STEP 10. Flush security privileges, this causes the user settings to become active
flush privileges;
You can then perform a test connection.
You may receive the message below when performing a test connection.
[root@MYSQL01 log]# mysql -u testuser -p
Enter password:
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled
Set the flag to allow cleartext.
[root@MYSQL01 log]# export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
If you are connecting from MySQL workbench edit your connection, select “Advanced” then “Enable Clear text Authentication Plugin”. Log into MySQL using your Active Directory Username / Password. You can verify which AD user and MySQL user you are logged in as. In Fig 2 I am authenticated as user testuser mapped to the MySQL user mySQL_AD. Note if you enable clear text authentication plugin it is recommended that you connect to MySQL using SSL via workbench.
SELECT USER(),CURRENT_USER();
Fig 2
The configuration below were used in support of the above configurations.
Additional configuration information in /etc/samba/smb.conf
[global]
workgroup = LAB
server string = Samba Server Version %v
realm = LAB.NET
netbios name = MYSQL01
encrypt passwords = yes
password server = dc01.lab.net
#idmap config * : backend = rid
#idmap config * : range = 10000-20000
idmap config * : range = 16777216-33554431
winbind use default domain = Yes
winbind enum users = Yes
winbind enum groups = Yes
winbind nested groups = Yes
winbind separator = +
winbind refresh tickets = yes
winbind offline logon = true
template shell = /bin/bash
template homedir = /home/%D/%U
preferred master = no
dns proxy = no
wins server = dc01.lab.net
wins proxy = no
inherit acls = Yes
map acl inherit = Yes
acl group control = yes
security = ads
passdb backend = tdbsam
Additional configuration information in /etc/krb5.conf
[logging]
default = FILE:/var/log/krb5libs.log
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log
[libdefaults]
default_realm = LAB.NET
dns_lookup_realm = true
dns_lookup_kdc = true
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true
proxiable = true
[realms]
LAB.NET = {
default_domain = LAB.NET
kdc = DC01.LAB.NET
admin_server = DC01.LAB.NET
}
[domain_realm]
.lab.net = LAB.NET
lab.net = LAB.NET
lab = LAB.NET
[appdefaults]
pam = {
ticket_lifetime = 1d
renew_lifetime = 1d
forwardable = true
proxiable = true
retain_after_close = false
minimum_uid = 0
debug = true
}
Additional configuration information in /etc/nsswitch.conf
passwd: files sss ldap winbind
shadow: files sss ldap winbind
group: files sss ldap winbind
Additional configuration information in /etc/pam.d/system-auth
auth required pam_env.so
auth sufficient pam_fprintd.so
auth sufficient pam_unix.so nullok try_first_pass
auth sufficient pam_krb5.so use_first_pass
auth sufficient pam_winbind.so cached_login use_first_pass
auth requisite pam_succeed_if.so uid >= 500 quiet
auth required pam_deny.so
account required pam_unix.so
account sufficient pam_localuser.so
account sufficient pam_succeed_if.so uid < 500 quiet
account [default=bad success=ok user_unknown=ignore] pam_krb5.so
account [default=bad success=ok user_unknown=ignore] pam_winbind.so cached_login
account required pam_permit.so
password requisite pam_cracklib.so try_first_pass retry=3 type=
password sufficient pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password sufficient pam_krb5.so use_authtok
password sufficient pam_winbind.so cached_login use_authtok
password required pam_deny.so
session optional pam_keyinit.so revoke
session required pam_limits.so
session optional pam_mkhomedir.so skel=/etc/skel umask=0027
session [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session required pam_unix.so
session optional pam_krb5.so
Reference : http://dev.mysql.com/doc/refman/5.6/en/proxy-users.html
Tater's Tech Blog 