【MySQL的ROLE解決了什么問(wèn)題】
假設(shè)你是一個(gè)職業(yè)素養(yǎng)良好的DBA比較同時(shí)又比較注重權(quán)限管理的話(huà)；可能遇到過(guò)這樣的問(wèn)題，數(shù)據(jù)庫(kù)中有多個(gè)開(kāi)發(fā)人員的賬號(hào)；有一天要建
一個(gè)新的schema，如果你希望之前所有的賬號(hào)都能操作這個(gè)schema下的表的話(huà)，在mysql-8.0之前你要對(duì)第一個(gè)賬號(hào)都單獨(dú)的賦一次權(quán)。
mysql-8.0.x所權(quán)限抽象了出來(lái)用ROLE來(lái)表示，當(dāng)你為ROLE增加新的權(quán)限的時(shí)候，與這個(gè)ROLE關(guān)聯(lián)的所有用戶(hù)的權(quán)限也就一并變化了；針對(duì)
上面提到的場(chǎng)景在mysql-8.0.x下只要一條SQL就解決了。
【機(jī)智的MySQL開(kāi)發(fā)】
MySQL引進(jìn)ROLE用了一個(gè)非常機(jī)智的做法，既然ROLE是一堆權(quán)限的象征，這東西在MySQL里面本來(lái)就有呀！它就是USER呀。
1): 創(chuàng)建角色
查看mysql.user表真會(huì)被MySQL的機(jī)智給嚇到
select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| devgroup | % |
| backup | 127.0.0.1 |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
說(shuō)好的role事實(shí)上只是一個(gè)user呀！
2): 給角色賦權(quán)
grant all on tempdb.* to devgroup;
Query OK, 0 rows affected (0.07 sec)
和操作用戶(hù)比起來(lái)是一樣一樣的！
3):創(chuàng)建用戶(hù)并把角色的權(quán)限賦給它
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):測(cè)試剛創(chuàng)建的用戶(hù)是否可以登錄
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)
【角色和用戶(hù)只是一個(gè)硬幣的兩面】
如果你還是覺(jué)得“角色”和“用戶(hù)”是兩個(gè)不一樣的東西、那我只能是出大招了
1): root@127.0.0.1 用戶(hù)當(dāng)成角色賦給剛才的tom用戶(hù)
grant root@'127.0.0.1' to tom@'127.0.0.1';
Query OK, 0 rows affected (0.04 sec)
2):用戶(hù)tom用戶(hù)檢察一下自己的權(quán)限
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的權(quán)限已經(jīng)被套上去了、既然都是root用戶(hù)的權(quán)限了我們來(lái)刪除一個(gè)tempdb庫(kù)看一下吧！
3): 刪庫(kù)
drop database tempdb;
ERROR 1044 (42000): Access denied for user 'tom'@'127.0.0.1' to database 'tempdb'
看起來(lái)沒(méi)有權(quán)限刪除這個(gè)庫(kù)呀！事實(shí)上是MySQL-8默認(rèn)并不會(huì)激活role，關(guān)于是否激活role是由activate_all_roles_on_login這個(gè)參數(shù)控制的
4): 開(kāi)啟activate_all_roles_on_login
set @@global.activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)
5): 重新登錄一次tom再試著刪除一下tempdb庫(kù)
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的詳細(xì)內(nèi)容，更多關(guān)于MySQL8 新特性ROLE的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL 8.0用戶(hù)和角色管理原理與用法詳解
- MySQL 角色(role)功能介紹
