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

MySQL是一个开源的关系数据库管理系统。它通常作为LAMP栈的一部分部署，并且在撰写本文时，它是世界上最流行的开源数据库。

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

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

如果您无权访问MySQL数据库并且想自己设置一个，您可以按照我们关于如何安装 MySQL的教程进行操作。

同样，无论您的服务器的底层操作系统如何，创建新MySQL用户并授予他们权限的方法通常都是相同的。

创建新用户

安装MySQL后会创建一个root用户帐户，您可以使用它来管理您的数据库。此用户对MySQL服务器拥有完全权限。

这意味着它可以完全控制每个数据库、表、用户等。因此，最好避免在管理功能之外使用此帐户。此步骤概述了如何使用root用户创建新用户帐户并授予其权限。

在运行MySQL 5.7 或者和更高版本的Ubuntu系统中，MySQL root用户默认设置为使用auth_socket插件进行身份验证，而不是使用密码。

该插件要求调用MySQL客户端的操作系统用户名与命令中指定的MySQL用户名相匹配。

这意味着您在调用mysql命令之前必须先切换到系统root用户，以便获得对MySQL root用户的访问权限。

sudo mysql

如果您的MySQL root用户配置为使用密码进行身份验证，您将需要使用不同的命令来访问MySQL shell。

以下将以普通用户权限运行MySQL客户端，并且您只能通过使用正确的密码进行身份验证来获得数据库的管理员权限。

mysql -u root -p

进入MySQL提示符，您可以使用CREATE USER语句创建新用户。这将遵循以下语法。

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

在CREATE USER之后，您需要指定一个用户名。紧随其后的是一个@符号，然后是用户本地计算机的主机名host。

如果你只打算从你的Ubuntu服务器本地访问，你可以指定localhost。并不总是需要将用户名和主机都用单引号括起来，但这样做有助于防止错误。

如需要允许用户的远程访问，并且允许任意远程主机的连接，可以将localhost改为百分号%，处于安全考虑，通常不建议将root用户从任意主机连接。

在选择用户的身份验证插件时，您有多种选择。前面提到的auth_socket插件很方便，因为它提供了强大的安全性，无需用户输入有效密码即可访问数据库。

但它也阻止了远程连接，当外部程序需要与MySQL交互时，这会使事情变得复杂。

作为替代方案，您可以完全省略部分语法，让用户使用MySQL的默认插件进行身份验证。

运行SQL语句命令来创建一个使用caching_sha2_password。请务必更改myfreax为您的用户名和password为您的强密码。

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

注意：某些 PHP 版本存在一个已知问题，如果您计划将此数据库与PHP应用程序一起使用，例如phpMyAdmin，你的PHP应用将无法访问MySQL。

您可能希望创建一个用户，该用户将使用旧的mysql_native_password插件进行身份验证。创建新用户后，您可以授予他们适当的权限。

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

授予用户权限

这是授予用户权限的语法GRANT PRIVILEGE ON database.table TO 'username'@'host';。

此示例语法中的PRIVILEGE值定义了允许用户对指定的数据库表和执行的操作。您可以在一个SQL语句中向同一用户授予多个权限，每个命令之间用逗号分隔。

您还可以通过输入星号 * 代替数据库和表名称来全局授予用户权限。在SQL中，星号是用于表示所有数据库或表的特殊字符。

为了说明，以下SQL语句授予用户CREATE、ALTER和DROP数据库、表和用户的全局权限，以及对服务器上任何表中的INSERT、UPDATE和DELETE数据的权限。

它还授予用户使用查询数据SELECT、使用关键字创建外键以及使用特权执行FLUSH操作的能力。

但是，您应该只授予用户他们需要的权限，因此可以根据需要随意调整您自己用户的权限。您可以在MySQL 官方文档中找到可用权限的完整列表。

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

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

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

某些用户可能希望授予他们的MySQL用户所有权限，这将为他们提供类似于root用户超级广泛的权限。

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

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

许多教程建议在创建用户授权语句之后立即运行SQL语句FLUSH PRIVILEGES，以重新加载授权表以确保新权限生效。

但是，根据官方MySQL文档，当您使用类似的帐户管理语句间接修改授权表时，数据库将立即将授权表重新加载到内存中。

这意味着在我们的示例中不需要运行FLUSH PRIVILEGESSQL语句。即使运行它，也不会对系统产生任何负面影响0。

如果您需要撤销权限，其SQL语法结构几乎与授予权限相同。在撤销权限时，SQL语法要求您使用FROM，而不是您在授予权限时使用的TO。

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

您可以通过运行SHOW GRANTSSQL语句查看用户的当前权限。可以像使用删除数据库一样DROP删除用户。

SHOW GRANTS FOR 'username'@'host';
DROP USER 'username'@'localhost';

创建MySQL用户并授予他们权限后，您可以退出MySQL客户端运行exit语句。

将来，要以新的MySQL用户登录，可以运行以下命令。-p选项将使MySQL客户端提示您输入MySQL用户的密码以进行身份​​验证。

mysql -u myfreax -p

结论

通过学习本教程，您已经了解了如何在MySQL数据库中添加新用户并授予他们各种权限。

从这里开始，您可以继续为您的MySQL用户探索和试验不同的权限设置，或者您可能想了解更多关于一些更高级别的MySQL配置的信息。