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

场景

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

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

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

1. 分析师应该能够查看原始数据库中的数据库和表。
2. 分析师不应该能够编辑原始数据库。
3. 分析师不应该能够创建新的数据库。
4. 分析师应对“_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"@"%"|
 ----------------------------------------------------- 

结论

设置适当的 MySQL 用户权限可能很棘手，但这是数据库管理的一个重要方面。通过仔细使用 GRANT 和 REVOKE 语句并始终验证结果，您可以为您的用户创建一个安全且功能齐全的环境。

记住这些要点：

1. 始终从最小特权原则开始。
2. 进行更改后使用 SHOW GRANTS 验证权限。
3. 小心全局权限（ON .）。
4. 进行更改后不要忘记刷新权限。

通过遵循本文中概述的这些准则和步骤，您将能够有效地管理 MySQL 用户权限。