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-side plugin
is enabled and that client programs are recent enough to
include the client-side plugin built into
libmysqlclient (MySQL 5.5.13 or higher).
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 or
GRANT statement, use the name
authentication_windows. Suppose that the
Tasha should be permitted to connect to
MySQL, as well as any users in the
Users group. To set this up, create a MySQL account
sql_admin that uses the Windows
plugin for authentication:
CREATE USER sql_admin IDENTIFIED WITH authentication_windows AS 'Rafal, Tasha, Administrators, "Power Users"';
The plugin name is
The string following the
AS keyword is the
authentication string. It specifies that the Windows users
permitted to authenticate to the server as the MySQL user
sql_admin, as are any Windows users in the
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:
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
Tasha, or is in the
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
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:
For the latter syntax, with no
sql_user_name value given, the
implicit value is the MySQL user created by the
CREATE USER statement.
Thus, these statements are equivalent:
CREATE USER sql_admin IDENTIFIED WITH authentication_windows AS 'Rafal, Tasha, Administrators, "Power Users"'; CREATE USER sql_admin IDENTIFIED WITH authentication_windows AS 'Rafal=sql_admin, Tasha=sql_admin, Administrators=sql_admin, "Power Users"=sql_admin';
Each backslash (
'\') in a value must be
doubled because backslash is the escape character in MySQL
Leading and trailing spaces not inside double quotation marks are ignored.
sql_user_name values can
contain anything except equal sign, comma, or space.
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 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.
use conventional syntax for Windows principals, either
local or in a domain. Examples (note the doubling of
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
sql_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'
win_user connects to the MySQL server,
there is a match both to
win_group and to
win_user. The plugin authenticates the user
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:
CREATE USER sql_accounting IDENTIFIED WITH authentication_windows AS 'SomeDomain\\Accounting';
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 5.7, “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:
MyDomain\domain_user local and domain
Windows users should map to the
local_wlad MySQL account.
Users in the
group should map to the
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_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_admin as the proxied accounts.
Create the proxy MySQL account:
CREATE USER win_proxy IDENTIFIED WITH authentication_windows AS 'local_user = local_wlad, MyDomain\\domain_user = local_wlad, MyDomain\\Developers = local_dev, BUILTIN\\Administrators = local_admin';
For proxying to work, the proxied accounts must exist, so create them:
CREATE USER local_wlad IDENTIFIED BY 'wlad_pass'; CREATE USER local_dev IDENTIFIED BY 'dev_pass'; CREATE USER local_admin IDENTIFIED BY 'admin_pass';
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
statements (not shown) that grant each proxied account the
privileges it needs.
The proxy account must have the
PROXY privilege for each of
the proxied accounts:
GRANT PROXY ON local_wlad TO win_proxy; GRANT PROXY ON local_dev TO win_proxy; GRANT PROXY ON local_admin TO win_proxy;
Now the Windows users
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
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 (
win_proxy in the preceding
instructions. For information about the default proxy user,
see Section 5.7, “Proxy Users”.
To use the Windows authentication plugin with Connector/Net connection strings in Connection/Net 6.4.4 and higher, see Using the Windows Native Authentication Plugin.
Additional control over the Windows authentication plugin is
provided by the
system variables. See
Server System Variables.