Rechercher dans le manuel MySQL
6.5.1.5 PAM Pluggable Authentication
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 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 login user, based on the groups the external user is in 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, a user named
joe
can connect and have the privileges of the user nameddeveloper
.
PAM pluggable authentication has been tested on Linux and macOS.
The PAM plugin uses the information passed to it by MySQL Server
(such as user name, host name, password, and authentication
string), plus whatever method is available for PAM lookup. The
plugin checks the user credentials against PAM and returns
'Authentication succeeded, Username is
or
user_name
''Authentication failed'
.
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.17 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 clear-text 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 clear-text plugin in all MySQL
distributions enables clients from any distribution to connect
to a server that has the server-side 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.3.10, “Pluggable Authentication”. For information
about the mysql_clear_password
plugin, see
Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”. For proxy
user information, see Section 6.3.11, “Proxy Users”.
Installing PAM Pluggable Authentication
This section describes how to install the PAM authentication plugin. For general information about installing plugins, see Section 5.6.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 (adjust 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 register the plugin at runtime, use this
statement (adjust the .so
suffix as
necessary):
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.
To verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS
table
or use the SHOW PLUGINS
statement (see
Section 5.6.2, “Obtaining Server Plugin Information”). For example:
- FROM INFORMATION_SCHEMA.PLUGINS
- +--------------------+---------------+
- | PLUGIN_NAME | PLUGIN_STATUS |
- +--------------------+---------------+
- | authentication_pam | ACTIVE |
- +--------------------+---------------+
If the plugin fails 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
INSTALL PLUGIN
, it remains installed across server restarts. To uninstall it, useUNINSTALL PLUGIN
:- UNINSTALL PLUGIN authentication_pam;
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 is running with the server-side 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
statement, use the
name authentication_pam
. For example:
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:
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:
pam_service_name[,group_name=mysql_user_name]...
The plugin parses the authentication string on each login check. To minimize overhead, keep the string as short as possible.
Each
pair must be preceded by a comma.group_name
=mysql_user_name
Leading and trailing spaces not inside double quotation marks are ignored.
Unquoted
pam_service_name
,group_name
, andmysql_user_name
values can contain anything except equal sign, comma, or space.If a
pam_service_name
,group_name
, ormysql_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
pair in the list from left to right and tries to find a match for thegroup_name
=mysql_user_name
group_name
value in a non-MySQL directory of the groups assigned to the authenticated user and returnsmysql_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
to match the external user,user_name
@host_name
user_name
must be the login name andhost_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. MySQL user names are limited to 32 characters (see Section 6.2.3, “Grant Tables”), which limits PAM nonproxy authentication to Unix accounts with names of at most 32 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 namedmysql
in the PAM configuration directory (creating the file if it does not exist). If you use a service name different frommysql
, the file name will differ and you must use a different name in theAS '
clause ofauth_string
'CREATE USER
statements.The examples use a login name of
antonio
and password ofverysecret
. 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
PAM Pluggable Authentication Debugging.
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.
Verify that Unix authentication in PAM permits you to log in as
antonio
with passwordverysecret
.Set up PAM to authenticate the
mysql
service by creating a file named/etc/pam.d/mysql
. 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, themysql
file might look like this:#%PAM-1.0 auth include password-auth account include password-auth
For Gentoo Linux, use
system-login
rather thanpassword-auth
. For macOS, uselogin
rather thanpassword-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 Unix login name and define it to authenticate using the PAM plugin:
Connect to the MySQL server using the mysql command-line client. For example:
mysql --user=antonio --password --enable-cleartext-plugin mydb Enter password: verysecret
The server should permit the connection and the following query should return output as shown:
- +-------------------+-------------------+--------------+
- +-------------------+-------------------+--------------+
- +-------------------+-------------------+--------------+
This demonstrates that
antonio
uses the privileges granted to theantonio
MySQL account, and that no proxying has occurred.
The client-side mysql_clear_password
plugin with which the server-side PAM plugin communicates
sends the password to the MySQL server in clear text so it
can be passed to PAM. This 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.To avoid password exposure with the
mysql_clear_password
plugin enabled, MySQL clients should connect to the MySQL server using a secure connection.
For additinal information, see Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”, and Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
On some systems, Unix authentication uses
/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 your system uses
/etc/shadow
, you may be able enable
access to it by MySQL using this method (assuming that the
MySQL server is run from the mysql
system
account):
Create a
shadow
group in/etc/group
.Add the
mysql
user to theshadow
group in/etc/group
.Assign
/etc/group
to theshadow
group and enable the group read permission:chgrp shadow /etc/shadow chmod g+r /etc/shadow
Restart the MySQL server.
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.
Verify that LDAP authentication in PAM permits you to log in as
antonio
with passwordverysecret
.Set up PAM to authenticate the
mysql
service through LDAP by creating a file named/etc/pam.d/mysql
. 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, themysql
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.
MySQL account creation and connecting to the server is the same as described in Unix Password Authentication without Proxy Users.
The authentication scheme described here uses proxying and 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 user authenticated using PAM, such that all the external logins are mapped to the MySQL accounts that hold the privileges. Any user who connects 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 LDAP Authentication without Proxy Users.
For information regarding possible problems related to
/etc/shadow
, see
Unix Password Authentication without Proxy Users.
Verify that Unix authentication in PAM permits you to log in as
antonio
with passwordverysecret
and thatantonio
is a member of theroot
orusers
group.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
For Gentoo Linux, use
system-login
rather thanpassword-auth
. For macOS, uselogin
rather thanpassword-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 the external PAM users to the proxied accounts. It maps external users from theroot
PAM group to thedeveloper
MySQL account and the external users from theusers
PAM group to thedata_entry
MySQL account: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.
NoteIf your MySQL installation has anonymous users, they might conflict with the default proxy user. For more information about this problem, and ways of dealing with it, see Default Proxy User and Anonymous User Conflicts.
Create the proxied accounts that will be used to access the databases:
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
ordata_entry
account by proxy based on their PAM group.Grant the
PROXY
privilege to the proxy account for each proxied account:Connect to the MySQL server using the mysql command-line client. For example:
mysql --user=antonio --password --enable-cleartext-plugin mydb Enter password: verysecret
The server authenticates the connection using the
''@''
account. The privilegesantonio
will have depends on what PAM groups he is a member of. Ifantonio
is a member of theroot
PAM group, the PAM plugin mapsroot
to thedeveloper
MySQL user name and returns that name to the server. The server verifies that''@''
has thePROXY
privilege fordeveloper
and permits the connection. the following query should return output as shown:- +-------------------+---------------------+--------------+
- +-------------------+---------------------+--------------+
- | antonio@localhost | developer@localhost | ''@'' |
- +-------------------+---------------------+--------------+
This demonstrates that
antonio
uses the privileges granted to thedeveloper
MySQL account, and that proxying occurred through the default proxy user account.If
antonio
is not a member of theroot
PAM group but is a member of theusers
group, a similar process occurs, but the plugin mapsuser
group membership to thedata_entry
MySQL user name and returns that name to the server. In this case,antonio
uses the privileges of thedata_entry
MySQL account:- +-------------------+----------------------+--------------+
- +-------------------+----------------------+--------------+
- | antonio@localhost | data_entry@localhost | ''@'' |
- +-------------------+----------------------+--------------+
The client-side mysql_clear_password
plugin with which the server-side PAM plugin communicates
sends the password to the MySQL server in clear text so it
can be passed to PAM. This 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.To avoid password exposure with the
mysql_clear_password
plugin enabled, MySQL clients should connect to the MySQL server using a secure connection.
For additinal information, see Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication”, and Section 6.4.1, “Configuring MySQL to Use Encrypted Connections”.
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 problems 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.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-pam-pluggable-authentication.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.