mysql8在之前的版本中并没有角色(role)的概念,在角色概念出现之前,mysql对用户授权是一个用户起码一条授权记录,假如有多个用户需要同样的权限,那只能增加多个授权记录,同理,如果需要对多个相同权限用户进行权限调整,只能逐个操作。现在则可以简化这一流程,只需调整角色权限即可,不再需要对每个用户做维护。
图示变化:
一、角色的日常使用
1、创建角色,和创建用户一样,默认host后缀是%,可以看出角色默认是无密码的
mysql> create role 'read_role';
mysql> select host,user,authentication_string from mysql.user where user='read_role';
+------+-----------+-----------------------+
| host | user | authentication_string |
+------+-----------+-----------------------+
| % | read_role| |
+------+-----------+-----------------------+
2、角色设置权限,和用户授权一样
mysql> grant select on db_kenyon.* to 'read_role';
mysql> show grants for 'read_role';
+--------------------------------------------------+
| Grants for read_role@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `read_role`@`%` |
| GRANT SELECT ON `db_kenyon`.* TO `read_role`@`%` |
+--------------------------------------------------+
3、关联用户角色
mysql> create user 'read_usr1' identified by 'Eric@2020';
mysql> create user 'read_usr2' identified by 'Eric@2020';
mysql> grant 'read_role' to 'read_usr1','read_usr2';
mysql> select *from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+-----------+-------------------+
| % | read_role | % | read_usr1 | N |
| % | read_role | % | read_usr2 | N |
+-----------+-----------+---------+-----------+-------------------+
mysql> show grants for 'read_usr1' using 'read_role';
+--------------------------------------------------+
| Grants for read_usr1@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `read_usr1`@`%` |
| GRANT SELECT ON `db_kenyon`.* TO `read_usr1`@`%` |
| GRANT `read_role`@`%` TO `read_usr1`@`%` |
+--------------------------------------------------+
4、 测试登录,需要设置默认角色,否则会报错
[root@ ~]# mysql -uread_usr1 db_kenyon -p
Enter password:
ERROR 1044 (42000): Access denied for user 'read_usr1'@'%' to database 'db_kenyon'
--设置默认角色,返回管理员窗口
mysql> set default role 'read_role' to 'read_usr1';
mysql> select * from mysql.default_roles;
+------+-----------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-----------+-------------------+-------------------+
| % | read_usr1 | % | read_role |
+------+-----------+-------------------+-------------------+
mysql> \q
Bye
[root@ ~]# mysql -uread_usr1 -pEric@2020
mysql> select current_role();
+-----------------+
| current_role() |
+-----------------+
| `read_role`@`%` |
+-----------------+
5、回收权限
mysql> revoke select on db_kenyon.* from 'read_role';
--用户对应的权限相应被回收
mysql> show grants for 'read_usr1';
+------------------------------------------+
| Grants for read_usr1@% |
+------------------------------------------+
| GRANT USAGE ON *.* TO `read_usr1`@`%` |
| GRANT `read_role`@`%` TO `read_usr1`@`%` |
+------------------------------------------+
6、删除角色
mysql> drop role 'read_role'@'%';
二、总结:
1、新增角色模块可以简化并统一管理有相同权限的用户,方便管理人员维护
2、mysql库里新增了两张系统表defalut_role与role_edges用来查看和管理角色属性,前者是系统中默认的角色,后者是用户与角色的配置关系,角色表存放在user表中和用户信息混用
3、新增函数current_role()来查看当前用户的角色属性,相比较oracle略繁琐
4、和普通用户不同,新增的角色默认是无密码的,账户是锁定(account locked)的
5、mysql8版本的很多功能越来越趋向oracle,新增角色也是之一