Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Using the PAM authentication plugin
+2 Vote Up -0 Vote Down
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';

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.