MySQL 权限管理详解（面试重点）
一、权限管理核心语法
1. GRANT 授予权限
GRANT 权限1, 权限2, ...
ON 权限级别
TO '用户名'@'主机' [IDENTIFIED BY '密码']
[WITH GRANT OPTION | 其他资源限制];
-
权限列表：
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES, EXECUTE, SHOW VIEW, GRANT OPTION, ... -
权限级别： 级别 语法示例 说明 全局权限 *.* 所有数据库的所有表 数据库级权限 数据库名.* 指定数据库的所有表 表级权限 数据库名.表名 指定数据库的指定表 列级权限 列名 需在权限后指定列（如 SELECT(col1)） 存储过程权限 PROCEDURE 过程名 指定存储过程的执行权限 -
关键选项：
- WITH GRANT OPTION：允许用户将自身权限授予他人（谨慎使用）。
- MAX_QUERIES_PER_HOUR 10：限制用户每小时最大查询数（资源控制）。
示例：
-- 授予用户对test数据库所有表的SELECT和INSERT权限，并允许其授权他人
GRANT SELECT, INSERT ON test.* TO 'user1'@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;-- 授予全局权限（仅允许DBA使用）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
2. REVOKE 撤销权限
REVOKE 权限1, 权限2, ...
ON 权限级别
FROM '用户名'@'主机';
- 注意：撤销权限需与授予时的权限级别完全匹配。
- 级联撤销：若用户通过WITH GRANT OPTION授予他人权限，撤销时需手动处理。
示例：
-- 撤销用户对test数据库的INSERT权限
REVOKE INSERT ON test.* FROM 'user1'@'%';-- 撤销所有权限（不会删除用户）
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'%';
二、账户管理
1. 创建账户
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
-- 示例：
CREATE USER 'user2'@'192.168.1.%' IDENTIFIED BY 'Pass123!';
-
主机限制：
- %：允许任意主机访问。
- localhost：仅允许本地访问。
- 192.168.1.%：允许指定IP段访问。
2. 修改账户
-- 修改用户名（需直接操作mysql.user表）
UPDATE mysql.user SET user='new_user' WHERE user='old_user';
FLUSH PRIVILEGES; -- 必须刷新权限-- 修改密码（推荐方式）
ALTER USER '用户'@'主机' IDENTIFIED BY '新密码';
3. 删除账户
DROP USER '用户名'@'主机';
-- 示例：
DROP USER 'user1'@'%';
三、查看权限
1. 查看用户权限
SHOW GRANTS FOR '用户'@'主机';
-- 示例：
SHOW GRANTS FOR 'user1'@'%';
2. 查询系统权限表
USE mysql;
-- 查看所有用户
SELECT user, host FROM user;-- 查看全局权限
SELECT * FROM user WHERE user='user1' AND host='%';-- 查看数据库级权限
SELECT * FROM db WHERE user='user1';-- 查看表级权限
SELECT * FROM tables_priv WHERE user='user1';
四、面试高频问题
1. WITH GRANT OPTION 的作用与风险
-
作用：允许用户将其权限授予其他用户。
-
风险：若恶意用户获得此权限，可能导致权限扩散。
-
案例：
-- user1将权限授予user2后，若撤销user1权限，user2权限仍保留！ GRANT SELECT ON test.* TO 'user1'@'%' WITH GRANT OPTION;
2. 权限生效时机
- 立即生效：使用 FLUSH PRIVILEGES; 或重启MySQL服务。
- 自动生效：通过 GRANT/REVOKE 语句操作权限时自动生效。
3. 列级权限如何实现？
-- 授予user1对test.orders表的amount列的SELECT权限
GRANT SELECT (amount) ON test.orders TO 'user1'@'%';
4. 如何限制用户资源？
-- 限制用户每小时最多10次查询
GRANT USAGE ON *.* TO 'user1'@'%'
WITH MAX_QUERIES_PER_HOUR 10;
五、最佳实践
- 最小权限原则：仅授予用户必要的最低权限。
- 避免通配符主机名：尽量限制IP范围（如 192.168.1.%）。
- 定期审查权限：清理未使用的账户和过期权限。
- 使用角色管理（MySQL 8.0+）：通过角色批量管理权限。
