Rechercher dans le manuel MySQL
6.5.1.6 Windows Pluggable Authentication
Windows 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 for Windows supports an authentication method that performs external authentication on Windows, enabling MySQL Server to use native Windows services to authenticate client connections. Users who have logged in to Windows can connect from MySQL client programs to the server based on the information in their environment without specifying an additional password.
The client and server exchange data packets in the authentication handshake. As a result of this exchange, the server creates a security context object that represents the identity of the client in the Windows OS. This identity includes the name of the client account. Windows pluggable authentication uses the identity of the client to check whether it is a given account or a member of a group. By default, negotiation uses Kerberos to authenticate, then NTLM if Kerberos is unavailable.
Windows pluggable authentication provides these capabilities:
External authentication: Windows authentication enables MySQL Server to accept connections from users defined outside the MySQL grant tables who have logged in to Windows.
Proxy user support: Windows authentication can return to MySQL a user name different from the client user. This means that the plugin can return the MySQL user that defines the privileges the external Windows-authenticated user should have. For example, a user named
joe
can connect and have the privileges of the user nameddeveloper
.
The following table shows the plugin and library file names. The
file must be located in the directory named by the
plugin_dir
system variable.
Table 6.18 Plugin and Library Names for Windows Authentication
Plugin or File | Plugin or File Name |
---|---|
Server-side plugin | authentication_windows |
Client-side plugin | authentication_windows_client |
Library file | authentication_windows.dll |
The library file includes only the server-side plugin. The
client-side plugin is built into the
libmysqlclient
client library.
The server-side Windows authentication plugin is included only in MySQL Enterprise Edition. It is not included in MySQL community distributions. The client-side plugin is included in all distributions, including community distributions. This permits clients from any distribution to connect to a server that has the server-side plugin loaded.
The Windows authentication plugin is supported on any version of Windows supported by MySQL 8.0 (see https://www.mysql.com/support/supportedplatforms/database.html).
The following sections provide installation and usage information specific to Windows pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.3.10, “Pluggable Authentication”. For proxy user information, see Section 6.3.11, “Proxy Users”.
Installing Windows Pluggable Authentication
This section describes how to install the Windows 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.
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:
[mysqld]
plugin-load-add=authentication_windows.dll
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:
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_windows | ACTIVE |
- +------------------------+---------------+
If the plugin fails to initialize, check the server error log for diagnostic messages.
To associate MySQL accounts with the Windows authentication plugin, see Using Windows Pluggable Authentication.
The method used to uninstall the Windows 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_windows;
In addition, remove any startup options that set Windows plugin-related system variables.
The Windows authentication plugin supports the use of MySQL accounts such that users who have logged in to Windows can connect to the MySQL server without having to specify an additional password. It is assumed that the server is running with the server-side plugin enabled, as described in Installing Windows Pluggable Authentication. Once the DBA has enabled the server-side plugin and set up accounts to use it, clients can connect using those accounts with no other setup required on their part.
To refer to the Windows authentication plugin in the
IDENTIFIED WITH
clause of a
CREATE USER
statement, use the
name authentication_windows
. Suppose that
the Windows users Rafal
and
Tasha
should be permitted to connect to
MySQL, as well as any users in the
Administrators
or Power
Users
group. To set this up, create a MySQL account
named sql_admin
that uses the Windows
plugin for authentication:
- IDENTIFIED WITH authentication_windows
The plugin name is authentication_windows
.
The string following the AS
keyword is the
authentication string. It specifies that the Windows users
named Rafal
or Tasha
are
permitted to authenticate to the server as the MySQL user
sql_admin
, as are any Windows users in the
Administrators
or Power
Users
group. The latter group name contains a space,
so it must be quoted with double quote characters.
After you create the sql_admin
account, a
user who has logged in to Windows can attempt to connect to
the server using that account:
C:\> mysql --user=sql_admin
No password is required here. The
authentication_windows
plugin uses the
Windows security API to check which Windows user is
connecting. If that user is named Rafal
or
Tasha
, or is in the
Administrators
or Power
Users
group, the server grants access and the client
is authenticated as sql_admin
and has
whatever privileges are granted to the
sql_admin
account. Otherwise, the server
denies access.
Authentication string syntax for the Windows authentication plugin follows these rules:
The string consists of one or more user mappings separated by commas.
Each user mapping associates a Windows user or group name with a MySQL user name:
win_user_or_group_name=mysql_user_name win_user_or_group_name
For the latter syntax, with no
mysql_user_name
value given, the implicit value is the MySQL user created by theCREATE USER
statement. Thus, these statements are equivalent:Each backslash (
'\'
) in a value must be doubled because backslash is the escape character in MySQL strings.Leading and trailing spaces not inside double quotation marks are ignored.
Unquoted
win_user_or_group_name
andmysql_user_name
values can contain anything except equal sign, comma, or space.If a
win_user_or_group_name
and 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 name contains space characters. All characters within double quotes are legal except double quotation mark and backslash. To include either character, escape it with a backslash.win_user_or_group_name
values use conventional syntax for Windows principals, either local or in a domain. Examples (note the doubling of backslashes):domain\\user .\\user domain\\group .\\group BUILTIN\\WellKnownGroup
When invoked by the server to authenticate a client, the
plugin scans the authentication string left to right for a
user or group match to the Windows user. If there is a match,
the plugin returns the corresponding
mysql_user_name
to the MySQL
server. If there is no match, authentication fails.
A user name match takes preference over a group name match.
Suppose that the Windows user named
win_user
is a member of
win_group
and the authentication string
looks like this:
'win_group = sql_user1, win_user = sql_user2'
When win_user
connects to the MySQL server,
there is a match both to win_group
and to
win_user
. The plugin authenticates the user
as sql_user2
because the more-specific user
match takes precedence over the group match, even though the
group is listed first in the authentication string.
Windows authentication always works for connections from the same computer on which the server is running. For cross-computer connections, both computers must be registered with Windows Active Directory. If they are in the same Windows domain, it is unnecessary to specify a domain name. It is also possible to permit connections from a different domain, as in this example:
Here SomeDomain
is the name of the other
domain. The backslash character is doubled because it is the
MySQL escape character within strings.
MySQL supports the concept of proxy users whereby a client can connect and authenticate to the MySQL server using one account but while connected has the privileges of another account (see Section 6.3.11, “Proxy Users”). Suppose that you want Windows users to connect using a single user name but be mapped based on their Windows user and group names onto specific MySQL accounts as follows:
The
local_user
andMyDomain\domain_user
local and domain Windows users should map to thelocal_wlad
MySQL account.Users in the
MyDomain\Developers
domain group should map to thelocal_dev
MySQL account.Local machine administrators should map to the
local_admin
MySQL account.
To set this up, create a proxy account for Windows users to
connect to, and configure this account so that users and
groups map to the appropriate MySQL accounts
(local_wlad
, local_dev
,
local_admin
). In addition, grant the MySQL
accounts the privileges appropriate to the operations they
need to perform. The following instructions use
win_proxy
as the proxy account, and
local_wlad
, local_dev
,
and local_admin
as the proxied accounts.
Create the proxy MySQL account:
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.
For proxying to work, the proxied accounts must exist, so create them:
If you do not let anyone know the passwords for these accounts, other users cannot use them to connect directly to the MySQL server.
You should also issue
GRANT
statements (not shown) that grant each proxied account the privileges it needs.The proxy account must have the
PROXY
privilege for each proxied account:
Now the Windows users local_user
and
MyDomain\domain_user
can connect to the
MySQL server as win_proxy
and when
authenticated have the privileges of the account given in the
authentication string—in this case,
local_wlad
. A user in the
MyDomain\Developers
group who connects as
win_proxy
has the privileges of the
local_dev
account. A user in the
BUILTIN\Administrators
group has the
privileges of the local_admin
account.
To configure authentication so that all Windows users who do
not have their own MySQL account go through a proxy account,
substitute the default proxy user (''@''
)
for win_proxy
in the preceding
instructions. For information about the default proxy user,
see Section 6.3.11, “Proxy Users”.
To use the Windows authentication plugin with Connector/NET connection strings in Connector/NET 6.4.4 and higher, see Using the Windows Native Authentication Plugin.
Additional control over the Windows authentication plugin is
provided by the
authentication_windows_use_principal_name
and
authentication_windows_log_level
system variables. See
Section 5.1.8, “Server System Variables”.
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-windows-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.