MySQL用户管理与权限控制详解-EW帮帮网

一、用户表（user）核心结构

MySQL用户账户信息存储在系统数据库mysql的user表中，核心字段解析：

字段名称	说明	示例值
`user`	用户名（唯一标识）	`admin`
`host`	允许登录的主机地址（支持通配符）	`localhost` / `%.example.com` / `192.168.1.%`
`authentication_string`	加密后的登录密码（MySQL 5.7+使用`caching_sha2_password`默认加密）	`*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9`
`ssl_cipher`	SSL连接时使用的加密算法	`AES256-SHA`
`x509_issuer`	X.509证书颁发者（用于SSL验证）	`CN=MySQL Server`
`plugin`	身份验证插件（默认`caching_sha2_password`，旧版本为`mysql_native_password`）	`caching_sha2_password`

二、用户账户管理

1. 创建用户

-- 标准语法（推荐）
CREATE USER '用户名'@'登录主机' 
IDENTIFIED BY '密码' [EXPIRE NEVER | DEFAULT]  -- 密码有效期（默认永久有效）
[REQUIRE SSL | X509]  -- 强制使用安全连接
[WITH MAX_QUERIES_PER_HOUR 1000];  -- 资源限制（可选）

-- 示例：
-- 允许本地登录的用户
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'Dev@123456';
-- 允许任意主机登录（谨慎！）
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongPwd!123';
-- 创建时指定SSL连接
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'Pwd123' REQUIRE SSL;

2. 删除用户

-- 标准语法（MySQL 8.0+支持IF EXISTS避免报错）
DROP USER IF EXISTS '用户名'@'登录主机';

-- 示例：
DROP USER 'test_user'@'%';  -- 删除远程用户
DROP USER 'local_admin'@'localhost';  -- 删除本地用户

三、权限管理核心操作

1. 授予权限

-- 语法格式
GRANT <权限列表> ON <数据库.对象> TO '用户名'@'登录主机'
[IDENTIFIED BY '新密码']  -- 可选：授权时修改密码
[WITH GRANT OPTION];  -- 允许用户将权限转授给其他用户

-- 权限列表说明：
-- 单个权限：SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER等
-- 组合权限：ALL PRIVILEGES（除GRANT OPTION外的所有权限）
-- 特殊权限：GRANT OPTION（授权权限）

-- 对象范围：
-- *.*：所有数据库的所有对象
-- db_name.*：指定数据库的所有对象
-- db_name.table_name：指定数据库的特定表
-- COLUMN权限：GRANT SELECT (col1, col2) ON db.table TO ...

-- 示例：
-- 授予test库所有表的读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'app_user'@'%';
-- 授予所有数据库的管理权限（谨慎！）
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' WITH GRANT OPTION;
-- 授权时修改密码
GRANT SELECT ON mysql.* TO 'audit'@'192.168.1.100' IDENTIFIED BY 'NewPwd!456';

2. 回收权限

-- 语法格式
REVOKE <权限列表> ON <数据库.对象> FROM '用户名'@'登录主机';

-- 示例：
-- 回收test库的删除权限
REVOKE DELETE ON test.* FROM 'app_user'@'%';
-- 回收所有权限（保留用户账户）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'temp_user'@'localhost';

四、密码管理最佳实践

1. 修改密码的三种方式

-- 方式1：使用ALTER USER（推荐，安全合规）
ALTER USER '用户名'@'登录主机' 
IDENTIFIED BY '新密码'  -- 自动更新authentication_string
[EXPIRE AFTER 30 DAYS];  -- 设置密码有效期（如30天）

-- 方式2：直接操作user表（需刷新权限，不推荐）
UPDATE mysql.user SET authentication_string = SHA2('新密码', 512) 
WHERE user = 'lhd' AND host = 'localhost';
FLUSH PRIVILEGES;  -- 手动刷新权限缓存

-- 方式3：客户端命令行（临时登录时使用）
mysqladmin -u用户名 -p旧密码 password '新密码'

2. 密码加密机制

MySQL 5.7之前：使用PASSWORD()函数（SHA1加密，安全性较低）
MySQL 5.7+：默认使用caching_sha2_password插件（SHA2-512加密）

兼容性处理：

-- 允许旧版客户端连接（使用mysql_native_password）
ALTER USER 'legacy_user'@'%' IDENTIFIED WITH mysql_native_password BY '旧密码';

五、权限刷新与状态查看

1. 刷新权限缓存

FLUSH PRIVILEGES;  -- 强制重新加载权限表（修改user表后必需）

2. 查看当前用户与权限

-- 查看当前登录用户
SELECT CURRENT_USER(), USER(), SESSION_USER();  -- 三者可能不同（需注意主机匹配）

-- 查看指定用户的权限
SHOW GRANTS FOR '用户名'@'登录主机';  -- 显示授权语句
SHOW GRANTS;  -- 显示当前用户权限（等效于SHOW GRANTS FOR CURRENT_USER()）

-- 直接查询user表（需有SUPER权限）
SELECT user, host, plugin, ssl_cipher FROM mysql.user;

六、安全最佳实践

最小权限原则：
- 不为应用用户授予ALL PRIVILEGES，按需分配最小权限
- 禁止使用'root'@'%'账户远程登录

定期清理无效账户：

-- 删除90天未登录的用户（需配合审计日志）
DELETE FROM mysql.user WHERE last_active < NOW() - INTERVAL 90 DAY;

启用安全连接：

-- 要求客户端使用SSL连接
ALTER USER 'secure_user'@'%' REQUIRE SSL;
-- 查看服务器SSL状态
SHOW STATUS LIKE 'Ssl_%';

监控权限变更：
- 开启二进制日志记录权限操作
- 使用FLUSH PRIVILEGES后检查general_log

总结

MySQL用户管理通过user表和权限系统实现细粒度控制，核心操作包括用户创建、权限授予/回收、密码管理。实践中需遵循“最小权限”和“安全连接”原则，避免直接操作系统表，优先使用GRANT/ALTER USER等标准语句。定期审计用户列表和权限配置，是保障数据库安全的重要手段。

MySQL用户管理与权限控制详解