4，MySQL的权限管理

一、查看权限信息

1. SHOW PRIVILEGES

• 作用​：列出MySQL支持的所有权限类型及其说明。
• ​输出字段​：​Privilege​：权限名称（如SELECT、CREATE）。​Context​：权限适用的资源层级（如Global、Database、Table）。​Comment​：权限的用途说明。
• ​示例​：

SHOW PRIVILEGES;

输出包含如GRANT OPTION（允许转授权限）、USAGE（仅连接权限）等系统级权限

。

2. SHOW GRANTS

• ​作用​：查看指定用户的权限详情。
• ​语法​：

SHOW GRANTS FOR ‘用户名’@’主机名’;

• ​示例​：

SHOW GRANTS FOR ‘root’@’localhost’; — 查看root用户权限

SHOW GRANTS FOR CURRENT_USER(); — 查看当前用户权限

输出格式为GRANT语句，包含权限范围（如*.*表明所有库表）和WITH GRANT OPTION标记

。

二、授予权限

1. 基本语法

GRANT 权限列表 ON 资源范围 TO ‘用户名’@’主机名’ [IDENTIFIED BY ‘密码’] [WITH GRANT OPTION];

• ​权限列表​：如SELECT, INSERT或ALL PRIVILEGES。
• ​资源范围​：*.*：所有库表。db_name.*：指定库所有表。db_name.table_name：指定表。table_name(column1, column2)：指定表的列权限。
• ​示例​：

GRANT SELECT ON *.* TO ‘test02’@’%’ IDENTIFIED BY ‘123456’; — 授权查询所有库表

GRANT INSERT, UPDATE ON db1.* TO ‘user1’@’localhost’; — 授权插入和更新db1库

2. 关键权限说明

• ​SELECT/INSERT/UPDATE/DELETE​：表级数据操作权限。
• ​ALTER​：修改表结构（需配合CREATE权限）。
• ​CREATE/DROP​：创建或删除库/表。
• ​GRANT OPTION​：允许用户转授已获权限（需谨慎使用）

。

三、回收权限

1. 基本语法

REVOKE 权限列表 ON 资源范围 FROM ‘用户名’@’主机名’;

FLUSH PRIVILEGES; — 刷新权限使变更生效

• ​示例​：

REVOKE SELECT ON *.* FROM ‘test02’@’%’; — 回收查询权限

2. 注意事项

• 回收权限后需执行FLUSH PRIVILEGES，否则变更可能不生效。
• 若权限通过角色继承，需先回收角色权限

。

四、新旧版本授权差异（MySQL 8.0+）

1. ​用户创建与授权分离​：

• ​旧版​：GRANT语句隐式创建用户（若不存在）。​8.0+​​：需显式创建用户后再授权：

CREATE USER ‘oldboy’@’%’ IDENTIFIED BY ‘123456’;

GRANT ALL PRIVILEGES ON *.* TO ‘oldboy’@’%’;

1. ​角色功能​：8.0+支持角色（Role）批量管理权限，简化复杂权限分配

。

五、特殊权限说明

六、权限存储表

MySQL权限信息存储在mysql库的系统表中：

• ​user表​：全局权限（如SELECT、SHUTDOWN）。
• ​db表​：数据库级权限。
• ​tables_priv表​：表级权限。
• ​columns_priv表​：列级权限。
• ​procs_priv表​：存储过程权限。

​注意​：直接操作这些表风险较高，提议通过GRANT/REVOKE管理权限

。

七、角色功能（MySQL 8.0+）

1. 创建角色

CREATE ROLE ‘role_readonly’, ‘role_developer’;

2. 授权角色

GRANT SELECT ON db1.* TO ‘role_readonly’; — 授权查询db1库

GRANT ALL PRIVILEGES ON db2.* TO ‘role_developer’; — 授权db2库所有操作

3. 绑定用户

GRANT ‘role_readonly’ TO ‘user1’@’localhost’; — 用户继承角色权限

4. 激活角色

• ​手动激活​：

SET DEFAULT ROLE ‘role_readonly’ TO ‘user1’@’localhost’;

• ​自动激活​：

SET GLOBAL
activate_all_roles_on_login = ON; — 登录自动激活角色

八、最佳实践

1. ​最小权限原则​：按需分配权限（如开发人员仅需SELECT和INSERT）。
2. ​定期审计​：通过SHOW GRANTS检查权限变更。
3. ​避免root远程登录​：限制root仅本地访问。
4. ​使用角色管理复杂权限​：简化批量授权

。