mysql8.0角色管理

Posted on Views:

MySQL 8.0引入了完整的数据库角色（Role）管理机制。角色可以理解为一组权限的集合，通过将角色授予用户，实现权限集中管理，避免为每个用户重复授权。

1.角色管理的基本流程

  • 1.创建角色
    CREATE ROLE

  • 2.给角色授权
    GRANT权限TO角色

  • 3.将角色授予用户
    GRANT角色TO用户

  • 4.激活角色并验证权限
    SET DEFAULT ROLE / SET ROLE

2.创建角色并授权

以下示例创建一个只能查询和新增数据的业务角色。

2.1创建角色

CREATE ROLE 'app_read_write';

也可以一次创建多个角色：

CREATE ROLE
    'app_read',
    'app_write',
    'app_admin';

2.2给角色授予权限

授予指定数据库的查询权限：

GRANT SELECT
ON mydb.*
TO 'app_read';

授予指定数据库的新增、修改和删除权限：

GRANT INSERT, UPDATE, DELETE
ON mydb.*
TO 'app_write';

授予全部数据库级权限（不包括所有全局管理权限）：

GRANT ALL PRIVILEGES
ON mydb.*
TO 'app_admin';

注意：ALL PRIVILEGES ON mydb.*仅代表该数据库范围内的全部可授予权限，并不等于全局SUPER权限。

2.3创建用户并授予角色

CREATE USER 'zhangsan'@'localhost'
IDENTIFIED BY 'StrongPassword_123!';

GRANT 'app_read_write'
TO 'zhangsan'@'localhost';

这里的角色名、用户名和主机名均使用引号包围。

2.4设置默认角色

这是非常关键的一步。MySQL 8.0默认情况下，授予用户角色后，角色不一定会自动激活。

SET DEFAULT ROLE ALL
TO 'zhangsan'@'localhost';

用户下次登录时，所有已授予的角色都会默认激活。

也可以仅激活指定角色：

SET DEFAULT ROLE 'app_read'
TO 'zhangsan'@'localhost';

3.角色激活与取消

在当前会话中执行：

-- 激活所有已授予的角色
SET ROLE ALL;

-- 仅激活指定角色
SET ROLE 'app_read';

-- 激活除指定角色以外的所有角色
SET ROLE ALL EXCEPT 'app_write';

-- 激活默认角色
SET ROLE DEFAULT;

-- 禁用所有角色
SET ROLE NONE;

注意：SET ROLE NONE只会禁用当前会话中的角色，不会删除角色，也不会撤销直接授予用户的权限。

4.查看角色与权限

操作 SQL语句
查看当前用户 SELECT CURRENT_USER();
查看当前激活的角色 SELECT CURRENT_ROLE();
查看用户的授权 SHOW GRANTS FOR 'zhangsan'@'localhost';
查看角色的权限 SHOW GRANTS FOR 'app_read';
查看用户获得的角色 SHOW GRANTS FOR 'zhangsan'@'localhost' USING 'app_read_write';
查看角色相关系统变量 SHOW VARIABLES LIKE '%roles%';

还可以查询角色授予关系：

SELECT *
FROM mysql.role_edges;

查询默认角色配置：

SELECT *
FROM mysql.default_roles;

注意：mysql.role_edges记录角色授予关系，mysql.default_roles记录默认角色配置。查看这些系统表通常需要相应权限。

5.撤销权限与删除角色

5.1撤销角色的某项权限

REVOKE INSERT, UPDATE, DELETE
ON mydb.*
FROM 'app_write';

5.2撤销用户拥有的角色

REVOKE 'app_read_write'
FROM 'zhangsan'@'localhost';

5.3删除角色

DROP ROLE 'app_read_write';

删除角色会撤销该角色对用户和其他角色的相应授予关系。操作前应确认没有业务依赖。

6.生产环境建议

  • 按职责拆分角色：例如只读、读写、运维管理角色。
  • 遵循最小权限原则：不要为了方便而授予不必要的ALL PRIVILEGES。
  • 明确设置默认角色：避免用户登录后角色未激活，导致权限不足。
  • 定期检查授权关系：使用SHOW GRANTS核对用户和角色的权限。
  • 区分用户和角色：用户用于登录认证，角色用于集中管理权限。