本指南将详细介绍如何在 MySQL 数据库中创建新用户，并授予他们执行各种数据库操作所需的精确权限。良好的用户和权限管理是保障数据库安全和系统稳定运行的关键。

前提条件

在开始之前，请确保你满足以下条件：

• 访问 MySQL 数据库： 你需要一个已安装并运行 MySQL 的数据库环境。本指南以 Ubuntu 20.04 上的 MySQL 为例，但所描述的原则和 SQL 命令适用于任何 MySQL 安装。
• root 用户访问权限： 了解如何以 root 用户身份登录 MySQL shell。

创建新用户

在 MySQL 中，root 用户拥有最高权限，通常不建议在日常操作中使用。为了提升安全性并遵循最小权限原则，我们应为不同的应用或用户创建具有特定权限的新用户。

1. 登录 MySQL root 用户

根据你的系统配置，选择以下方式之一登录到 MySQL root 用户：

• 对于使用 auth_socket 插件的系统（Ubuntu 20.04 及更高版本默认）：

  sudo mysql
  

• 对于配置了密码的 root 用户：

  mysql -u root -p
  

  然后系统会提示你输入 root 密码。

2. 创建用户

使用 CREATE USER 语句来创建新用户。其基本语法如下：

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

• username：你为新用户选择的名称。
• host：指定用户可以从哪个主机连接到 MySQL。
  • localhost：仅允许从 MySQL 服务器本机连接。
  • %：允许从任何主机连接（生产环境应谨慎使用，或限制为特定 IP 地址）。
  • 192.168.1.100：仅允许从特定 IP 地址连接。
• authentication_plugin：指定用户的身份验证插件。
  • caching_sha2_password：MySQL 8.0 及更高版本的默认插件，提供更强的安全性，但可能与某些旧版客户端或 PHP 版本不兼容。
  • mysql_native_password：一个较旧但兼容性更好的插件，如果遇到兼容性问题，可以考虑使用。
• password：为用户设置的强密码。

示例：

• 使用 caching_sha2_password 创建用户 sammy，并只允许从 localhost 连接：

  CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'your_strong_password';
  

• 如果遇到兼容性问题，可以指定使用 mysql_native_password：

  CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';
  

• 更改现有用户的认证插件： 如果用户已存在且需要更改认证插件，可以使用 ALTER USER 语句。例如，将用户 sammy 的认证插件更改为 mysql_native_password：

  ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';
  

授予用户权限

创建用户后，你需要授予他们对特定数据库或表执行操作的权限。

1. 授予权限的通用语法

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

• PRIVILEGE：指定允许用户执行的操作，例如 SELECT (读取数据), INSERT (插入数据), UPDATE (更新数据), DELETE (删除数据), CREATE (创建表或数据库), ALTER (修改表结构), DROP (删除表或数据库), REFERENCES (外键引用), RELOAD (重新加载权限表) 等。你可以列出多个权限，用逗号 , 分隔。
• database.table：指定权限生效的数据库和表。
  • mydatabase.mytable：仅对 mydatabase 数据库中的 mytable 表授予权限。
  • mydatabase.*：对 mydatabase 数据库中的所有表授予权限。
  • *.*：对所有数据库中的所有表授予权限（应谨慎使用）。
• TO 'username'@'host'：指定要授予权限的用户。

2. 常用权限组合示例

• 授予对特定数据库 mydatabase 的所有操作权限：

  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON mydatabase.* TO 'sammy'@'localhost';
  

• 授予对所有数据库和所有表的全面操作权限（不包括 GRANT OPTION），通常用于数据库管理员或特殊用户：

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

• 授予用户将权限授予其他用户的能力（WITH GRANT OPTION）： WITH GRANT OPTION 允许该用户将其拥有的权限转授给其他用户。这增加了灵活性，但也带来了安全风险，应谨慎使用。

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

• 授予所有权限（极不推荐，除非你完全理解其安全风险）：

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

  这条语句将授予用户所有可能的权限，并允许其将这些权限授予其他用户。

3. 刷新权限

在某些情况下，你可能需要刷新 MySQL 的权限缓存，以确保新的权限设置立即生效。虽然 GRANT 语句通常会自动重新加载权限，但显式刷新也无害：

FLUSH PRIVILEGES;

4. 撤销权限

要撤销用户的权限，可以使用 REVOKE 命令。请注意，撤销权限时使用 FROM 而不是 TO。

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

示例： 撤销用户 sammy 对 mydatabase 数据库的 DELETE 权限：

REVOKE DELETE ON mydatabase.* FROM 'sammy'@'localhost';

5. 查看用户权限

要检查特定用户当前拥有的权限，可以使用 SHOW GRANTS 命令：

SHOW GRANTS FOR 'username'@'host';

示例：

SHOW GRANTS FOR 'sammy'@'localhost';

6. 退出 MySQL 客户端

完成权限管理后，你可以退出 MySQL shell：

exit

7. 以新用户身份登录

现在，你可以尝试以新创建的用户身份登录，并验证其权限：

mysql -u sammy -p

系统会提示你输入 sammy 用户的密码。

移除 MySQL 用户

如果一个用户不再需要访问数据库，出于安全考虑，应将其移除。

1. 移除用户

使用 DROP USER 命令来删除一个 MySQL 用户。

DROP USER 'username'@'host';

• username：要删除的用户名。
• host：用户连接的主机名或 IP 地址。

示例：

DROP USER 'sammy'@'localhost';

注意：

• 此操作不可逆，删除用户后，其所有权限也会随之删除。
• 你不能删除当前正在连接到 MySQL 服务器的用户。

常见错误与调试

在管理 MySQL 用户和权限时，可能会遇到一些常见问题。

1. “Access denied for user” 错误

• 原因： 用户名、密码或连接主机不正确，或者用户没有足够的权限来执行请求的操作。
• 解决方法：
  • 仔细检查你输入的用户名、密码和主机是否完全正确。
  • 以 root 用户身份登录，然后使用 SHOW GRANTS FOR 'username'@'host'; 命令检查用户的权限。如果权限不足，使用 GRANT 命令授予必要的权限。
  • 确保用户对你尝试访问的特定数据库或表拥有权限。

2. 用户无法远程连接

• 原因： 用户账户配置为只允许从特定主机（如 localhost）连接，或 MySQL 服务器的防火墙阻止了外部连接。
• 解决方法：
  • 确认在创建或修改用户时，host 参数是否设置为 %（允许任意主机连接）或具体的远程 IP 地址。例如：

    GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
    FLUSH PRIVILEGES;
    

  • 检查服务器的防火墙（如 ufw 或 firewalld），确保 MySQL 默认端口 3306 允许传入连接。
  • 检查 MySQL 配置文件（通常是 /etc/mysql/mysql.conf.d/mysqld.cnf 或 /etc/my.cnf），确保 bind-address 设置为 0.0.0.0 或服务器的公共 IP 地址，而不是 127.0.0.1。修改后需要重启 MySQL 服务。

3. “Error 1396: Operation CREATE USER failed” 错误

• 原因： 你尝试创建的用户已经存在。
• 解决方法：
  • 在尝试创建用户之前，可以使用以下 SQL 查询检查用户是否存在：

    SELECT User, Host FROM mysql.user WHERE User = 'newuser';
    

  • 如果用户已存在且你需要重新创建，可以先使用 DROP USER 'newuser'@'host'; 命令将其删除，然后再重新创建。

常见问题解答 (FAQs)

1. 如何创建具有有限权限的 MySQL 用户？

在 GRANT 语句中，明确列出用户所需的确切权限，而不是使用 ALL PRIVILEGES。例如，仅授予 SELECT、INSERT、UPDATE、DELETE 权限：

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

2. 如何检查 MySQL 用户权限？

使用 SHOW GRANTS FOR 'username'@'host'; 命令可以查看特定用户的所有权限。

3. GRANT ALL PRIVILEGES 和特定权限的区别是什么？

• GRANT ALL PRIVILEGES 授予所有可用权限，通常存在安全风险，因为它赋予了用户对数据库的完全控制权。
• 特定权限（如 SELECT、INSERT）则只授予用户执行特定操作的能力，遵循最小权限原则，大大降低了潜在的安全风险。

4. 如何允许 MySQL 用户远程访问？

在 CREATE USER 或 GRANT 语句中，将 host 参数设置为 '%'（允许任何 IP 连接）或具体的远程 IP 地址。例如：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

同时，确保 MySQL 服务器的防火墙和 bind-address 配置允许远程连接。

5. 如何安全删除 MySQL 用户？

最安全的方式是首先撤销该用户的所有权限，然后删除用户。

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';
DROP USER 'username'@'localhost';

尽管 DROP USER 会自动删除权限，但显式 REVOKE 是一种好的习惯。

结论

通过本指南，你已经掌握了在 MySQL 数据库中创建新用户、授予和撤销各种权限、以及处理常见用户管理问题的方法。安全且精细的权限管理是任何生产环境中数据库管理的重要组成部分。你可以继续探索和实验不同的权限设置，或深入了解更高阶的 MySQL 安全配置。

关于

关注我获取更多资讯

📢 公众号

💬 个人号