MySQL8.0-新特性-role

一、简介

role可以看做一个权限的集合，这个集合有一个统一的名字role名。可以给多个账户统一的某个role的权限权限的修改直接通过修改role来实现，不需要每个账户一个一个的grant权限，方便运维和管理。role可以创建、删除、修改并作用到他管理的账户上。

二、案例

1、创建表

mysql> create table t1(id1 int primary key,id2 int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)

2、创建role，并赋予role对t1的select权限

mysql> create role app_read;
Query OK, 0 rows affected (0.05 sec)

mysql> grant select on yzs.t1 to app_read;
Query OK, 0 rows affected (0.01 sec)

3、创建用户，并赋予用户role

mysql> create user read_user@localhost identified by 'read_user1pass';
Query OK, 0 rows affected (0.02 sec)

mysql> grant app_read to read_user@localhost;
Query OK, 0 rows affected (0.00 sec)

4、以read_user用户登录，查看效果

[rdb@localhost ~]$ mysql -uread_user -p'read_user1pass'
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 8
Server version: 8.0.3-rc-debug-log Source distribution

Copyright (c) 2000, 2017, 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 yzs
ERROR 1044 (42000): Access denied for user 'read_user'@'localhost' to database 'yzs'
mysql> show grants;
+-------------------------------------------------+
| Grants for read_user@localhost                  |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`localhost`   |
| GRANT `app_read`@`%` TO `read_user`@`localhost` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for read_user@localhost using app_read;
+-------------------------------------------------------+
| Grants for read_user@localhost                        |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`localhost`         |
| GRANT SELECT ON `yzs`.`t1` TO `read_user`@`localhost` |
| GRANT `app_read`@`%` TO `read_user`@`localhost`       |
+-------------------------------------------------------+
3 rows in set (0.00 sec)

5、需要在用户连接时手动激活哪些role，需要重新登录

mysql> set default role all to  `read_user`@`localhost`;
Query OK, 0 rows affected (0.01 sec)

mysql> use yzs
ERROR 1044 (42000): Access denied for user 'read_user'@'localhost' to database 'yzs'
mysql> exit
Bye
[rdb@localhost ~]$ mysql -uread_user -p'read_user1pass'
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 9
Server version: 8.0.3-rc-debug-log Source distribution

Copyright (c) 2000, 2017, 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 yzs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user();
+---------------------+
| user()              |
+---------------------+
| read_user@localhost |
+---------------------+
1 row in set (0.00 sec)

mysql> select *from t1;
+-----+------+
| id1 | id2  |
+-----+------+
|   1 |    1 |
+-----+------+
1 row in set (0.01 sec)

6、修改role权限，也需要重新登录

mysql> insert into t1 values(2,2);
ERROR 1142 (42000): INSERT command denied to user 'read_user'@'localhost' for table 't1'
mysql> grant insert on t1 to app_read;
ERROR 1142 (42000): INSERT, GRANT command denied to user 'read_user'@'localhost' for table 't1'
mysql> exit
Bye
[rdb@localhost ~]$ mysql -uroot -p'db10$ZTE'
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 10
Server version: 8.0.3-rc-debug-log Source distribution

Copyright (c) 2000, 2017, 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 yzs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant insert on t1 to app_read;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye
[rdb@localhost ~]$ mysql -uread_user -p'read_user1pass'
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 11
Server version: 8.0.3-rc-debug-log Source distribution

Copyright (c) 2000, 2017, 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 yzs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into t1 values(2,2);
Query OK, 1 row affected (0.00 sec)

7、删除role的某个权限

[rdb@localhost ~]$ mysql -uroot -p'db10$ZTE'
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 12
Server version: 8.0.3-rc-debug-log Source distribution

Copyright (c) 2000, 2017, 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 yzs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> revoke insert on t1 from app_read;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[rdb@localhost ~]$ mysql -uread_user -p'read_user1pass'
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 13
Server version: 8.0.3-rc-debug-log Source distribution

Copyright (c) 2000, 2017, 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 yzs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into t1 values(3,3);
ERROR 1142 (42000): INSERT command denied to user 'read_user'@'localhost' for table 't1'
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `app_read`@`%` |
+----------------+
1 row in set (0.00 sec)

8、mysql库增加了2个管理表

mysql> show tables like '%role%';
+--------------------------+
| Tables_in_mysql (%role%) |
+--------------------------+
| default_roles            |
| role_edges               |
+--------------------------+
2 rows in set (0.00 sec)

mysql> select *from default_roles;
+-----------+-----------+-------------------+-------------------+
| HOST      | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+-----------+-------------------+-------------------+
| localhost | read_user | %                 | app_read          |
+-----------+-----------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> select *from role_edges;
+-----------+-----------+-----------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+-----------+-------------------+
| %         | app_read  | localhost | read_user | N                 |
+-----------+-----------+-----------+-----------+-------------------+
1 row in set (0.00 sec)

9、参考

https://dev.mysql.com/doc/refman/8.0/en/roles.html?spm=a2c4e.11153959.blogcont60654.8.5be005eeqOTTPO