MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
PAM pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.
MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use PAM (Pluggable Authentication Modules) to authenticate MySQL users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as traditional Unix passwords or an LDAP directory.
PAM pluggable authentication provides these capabilities:
External authentication: PAM authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables and that authenticate using methods supported by PAM.
Proxy user support: PAM authentication can return to MySQL a
user name different from the external user name passed by
the client program, based on the PAM groups the external
user is a member of and the authentication string provided.
This means that the plugin can return the MySQL user that
defines the privileges the external PAM-authenticated user
should have. For example, an operating sytem user named
joe
can connect and have the privileges
of a MySQL user named developer
.
PAM pluggable authentication has been tested on Linux and macOS.
The following table shows the plugin and library file names. The
file name suffix might differ on your system. The file must be
located in the directory named by the
plugin_dir
system variable. For
installation information, see
Installing PAM Pluggable Authentication.
Table 6.12 Plugin and Library Names for PAM Authentication
Plugin or File | Plugin or File Name |
---|---|
Server-side plugin | authentication_pam |
Client-side plugin | mysql_clear_password |
Library file | authentication_pam.so |
The client-side mysql_clear_password
cleartext plugin that communicates with the server-side PAM
plugin is built into the libmysqlclient
client library and is included in all distributions, including
community distributions. Inclusion of the client-side cleartext
plugin in all MySQL distributions enables clients from any
distribution to connect to a server that has the server-side PAM
plugin loaded.
The following sections provide installation and usage information specific to PAM pluggable authentication:
For general information about pluggable authentication in MySQL,
see Section 6.2.11, “Pluggable Authentication”. For information
about the mysql_clear_password
plugin, see
Section 6.4.1.5, “Client-Side Cleartext Pluggable Authentication”. For proxy
user information, see Section 6.2.12, “Proxy Users”.
This section provides a general overview of how MySQL and PAM work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use specific PAM services, see Using 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 client user name is the external user name.
For accounts that use the PAM server-side
authentication plugin, the corresponding client-side
plugin is mysql_clear_password
.
This client-side plugin performs no password hashing,
with the result that the client sends the password to
the server as cleartext.
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:
A PAM service name, which is a name that the system administrator can use to refer to an authentication method for a particular application. There can be multiple applications associated with a single database server instance, so the choice of service name is left to the SQL application developer.
Optionally, if proxying is to be used, a mapping from PAM groups to MySQL user names.
The plugin uses the PAM service named in the
authentication string to check the user credentials and
returns 'Authentication succeeded, Username is
or
user_name
''Authentication failed'
. The password
must be appropriate for the password store used by the PAM
service. Examples:
For traditional Unix passwords, the service looks up
passwords stored in the
/etc/shadow
file.
For LDAP, the service looks up passwords stored in an LDAP directory.
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:
For traditional Unix passwords, groups are Unix groups
defined in the /etc/group
file,
possibly supplemented with additional PAM information
in a file such as
/etc/security/group.conf
.
For LDAP, groups are LDAP groups defined in an LDAP directory.
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.
This section describes how to install the PAM authentication plugin. For general information about installing plugins, see Section 5.5.1, “Installing and Uninstalling Plugins”.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
the plugin_dir
system
variable). If necessary, configure the plugin directory
location by setting the value of
plugin_dir
at server startup.
The plugin library file base name is
authentication_pam
. The file name suffix
differs per platform (for example, .so
for Unix and Unix-like systems, .dll
for
Windows).
To load the plugin at server startup, use the
--plugin-load-add
option to
name the library file that contains it. With this
plugin-loading method, the option must be given each time the
server starts. For example, put these lines in the server
my.cnf
file, adjusting the
.so
suffix for your platform as
necessary:
[mysqld] plugin-load-add=authentication_pam.so
After modifying my.cnf
, restart the
server to cause the new settings to take effect.
Alternatively, to load the plugin at runtime, use this
statement, adjusting the .so
suffix for
your platform as necessary:
INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';
INSTALL PLUGIN
loads the plugin
immediately, and also registers it in the
mysql.plugins
system table to cause the
server to load it for each subsequent normal startup without
the need for --plugin-load-add
.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement (see
Section 5.5.2, “Obtaining Server Plugin Information”). For example:
mysql>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE '%pam%';
+--------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------------+---------------+ | authentication_pam | ACTIVE | +--------------------+---------------+
If the plugin failed to initialize, check the server error log for diagnostic messages.
To associate MySQL accounts with the PAM plugin, see Using PAM Pluggable Authentication.
The method used to uninstall the PAM authentication plugin depends on how you installed it:
If you installed the plugin at server startup using a
--plugin-load-add
option,
restart the server without the option.
If you installed the plugin at runtime using an
INSTALL PLUGIN
statement,
it remains installed across server restarts. To uninstall
it, use UNINSTALL PLUGIN
:
UNINSTALL PLUGIN authentication_pam;
This section describes in general terms how to use the PAM authentication plugin to connect from MySQL client programs to the server. The following sections provide instructions for using PAM authentication in specific ways. It is assumed that the server is running with the server-side PAM plugin enabled, as described in Installing PAM Pluggable Authentication.
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 USERuser
IDENTIFIED WITH authentication_pam AS 'auth_string
';
The authentication string specifies the following types of information:
The PAM service name (see
How PAM Authentication of MySQL Users Works).
Examples in the following discussion use a service name of
mysql-unix
for authentication using
traditional Unix passwords, and
mysql-ldap
for authentication using
LDAP.
For proxy support, PAM provides a way for a PAM module to return to the server a MySQL user name other than the external user name passed by the client program when it connects to the server. Use the authentication string to control the mapping from external user names to MySQL user names. If you want to take advantage of proxy user capabilities, the authentication string must include this kind of mapping.
For example, if an account uses the
mysql-unix
PAM service name and should map
operating system users in the root
and
users
PAM groups to the
developer
and data_entry
MySQL users, respectively, use a statement like this:
CREATE USER user
IDENTIFIED WITH authentication_pam
AS 'mysql-unix, 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 PAM group mapping list consisting of one or more keyword/value pairs each specifying a PAM group name and a MySQL user name:
pam_service_name
[,pam_group_name
=mysql_user_name
]...
The plugin parses the authentication string for each connection attempt that uses the account. To minimize overhead, keep the string as short as possible.
Each
pair must be preceded by a comma.
pam_group_name
=mysql_user_name
Leading and trailing spaces not inside double quotation marks are ignored.
Unquoted pam_service_name
,
pam_group_name
, and
mysql_user_name
values can
contain anything except equal sign, comma, or space.
If a pam_service_name
,
pam_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 the external user name (the name passed by the client), it looks for a PAM group mapping list in the authentication string and, if present, uses it to return a different MySQL user name to the MySQL server based on which PAM groups the external user is a member of:
If the authentication string contains no PAM group mapping list, the plugin returns the external name.
If the authentication string does contain a PAM group
mapping list, the plugin examines each
pair in the list from left to right and tries to find a
match for the pam_group_name
=mysql_user_name
pam_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 PAM
group, it returns the external name. If the plugin is not
capable of looking up a group in a directory, it ignores
the PAM group mapping list and returns the external 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 PAM authentication. (For a MySQL account of
'
to match the external user,
user_name
'@'host_name
'user_name
must be the external
user name and host_name
must
match the host from which the client connects.)
Authentication can be performed by various PAM-supported
methods. Later discussion shows how to authenticate client
credentials using traditional Unix passwords, and
passwords in LDAP.
PAM authentication, when not done through proxy users or PAM groups, requires the MySQL user name to be same as the operating system user name. MySQL user names are limited to 16 characters (see Section 6.2.3, “Grant Tables”), which limits PAM nonproxy authentication to Unix accounts with names of at most 16 characters.
Proxy users only, with PAM group mapping. For this scenario, create one or more 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 based on the external PAM groups the users are members of) to map all the external user names to the few MySQL accounts holding the privilege sets. Any client who connects and specifies an external user name as the client user name is mapped to one of the MySQL accounts and uses its privileges. The discussion shows how to set this up using traditional Unix passwords, but other PAM methods such as LDAP could be used instead.
Variations on these scenarios are possible:
You can permit some users to log in directly (without proxying) but require others to connect through proxy accounts.
You can use one PAM authentication method for some users,
and another method for other users, by using differing PAM
service names among your PAM-authenticated accounts. For
example, you can use the mysql-unix
PAM
service for some users, and mysql-ldap
for others.
The examples make the following assumptions. You might need to make some adjustments if your system is set up differently.
The login name and password are antonio
and antonio_password
,
respectively. Change these to correspond to the user you
want to authenticate.
The PAM configuration directory is
/etc/pam.d
.
The PAM service name corresponds to the authentication
method (mysql-unix
or
mysql-ldap
in this discussion). To use
a given PAM service, you must set up a PAM file with the
same name in the PAM configuration directory (creating the
file if it does not exist). In addition, you must use the
PAM service name in the authentication string of
CREATE USER
or
GRANT
statements for any
account that authenticates using that service.
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 issues
that occur when the plugin performs authentication. For more
information, see
PAM Authentication Debugging.
This authentication scenario uses PAM to check external users defined in terms of operating system user names and Unix passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through traditional Unix password store.
Traditional Unix passwords are checked using the
/etc/shadow
file. For information
regarding possible issues related to this file, see
PAM Authentication Access to Unix Password Store.
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:
shell>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.
The client-side mysql_clear_password
authentication plugin leaves the password untouched, so
client programs send it to the MySQL server as cleartext.
This enables the password to be passed as is to PAM. A
cleartext password is necessary to use the server-side PAM
library, but may be a security problem in some
configurations. These measures minimize the risk:
To make inadvertent use of the
mysql_clear_password
plugin less
likely, MySQL clients must explicitly enable it (for
example, with the
--enable-cleartext-plugin
option). See
Section 6.4.1.5, “Client-Side Cleartext Pluggable Authentication”.
To avoid password exposure with the
mysql_clear_password
plugin enabled,
MySQL clients should connect to the MySQL server using
an encrypted connection. See
Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.
This authentication scenario uses PAM to check external users defined in terms of operating system user names and LDAP passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through LDAP.
To use PAM LDAP pluggable authentication for MySQL, these prerequisites must be satisfied:
An LDAP server must be available for the PAM LDAP service to communicate with.
LDAP users to be authenticated by MySQL must be present in the directory managed by the LDAP server.
Configure MySQL for PAM LDAP authentication as follows:
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.
The authentication scheme described here uses proxying and PAM group mapping to map connecting MySQL users who authenticate using PAM onto other 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 account authenticated using PAM, such that all the external users are mapped to the MySQL accounts that hold the privileges. Any user who connects using the proxy account is mapped to one of those MySQL accounts, the privileges for which determine the database operations permitted to the external user.
The procedure shown here uses Unix password authentication. To use LDAP instead, see the early steps of PAM LDAP Authentication without Proxy Users.
Traditional Unix passwords are checked using the
/etc/shadow
file. For information
regarding possible issues related to this file, see
PAM Authentication Access to Unix Password Store.
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.
If your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this issue, and ways of dealing with it, see Default Proxy User and Anonymous User Conflicts.
Create the proxied accounts and grant to each one the privileges it should have:
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'very secret password
'; CREATE USER 'data_entry'@'localhost' IDENTIFIED BY 'very secret password
'; GRANT ALL PRIVILEGES ON mydevdb.* TO 'developer'@'localhost'; GRANT ALL PRIVILEGES ON mydb.* TO 'data_entry'@'localhost';
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 use the
developer
or
data_entry
account by proxy based on
their PAM group.
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
.
shell>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 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:
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 occurred through the default proxy
account.
The client-side mysql_clear_password
authentication plugin leaves the password untouched, so
client programs send it to the MySQL server as cleartext.
This enables the password to be passed as is to PAM. A
cleartext password is necessary to use the server-side PAM
library, but may be a security problem in some
configurations. These measures minimize the risk:
To make inadvertent use of the
mysql_clear_password
plugin less
likely, MySQL clients must explicitly enable it (for
example, with the
--enable-cleartext-plugin
option). See
Section 6.4.1.5, “Client-Side Cleartext Pluggable Authentication”.
To avoid password exposure with the
mysql_clear_password
plugin enabled,
MySQL clients should connect to the MySQL server using
an encrypted connection. See
Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.
On some systems, Unix authentication uses a password store
such as /etc/shadow
, a file that
typically has restricted access permissions. This can cause
MySQL PAM-based authentication to fail. Unfortunately, the PAM
implementation does not permit distinguishing “password
could not be checked” (due, for example, to inability
to read /etc/shadow
) from “password
does not match.” If you are using Unix password store
for PAM authentication, you may be able to enable access to it
from MySQL using one of the following methods:
Assuming that the MySQL server is run from the
mysql
operating system account, put
that account in the shadow
group that
has /etc/shadow
access:
Create a shadow
group in
/etc/group
.
Add the mysql
operating system user
to the shadow
group in
/etc/group
.
Assign /etc/group
to the
shadow
group and enable the group
read permission:
chgrp shadow /etc/shadow chmod g+r /etc/shadow
Restart the MySQL server.
If you are using the pam_unix
module
and the unix_chkpwd utility, enable
password store access as follows:
chmod u-s /usr/sbin/unix_chkpwd setcap cap_dac_read_search+ep /usr/sbin/unix_chkpwd
Adjust the path to unix_chkpwd as necessary for your platform.
The PAM authentication plugin checks at initialization time
whether the AUTHENTICATION_PAM_LOG
environment value is set (the value does not matter). If so,
the plugin enables logging of diagnostic messages to the
standard output. These messages may be helpful for debugging
PAM-related issues that occur when the plugin performs
authentication.
Some messages include reference to PAM plugin source files and line numbers, which enables plugin actions to be tied more closely to the location in the code where they occur.
Another technique for debugging connection failures and determining what is happening during connection attempts is to configure PAM authentication to permit all connections, then check the system log files. This technique should be used only on a temporary basis, and not on a production server.
Configure a PAM service file named
/etc/pam.d/mysql-any-password
with these
contents (the format may differ on some systems):
#%PAM-1.0 auth required pam_permit.so account required pam_permit.so
Create an account that uses the PAM plugin and names the
mysql-any-password
PAM service:
CREATE USER 'testuser'@'localhost' IDENTIFIED WITH authentication_pam AS 'mysql-any-password';
The mysql-any-password
service file causes
any authentication attempt to return true, even for incorrect
passwords. If an authentication attempt fails, that tells you
the configuration problem is on the MySQL side. Otherwise, the
problem is on the operating system/PAM side. To see what might
be happening, check system log files such as
/var/log/secure
,
/var/log/audit.log
,
/var/log/syslog
, or
/var/log/messages
.
After determining what the problem is, remove the
mysql-any-password
PAM service file to
disable any-password access.