【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): 创建角色
1
|
create role devgroup;
|
查看mysql.user表真会被MySQL的机智给吓到
1
2
3
4
5
6
7
8
9
|
select user ,host from mysql. user ;
+ ------------------+-----------+
| user | host |
+ ------------------+-----------+
| devgroup | % |
| backup | 127.0.0.1 |
| mysql.sys | localhost |
| root | localhost |
+ ------------------+-----------+
|
说好的role事实上只是一个user呀!
2): 给角色赋权
1
2
|
grant all on tempdb.* to devgroup;
Query OK, 0 rows affected (0.07 sec)
|
和操作用户比起来是一样一样的!
3):创建用户并把角色的权限赋给它
1
2
3
4
5
|
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):测试刚创建的用户是否可以登录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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用户
1
2
|
grant root@ '127.0.0.1' to tom@ '127.0.0.1' ;
Query OK, 0 rows affected (0.04 sec)
|
2):用户tom用户检察一下自己的权限
1
2
3
4
5
6
7
8
|
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): 删库
1
2
|
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
1
2
|
set @@ global .activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)
|
5): 重新登录一次tom再试着删除一下tempdb库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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)
|
以上就是详解MySQL8的新特性ROLE的详细内容,更多关于MySQL8 新特性ROLE的资料请关注服务器之家其它相关文章!
原文链接:https://www.cnblogs.com/JiangLe/p/9851958.html