Ubuntu 12.04.3 LTS Precise Mysql 5.5 Active directory authentication with Percona PAM Authentication Plugin for MySQL

Ubuntu 12.04.3 LTS Precise  Mysql 5.5  Active directory authentication with Percona PAM Authentication Plugin for MySQL

ubuntu mysql bullseye-800

 

Update your reprository

root@sftestdb03:~# apt-get update
root@sftestdb03:~# apt-get upgrade
root@sftestdb03:~# apt-get install automake
root@sftestdb03:~# apt-get install libtool autopoint
root@sftestdb03:~# apt-get install make
root@sftestdb03:~# apt-get install libpam0g-dev

I like to disable apparmor
root@sftestdb03:~# /etc/init.d/apparmor stop
* Clearing AppArmor profiles cache [ OK ]

root@sftestdb03:~# /etc/init.d/apparmor teardown
root@sftestdb03:~# update-rc.d -f apparmor remove
root@sftestdb03:~# apt-get purge apparmor

Reboot your server

Now lets install Percona Mysql 5.5 database serverm pam auth supported after 5.5
First adding percona repository to my ubuntu source list.

root@sftestdb03:~# apt-key adv –keyserver keys.gnupg.net –recv-keys 1C4CBDCDCD2EFD2A
root@sftestdb03:~# vi /etc/apt/sources.list

#Add this line to the top in sources.list, Percona repository

deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

Now we can install server with apt
root@sftestdb03:~# apt-get install percona-server-server-5.5 percona-server-client-5.5
Installation of percona-pam-for-mysql is not necessary if you use percona-server-server-5.5, because it comes with it, you just need to enable this plugin after installation.

Lets install plugin from mysql console
mysql> INSTALL PLUGIN auth_pam SONAME ‘auth_pam.so’;
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN auth_pam_compat SONAME ‘auth_pam_compat.so’;
Query OK, 0 rows affected (0.01 sec)

Lets make sure plugin is installed
mysql> SHOW PLUGINS;

| auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |
| auth_pam_compat | ACTIVE | AUTHENTICATION | auth_pam_compat.so | GPL |
+————————–+———-+——————–+——————–+———+
Now it is time to test unix pam authentication first, and make sure our plugin works

Add unix user and set a password
root@sftestdb03:~# useradd test
root@sftestdb03:~# passwd test

Add a user in mysql console and choose auth_pam polugin to provide password

mysql> CREATE USER test IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now lets create a mysqld file under pam.d folder
root@sftestdb03:~# vi /etc/pam.d/mysqld

auth required pam_warn.so
auth required pam_unix.so audit
account required pam_unix.so audit
root@sftestdb03:~# chgrp mysql /etc/shadow
root@sftestdb03:~# chmod g+r /etc/shadow
Lets test with basic Unix Pam authentication and make sure it woks

root@sftestdb03:~/src/percona-pam-for-mysql# mysql -u test -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.32-31.0 Percona Server (GPL), Release 31.0
Copyright (c) 2009-2013 Percona Ireland Ltd.
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>

 

Booom we are in! It works with pam_auth.

 

Now most fun part starts, lets configure our kerberos:

First lets install Pam module for MIT Kerberos
root@sftestdb03:~# apt-get install libpam-krb5

And now lets configure it to use our Active directory(I use MS Server 2008 and tested with 2003)
You can edit and make your config little shorter, I did not :)

vi /etc/krb5.conf

[libdefaults]
ticket_lifetime = 24000
default_realm = AD.SANFRANCISCOTECH.COM
dns_lookup_realm = true
dns_lookup_kdc = true
[realms]

AD.SANFRANCISCOTECH.COM = {
kdc = DC01.ad.SANFRANCISCOTECH.COM
admin_server = DC01.ad.SANFRANCISCOTECH.COM
default_domain = ad.SANFRANCISCOTECH.COM
}

[domain_realm]
.domain.internal = ad.SANFRANCISCOTECH.COM
domain.internal = ad.SANFRANCISCOTECH.COM

[appdefaults]
pam = {
ticket_lifetime = 1d
renew_lifetime = 1d
forwardable = true
proxiable = false
retain_after_close = false
minimum_uid = 2
try_first_pass = true
ignore_root = true
schema = ad
ldapservs = DC01.ad.SANFRANCISCOTECH.COM
ldapport = 389
binddn = cn=Administrator,cn=Users,dc=ad,dc=sanfranciscotech,dc=com
basedn = dc=ad,dc=sanfranciscotech,dc=com
ldapuser = domain auth user
ldappass = ***********
passwd = /etc/passwd
shadow = /etc/shadow
groups = /etc/group
groups_list = audio,cdrom,cdrw,usb,plugdev,video,games
homedir = /home
defshell = /bin/bash
}

 

Edit mysqld file under pam.d folder, this will disable local login, but if you want to enable it, you can include pam_unix also.
root@sftestdb03:~# vi /etc/pam.d/mysqld

auth required pam_warn.so
auth required pam_krb5.so audit
account required pam_krb5.so audit
Now lets test our Mysql Active Directory authentication.

root@sftestdb03:~# mysql -u garibmehdiyev -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.5.32-31.0 Percona Server (GPL), Release 31.0
Copyright (c) 2009-2013 Percona Ireland Ltd.
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>

Boom, we are in! And that it is, you have enabled Active directory auth for your mysql!

 

Please let me know if you had any problem while following my instructions

I will test and post it for Mysql Server 5.5 Community edition also

 

 

Some terms:

mysql active directory aithentication

mysql ldap aithentication

mysql kerberos aithentication

mysql pam aithentication

Tagged with: , , ,
Posted in Administration, Oracle, Ubuntu

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>