Skip navigation links
5.7.3.3 PAM Authentication Plugin Debugging »
Section Navigation      [Toggle]

5.7.3.2. 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 and that client programs are recent enough to include the client-side plugin.

Note

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 SSL. See Section 5.7.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: 

CREATE USER user
  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 users, respectively, use a statement like this: 

CREATE USER user
  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 SQL user name: 

    pam_service_name[,group_name=sql_user_name]...

  • Each group_name=sql_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 sql_user_name values can contain anything except equal sign, comma, or space.

  • If a pam_service_name, group_name, or sql_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.

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

If the plugin successfully authenticates a login name, it looks for a group mapping list in the authentication string and 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=sql_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 sql_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. 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, “Privilege System 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 a few MySQL accounts that define different sets of privileges. (Ideally, nobody should log in through these 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 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 different service name, the file name will be different and you must use a different name in the AS 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. If so, the plugin enables logging of diagnostic messages to the standard output. These messages may be helpful for debugging PAM-related problems that occur when the plugin performs authentication. For more information, see Section 5.7.3.3, “PAM Authentication Plugin Debugging”.

5.7.3.2.1. Unix Password Authentication without Proxy Users

This authentication scenario uses PAM only to check Unix user 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.

  1. Verify that Unix authentication in PAM permits you to log in as antonio with password verysecret.

  2. Set up PAM to authenticate the mysql service. Put the following in /etc/pam.d/mysql: 

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

  3. Create a MySQL account with the same user name as the Unix login name and define it to authenticate using the PAM plugin: 

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

  4. Try to connect to the MySQL server using the mysql command-line client. For example: 

    mysql --user=antonio --password=verysecret mydb

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

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

    This shows that antonio uses the privileges granted to the antonio MySQL account, and that no proxying has occurred.

5.7.3.2.2. LDAP Authentication without Proxy Users

This authentication scenario uses PAM only to check LDAP user 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.

  1. Verify that LDAP authentication in PAM permits you to log in as antonio with password verysecret.

  2. Set up PAM to authenticate the mysql service through LDAP. Put the following in /etc/pam.d/mysql: 

    #%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.

  3. MySQL account creation and connecting to the server is the same as previously described in Section 5.7.3.2.1, “Unix Password Authentication without Proxy Users”.

5.7.3.2.3. Unix Password Authentication with Proxy Users and Group Mapping

This authentication scheme uses proxying and group mapping to map users who connect to the MySQL server through PAM onto a few MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy user authenticating through PAM that uses a mapping scheme to map all the external logins to the few MySQL accounts holding the privileges. Any user who connects is mapped to one of the MySQL accounts and uses its privileges.

The procedure shown here uses Unix password authentication. To use LDAP instead, see the early steps of Section 5.7.3.2.2, “LDAP Authentication without Proxy Users”.

  1. Verify that Unix authentication in PAM permits you to log in as antonio with password verysecret and that antonio is a member of the root or users group.

  2. Set up PAM to authenticate the mysql service. Put the following in /etc/pam.d/mysql: 

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

  3. Create the default proxy user that maps the external PAM users to the proxied accounts. It maps external users from the root PAM group to the developer MySQL account and the external users from the users PAM group to the data_entry MySQL account: 

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

    The mapping list following the service name is required when you set up proxy users. Otherwise, the plugin cannot tell how to map the name of PAM groups to the proper proxied user name.

  4. Create the proxied accounts that will be used to access the databases: 

    CREATE USER 'developer'@'localhost' IDENTIFIED BY 'very secret password';
GRANT ALL PRIVILEGES ON mydevdb.* TO 'developer'@'localhost';
CREATE USER 'data_entry'@'localhost' IDENTIFIED BY 'very secret password';
GRANT ALL PRIVILEGES ON mydb.* TO 'data_entry'@'localhost';

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

  5. Grant the PROXY privilege to the proxy account for the proxied accounts: 

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

  6. Try to connect to the MySQL server using the mysql command-line client. For example: 

    mysql --user=antonio --password=verysecret mydb

    The server authenticates the connection using the ''@'' account. The privileges antonio will have depends on what PAM groups he 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: 

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

    This shows that antonio uses the privileges granted to the developer MySQL account, and that proxying occurred through the default proxy user account.

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

    mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+-------------------+----------------------+--------------+
| USER()            | CURRENT_USER()       | @@proxy_user |
+-------------------+----------------------+--------------+
| antonio@localhost | data_entry@localhost | ''@''        |
+-------------------+----------------------+--------------+
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices