6.4.1.5 PAM Pluggable Authentication

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:

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:

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

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

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:

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 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:

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 proxied accounts that have the required privileges for MySQL access:

If you do not let anyone know the passwords for these accounts, clients cannot use them to connect directly to the MySQL server. Instead, it is expected that users who authenticate using PAM will use the developer or data_entry account by proxy based on their PAM group.

Other ways to ensure that the proxied accounts cannot be used directly:

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

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

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

The server authenticates the connection using the ''@'' 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 should return output as shown:

This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the developer MySQL user, and that proxying occurred 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:

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