The client program and the server communicate, with the client sending to the server the client user name (the operating system user name by default) and password:

The server finds a matching MySQL account based on the external user name and the host from which the client connects. The PAM plugin uses the information passed to it by MySQL Server (such as user name, host name, password, and authentication string). When you define a MySQL account that authenticates using PAM, the authentication string contains:

The plugin uses the PAM service named in the authentication string to check the user credentials and returns 'Authentication succeeded, Username is user_name' or 'Authentication failed'. The password must be appropriate for the password store used by the PAM service. Examples:

If the credentials check fails, the server refuses the connection.

Otherwise, the authentication string indicates whether proxying occurs. If the string contains no PAM group mapping, proxying does not occur. In this case, the MySQL user name is the same as the external user name.

Otherwise, proxying is indicated based on the PAM group mapping, with the MySQL user name determined based on the first matching group in the mapping list. The meaning of “PAM group” depends on the PAM service. Examples:

If the proxy user (the external user) has the PROXY privilege for the proxied MySQL user name, proxying occurs, with the proxy user assuming the privileges of the proxied user.

Verify that Unix authentication permits logins to the operating system with the user name antonio and password antonio_password.

Set up PAM to authenticate MySQL connections using traditional Unix passwords by creating a mysql-unix PAM service file named /etc/pam.d/mysql-unix. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, the mysql-unix file might look like this:

#%PAM-1.0
auth            include         password-auth
account         include         password-auth

For macOS, use login rather than password-auth.

The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead:

@include common-auth
@include common-account
@include common-session-noninteractive

Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the mysql-unix PAM service:

CREATE USER 'antonio'@'localhost'
  IDENTIFIED WITH authentication_pam
  AS 'mysql-unix';
GRANT ALL PRIVILEGES
  ON mydb.*
  TO 'antonio'@'localhost';

Here, the authentication string contains only the PAM service name, mysql-unix, which authenticates Unix passwords.

Use the mysql command-line client to connect to the MySQL server as antonio. For example:

$> mysql --user=antonio --password --enable-cleartext-plugin
Enter password: antonio_password

The server should permit the connection and the following query returns output as shown:

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+-------------------+--------------+
| USER()            | CURRENT_USER()    | @@proxy_user |
+-------------------+-------------------+--------------+
| antonio@localhost | antonio@localhost | NULL         |
+-------------------+-------------------+--------------+

This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the antonio MySQL user, and that no proxying has occurred.

Verify that Unix authentication permits logins to the operating system with the user name antonio and password antonio_password.

Set up PAM to authenticate MySQL connections using LDAP by creating a mysql-ldap PAM service file named /etc/pam.d/mysql-ldap. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, the mysql-ldap file might look like this:

#%PAM-1.0
auth        required    pam_ldap.so
account     required    pam_ldap.so

If PAM object files have a suffix different from .so on your system, substitute the correct suffix.

The PAM file format might differ on some systems.

Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the mysql-ldap PAM service:

CREATE USER 'antonio'@'localhost'
  IDENTIFIED WITH authentication_pam
  AS 'mysql-ldap';
GRANT ALL PRIVILEGES
  ON mydb.*
  TO 'antonio'@'localhost';

Here, the authentication string contains only the PAM service name, mysql-ldap, which authenticates using LDAP.

Connecting to the server is the same as described in PAM Unix Password Authentication without Proxy Users.

Verify that Unix authentication permits logins to the operating system with the user name antonio and password antonio_password.

Verify that antonio is a member of the root or users PAM group.

Set up PAM to authenticate the mysql-unix PAM service through operating system users by creating a file named /etc/pam.d/mysql-unix. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, the mysql-unix file might look like this:

#%PAM-1.0
auth            include         password-auth
account         include         password-auth

For macOS, use login rather than password-auth.

The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead:

@include common-auth
@include common-account
@include common-session-noninteractive

Create a default proxy user (''@'') that maps external PAM users to the proxied accounts:

CREATE USER ''@''
  IDENTIFIED WITH authentication_pam
  AS 'mysql-unix, root=developer, users=data_entry';

Here, the authentication string contains the PAM service name, mysql-unix, which authenticates Unix passwords. The authentication string also maps external users in the root and users PAM groups to the developer and data_entry MySQL user names, respectively.

The PAM group mapping list following the PAM service name is required when you set up proxy users. Otherwise, the plugin cannot tell how to perform mapping from external user names to the proper proxied MySQL user names.

Create the proxied accounts and grant to each one the privileges it should have:

CREATE USER 'developer'@'localhost'
  IDENTIFIED WITH mysql_no_login;
CREATE USER 'data_entry'@'localhost'
  IDENTIFIED WITH mysql_no_login;

GRANT ALL PRIVILEGES
  ON mydevdb.*
  TO 'developer'@'localhost';
GRANT ALL PRIVILEGES
  ON mydb.*
  TO 'data_entry'@'localhost';

The proxied accounts use the mysql_no_login authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, users who authenticate using PAM are expected to use the developer or data_entry account by proxy based on their PAM group. (This assumes that the plugin is installed. For instructions, see Section 8.4.1.9, “No-Login Pluggable Authentication”.) For alternative methods of protecting proxied accounts against direct use, see Preventing Direct Login to Proxied Accounts.

Grant to the proxy account the PROXY privilege for each proxied account:

GRANT PROXY
  ON 'developer'@'localhost'
  TO ''@'';
GRANT PROXY
  ON 'data_entry'@'localhost'
  TO ''@'';

Use the mysql command-line client to connect to the MySQL server as antonio.

$> mysql --user=antonio --password --enable-cleartext-plugin
Enter password: antonio_password

The server authenticates the connection using the default ''@'' proxy account. The resulting privileges for antonio depend on which PAM groups antonio is a member of. If antonio is a member of the root PAM group, the PAM plugin maps root to the developer MySQL user name and returns that name to the server. The server verifies that ''@'' has the PROXY privilege for developer and permits the connection. The following query returns output as shown:

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+---------------------+--------------+
| USER()            | CURRENT_USER()      | @@proxy_user |
+-------------------+---------------------+--------------+
| antonio@localhost | developer@localhost | ''@''        |
+-------------------+---------------------+--------------+

This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the developer MySQL user, and that proxying occurs through the default proxy account.

If antonio is not a member of the root PAM group but is a member of the users PAM group, a similar process occurs, but the plugin maps user PAM group membership to the data_entry MySQL user name and returns that name to the server:

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+----------------------+--------------+
| USER()            | CURRENT_USER()       | @@proxy_user |
+-------------------+----------------------+--------------+
| antonio@localhost | data_entry@localhost | ''@''        |
+-------------------+----------------------+--------------+

This demonstrates that the antonio operating system user is authenticated to have the privileges of the data_entry MySQL user, and that proxying occurs through the default proxy account.