The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on
Ubuntu.
So here is how it works on Ubuntu (and probably also on other
Debian based systems).
Please note that the PAM authentication plugin is an enterprise
feature.
1. Make sure the plugin is loaded
This can be done by adding the following to the mysqld section of
my.cnf (Don't forget to restart). You could also use INSTALL
PLUGIN to load it without restart.
plugin-load=authentication_pam.so
2. Add a user which will use the plugin
mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam;
Query OK, 0 rows affected (0.00 sec)
3. Add a pam config file for 'mysql':
Create /etc/pam.d/mysql with the following contents:
@include common-auth
@include common-account
@include common-session-noninteractive
4. Login with the user
mysql -p --enable-cleartext-plugin
5. Verify if you're really connected as the correct user.
mysql> select user(),current_user(),@@proxy_user;
+-------------------+-------------------+--------------+
| user() | current_user() | @@proxy_user |
+-------------------+-------------------+--------------+
| dveeden@localhost | dveeden@localhost | NULL |
+-------------------+-------------------+--------------+
1 row in set (0.00 sec)
If some step doesn't work then the /var/log/auth.log file can be
very helpfull.
The plugin has many more options. It allows you to proxy users
and use different PAM configurations for different users. The
plugin is used on a per user basis so you could use native
authentication for your application users and PAM authentication
with LDAP for administrators and/or developers.
Please note that SHOW GRANTS does not indicate a authentication
plugin and blindly copying the grant statements to another server
to copy the user might result in users without password.
MySQL Utilities:
$ mysqluserclone --source=usr:pwd@srv -d 'dveeden'@'localhost'
# Source on 127.0.0.1: ... connected.
# Dumping grants for user dveeden@localhost
GRANT USAGE ON *.* TO 'dveeden'@'localhost'
Percona Toolkit:
$ pt-show-grants | grep dveeden
-- Grants for 'dveeden'@'localhost'
GRANT USAGE ON *.* TO 'dveeden'@'localhost';