MySQL :: Security in MySQL :: 6.1.8 Kerberos Pluggable Authentication (original) (raw)

6.1.8 Kerberos Pluggable Authentication

Note

Kerberos 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 users to authenticate to MySQL Server using Kerberos, provided that appropriate Kerberos tickets are available or can be obtained.

This authentication method is available in MySQL 8.0.26 and higher, for MySQL servers and clients on Linux. It is useful in Linux environments where applications have access to Microsoft Active Directory, which has Kerberos enabled by default. As of MySQL 8.0.27 (MySQL 8.0.32 for MIT Kerberos), the client-side plugin is supported on Windows as well. The server-side plugin is still supported only on Linux.

Kerberos pluggable authentication provides these capabilities:

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 theplugin_dir system variable. For installation information, seeInstalling Kerberos Pluggable Authentication.

Table 6.9 Plugin and Library Names for Kerberos Authentication

Plugin or File Plugin or File Name
Server-side plugin authentication_kerberos
Client-side plugin authentication_kerberos_client
Library file authentication_kerberos.so,authentication_kerberos_client.so

The server-side Kerberos 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 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 Kerberos pluggable authentication:

For general information about pluggable authentication in MySQL, see Section 4.17, “Pluggable Authentication”.

Prerequisites for Kerberos Pluggable Authentication

To use Kerberos pluggable authentication for MySQL, these prerequisites must be satisfied:

How Kerberos Authentication of MySQL Users Works

This section provides an overview of how MySQL and Kerberos work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use the Kerberos authentication plugins, seeUsing Kerberos Pluggable Authentication.

Familiarity is assumed here with Kerberos concepts and operation. The following list briefly defines several common Kerberos terms. You may also find the Glossary section ofRFC 4120 helpful.

Authentication using Kerberos requires a KDC server, for example, as provided by Microsoft Active Directory.

Kerberos authentication in MySQL uses Generic Security Service Application Program Interface (GSSAPI), which is a security abstraction interface. Kerberos is an instance of a specific security protocol that can be used through that abstract interface. Using GSSAPI, applications authenticate to Kerberos to obtain service credentials, then use those credentials in turn to enable secure access to other services.

On Windows, theauthentication_kerberos_client authentication plugin supports two modes, which the client user can set at runtime or specify in an option file:

With the Kerberos authentication plugins, applications and MySQL servers are able to use the Kerberos authentication protocol to mutually authenticate users and MySQL services. This way both the user and the server are able to verify each other's identity. No passwords are sent over the network and Kerberos protocol messages are protected against eavesdropping and replay attacks.

Kerberos authentication follows these steps, where the server-side and client-side parts are performed using theauthentication_kerberos andauthentication_kerberos_client authentication plugins, respectively:

  1. The MySQL server sends to the client application its service principal name. This SPN must be registered in the Kerberos system, and is configured on the server side using theauthentication_kerberos_service_principal system variable.
  2. Using GSSAPI, the client application creates a Kerberos client-side authentication session and exchanges Kerberos messages with the Kerberos KDC:
    • The client obtains a ticket-granting ticket from the authentication server.
    • Using the TGT, the client obtains a service ticket for MySQL from the ticket-granting service.
      This step can be skipped or partially skipped if the TGT, ST, or both are already cached locally. The client optionally may use a client keytab file to obtain a TGT and ST without supplying a password.
  3. Using GSSAPI, the client application presents the MySQL ST to the MySQL server.
  4. Using GSSAPI, the MySQL server creates a Kerberos server-side authentication session. The server validates the user identity and the validity of the user request. It authenticates the ST using the service key configured in its service keytab file to determine whether authentication succeeds or fails, and returns the authentication result to the client.

Applications are able to authenticate using a provided user name and password, or using a locally cached TGT or ST (for example, created using kinit or similar). This design therefore covers use cases ranging from completely userless and passwordless connections, where Kerberos service tickets are obtained from a locally stored Kerberos cache, to connections where both user name and password are provided and used to obtain a valid Kerberos service ticket from a KDC, to send to the MySQL server.

As indicated in the preceding description, MySQL Kerberos authentication uses two kinds of keytab files:

For information about keytab files, seehttps://web.mit.edu/kerberos/krb5-latest/doc/basic/keytab_def.html.

Installing Kerberos Pluggable Authentication

This section describes how to install the server-side Kerberos authentication plugin. For general information about installing plugins, see Installing and Uninstalling Plugins.

Note

The server-side plugin is supported only on Linux systems. On Windows systems, only the client-side plugin is supported (as of MySQL 8.0.27), which can be used on a Windows system to connect to a Linux server that uses Kerberos authentication.

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 ofplugin_dir at server startup.

The server-side plugin library file base name isauthentication_kerberos. The file name suffix for Unix and Unix-like systems is.so.

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. Also, specify values for any plugin-provided system variables you wish to configure. The plugin exposes these system variables, enabling its operation to be configured:

For details about all Kerberos authentication system variables, seeSection 6.1.13, “Pluggable Authentication System Variables”.

To load the plugin and configure it, put lines such as these in your my.cnf file, using values for the system variables that are appropriate for your installation:

[mysqld]
plugin-load-add=authentication_kerberos.so
authentication_kerberos_service_principal=mysql/krbauth.example.com@MYSQL.LOCAL
authentication_kerberos_service_key_tab=/var/mysql/data/mysql.keytab

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:

INSTALL PLUGIN authentication_kerberos
  SONAME 'authentication_kerberos.so';

INSTALL PLUGIN loads the plugin immediately, and also registers it in themysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

When you install the plugin at runtime without configuring its system variables in the my.cnf file, the system variableauthentication_kerberos_service_key_tab is set to the default value ofmysql.keytab in the data directory. The value of this system variable cannot be changed at runtime, so if you need to specify a different file, you need to add the setting to your my.cnf file then restart the MySQL server. For example:

[mysqld]
authentication_kerberos_service_key_tab=/var/mysql/data/mysql.keytab

If the keytab file is not in the correct place or does not contain a valid SPN key, the MySQL server does not validate this, but clients return authentication errors until you fix the issue.

Theauthentication_kerberos_service_principal system variable can be set and persisted at runtime without restarting the server, by using aSET PERSIST statement:

SET PERSIST authentication_kerberos_service_principal='mysql/krbauth.example.com@MYSQL.LOCAL';

SET PERSIST sets a value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change a value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. SeeSET Syntax for Variable Assignment.

To verify plugin installation, examine the Information SchemaPLUGINS table or use theSHOW PLUGINS statement (seeObtaining Server Plugin Information). For example:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME = 'authentication_kerberos';
+-------------------------+---------------+
| PLUGIN_NAME             | PLUGIN_STATUS |
+-------------------------+---------------+
| authentication_kerberos | ACTIVE        |
+-------------------------+---------------+

If a plugin fails to initialize, check the server error log for diagnostic messages.

To associate MySQL accounts with the Kerberos plugin, seeUsing Kerberos Pluggable Authentication.

Using Kerberos Pluggable Authentication

This section describes how to enable MySQL accounts to connect to the MySQL server using Kerberos pluggable authentication. It is assumed that the server is running with the server-side plugin enabled, as described inInstalling Kerberos Pluggable Authentication, and that the client-side plugin is available on the client host.

Verify Kerberos Availability

The following example shows how to test availability of Kerberos in Active Directory. The example makes these assumptions:

With those assumptions satisfied, follow this procedure:

  1. Verify that the Kerberos library is installed and configured correctly in the operating system. For example, to configure a MYSQL.LOCAL domain and realm for use during MySQL authentication, the/etc/krb5.conf Kerberos configuration file should contain something like this:
[realms]  
  MYSQL.LOCAL = {  
    kdc = krbauth.example.com  
    admin_server = krbauth.example.com  
    default_domain = MYSQL.LOCAL  
  }
  1. You may need to add an entry to/etc/hosts for the server host:
198.51.100.11 krbauth krbauth.example.com
  1. Check whether Kerberos authentication works correctly:
    1. Use kinit to authenticate to Kerberos:
    $> kinit karl@MYSQL.LOCAL  
Password for karl@MYSQL.LOCAL: (enter password here)

    The command authenticates for the Kerberos principal named karl@MYSQL.LOCAL. Enter the principal's password when the command prompts for it. The KDC returns a TGT that is cached on the client side for use by other Kerberos-aware applications.
    2. Use klist to check whether the TGT was obtained correctly. The output should be similar to this: 

    $> klist  
Ticket cache: FILE:/tmp/krb5cc_244306  
Default principal: karl@MYSQL.LOCAL  
Valid starting       Expires              Service principal  
03/23/2021 08🔞33  03/23/2021 18🔞33  krbtgt/MYSQL.LOCAL@MYSQL.LOCAL
Create a MySQL Account That Uses Kerberos Authentication

MySQL authentication using theauthentication_kerberos authentication plugin is based on a Kerberos user principal name (UPN). The instructions here assume that a MySQL user namedkarl authenticates to MySQL using Kerberos, that the Kerberos realm is namedMYSQL.LOCAL, and that the user principal name is karl@MYSQL.LOCAL. This UPN must be registered in several places:

Assume that the Kerberos user principal name has been registered by the appropriate service administrator, and that, as previously described inInstalling Kerberos Pluggable Authentication, the MySQL server has been started with appropriate configuration settings for the server-side Kerberos plugin. To create a MySQL account that corresponds to a Kerberos UPN of_`user`_@_`realmname`_, the MySQL DBA uses a statement like this:

CREATE USER user
  IDENTIFIED WITH authentication_kerberos
  BY 'realm_name';

The account named by user can include or omit the host name part. If the host name is omitted, it defaults to % as usual. The_realmname_ is stored as theauthentication_string value for the account in the mysql.user system table.

To create a MySQL account that corresponds to the UPNkarl@MYSQL.LOCAL, use this statement:

CREATE USER 'karl'
  IDENTIFIED WITH authentication_kerberos
  BY 'MYSQL.LOCAL';

If MySQL must construct the UPN for this account, for example, to obtain or validate tickets (TGTs or STs), it does so by combining the account name (ignoring any host name part) and the realm name. For example, the full account name resulting from the preceding CREATE USER statement is 'karl'@'%'. MySQL constructs the UPN from the user name part karl (ignoring the host name part) and the realm nameMYSQL.LOCAL to producekarl@MYSQL.LOCAL.

Note

Observe that when creating an account that authenticates using authentication_kerberos, theCREATE USER statement does not include the UPN realm as part of the user name. Instead, specify the realm (MYSQL.LOCAL in this case) as the authentication string in theBY clause. This differs from creating accounts that use theauthentication_ldap_sasl SASL LDAP authentication plugin with the GSSAPI/Kerberos authentication method. For such accounts, theCREATE USER statement does include the UPN realm as part of the user name. SeeCreate a MySQL Account That Uses GSSAPI/Kerberos for LDAP Authentication.

With the account set up, clients can use it to connect to the MySQL server. The procedure depends on whether the client host runs Linux or Windows, as indicated in the following discussion.

Use of authentication_kerberos is subject to the restriction that UPNs with the same user part but a different realm part are not supported. For example, you cannot create MySQL accounts that correspond to both these UPNs:

kate@MYSQL.LOCAL
kate@EXAMPLE.COM

Both UPNs have a user part of kate but differ in the realm part (MYSQL.LOCAL versus EXAMPLE.COM). This is disallowed.

Use the MySQL Account to Connect to the MySQL Server

After a MySQL account that authenticates using Kerberos has been set up, clients can use it to connect to the MySQL server as follows:

  1. Authenticate to Kerberos with the user principal name (UPN) and its password to obtain a ticket-granting ticket (TGT).
  2. Use the TGT to obtain a service ticket (ST) for MySQL.
  3. Authenticate to the MySQL server by presenting the MySQL ST.

The first step (authenticating to Kerberos) can be performed various ways:

Details of the client commands for connecting to the MySQL server differ for Linux and Windows, so each host type is discussed separately, but these command properties apply regardless of host type:

Connection Commands for Linux Clients

On Linux, the appropriate client command for connecting to the MySQL server varies depending on whether the command authenticates using a TGT from the Kerberos cache, or based on command options for the MySQL user name and the UPN password:

$> kinit karl@MYSQL.LOCAL  
Password for karl@MYSQL.LOCAL: (enter password here)

The resulting TGT for the UPN is cached and becomes available for use by other Kerberos-aware applications, such as programs that use the client-side Kerberos authentication plugin. In this case, invoke the client without specifying a user-name or password option: 

mysql  
  --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory

The client-side plugin finds the TGT in the cache, uses it to obtain a MySQL ST, and uses the ST to authenticate to the MySQL server.
As just described, when the TGT for the UPN is cached, user-name and password options are not needed in the client command. If the command includes them anyway, they are handled as follows: 

mysql  
  --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --user=karl  
In this case, authentication fails if the user name specified by the option does not match the user name part of the UPN in the TGT.  
mysql  
  --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --password  
In this case, the client-side plugin ignores the password. Because authentication is based on the TGT, it can succeed _even if the user-provided password is incorrect_. For this reason, the plugin produces a warning if a valid TGT is found that causes a password to be ignored.
mysql --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --user=karl  
  --password

The client-side Kerberos authentication plugin combines the user name (karl) and the realm specified in the user account (MYSQL.LOCAL) to construct the UPN (karl@MYSQL.LOCAL). The client-side plugin uses the UPN and password to obtain a TGT, uses the TGT to obtain a MySQL ST, and uses the ST to authenticate to the MySQL server.
Or, suppose that the Kerberos cache contains no TGT and the command specifies a password option but no user-name option: 

mysql --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --password

The client-side Kerberos authentication plugin uses the operating system login name as the MySQL user name. It combines that user name and the realm in the user' MySQL account to construct the UPN. The client-side plugin uses the UPN and the password to obtain a TGT, uses the TGT to obtain a MySQL ST, and uses the ST to authenticate to the MySQL server.

If you are uncertain whether a TGT exists, you can useklist to check.

Note

When the client-side Kerberos authentication plugin itself obtains the TGT, the client user may not want the TGT to be reused. As described inClient Configuration Parameters for Kerberos Authentication, the local /etc/krb5.conf file can be used to cause the client-side plugin to destroy the TGT when done with it.

Connection Commands for Windows Clients in SSPI Mode

On Windows, using the default client-side plugin option (SSPI), the appropriate client command for connecting to the MySQL server varies depending on whether the command authenticates based on command options for the MySQL user name and the UPN password, or instead uses a TGT from the Windows in-memory cache. For details about GSSAPI mode on Windows, see Commands for Windows Clients in GSSAPI Mode.

A command can explicitly specify options for the MySQL user name and the UPN password, or the command can omit those options:

mysql --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --user=karl  
  --password

The client-side Kerberos authentication plugin combines the user name (karl) and the realm specified in the user account (MYSQL.LOCAL) to construct the UPN (karl@MYSQL.LOCAL). The client-side plugin uses the UPN and password to obtain a TGT, uses the TGT to obtain a MySQL ST, and uses the ST to authenticate to the MySQL server.
Any information in the Windows in-memory cache is ignored; the user-name and password option values take precedence.

mysql  
  --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --password

The client-side Kerberos authentication plugin uses the logged-in user name as the MySQL user name and combines that user name and the realm in the user's MySQL account to construct the UPN. The client-side plugin uses the UPN and the password to obtain a TGT, uses the TGT to obtain a MySQL ST, and uses the ST to authenticate to the MySQL server.

mysql  
  --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory

The client-side plugin obtains the TGT from the Windows in-memory cache, uses the TGT to obtain a MySQL ST, and uses the ST to authenticate to the MySQL server.
This approach requires the client host to be part of the Windows Server Active Directory (AD) domain. If that is not the case, help the MySQL client discover the IP address for the AD domain by manually entering the AD server and realm as the DNS server and prefix:

  1. Start console.exe and selectNetwork and Sharing Center.
  2. From the sidebar of the Network and Sharing Center window, select Change adapter settings.
  3. In the Network Connections window, right-click the network or VPN connection to configure and select .
  4. From the Network tab, locate and click Internet Protocol Version 4 (TCP/IPv4), and then clickProperties.
  5. Click Advanced in the Internet Protocol Version 4 (TCP/IPv4) Properties dialog. The Advanced TCP/IP Settings dialog opens.
  6. From the DNS tab, add the Active Directory server and realm as a DNS server and prefix.
mysql  
  --default-auth=authentication_kerberos_client  
  --plugin-dir=path/to/plugin/directory  
  --user=karl

The client-side Kerberos authentication plugin compares the name specified by the user-name option against the logged-in user name. If the names are the same, the plugin uses the logged-in user TGT for authentication. If the names differ, authentication fails.

Connection Commands for Windows Clients in GSSAPI Mode

On Windows, the client user must specifyGSSAPI mode explicitly using theplugin_authentication_kerberos_client_mode plugin option to enable support through the MIT Kerberos library. The default mode is SSPI (seeCommands for Windows Clients in SSPI Mode).

It is possible to specify GSSAPI mode:

[mysql]  
plugin_authentication_kerberos_client_mode=GSSAPI

Or: 

[mysql]  
plugin-authentication-kerberos-client-mode=GSSAPI  
mysql [connection-options] --plugin_authentication_kerberos_client_mode=GSSAPI

Or: 

mysql [connection-options] --plugin-authentication-kerberos-client-mode=GSSAPI

The appropriate client command for connecting to the MySQL server varies depending on whether the command authenticates using a TGT from the MIT Kerberos cache, or based on command options for the MySQL user name and the UPN password. GSSAPI support through the MIT library on Windows is similar to GSSAPI on Linux (seeCommands for Linux Clients), with the following exceptions:

Client Configuration Parameters for Kerberos Authentication

This section applies only for client hosts running Linux, not client hosts running Windows.

Note

A client host running Windows with theauthentication_kerberos_client client-side Kerberos plugin set to GSSAPI mode does support client configuration parameters, in general, but the MIT Kerberos library on Windows does not support the destroy_tickets parameter described in this section.

If no valid ticket-granting ticket (TGT) exists at the time of MySQL client application invocation, the application itself may obtain and cache the TGT. If during the Kerberos authentication process the client application causes a TGT to be cached, any such TGT that was added can be destroyed after it is no longer needed, by setting the appropriate configuration parameter.

The authentication_kerberos_client client-side Kerberos plugin reads the local/etc/krb5.conf file. If this file is missing or inaccessible, an error occurs. Assuming that the file is accessible, it can include an optional[appdefaults] section to provide information used by the plugin. Place the information within the mysql part of the section. For example:

[appdefaults]
  mysql = {
    destroy_tickets = true
  }

The client-side plugin recognizes these parameters in themysql section:

On the client host, a client keytab file may be used to obtain a TGT and TS without supplying a password. For information about keytab files, seehttps://web.mit.edu/kerberos/krb5-latest/doc/basic/keytab_def.html.

Kerberos Authentication Debugging

The AUTHENTICATION_KERBEROS_CLIENT_LOG environment variable enables or disables debug output for Kerberos authentication.

Note

Despite CLIENT in the nameAUTHENTICATION_KERBEROS_CLIENT_LOG, the same environment variable applies to the server-side plugin as well as the client-side plugin.

On the server side, the permitted values are 0 (off) and 1 (on). Log messages are written to the server error log, subject to the server error-logging verbosity level. For example, if you are using priority-based log filtering, thelog_error_verbosity system variable controls verbosity, as described inPriority-Based Error Log Filtering (log_filter_internal).

On the client side, the permitted values are from 1 to 5 and are written to the standard error output. The following table shows the meaning of each log-level value.

Log Level Meaning
1 or not set No logging
2 Error messages
3 Error and warning messages
4 Error, warning, and information messages
5 Error, warning, information, and debug messages