6.3.7.4. The Windows Native Authentication Plugin

Note

The Windows authentication plugin is a commercial extension. To learn more about commercial products (MySQL Enterprise Edition), see http://www.mysql.com/products/.

As of MySQL 5.6.10, commercial distributions of MySQL for Windows include an authentication plugin 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. The Windows authentication plugin 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.

The Windows authentication plugin provides these capabilities:

External authentication: The plugin enables MySQL Server to accept connections from users defined outside the MySQL grant tables.
Proxy user support: The plugin 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 Windows user named joe can connect and have the privileges of the MySQL user named developer.

The following table shows the plugin and library file names. The file location must be the directory named by the plugin_dir system variable. For installation information, see Section 6.3.7.4.1, "Installing the Windows Authentication Plugin".

Table 6.12. MySQL Windows Authentication Plugin

Server-side plugin name	`authentication_windows`
Client-side plugin name	`authentication_windows_client`
Library object file name	`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 commercial distributions. 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 should work on Windows 2000 Professional and up. It requires MySQL Server 5.6.10 or newer.

For general information about pluggable authentication in MySQL, see Section 6.3.7, "Pluggable Authentication". For proxy user information, see Section 6.3.8, "Proxy Users".

6.3.7.4.1. Installing the Windows Authentication Plugin

The Windows authentication plugin must be installed in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, set the value of plugin_dir at server startup to tell the server the location of the plugin directory.

To enable the plugin, start the server with the --plugin-load option. For example, put these lines in your my.ini file:

[mysqld]plugin-load=authentication_windows.dll

Use the plugin name authentication_windows in the IDENTIFIED WITH clause of CREATE USER or GRANT statements for MySQL accounts that should be authenticated with this plugin.

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement. See Section 5.1.8.2, "Obtaining Server Plugin Information".

6.3.7.4.2. Using the Windows Authentication Plugin

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. 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 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:

CREATE USER sql_admin  IDENTIFIED WITH authentication_windows  AS 'Rafal, Tasha, Administrators, "Power Users"';

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=sql_user_namewin_user_or_group_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 strings.
Leading and trailing spaces not inside double quotation marks are ignored.
Unquoted win_user_or_group_name and sql_user_name values can contain anything except equal sign, comma, or space.
If a win_user_or_group_name and 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 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.\\userdomain\\group.\\groupBUILTIN\\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'

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:

CREATE USER sql_accounting  IDENTIFIED WITH authentication_windows  AS 'SomeDomain\\Accounting';

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.8, "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 and MyDomain\domain_user local and domain Windows users should map to the local_wlad MySQL account.
Users in the MyDomain\Developers domain group should map to the local_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:

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 GRANT 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 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.8, "Proxy Users".

To use the Windows authentication plugin with Connector/Net connection strings in Connection/Net 6.4.4 and higher, see Section 22.2.5.5, "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.4, "Server System Variables".