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

简介

MySQL 是一个开源的关系型数据库管理系统。它通常作为 LAMP stack 的一部分部署（LAMP 代表 Linux, Apache, MySQL, 和 PHP），截至目前，MySQL 是世界上最流行的开源数据库。

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

先决条件

为了按照本指南操作，您需要访问一个 MySQL 数据库。本指南假设该数据库安装在运行 Ubuntu 20.04 的虚拟专用服务器上，尽管它所概述的原则应该适用于无论您如何访问您的数据库。

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

请注意，您需要更改或自定义的示例命令部分将在本指南中以 这样 的方式突出显示。

创建新用户

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

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

sudo mysql

一旦您可以访问 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';

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

授予用户权限

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

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

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

举例来说，以下命令授予用户全局权限来 CREATE、ALTER 和 DROP 数据库、表和用户，以及在服务器上的任何表中执行 INSERT、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 用户向系统上的其他用户授予它拥有的任何权限。

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

FLUSH PRIVILEGES;

然而，根据官方 MySQL 文档，当您间接地使用账户管理语句（如 GRANT）修改授权表时，数据库将立即将授权表重新加载到内存中，这意味着在我们的情况下不需要 FLUSH PRIVILEGES 命令。另一方面，运行它也不会对系统产生任何负面影响。

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

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 基础知识的更多信息，您可以查看以下教程：

• 如何在云服务器上创建和管理 MySQL 和 MariaDB 数据库
• 如何在 MySQL 中设置复制
• 如何在 Ubuntu 20.04 上配置 MySQL 组复制