Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.7Mb
PDF (A4) - 0.7Mb
EPUB - 178.1Kb
HTML Download (TGZ) - 145.5Kb
HTML Download (Zip) - 155.5Kb

Security in MySQL  /  ...  /  Using the PAM Authentication Plugin Using the PAM Authentication Plugin

This section describes how to use the PAM authentication plugin to connect from MySQL client programs to the server. It is assumed that the server-side plugin is enabled, as described previously, and that client programs are recent enough to include the client-side plugin.


The client-side plugin with which the PAM plugin communicates simply sends the password to the server in clear text so it can be passed to PAM. This may be a security problem in some configurations, but is necessary to use the server-side PAM library. To avoid problems if there is any possibility that the password would be intercepted, clients should connect to MySQL Server using a secure connection. See Section 7.1.5, “The Cleartext Client-Side Authentication Plugin”.

To refer to the PAM authentication plugin in the IDENTIFIED WITH clause of a CREATE USER or GRANT statement, use the name authentication_pam. For example:

  IDENTIFIED WITH authentication_pam
  AS 'authentication_string';

The authentication string specifies the following types of information:

  • PAM supports the notion of service name, which is a name that the system administrator can use to configure the authentication method for a particular application. There can be several such applications associated with a single database server instance, so the choice of service name is left to the SQL application developer. When you define an account that should authenticate using PAM, specify the service name in the authentication string.

  • PAM provides a way for a PAM module to return to the server a MySQL user name other than the login name supplied at login time. Use the authentication string to control the mapping between login name and MySQL user name. If you want to take advantage of proxy user capabilities, the authentication string must include this kind of mapping.

For example, if the service name is mysql and users in the root and users PAM groups should be mapped to the developer and data_entry MySQL users, respectively, use a statement like this:

  IDENTIFIED WITH authentication_pam
  AS 'mysql, root=developer, users=data_entry';

Authentication string syntax for the PAM authentication plugin follows these rules:

  • The string consists of a PAM service name, optionally followed by a group mapping list consisting of one or more keyword/value pairs each specifying a group name and a MySQL user name:


    The plugin parses the authentication string on each login check. To minimize overhead, keep the string as short as possible.

  • Each group_name=mysql_user_name pair must be preceded by a comma.

  • Leading and trailing spaces not inside double quotation marks are ignored.

  • Unquoted pam_service_name, group_name, and mysql_user_name values can contain anything except equal sign, comma, or space.

  • If a pam_service_name, group_name, or mysql_user_name value is quoted with double quotation marks, everything between the quotation marks is part of the value. This is necessary, for example, if the value contains space characters. All characters are legal except double quotation mark and backslash (\). To include either character, escape it with a backslash.

If the plugin successfully authenticates a login name, it looks for a group mapping list in the authentication string and, if present, uses it to return a different user name to the MySQL server based on the groups the external user is a member of:

  • If the authentication string contains no group mapping list, the plugin returns the login name.

  • If the authentication string does contain a group mapping list, the plugin examines each group_name=mysql_user_name pair in the list from left to right and tries to find a match for the group_name value in a non-MySQL directory of the groups assigned to the authenticated user and returns mysql_user_name for the first match it finds. If the plugin finds no match for any group, it returns the login name. If the plugin is not capable of looking up a group in a directory, it ignores the group mapping list and returns the login name.

The following sections describe how to set up several authentication scenarios that use the PAM authentication plugin:

  • No proxy users. This uses PAM only to check login names and passwords. Every external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use external PAM authentication. (For a MySQL account of user_name@host_name to match the external user, user_name must be the login name and host_name must match the host from which the client connects.) Authentication can be performed by various PAM-supported methods. The discussion shows how to use traditional Unix passwords and LDAP.

    PAM authentication, when not done through proxy users or groups, requires the MySQL account to have the same user name as the Unix account. Because MySQL user names are limited to 16 characters (see Section 4.2, “Grant Tables”), this limits PAM nonproxy authentication to Unix accounts with names of at most 16 characters.

  • Proxy login only and group mapping. For this scenario, create one or a few MySQL accounts that define different sets of privileges. (Ideally, nobody should connect using those accounts directly.) Then define a default user authenticating through PAM that uses some mapping scheme (usually by the external groups the users are in) to map all the external logins to the few MySQL accounts holding the privilege sets. Any user that logs in is mapped to one of the MySQL accounts and uses its privileges. The discussion shows how to set this up using Unix passwords, but other PAM methods such as LDAP could be used instead.

Variations on these scenarios are possible. For example, you can permit some users to log in directly (without proxying) but require others to connect through proxy users.

The examples make the following assumptions. You might need to make some adjustments if your system is set up differently.

  • The PAM configuration directory is /etc/pam.d.

  • The PAM service name is mysql, which means that you must set up a PAM file named mysql in the PAM configuration directory (creating the file if it does not exist). If you use a service name different from mysql, the file name will be different and you must use a different name in the AS 'auth_string' clause of CREATE USER and GRANT statements.

  • The examples use a login name of antonio and password of verysecret. Change these to correspond to the users you want to authenticate.

The PAM authentication plugin checks at initialization time whether the AUTHENTICATION_PAM_LOG environment value is set in the server's startup environment. If so, the plugin enables logging of diagnostic messages to the standard output. Depending on how your server is started, the message might appear on the console or in the error log. These messages can be helpful for debugging PAM-related problems that occur when the plugin performs authentication. For more information, see Section, “PAM Authentication Plugin Debugging”.

User Comments
Sign Up Login You must be logged in to post a comment.