如何在MySQL中创建新用户并授予权限

介绍

MySQL 是一个开源的关系数据库管理系统。它通常作为 LAMP 堆栈 的一部分部署（代表 Linux、Apache、MySQL 和 [X130X ]PHP），在撰写本文时，它是世界上最受欢迎的开源数据库。

本指南概述了如何创建新的 MySQL 用户并授予他们执行各种操作所需的权限。

先决条件

为了遵循本指南，您需要访问 MySQL 数据库。本指南假定此数据库安装在运行 Ubuntu 20.04 的虚拟专用服务器上，但无论您如何访问数据库，它概述的原则都应该适用。

如果您无法访问 MySQL 数据库并且想自己设置一个，您可以按照我们关于如何安装 MySQL 的指南之一进行操作。同样，无论您的服务器的底层操作系统如何，创建新 MySQL 用户并授予他们权限的方法通常都是相同的。

您也可以启动由云提供商管理的 MySQL 数据库。有关如何启动 DigitalOcean 托管数据库的详细信息，请参阅我们的产品文档。

请注意，您需要更改或自定义的示例命令的任何部分都将在本指南中突出显示 like this。

创建新用户

安装后，MySQL 会创建一个 root 用户帐户，您可以使用它来管理您的数据库。此用户对 MySQL 服务器拥有完全权限，这意味着它可以完全控制每个数据库、表、用户等。因此，最好避免在管理功能之外使用此帐户。此步骤概述了如何使用 root MySQL 用户创建新用户帐户并授予其权限。

在运行 MySQL 5.7（及更高版本）的 Ubuntu 系统中，root MySQL 用户默认设置为使用 auth_socket 插件进行身份验证，而不是使用密码。该插件要求调用 MySQL 客户端的操作系统用户名与命令中指定的 MySQL 用户名相匹配。这意味着您需要在 mysql 命令之前加上 sudo 以使用 root Ubuntu 用户的权限调用它，以便获得对 root 的访问权限 MySQL 用户：

sudo mysql

注意： 如果您的 root MySQL 用户配置为使用密码进行身份验证，您将需要使用不同的命令来访问 MySQL shell。以下将以常规用户权限运行您的 MySQL 客户端，并且您只能通过使用正确的密码进行身份验证来获得数据库中的管理员权限：

mysql -u root -p

访问 MySQL 提示后，您可以使用 CREATE USER 语句创建新用户。这些遵循以下一般语法：

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

在 CREATE USER 之后，指定用户名。紧随其后的是 @ 符号，然后是该用户将连接的主机名。如果你只打算从你的 Ubuntu 服务器本地访问这个用户，你可以指定 localhost。并不总是需要将用户名和主机都用单引号括起来，但这样做有助于防止错误。

在选择用户的身份验证插件时，您有多种选择。前面提到的auth_socket插件可以很方便，因为它提供了强大的安全性，不需要有效用户输入密码来访问数据库。但它也阻止了远程连接，当外部程序需要与 MySQL 交互时，这会使事情变得复杂。

作为替代方案，您可以完全省略语法的 WITH authentication_plugin 部分，让用户使用 MySQL 的默认插件 caching_sha2_password 进行身份验证。 MySQL 文档推荐这个插件给想要使用密码登录的用户，因为它具有强大的安全功能。

运行以下命令以创建使用 caching_sha2_password 进行身份验证的用户。请务必将 sammy 更改为您的首选用户名，并将 password 更改为您选择的强密码：

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

注意：某些版本的 PHP 存在一个已知问题，导致 caching_sha2_password 出现问题。如果您计划将此数据库与 PHP 应用程序（例如 phpMyAdmin）一起使用，您可能希望创建一个用户，该用户将使用较旧但仍然安全的 mysql_native_password 插件进行身份验证：

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

如果您不确定，您始终可以创建一个使用 caching_sha2_plugin 进行身份验证的用户，然后稍后使用以下命令对其进行 ALTER 身份验证：

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

创建新用户后，您可以授予他们适当的权限。

授予用户权限

授予用户权限的一般语法如下：

GRANT PRIVILEGE ON database.table TO 'username'@'host';

此示例语法中的 PRIVILEGE 值定义了允许用户在指定的 database 和 table 上执行的操作。您可以在一个命令中向同一用户授予多个权限，每个命令之间用逗号分隔。您还可以通过输入星号 (*) 代替数据库和表名来全局授予用户权限。在 SQL 中，星号是用于表示“所有”数据库或表的特殊字符。

为了说明，以下命令授予用户对 CREATE、ALTER 和 DROP 数据库、表和用户的全局权限，以及对 [来自服务器上任何表的 X166X]、UPDATE 和 DELETE 数据。它还授予用户使用 SELECT 查询数据、使用 REFERENCES 关键字创建外键以及使用 RELOAD 权限执行 FLUSH 操作的能力。但是，您应该只授予用户他们需要的权限，因此可以根据需要随意调整您自己用户的权限。

您可以在官方 MySQL 文档中找到可用权限的完整列表。

运行此 GRANT 语句，将 sammy 替换为您自己的 MySQL 用户名，以将这些权限授予您的用户：

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

请注意，此语句还包括 WITH GRANT OPTION。这将允许您的 MySQL 用户将其拥有的任何权限授予系统上的其他用户。

Warning：一些用户可能希望授予他们的 MySQL 用户 ALL PRIVILEGES 权限，这将为他们提供类似于 root 用户权限的广泛超级用户权限，如下所示：

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

不应轻易授予如此广泛的特权 ' ，因为任何有权访问此 MySQL 用户的人都将完全控制服务器上的每个数据库。

许多指南建议在 CREATE USER 或 GRANT 语句之后立即运行 FLUSH PRIVILEGES 命令，以重新加载授权表以确保新权限生效：

FLUSH PRIVILEGES;

但是，根据官方MySQL文档，当您使用GRANT之类的帐户管理语句间接修改授权表时，数据库将立即将授权表重新加载到内存中，这意味着[ X246X] 命令在我们的例子中不是必需的。另一方面，运行它不会对系统产生任何负面影响。

如果您需要撤销权限，其结构几乎与授予权限相同：

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

请注意，撤销权限时，语法要求您使用 FROM，而不是授予权限时使用的 TO。

您可以通过运行 SHOW GRANTS 命令查看用户的当前权限：

SHOW GRANTS FOR 'username'@'host';

正如您可以使用 DROP 删除数据库一样，您可以使用 DROP 删除用户：

DROP USER 'username'@'localhost';

创建 MySQL 用户并授予他们权限后，您可以退出 MySQL 客户端：

exit

将来，要以新的 MySQL 用户身份登录，您将使用如下命令：

mysql -u sammy -p

-p 标志将导致 MySQL 客户端提示您输入 MySQL 用户的密码以进行身份验证。

结论

通过学习本教程，您已经了解了如何在 MySQL 数据库中添加新用户并授予他们各种权限。从这里开始，您可以继续为您的 MySQL 用户探索和试验不同的权限设置，或者您可能想了解更多有关一些更高级别的 MySQL 配置的信息。

有关 MySQL 基础知识的更多信息，您可以查看以下教程：