MariaDB 用户管理指南：如何创建用户、授予权限以及解决常见错误

引言

1.1 您想学习如何在 MariaDB 中创建用户吗？

您是否曾经想在 MariaDB 中创建新用户并配置适当的权限？
例如，您可能遇到以下情况：

• 您执行了 CREATE USER 但无法登录
• 您使用 GRANT 授予权限，但权限未生效
• 您不确定如何在生产环境中配置适当的权限
• 您想安全地删除不必要的用户

如果您面临这些问题，本文将帮助您全面理解在 MariaDB 中从创建用户到管理权限和删除用户的整个过程。

1.2 本文将教您什么

本文详细解释了 MariaDB 用户管理的以下方面：

• 如何在 MariaDB 中创建和删除用户
• 如何授予和管理权限（适当权限设置的最佳实践）
• 常见错误及其解决方法
• 开发和生产环境的最佳操作实践

所有主题都使用适合初学者的 SQL 示例进行解释，让您可以同时学习和实践。

1.3 目标读者

本文适用于：

• 初学者：那些第一次学习 MariaDB 用户管理的人
• 工程师：那些想在开发环境中配置适当权限的人
• 数据库管理员：那些旨在加强生产环境安全的人

即使您是 MariaDB 新手，本指南也提供可在实际操作中自信应用的实用信息。

在 MariaDB 中创建用户

2.1 基本用户创建命令

在 MariaDB 中，您使用 CREATE USER 命令创建新用户。
您可以使用以下语法指定用户名和密码：

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

例如，要在 localhost 上创建名为 yamada 的用户，密码为 password123：

CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';

执行此命令后，用户 yamada 将被创建，并且仅允许从localhost 登录（仅允许在服务器本身上登录）。

2.2 按用例的用户创建示例

在 MariaDB 中创建用户时，您可以使用 hostname 字段指定允许连接的来源。
根据您的用例适当配置设置非常重要。

使用 '%' 允许来自所有主机的连接，但
这会带来重大的安全风险，不推荐用于生产环境。
在生产环境中，最好指定 IP 地址或通过VPN 配置访问。

2.3 创建用户时的注意事项

创建用户时有几个重要注意事项。

(1) 检查现有用户

在创建用户之前，确认同名用户是否已存在非常重要。
您可以使用以下命令列出当前注册的用户：

SELECT user, host FROM mysql.user;

示例输出：

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
+---------+-----------+

如果已存在相同的 username@hostname 用户，执行 CREATE USER 将导致错误。

(2) 如果用户已存在该怎么办

在 MariaDB 中，您无法通过重新创建来覆盖现有用户。
如果同名用户已存在，请使用以下方法之一：

• 更改现有用户的密码

  ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';
  

• 删除不必要的用户并创建新用户

  DROP USER 'yamada'@'localhost';
  CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';
  

在 MariaDB 中管理权限

3.1 授予权限

基本语法

在 MariaDB 中，您可以使用 GRANT 命令授予用户权限。

GRANT privilege ON database_name.table_name TO 'username'@'hostname';

例如，向用户 yamada 授予 所有权限 在 test_db 上：

GRANT ALL PRIVILEGES ON test_db.* TO 'yamada'@'localhost';

此命令的含义：

• ALL PRIVILEGES : 授予所有权限
• test_db.* : 适用于 test_db 数据库中的所有表
• 'yamada'@'localhost' : 适用于从 localhost 登录的用户 yamada

3.2 常用权限

在 MariaDB 中，您可以授予以下权限：

如果您只想允许特定操作，建议逐个授予权限，而不是一次性授予全部权限。

3.3 按使用场景的权限设置

为每种情况分配合适的权限非常重要。

在生产环境中，避免轻易授予 ALL PRIVILEGES。

3.4 检查已授予的权限

要检查已授予的权限，请使用 SHOW GRANTS 命令。

SHOW GRANTS FOR 'username'@'hostname';

例如，检查用户 yamada 的权限：

SHOW GRANTS FOR 'yamada'@'localhost';

示例输出：

+-----------------------------------------------------------------------------------+
| Grants for yamada@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'yamada'@'localhost'                |
+-----------------------------------------------------------------------------------+

这显示了当前授予该用户的权限。

3.5 撤销权限（移除权限）

要从用户撤销特定权限，请使用 REVOKE 命令。

基本语法

REVOKE privilege ON database_name.table_name FROM 'username'@'hostname';

例如，从用户 yamada 撤销 UPDATE 权限：

REVOKE UPDATE ON test_db.* FROM 'yamada'@'localhost';

撤销所有权限

REVOKE ALL PRIVILEGES ON *.* FROM 'yamada'@'localhost';

3.6 当权限更改未生效时的处理方法

在 MariaDB 中，权限更改可能不会立即生效。如果出现这种情况，请运行以下命令：

FLUSH PRIVILEGES;

运行此命令会强制 MariaDB 重新加载权限表，使您的更改立即生效。

在 MariaDB 中删除用户

4.1 删除用户

要删除 MariaDB 用户，请使用 DROP USER 命令。

基本语法

DROP USER 'username'@'hostname';

例如，删除来自 localhost 的用户 yamada：

DROP USER 'yamada'@'localhost';

执行此命令后，用户 yamada 将从 MariaDB 系统中彻底移除。

4.2 删除用户前的检查事项

在删除用户之前，您应进行一些重要检查。请小心不要误删必要的用户。

(1) 检查目标用户是否存在

在 MariaDB 中，尝试删除不存在的用户会导致错误。运行以下命令以确认目标用户是否存在：

SELECT user, host FROM mysql.user;

示例输出：

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
| admin   | 192.168.1.100 |
+---------+-----------+

如果此列表中出现用户 yamada，则可以删除该用户。

(2) 检查用户是否当前已连接

在 MariaDB 中，如果您要删除的用户当前有活跃会话，则删除可能对该会话不会立即生效。使用以下命令检查用户是否活跃：

SELECT user, host FROM information_schema.processlist;

示例输出：

+---------+-----------+
| user    | host      |
+---------+-----------+
| yamada  | localhost |
| root    | localhost |
+---------+-----------+

在此示例中，用户 yamada 当前处于活动状态，因此您应在删除之前 终止会话。

(3) 强制终止会话

您可以使用 KILL 命令强制终止特定会话。

1. 首先，检查进程 ID（ID 列）：

   SELECT id, user, host FROM information_schema.processlist;
   

示例输出：

+----+---------+-----------+
| id | user    | host      |
+----+---------+-----------+
| 10 | yamada  | localhost |
| 11 | root    | localhost |
+----+---------+-----------+

2. 如果 yamada 的进程 ID 为 10，使用以下命令终止它：

   KILL 10;
   

4.3 删除后的清理

删除用户后，数据库中可能仍残留特权信息。在这种情况下，运行 FLUSH PRIVILEGES 以刷新特权表。

FLUSH PRIVILEGES;

执行此操作可确保已删除用户的信息从数据库系统中彻底移除。

4.4 删除用户时的重要注意事项

删除用户时请牢记以下要点：

1. 已删除的用户无法恢复 * 一旦执行 DROP USER，已删除的用户 无法恢复。 * 如果误删，则必须使用 CREATE USER 重新创建该用户。

2. 可能需要转移特权 * 如果被删除的用户负责 关键数据库操作，则必须事先将必要的特权转移给其他合适的用户。

3. 在生产环境中要小心 * 在生产环境中，突然删除可能导致系统停机或错误。 * 强烈建议 提前分析影响并创建备份。

常见错误与解决方案 (FAQ)

在 MariaDB 中管理用户时，您可能会遇到各种错误。本节解释 最常见的错误、其原因以及解决方法。

5.1 初学者 FAQ

Q1: MariaDB 与 MySQL 用户管理有什么区别？

MariaDB 和 MySQL 通常使用相同的语法，但在某些功能上存在差异。

MariaDB 支持 CREATE ROLE，实现基于角色的用户管理。

Q2: localhost 与 % 有何区别？

在 MariaDB 用户管理中，指定主机名至关重要。

• localhost：仅允许来自 本机 的连接
• %：允许来自 任何主机 的连接（存在安全风险）
• 192.168.1.100：仅允许来自 特定 IP 地址 的连接
• 192.168.1.%：仅允许来自 特定子网 (192.168.1.*) 的连接

5.2 故障排除

Q3: 如何修复 “ERROR 1396 (HY000): Operation CREATE USER failed”

原因： 指定的用户已存在，无法创建新用户。

解决方案： 检查用户是否存在，并在必要时删除或修改它。

1. 检查现有用户

   SELECT user, host FROM mysql.user WHERE user='yamada';
   

2. 如不需要则删除

   DROP USER 'yamada'@'localhost';
   

3. 改为更改密码

   ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';
   

Q4: 使用 GRANT 授予的特权未生效

原因： MariaDB 特权缓存未刷新。

解决方案： 执行 FLUSH PRIVILEGES 重新加载特权。

FLUSH PRIVILEGES;

Q5: 即使使用 % 仍无法远程连接

原因： MariaDB 配置文件可能限制了远程连接。

解决方案：

1. 编辑配置文件 /etc/mysql/my.cnf（或 /etc/mysql/mariadb.conf.d/50-server.cnf），将 bind-address 的值改为 0.0.0.0。

   bind-address = 0.0.0.0
   

2. 重启 MariaDB

   sudo systemctl restart mariadb
   

3. 在 GRANT 命令中使用 % 以允许连接

   GRANT ALL PRIVILEGES ON test_db.* TO 'remote_user'@'%' IDENTIFIED BY 'password';
   

Q6：获取 “Access denied for user ‘user’@’host’” 错误

原因： 用户没有适当的权限，或密码不正确。

解决方案：

1. 检查当前权限

   SHOW GRANTS FOR 'user'@'host';
   

2. 授予适当的权限

   GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';
   

3. 确认使用了正确的密码登录

   mysql -u user -p
   

结论

MariaDB 用户管理对于提升数据库安全性和运营效率至关重要。
在本文中，我们详细介绍了 用户创建、权限管理、用户删除以及常见错误及其解决方案。

6.1 回顾

① 在 MariaDB 中创建用户

• 使用 CREATE USER 命令创建新用户
• 使用 @'hostname' 限制访问
• 在创建新用户之前始终 检查现有用户

② 在 MariaDB 中管理权限

• 使用 GRANT 分配 最小必要权限
• 使用 SHOW GRANTS 验证当前权限
• 使用 REVOKE 移除不必要的权限
• 使用 FLUSH PRIVILEGES 应用更改

③ 在 MariaDB 中删除用户

• 使用 DROP USER 命令删除用户
• 删除前检查 活跃会话
• 使用 FLUSH PRIVILEGES 清除权限缓存

④ 常见错误及解决方案

• ERROR 1396 (HY000) → 检查是否存在该用户并在必要时删除
• 权限更改未生效 → 执行 FLUSH PRIVILEGES
• Access denied for user → 验证权限和密码
• 无法使用 % 进行远程连接 → 调整 bind-address 配置

6.2 MariaDB 用户管理的最佳实践

• 仅授予最小必要权限
• 避免随意授予 ALL PRIVILEGES
• 在可能的情况下，将生产用户限制为 SELECT、INSERT、UPDATE、DELETE
• 始终验证权限更改
• 使用 SHOW GRANTS 确认配置正确
• 在删除用户前分析影响
• 在生产环境中保持谨慎并创建备份
• 实施强安全实践
• 对远程连接应用 IP 限制
• 为 root 用户设置强密码
• 定期删除不必要的用户和权限

6.3 最后思考

通过本指南，您已经了解了 从基础到高级的 MariaDB 用户管理技术。
利用这些知识实现 安全高效的数据库管理。

我们将继续分享有价值的 MariaDB 相关见解，敬请关注！ 🚀