MySQL 用户权限：实用指南-mysql教程-PHP中文网

MySQL 用户权限：实用指南

碧海醫心

发布： 2024-09-20 15:39:00

转载

1287人浏览过

mysql 用户权限：实用指南

在数据库管理领域，控制用户访问对于维护数据完整性和安全性至关重要。这篇博文将引导您完成设置 mysql 用户权限的真实场景，包括过程、潜在陷阱和调试步骤。

场景

假设您是一家拥有多个数据库的公司的数据库管理员：

原始数据库：产品、客户、订单、分析
复制数据库：products_copy、customers_copy、orders_copy、analytics_copy

您的任务是为名为“analyst”的用户设置权限，并满足以下要求：

分析师应该能够查看原始数据库中的数据库和表。
分析师不应该能够编辑原始数据库。
分析师不应该能够创建新的数据库。
分析师应对“_copy”数据库拥有完全访问权限（查看、编辑、删除、创建表）。

让我们深入了解如何使用 mysql 的 grant 和 revoke 语句来实现这一点。

第 1 步：初始设置

首先，我们需要使用管理帐户连接到mysql服务器：

mysql -h hostname -p port -u admin -p

登录后复制

将“主机名”、“端口”和“管理员”替换为您的实际服务器详细信息和管理员用户名。

第 2 步：创建用户

如果用户尚不存在，我们需要创建它：

create user 'analyst'@'%' identified by 'password';

登录后复制

将“密码”替换为安全强度高的密码。

第 3 步：授予必要的权限

现在，让我们授予所需的权限：

-- grant select on original databases
grant select on products.* to 'analyst'@'%';
grant select on customers.* to 'analyst'@'%';
grant select on orders.* to 'analyst'@'%';
grant select on analytics.* to 'analyst'@'%';

-- grant all privileges on copy databases
grant all privileges on products_copy.* to 'analyst'@'%';
grant all privileges on customers_copy.* to 'analyst'@'%';
grant all privileges on orders_copy.* to 'analyst'@'%';
grant all privileges on analytics_copy.* to 'analyst'@'%';

-- grant global privileges
grant process, show databases on *.* to 'analyst'@'%';

-- apply the changes
flush privileges;

登录后复制

第 4 步：验证权限

设置权限后，验证它们至关重要：

show grants for 'analyst'@'%';

登录后复制

调试和故障排除

问题一：权限过多

在我们的场景中，我们最初遇到了“分析师”拥有太多权限的问题：

mysql> show grants for 'analyst'@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grants for analyst@%                                                                                                                                                                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 "analyst"@"%" with grant option |
| grant replication_applier,role_admin on *.* to "analyst"@"%" with grant option                                                                                                                                                                                                                                                    |
...
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

登录后复制

解决方案：

为了解决这个问题，我们撤销了所有权限，然后仅授予必要的权限：

revoke all privileges, grant option from 'analyst'@'%';
grant process, show databases on *.* to 'analyst'@'%';
-- then re-grant the specific permissions as shown in step 3

登录后复制

问题 2：缺少复制数据库的权限

修复过多的权限后，我们注意到复制数据库的权限丢失了：

mysql> show grants for 'analyst'@'%';
+-----------------------------------------------------+
| grants for analyst@%                                |
+-----------------------------------------------------+
| grant process, show databases on *.* to "analyst"@"%"|
| grant select on "products".* to "analyst"@"%"       |
| grant select on "customers".* to "analyst"@"%"      |
| grant select on "orders".* to "analyst"@"%"         |
| grant select on "analytics".* to "analyst"@"%"      |
+-----------------------------------------------------+

登录后复制

解决方案：

我们添加了复制数据库缺少的授权：

grant all privileges on products_copy.* to 'analyst'@'%';
grant all privileges on customers_copy.* to 'analyst'@'%';
grant all privileges on orders_copy.* to 'analyst'@'%';
grant all privileges on analytics_copy.* to 'analyst'@'%';
flush privileges;

登录后复制

最终结果

应用所有这些更改和修复后，最终的拨款应如下所示：

mysql> SHOW GRANTS FOR 'analyst'@'%';
+-----------------------------------------------------+
| Grants for analyst@%                                |
+-----------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
+-----------------------------------------------------+

登录后复制