mysql安装后配置数据库权限与角色管理

mysql 8.0+ 默认禁用 root 远程登录，需通过本地 root 执行 CREATE USER 和 GRANT 授权；推荐用角色（ROLE）统一管理权限，并注意 SHOW GRANTS 不显示角色继承权限。

MySQL 8.0+ 默认禁用 root 远程登录，必须显式授权

安装完 MySQL 8.0 或更高版本后，root@localhost 账号默认只允许本地连接，且密码策略严格。直接用 navicat、DBeaver 等工具远程连 root 会报错：access denied for user 'root'@'xxx.xxx.xxx.xxx'。这不是密码错了，是权限没开。

解决方法不是改配置文件（如 my.cnf）或降级认证插件，而是用已有的本地 root@localhost 登录后执行授权：

CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPass123!'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

注意点：

• 'admin'@'%' 中的 % 表示任意主机，生产环境建议限定 IP 段，比如 'admin'@'192.168.1.%'
• MySQL 8.0 默认使用 caching_sha2_password 插件，部分旧客户端不兼容，所以显式指定 mysql_native_password
• GRANT ... WITH GRANT OPTION 才能让该用户后续给其他人授予权限；没加这句，GRANT 会报错

用角色（ROLE）批量管理权限比反复 GRANT 更可靠

给几十个开发人员逐个 GRANT select, INSERT ON app_db.* TO 'dev1'@'...' 容易漏、难回收。MySQL 8.0 支持角色（ROLE），本质是「权限容器」，先建角色、赋权，再把角色分配给用户。

典型流程：

CREATE ROLE 'app_reader', 'app_writer'; GRANT SELECT ON app_db.* TO 'app_reader'; GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'app_writer'; GRANT 'app_reader' TO 'dev1'@'%', 'dev2'@'%'; GRANT 'app_writer' TO 'dev3'@'%'; SET default ROLE 'app_reader' TO 'dev1'@'%';

关键细节：

• 角色本身不带密码，也不可直接登录，只是权限集合
• 用户被授予角色后，需执行 SET DEFAULT ROLE 才能在下次登录时自动激活；否则登录后权限为空，得手动 SET ROLE 'app_reader'
• 回收权限只需 DROP ROLE 'app_reader' 或 REVOKE 'app_reader' FROM 'dev1'@'%'，不用遍历每个用户

SHOW GRANTS 不显示角色继承的权限，容易误判实际权限

执行 SHOW GRANTS FOR 'dev1'@'%' 只返回直接赋予该用户的权限（如 GRANT USAGE ON *.*），不会列出通过角色获得的权限。这导致 dba 查权限时以为用户没权限，实际却能操作表。

要查完整权限，必须用：

SHOW GRANTS FOR 'dev1'@'%' USING 'app_reader';

或者更彻底的方式（模拟用户视角）：

SELECT * FROM information_schema.role_table_grants  WHERE grantee = "'dev1'@'%'" OR role_name = 'app_reader';

常见误区：

• 认为 SHOW GRANTS 是权威权限视图 —— 实际它只展示「显式授权」，角色权限藏在元数据里
• 删除角色前没检查谁在用：SELECT * FROM mysql.role_edges WHERE to_role = 'app_reader';
• 用户有多个角色但没设默认角色，登录后权限为零，连 USE app_db 都失败

生产环境务必关闭匿名用户和 test 数据库

MySQL 安装后常残留 ''@'localhost'（空用户名）和 test 数据库，这是安全隐患。攻击者可能利用空用户绕过认证，或往 test 库写恶意数据。

清理命令（用 root@localhost 执行）：

DROP USER ''@'localhost'; DROP DATABASE IF EXISTS test; DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\_%'; FLUSH PRIVILEGES;

额外提醒：

• 不要依赖 skip-grant-tables 来重置密码——它会完全跳过权限系统，重启后若忘记关，等于裸奔
• 修改 root 密码必须用 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';，老版本的 SET PASSWORD 在 8.0+ 已废弃
• 如果应用连接池长期复用连接，改完权限后要重启应用，否则旧连接仍持有旧权限缓存

权限模型比想象中细：用户、角色、代理用户、动态权限（如 BACKUP_ADMIN）层层嵌套。最常出问题的不是不会授权，而是查权限时只看 SHOW GRANTS，忽略了角色链和默认角色设置。