【mysql的role解决了什么问题】
假设你是一个职业素养良好的dba比较同时又比较注重权限管理的话；可能遇到过这样的问题，数据库中有多个开发人员的账号；有一天要建
一个新的schema，如果你希望之前所有的账号都能操作这个schema下的表的话，在mysql-8.0之前你要对第一个账号都单独的赋一次权。
mysql-8.0.x所权限抽象了出来用role来表示，当你为role增加新的权限的时候，与这个role关联的所有用户的权限也就一并变化了；针对
上面提到的场景在mysql-8.0.x下只要一条sql就解决了。
【机智的mysql开发】
mysql引进role用了一个非常机智的做法，既然role是一堆权限的象征，这东西在mysql里面本来就有呀！它就是user呀。
1): 创建角色
create role devgroup;
查看mysql.user表真会被mysql的机智给吓到
select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | devgroup | % | | backup | 127.0.0.1 | | mysql.sys | localhost | | root | localhost | +------------------+-----------+
说好的role事实上只是一个user呀！
2): 给角色赋权
grant all on tempdb.* to devgroup; query ok, 0 rows affected (0.07 sec)
和操作用户比起来是一样一样的！
3):创建用户并把角色的权限赋给它
create user tom@'127.0.0.1' identified by '123456'; query ok, 0 rows affected (0.09 sec) grant devgroup to tom@'127.0.0.1'; query ok, 0 rows affected (0.09 sec)
4):测试刚创建的用户是否可以登录
mysql -h127.0.0.1 -p3306 -utom -p123456 mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 16 server version: 8.0.13 mysql community server - gpl copyright (c) 2000, 2018, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> show grants; +-------------------------------------------+ | grants for tom@127.0.0.1 | +-------------------------------------------+ | grant usage on *.* to `tom`@`127.0.0.1` | | grant `devgroup`@`%` to `tom`@`127.0.0.1` | +-------------------------------------------+ 2 rows in set (0.00 sec)
【角色和用户只是一个硬币的两面】
如果你还是觉得“角色”和“用户”是两个不一样的东西、那我只能是出大招了
1): root@127.0.0.1 用户当成角色赋给刚才的tom用户
grant root@'127.0.0.1' to tom@'127.0.0.1'; query ok, 0 rows affected (0.04 sec)
2):用户tom用户检察一下自己的权限
show grants; +--------------------------------------------------------------+ | grants for tom@127.0.0.1 | +--------------------------------------------------------------+ | grant usage on *.* to `tom`@`127.0.0.1` | | grant `devgroup`@`%`,`root`@`127.0.0.1` to `tom`@`127.0.0.1` | +--------------------------------------------------------------+ 2 rows in set (0.00 sec)
可以看到root@127.0.0.1的权限已经被套上去了、既然都是root用户的权限了我们来删除一个tempdb库看一下吧！
3): 删库
drop database tempdb; error 1044 (42000): access denied for user 'tom'@'127.0.0.1' to database 'tempdb'
看起来没有权限删除这个库呀！事实上是mysql-8默认并不会激活role，关于是否激活role是由activate_all_roles_on_login这个参数控制的
4): 开启activate_all_roles_on_login
set @@global.activate_all_roles_on_login=1; query ok, 0 rows affected (0.00 sec)
5): 重新登录一次tom再试着删除一下tempdb库
mysql -h127.0.0.1 -p3306 -utom -p123456 mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 18 server version: 8.0.13 mysql community server - gpl copyright (c) 2000, 2018, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> use tempdb; database changed mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | tempdb | +--------------------+ 5 rows in set (0.01 sec) mysql> drop database tempdb; query ok, 0 rows affected (0.09 sec)
