角色

导学

MySQL 5.7 不支持角色（Role）机制。角色是 MySQL 8.0 才引入的新特性。在 5.7 中，权限只能直接授予用户。本节先澄清这一版本差异，再介绍 5.7 中批量管理权限的实用替代方案。

版本差异速查：

特性	MySQL 5.7	MySQL 8.0
`CREATE ROLE`	❌ 不支持	✅ 支持
`GRANT role TO user`	❌ 不支持	✅ 支持
`SET ROLE`	❌ 不支持	✅ 支持
`SET DEFAULT ROLE`	❌ 不支持	✅ 支持
激活/切换角色	❌ 不支持	✅ 支持

定义

角色（Role）：命名的权限集合，本身不能登录，但可以授予用户使其继承权限。这是**MySQL 8.0+**的特性，MySQL 5.7 中不存在。

在 MySQL 5.7 中，权限管理只能采用直接授权模式：每个用户的权限通过 GRANT 直接赋予，无法通过中间层（角色）批量继承。

MySQL 5.7 的替代方案

方案一：直接批量 GRANT（最常用）

虽然没有角色，但可以通过脚本批量给多个用户授予相同权限。

前置准备：

CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4;
USE app_db;

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5, 2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

创建用户并直接授权：

-- 创建只读用户
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'Analyst123!';
CREATE USER 'analyst'@'%' IDENTIFIED BY 'Analyst123!';

-- 创建读写用户
CREATE USER 'backend'@'localhost' IDENTIFIED BY 'Backend123!';
CREATE USER 'backend'@'%' IDENTIFIED BY 'Backend123!';

-- 创建管理员用户
CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'DbAdmin123!';

批量授权：

-- 给所有分析师授予只读权限
GRANT SELECT ON app_db.* TO 'analyst'@'localhost';
GRANT SELECT ON app_db.* TO 'analyst'@'%';

-- 给所有后端开发授予读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'backend'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'backend'@'%';

-- 给管理员授予所有权限
GRANT ALL PRIVILEGES ON app_db.* TO 'db_admin'@'localhost' WITH GRANT OPTION;

验证权限：

SHOW GRANTS FOR 'analyst'@'localhost';
SHOW GRANTS FOR 'backend'@'localhost';
SHOW GRANTS FOR 'db_admin'@'localhost';

analyst 的权限：

Grants for analyst@localhost
GRANT USAGE ON . TO 'analyst'@'localhost'
GRANT SELECT ON `app_db`.* TO 'analyst'@'localhost'

backend 的权限：

Grants for backend@localhost
GRANT USAGE ON . TO 'backend'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO 'backend'@'localhost'

db_admin 的权限：

Grants for db_admin@localhost
GRANT USAGE ON . TO 'db_admin'@'localhost'
GRANT ALL PRIVILEGES ON `app_db`.* TO 'db_admin'@'localhost' WITH GRANT OPTION

结果解读：

每个用户的权限直接绑定，没有中间层（角色）
新增用户时需要重新执行一遍 GRANT 语句
权限变更时需要逐个用户 REVOKE 再 GRANT

方案二：用存储过程封装批量授权逻辑

将"权限模板"封装在存储过程中，模拟角色的批量授予效果。

创建存储过程：

DELIMITER //

CREATE PROCEDURE GrantReadOnly(IN user_host VARCHAR(100))
BEGIN
    SET @sql = CONCAT('GRANT SELECT ON app_db.* TO ', user_host);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

CREATE PROCEDURE GrantReadWrite(IN user_host VARCHAR(100))
BEGIN
    SET @sql = CONCAT('GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO ', user_host);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

调用存储过程批量授权：

-- 给新用户授予只读权限
CALL GrantReadOnly("'new_analyst'@'localhost'");

-- 给新用户授予读写权限
CALL GrantReadWrite("'new_dev'@'localhost'");

结果解读：

存储过程充当了"权限模板"的角色，减少重复 SQL
但本质上仍是直接授权，没有真正的角色继承机制
删除用户权限时仍需逐个 REVOKE

方案三：用脚本/程序管理权限（生产推荐）

在应用层或运维脚本中维护权限配置，通过程序批量执行 GRANT/REVOKE。

示例：Python 批量授权脚本思路

# 权限配置表（在程序中维护）
permissions = {
    'analyst': ['SELECT'],
    'backend': ['SELECT', 'INSERT', 'UPDATE', 'DELETE'],
    'admin': ['ALL PRIVILEGES']
}

# 批量执行 GRANT
for user, perms in permissions.items():
    sql = f"GRANT {', '.join(perms)} ON app_db.* TO '{user}'@'localhost'"
    # execute(sql)

结果解读：

将"角色"概念下沉到应用配置层
新增用户时只需在配置表中添加一行
权限变更时修改配置表并重新执行脚本
这是 MySQL 5.7 生产环境中最实用的"伪角色"方案

常见误区

误区	正解
"MySQL 5.7 支持 `CREATE ROLE`"	不支持。`CREATE ROLE` 是 MySQL 8.0 的语法，5.7 执行会报错。
"MySQL 5.7 可以用 `GRANT 'role' TO 'user'`"	不支持。这种语法在 5.7 中会报错。
"MySQL 5.7 有 `SET ROLE` 切换角色"	不支持。`SET ROLE` 是 8.0 特性。
"没有角色就无法批量管理权限"	可以用存储过程、脚本或程序配置层模拟批量授权。

面试考点

Q：MySQL 5.7 支持角色吗？

不支持。角色（Role）是 MySQL 8.0 引入的特性。5.7 中权限只能直接授予用户，无法通过角色继承。替代方案包括：直接批量 GRANT、存储过程封装、应用层配置管理。

Q：MySQL 5.7 和 8.0 在权限管理上的最大区别？

5.7 只有直接授权模型；8.0 引入了角色机制，支持 CREATE ROLE、GRANT role TO user、SET ROLE 等完整角色生命周期管理。

Q：在 5.7 中如何模拟角色的批量授权效果？

直接批量 GRANT 给同类用户；2. 用存储过程封装权限模板；3. 在应用层维护权限配置表，通过脚本同步到数据库。

小结

MySQL 5.7 不支持角色（Role），这是 8.0 的特性
5.7 中权限只能直接授予用户，没有继承机制
替代方案：批量 GRANT、存储过程封装、应用层配置管理
如果业务需要角色机制，建议升级到 MySQL 8.0

下一章引子：权限管理除了授权，还需要定期审计和回收——SQL 注入防范是权限管理的重要延伸。