MySQL--DCL全解全知

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;

五、最佳实践

1. 最小权限原则：仅授予用户必要的最低权限。
2. 避免通配符主机名：尽量限制IP范围（如 192.168.1.%​）。
3. 定期审查权限：清理未使用的账户和过期权限。
4. 使用角色管理（MySQL 8.0+）：通过角色批量管理权限。