使用 Azure Active Directory 进行 MySQL 的身份验证Use Azure Active Directory for authentication with MySQL

本文将分步介绍如何使用 Azure Database for MySQL 配置 Azure Active Directory 访问权限以及如何使用 Azure AD 令牌进行连接。This article will walk you through the steps how to configure Azure Active Directory access with Azure Database for MySQL, and how to connect using an Azure AD token.

重要

Azure Active Directory 身份验证仅适用于 MySQL 5.7 和更高版本。Azure Active Directory authentication is only available for MySQL 5.7 and newer.

设置 Azure AD 管理员用户Setting the Azure AD Admin user

只有 Azure AD 管理员用户才能为基于 Azure AD 的身份验证创建/启用用户。Only an Azure AD Admin user can create/enable users for Azure AD-based authentication. 若要创建 Azure AD 管理员用户,请按照以下步骤操作To create an Azure AD Admin user, please follow the following steps

  1. 在 Azure 门户中,选择要为 Azure AD 启用的 Azure Database for MySQL 实例。In the Azure portal, select the instance of Azure Database for MySQL that you want to enable for Azure AD.
  2. 在“设置”下,选择“Active Directory 管理员”:Under Settings, select Active Directory Admin:

设置 Azure AD 管理员

  1. 在客户租户中选择一个有效的 Azure AD 用户作为 Azure AD 管理员。Select a valid Azure AD user in the customer tenant to be Azure AD administrator.

重要

设置管理员时,将向具有完全管理员权限的 Azure Database for MySQL 服务器添加新用户。When setting the administrator, a new user is added to the Azure Database for MySQL server with full administrator permissions.

每个 MySQL 服务器只能创建一个 Azure AD 管理员,选择另一个管理员将覆盖为服务器配置的现有 Azure AD 管理员。Only one Azure AD admin can be created per MySQL server and selection of another one will overwrite the existing Azure AD admin configured for the server.

在将来的版本中,我们将支持指定 Azure AD 组,而不是让单个用户拥有多个管理员,但目前尚不支持此功能。In a future release we will support specifying an Azure AD group instead of an individual user to have multiple administrators, however this is currently not supported yet.

配置管理员后,现在就可以登录了:After configuring the administrator, you can now sign in:

使用 Azure AD 连接到 Azure Database for MySQLConnecting to Azure Database for MySQL using Azure AD

以下概要关系图概述了将 Azure AD 身份验证与Azure Database for MySQL 配合使用的工作流:The following high-level diagram summarizes the workflow of using Azure AD authentication with Azure Database for MySQL:

身份验证流

我们设计了 Azure AD 集成以便使用常见的 MySQL 工具(例如 mysql CLI),这些工具不能识别 Azure AD,并且仅支持在连接到 MySQL 时指定用户名和密码。We've designed the Azure AD integration to work with common MySQL tools like the mysql CLI, which are not Azure AD aware and only support specifying username and password when connecting to MySQL. 我们会将 Azure AD 令牌作为密码传递,如上图所示。We pass the Azure AD token as the password as shown in the picture above.

目前已测试以下客户端:We currently have tested the following clients:

  • MySQLWorkbenchMySQLWorkbench
  • Mysql CLIMysql CLI

我们还测试了最常用的应用程序驱动程序,你可以在本页面末尾查看详细信息。We have also tested most common application drivers, you can see details at the end of this page.

以下是用户/应用程序使用 Azure AD 进行身份验证所需的步骤:These are the steps that a user/application will need to do authenticate with Azure AD described below:

先决条件Prerequisites

请确保已安装 Azure CLIMake sure you have the Azure CLI installed.

步骤 1:使用 Azure AD 进行身份验证Step 1: Authenticate with Azure AD

首先使用 Azure CLI 工具进行 Azure AD 的身份验证。Start by authenticating with Azure AD using the Azure CLI tool.

az login

该命令将启动浏览器窗口,以显示 Azure AD 身份验证页。The command will launch a browser window to the Azure AD authentication page. 这一操作需要提供 Azure AD 的用户 ID 和密码。It requires you to give your Azure AD user ID and the password.

步骤 2:检索 Azure AD 访问令牌Step 2: Retrieve Azure AD access token

调用 Azure CLI 工具,获取步骤 1 中经过 Azure AD 身份验证的用户的访问令牌,以访问 Azure Database for MySQL。Invoke the Azure CLI tool to acquire an access token for the Azure AD authenticated user from step 1 to access Azure Database for MySQL.

示例:Example:

az account get-access-token --resource https://ossrdbms-aad.database.chinacloudapi.cn

上述资源值必须完全按所示方式指定。The above resource value must be specified exactly as shown. 可以使用以下命令来查看资源值:The resource value can be looked up using:

az cloud show

对于 Azure CLI 版本 2.0.71 和更高版本,可在以下更方便的版本中指定该命令:For Azure CLI version 2.0.71 and later, the command can be specified in the following more convenient version:

az account get-access-token --resource-type oss-rdbms

身份验证成功后,Azure AD 将返回访问令牌:After authentication is successful, Azure AD will return an access token:

{
  "accessToken": "TOKEN",
  "expiresOn": "...",
  "subscription": "...",
  "tenant": "...",
  "tokenType": "Bearer"
}

该令牌是一个 Base 64 字符串,该字符串对有关经过身份验证的用户的所有信息进行编码,并且针对的是 Azure Database for MySQL 服务。The token is a Base 64 string that encodes all the information about the authenticated user, and which is targeted to the Azure Database for MySQL service.

备注

访问令牌的有效期为 5 - 60 分钟。The access token validity is anywhere between 5 minutes to 60 minutes. 建议在即将启动 Azure Database for MySQL 登录之前获取访问令牌。We recommend you get the access token just before initiating the login to Azure Database for MySQL.

步骤 3:使用令牌作为 MySQL 登录的密码Step 3: Use token as password for logging in with MySQL

在连接时,需要将访问令牌用作 MySQL 用户密码。When connecting you need to use the access token as the MySQL user password. 使用 GUI 客户端(如 MySQLWorkbench)时,可以使用上面的方法来检索令牌。When using GUI clients such as MySQLWorkbench, you can use the method above to retrieve the token.

使用 CLI 时,可以使用下面的方法快速连接:When using the CLI, you can use this short-hand to connect:

示例 (Linux/macOS):Example (Linux/macOS):

mysql -h mydb.mysql.database.chinacloudapi.cn \ 
      --user user@tenant.partner.onmschina.cn.com@mydb \ 
  --enable-cleartext-plugin \ 
  --password=`az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken`

连接时的重要注意事项如下:Important considerations when connecting:

  • user@tenant.partner.onmschina.cn 是你尝试要以其身份连接的 Azure AD 用户或组的名称user@tenant.partner.onmschina.cn is the name of the Azure AD user or group you are trying to connect as
  • 在 Azure AD 用户/组名称的后面始终要追加服务器名称(例如 @mydbAlways append the server name after the Azure AD user/group name (e.g. @mydb)
  • 请确保使用 Azure AD 用户或组名称的确切拼写方式Make sure to use the exact way the Azure AD user or group name is spelled
  • Azure AD 的用户名和组的名称区分大小写Azure AD user and group names are case sensitive
  • 在作为组进行连接时,请只使用组名称(例如 GroupName@mydbWhen connecting as a group, use only the group name (e.g. GroupName@mydb)
  • 如果名称包含空格,请在每个空格前使用 \ 对该空格进行转义If the name contains spaces, use \ before each space to escape it

请注意“enable-cleartext-plugin”设置 - 需要对其他客户端使用类似的配置,以确保令牌在不进行哈希处理的情况下发送到服务器。Note the "enable-cleartext-plugin" setting – you need to use a similar configuration with other clients to make sure the token gets sent to the server without being hashed.

现在可以使用 Azure AD 身份验证向 MySQL 服务器进行身份验证了。You are now authenticated to your MySQL server using Azure AD authentication.

在 Azure Database for MySQL 中创建 Azure AD 用户Creating Azure AD users in Azure Database for MySQL

若要向 Azure Database for MySQL 数据库添加 Azure AD 用户,请在连接后执行以下步骤(请参阅下文中介绍连接方法的小节):To add an Azure AD user to your Azure Database for MySQL database, perform the following steps after connecting (see later section on how to connect):

  1. 首先确保 Azure AD 用户 @yourtenant.partner.onmschina.cn 是 Azure AD 租户中的有效用户。First ensure that the Azure AD user `@yourtenant.partner.onmschina.cn is a valid user in Azure AD tenant.
  2. 以 Azure AD 管理员用户身份登录到 Azure Database for MySQL 实例。Sign in to your Azure Database for MySQL instance as the Azure AD Admin user.
  3. 在 Azure Database for MySQL 中创建用户 <user>@yourtenant.partner.onmschina.cnCreate user <user>@yourtenant.partner.onmschina.cn in Azure Database for MySQL.

示例:Example:

CREATE AADUSER 'user1@yourtenant.partner.onmschina.cn';

如果用户名超过 32 个字符,建议改用别名,以供连接时使用:For user names that exceed 32 characters, it is recommended you use an alias instead, to be used when connecting:

示例:Example:

CREATE AADUSER 'userWithLongName@yourtenant.partner.onmschina.cn' as 'userDefinedShortName'; 

备注

通过 Azure AD 对用户进行身份验证时,不会授权用户访问 Azure Database for MySQL 数据库中的对象。Authenticating a user through Azure AD does not give the user any permissions to access objects within the Azure Database for MySQL database. 必须手动向用户授予所需的权限。You must grant the user the required permissions manually.

在 Azure Database for MySQL 中创建 Azure AD 组Creating Azure AD groups in Azure Database for MySQL

若要启用 Azure AD 组以访问数据库,请使用与用户相同的机制,只不过要指定组名称:To enable an Azure AD group for access to your database, use the same mechanism as for users, but instead specify the group name:

示例:Example:

CREATE AADUSER 'Prod_DB_Readonly';

登录时,组成员将使用其个人访问令牌,但使用指定为用户名的组名称进行签名。When logging in, members of the group will use their personal access tokens, but sign with the group name specified as the username.

令牌验证Token Validation

Azure Database for MySQL 中的 Azure AD 身份验证可确保 MySQL 服务器中存在用户,并通过验证令牌的内容来检查令牌的有效性。Azure AD authentication in Azure Database for MySQL ensures that the user exists in the MySQL server, and it checks the validity of the token by validating the contents of the token. 执行以下令牌验证步骤:The following token validation steps are performed:

  • 令牌是否由 Azure AD 签名,并且未被篡改Token is signed by Azure AD and has not been tampered with
  • 是否由 Azure AD 为与服务器关联的租户颁发令牌Token was issued by Azure AD for the tenant associated with the server
  • 令牌是否未过期Token has not expired
  • 令牌是否用于 Azure Database for MySQL 资源(而不是其他 Azure 资源)Token is for the Azure Database for MySQL resource (and not another Azure resource)

与应用程序驱动程序的兼容性Compatibility with application drivers

大部分驱动程序均受支持,但请确保使用以明文形式发送密码的设置,这样,无需进行修改就能发送令牌。Most drivers are supported, however make sure to use the settings for sending the password in clear-text, so the token gets sent without modification.

  • C/C++C/C++
    • libmysqlclient:支持libmysqlclient: Supported
    • mysql-connector-c++:支持mysql-connector-c++: Supported
  • JavaJava
    • 连接器/J (mysql-connector-java):支持,必须使用 useSSL 设置Connector/J (mysql-connector-java): Supported, must utilize useSSL setting
  • PythonPython
    • 连接器/Python:支持Connector/Python: Supported
  • RubyRuby
    • mysql2:支持mysql2: Supported
  • .NET.NET
    • mysql-connector-net:支持,需要为 mysql_clear_password 添加插件mysql-connector-net: Supported, need to add plugin for mysql_clear_password
    • mysql-net/MySqlConnector:支持mysql-net/MySqlConnector: Supported
  • Node.jsNode.js
    • mysqljs:不支持(不以明文形式发送无修补程序的令牌)mysqljs: Not supported (does not send token in cleartext without patch)
    • node-mysql2:支持node-mysql2: Supported
  • PerlPerl
    • DBD::mysql:支持DBD::mysql: Supported
    • Net::MySQL:不支持Net::MySQL: Not supported
  • GoGo
    • go-sql-driver:支持,将 ?tls=true&allowCleartextPasswords=true 添加到连接字符串go-sql-driver: Supported, add ?tls=true&allowCleartextPasswords=true to connection string

后续步骤Next steps