MySQL的权限管理设置方法

MySQL 的权限管理是其安全体系的核心部分，通过精细控制用户对不同数据库对象的访问权限（如查询、修改、创建等），可有效防止未授权操作、数据泄露或恶意破坏。以下是 MySQL 权限管理的完整设置方法，涵盖用户创建、权限分配、权限查看、权限回收、密码管理及最佳实践。

一、权限管理核心概念

1. 权限层级

MySQL 权限按作用范围分为多个层级，从高到低依次为：

全局权限：作用于所有数据库（如 SELECT、INSERT 等），存储在 mysql.user 表。
数据库权限：作用于指定数据库（如 db1.*），存储在 mysql.db 表。
表权限：作用于指定表的特定操作（如 db1.table1），存储在 mysql.tables_priv 表。
列权限：作用于表中特定列的操作（如仅允许修改 table1.name 列），存储在 mysql.columns_priv 表。
存储过程/函数权限：作用于存储过程或函数（如 EXECUTE），存储在 mysql.procs_priv 表。

2. 权限类型

常见权限包括：

数据操作：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）。
结构操作：CREATE（创建库/表）、ALTER（修改表结构）、DROP（删除库/表）、INDEX（创建索引）。
管理权限：GRANT OPTION（允许转授权限）、SUPER（超级权限，如终止线程）、RELOAD（重载配置）、SHUTDOWN（关闭服务）。
特殊权限：PROCESS（查看所有进程）、FILE（读写服务器文件）、REPLICATION SLAVE（主从复制）等。

二、权限管理操作步骤

1. 登录 MySQL 并切换至权限数据库

使用管理员账户（如 root）登录 MySQL：

mysql -u root -p

切换到 mysql 系统数据库（存储用户和权限信息）：

USE mysql;

2. 创建用户

语法：

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

主机（Host）：限制用户登录的来源，% 表示任意主机（生产环境慎用），localhost 仅本地，192.168.1.% 表示特定网段。
示例：创建仅允许本地登录的用户 app_user，密码 App@123456： CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'App@123456';

3. 授予权限

语法：

GRANT 权限列表 ON 作用范围 TO '用户名'@'主机' [WITH GRANT OPTION];

权限列表：用逗号分隔（如 SELECT, INSERT, UPDATE），或用 *（所有权限，谨慎使用）。
作用范围：格式为 数据库.表（如 test_db.* 表示 test_db 库下所有表，*.* 表示所有库表）。
WITH GRANT OPTION：允许用户将自己的权限转授给其他用户（高风险，仅信任用户使用）。

示例：

授予 app_user 对 test_db 库所有表的查询、插入、更新权限： GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'app_user'@'localhost';
授予 admin_user 对所有库表的超级权限（仅用于管理）： GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' WITH GRANT OPTION;

4. 刷新权限

权限变更后需执行以下命令使生效（MySQL 8.0+ 部分操作自动刷新，但建议手动执行）：

FLUSH PRIVILEGES;

5. 查看用户权限

（1）查看指定用户的权限

SHOW GRANTS FOR '用户名'@'主机';

示例：查看 app_user@localhost 的权限：

SHOW GRANTS FOR 'app_user'@'localhost';
-- 输出示例：GRANT SELECT, INSERT, UPDATE ON `test_db`.* TO `app_user`@`localhost`

（2）查看所有用户及权限

查询 mysql.user 表（全局权限）：

SELECT user, host, authentication_string FROM mysql.user; -- MySQL 5.7+
-- 或（MySQL 8.0+）：SELECT user, host, authentication_string FROM mysql.user;

查询具体层级的权限（如数据库权限）：

SELECT * FROM mysql.db WHERE user='app_user'\G; -- \G 格式化输出

6. 回收权限

语法：

REVOKE 权限列表 ON 作用范围 FROM '用户名'@'主机';

示例：回收 app_user 对 test_db 库的 UPDATE 权限：

REVOKE UPDATE ON test_db.* FROM 'app_user'@'localhost';
FLUSH PRIVILEGES; -- 刷新权限

7. 修改用户密码

（1）管理员修改其他用户密码

ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';
-- 或（旧版本兼容）：SET PASSWORD FOR '用户名'@'主机' = PASSWORD('新密码');

示例：修改 app_user 的密码为 NewApp@789：

ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewApp@789';

（2）用户自行修改密码

ALTER USER USER() IDENTIFIED BY '新密码'; -- USER() 返回当前用户
-- 或：SET PASSWORD = PASSWORD('新密码');

8. 删除用户

语法：

DROP USER '用户名'@'主机';

示例：删除 app_user@localhost：

DROP USER 'app_user'@'localhost';
FLUSH PRIVILEGES;

三、MySQL 8.0+ 新特性与注意事项

1. 认证插件变化

MySQL 8.0 默认使用 caching_sha2_password 替代旧版的 mysql_native_password，部分客户端（如旧版 PHP）可能不兼容。若需兼容，可显式指定插件：

CREATE USER 'app_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'App@123456';

2. 权限粒度细化

支持对角色（Role）的管理，可将一组权限封装为角色，简化批量授权：

-- 创建角色
CREATE ROLE 'read_write_role';
-- 为角色授权
GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'read_write_role';
-- 将角色赋予用户
GRANT 'read_write_role' TO 'app_user'@'localhost';
-- 激活角色（默认需手动激活）
SET DEFAULT ROLE 'read_write_role' TO 'app_user'@'localhost';

四、权限管理最佳实践

最小权限原则：仅授予用户完成任务所需的最小权限（如应用用户仅需 SELECT, INSERT, UPDATE，避免 DROP 或 ALL）。
限制主机范围：避免使用 %（任意主机），根据业务需求限制为具体 IP 或网段（如 192.168.1.%）。
定期审计权限：通过 SHOW GRANTS 或查询 mysql 系统表，检查冗余或过期权限。
禁用危险权限：如 FILE（可读取服务器文件）、SUPER（可终止进程）等，仅必要时授予。
密码策略：启用密码复杂度（如长度≥8位、包含字母/数字/符号），定期更换密码。
避免共享账户：为每个应用或服务创建独立用户，便于追踪操作和权限回收。