在 Azure Database for MySQL 服务器中创建用户Create users in Azure Database for MySQL server

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本文介绍如何在 Azure Database for MySQL 服务器中创建用户。This article describes how you can create users in an Azure Database for MySQL server.

首次创建 Azure Database for MySQL 时,需要提供服务器管理员登录用户名和密码。When you first created your Azure Database for MySQL, you provided a server admin login user name and password. 有关详细信息,可以参考快速入门For more information, you can follow the Quickstart. 你可以从 Azure 门户中找到你的服务器管理员登录用户名。You can locate your server admin login user name from the Azure portal.

服务器管理员用户可获得服务器的某些权限,如下所示:选择、插入、更新、删除、创建、放置、重载、处理、引用、索引、更改、显示数据库、创建临时表、锁定表、执行、复制从属、复制客户端、创建视图、显示视图、创建例程、更改例程、创建用户、事件、触发器The server admin user gets certain privileges for your server as listed: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER

创建 Azure Database for MySQL 服务器后,你可以使用第一个服务器管理员用户帐户来创建其他用户,并授予这些用户管理员访问权限。Once the Azure Database for MySQL server is created, you can use the first server admin user account to create additional users and grant admin access to them. 此外,服务器管理员帐户还可以用于创建只能访问各个数据库架构的权限较低的用户。Also, the server admin account can be used to create less privileged users that have access to individual database schemas.

备注

不支持 SUPER 权限和 DBA 角色。The SUPER privilege and DBA role are not supported. 请在“限制”一文中查看权限,以了解服务中不支持的权限。Review the privileges in the limitations article to understand what's not supported in the service.

如何在 Azure Database for MySQL 中创建其他管理员用户How to create additional admin users in Azure Database for MySQL

  1. 获取连接信息和管理员用户名。Get the connection information and admin user name. 若要连接到数据库服务器,需提供完整的服务器名称和管理员登录凭据。To connect to your database server, you need the full server name and admin sign-in credentials. 你可以在 Azure 门户的服务器“概述”页或“属性”页中轻松找到服务器名称和登录信息。 You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.

  2. 使用管理员帐户和密码连接到你的数据库服务器。Use the admin account and password to connect to your database server. 使用你的首选客户端工具,如 MySQL Workbench、mysql.exe、HeidiSQL 或其他工具。Use your preferred client tool, such as MySQL Workbench, mysql.exe, HeidiSQL, or others. 如果你不确定如何连接,请参阅使用 MySQL Workbench 连接和查询数据If you are unsure of how to connect, see Use MySQL Workbench to connect and query data

  3. 编辑并运行下面的 SQL 代码。Edit and run the following SQL code. 将占位符值 new_master_user 替换为你的新用户名。Replace your new user name for the placeholder value new_master_user. 此语法会将所有数据库架构 ( . ) 上列出的权限授予该用户名(本示例中的 new_master_user)。This syntax grants the listed privileges on all the database schemas (.) to the user name (new_master_user in this example).

    CREATE USER 'new_master_user'@'%' IDENTIFIED BY 'StrongPassword!';
    
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'new_master_user'@'%' WITH GRANT OPTION;
    
    FLUSH PRIVILEGES;
    
  4. 验证授予Verify the grants

    USE sys;
    
    SHOW GRANTS FOR 'new_master_user'@'%';
    

如何在 Azure Database for MySQL 中创建数据库用户How to create database users in Azure Database for MySQL

  1. 获取连接信息和管理员用户名。Get the connection information and admin user name. 若要连接到数据库服务器,需提供完整的服务器名称和管理员登录凭据。To connect to your database server, you need the full server name and admin sign-in credentials. 你可以在 Azure 门户的服务器“概述”页或“属性”页中轻松找到服务器名称和登录信息。 You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.

  2. 使用管理员帐户和密码连接到你的数据库服务器。Use the admin account and password to connect to your database server. 使用你的首选客户端工具,如 MySQL Workbench、mysql.exe、HeidiSQL 或其他工具。Use your preferred client tool, such as MySQL Workbench, mysql.exe, HeidiSQL, or others. 如果你不确定如何连接,请参阅使用 MySQL Workbench 连接和查询数据If you are unsure of how to connect, see Use MySQL Workbench to connect and query data

  3. 编辑并运行下面的 SQL 代码。Edit and run the following SQL code. 将占位符值 db_user 替换为预期的新用户名,并将占位符值 testdb 替换为你自己的数据库名称。Replace the placeholder value db_user with your intended new user name, and placeholder value testdb with your own database name.

    出于举例的目的,此 sql 代码语法将创建一个名为 testdb 的新数据库。This sql code syntax creates a new database named testdb for example purposes. 然后,它在 MySQL 服务中创建新用户,并将所有权限授予该用户的新数据库架构 (testdb.*)。Then it creates a new user in the MySQL service, and grants all privileges to the new database schema (testdb.*) for that user.

    CREATE DATABASE testdb;
    
    CREATE USER 'db_user'@'%' IDENTIFIED BY 'StrongPassword!';
    
    GRANT ALL PRIVILEGES ON testdb . * TO 'db_user'@'%';
    
    FLUSH PRIVILEGES;
    
  4. 验证数据库中的授予。Verify the grants within the database.

    USE testdb;
    
    SHOW GRANTS FOR 'db_user'@'%';
    
  5. 使用新用户名和密码登录到服务器,指定选定的数据库。Log in to the server, specifying the designated database, using the new user name and password. 此示例显示了 mysql 命令行。This example shows the mysql command line. 使用此命令,会提示你输入用户名的密码。With this command, you are prompted for the password for the user name. 替换你自己的服务器名称、数据库名称和用户名。Replace your own server name, database name, and user name.

    mysql --host mydemoserver.mysql.database.chinacloudapi.cn --database testdb --user db_user@mydemoserver -p
    

后续步骤Next steps

针对新用户计算机的 IP 地址打开防火墙,使其能够连接:使用 Azure 门户Azure CLI 创建和管理 Azure Database for MySQL 防火墙规则Open the firewall for the IP addresses of the new users' machines to enable them to connect: Create and manage Azure Database for MySQL firewall rules by using the Azure portal or Azure CLI.

有关用户帐户管理的详细信息,请参阅 MySQL 产品文档,了解用户帐户管理GRANT 语法权限For more information regarding user account management, see MySQL product documentation for User account management, GRANT Syntax, and Privileges.