mysql 8.0 新特性—— role

最新推荐文章于 2022-07-26 19:42:22 发布

FEI441544558

最新推荐文章于 2022-07-26 19:42:22 发布

阅读量221

点赞数

分类专栏： MySQL MySQL 8.x新特性文章标签： mysql 数据库

本文链接：https://blog.csdn.net/FEI441544558/article/details/108296310

版权

MySQL 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

MySQL 8.x新特性

1 篇文章 0 订阅

订阅专栏

role

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

跟5.x版本grant权限的区别

5.x 创建用户 --> grant权限给用户
8.x 创建role-> grant权限给role -> 创建用户 -->grant role 给用户
也就是说8.x中多了一个role，是先把权限给到role，然后再把role当做权限给到用户，在5.x的用户权限赋予中多了中间者 role。

实例

创建role角色app_read，赋予权限

mysql> create role app_read;
mysql> grant select on test.* to app_read;

创建用户，赋予角色app_read

mysql> create user read_user@localhost identified by 'read_userpwd';
mysql> grant app_read to read_user@localhost;

重新登录用户read_user@localhost 激活权限

查看read_user@localhost权限

mysql> show grants for read_user@localhost;
+-------------------------------------------------+
| Grants for read_user@localhost                  |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user`@`localhost`   |
| GRANT `app_read`@`%` TO `read_user`@`localhost` |
+-------------------------------------------------+

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 `test`.`*` TO `read_user`@`localhost` |
| GRANT `app_read`@`%` TO `read_user`@`localhost`       |
+-------------------------------------------------------+

默认角色role需要手动设置，重新登录后生效

mysql> set default role all to  `read_user`@`localhost`;

SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ] ...

set default role 可以设置多个用户权限，alter user只能设置一个用户。

ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}

参照：https://dev.mysql.com/doc/refman/8.0/en/set-default-role.html

修改角色role权限，重新登录后生效

mysql> grant insert on t1 to app_read;

撤销角色role的某个权限

mysql> revoke insert on t1 from app_read;

撤销用户角色role

mysql> revoke app_read from `read_user`@`localhost`;

mysql库增加了2个管理表

mysql> show tables like '%role%';
+--------------------------+
| Tables_in_mysql (%role%) |
+--------------------------+
| default_roles            |
| role_edges               |
+--------------------------+

mysql> select * from **default_roles**;
+-----------+-----------+-------------------+-------------------+
| HOST      | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+-----------+-------------------+-------------------+
| localhost | read_user | %                 | app_read          |
+---------------------------------------------------------------+

mysql> select *from role_edges;
+-----------+-----------+-----------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+-----------+-------------------+
| %         | app_read  | localhost | read_user | N                 |
+-------------------------------------------------------------------+