mysql8.0的角色管理

欢迎来到蓝队云技术小课堂，每天分享一个技术小知识。

1. 角色的理解

角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限,并且像用户账户一样，角色可以拥有授予和撤销的权限。

引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

2. 创建角色

在实际应用中，为了安全性，需要给用户授予权限, 当用户数量较多时，为了避兔单独给每一个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色。

创建角色使用 CREATE ROLE 语句，语法如下：

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...;

角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。

示例：我们现在需要创建一个经理的角色，就可以用下面的代码：

CREATE ROLE 'manager'@'localhost';

这里创建了一个角色，角色名称是"manager",角色可以登录的主机是"localhost"，意思是只能从数据厍服务器运行的这台计算机登录这个账号。你也可以不写主机名，直接创建角色 "manager"。

CREATE ROLE 'manager';

如果不写主机名MySQL默认是通配符%，意思是这个账号可以从任何一台主机上登录数据库。

同样道理，如果我们要创建库管的角色，就可以用下面的代码

CREATE ROLE 'stocker';

还可以通过如下命令，一次性创建3个角色：

CREATE ROLE 'app_developer','app_read','app_write';

3. 给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称，图中列出了部分权限列表。

SHOW PRIVILEGES\\\\G

示例1: 我们现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码来实现：

GRANT SELECT ON demo.settlement TO 'manager';

GRANT SELECT ON demo.goodsmaster TO 'manager';

GRANT SELECT ON demo.invcount TO 'manager';

如果我们需要赋予库管角色盘点表的增删改权限、商品信息表的只读权限，对应付账款表没有权限，就可以这样：

GRANT SELECT,INSERT,DELETE,UPDATE ON demo.invcount TO 'stocker';

GRANT SELECT ON demo.goodsmaster TO 'stocker';

示例2：

GRANT ALL PRIVILEGES ON app_db.* TO 'app_developer'; -- 给app_db数据库中的所有表的所有权限

GRANT SELECT ON app_db.* TO 'app_read'; -- 给app_db数据库中的所有表的查询权限

GRANT INSERT,DELETE,UPDATE  ON app_db.* TO 'app_write'; -- 给app_db数据库中的所有表的修改权限

示例3 创建三个角色，分别拥有全部权限、查询权限和读写权限, 步骤如下所示·

(1) 使用如下sql，创建三个角色, 角色名为school_admin, school_read, school_write

CREATE ROLE 'school_admin','school_read','school_write';

(2) 给每个角色授予对应的权限，school_admin可以对数据库shool中的所有表进行任何操作, school_read只能对数据厍中的表进行查询, school_write可以对数据库shool 中的表进行读与操作，SQ语句如下。

GRANT ALL PRIVILEGES ON school.* TO 'school_admin'; 

GRANT SELECT ON school.* TO 'school_read'; 

GRANT SELECT,INSERT,DELETE,UPDATE  ON school.* TO 'school_write';

4. 查看角色的权限

赋予角色权限之后，我们可以通过 SHOW GRANTS语句，来查看权限是否创建成功了

mysql> SHOW GRANTS FOR 'manager'@'%';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+

只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限。代码的最后三行代表了我们给角色“manager”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权限。

再示例：来看看库管角色的权限：

mysql> SHOW GRANTS FOR 'stocker'@'%';
+-----------------------------------------------------------------------------+
| Grants for manager@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `stocker`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `stocker`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `demo`.`invcount` TO `stocker`@`%` |
+-----------------------------------------------------------------------------+

结果显示，库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。

5. 回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。

修改了角色的权限，会影响拥有该角色的账户的权限。

撤销角色权限的SQL语法如下:

REVOKE privileges ON tablename FROM 'rolename';

示例1：撤销school_write角色的权限。

（1）使用如下语句撤销school_write角色的权限。

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write'@'%';

（2）撤销后使用SHOW语句查看school_write对应的权限，语句如下。

SHOW GRANTS FOR 'school_write'@'%';

示例2:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write'@'%'; # 收回角色的权限

6. 删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。删除角色的操作很简单。语法如下：

DROP ROLE role1 [, role2]...;

注意：如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

示例：删除角色school_read。

DROP ROLE 'school_read'@'%'

7. 给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下：

GRANT role [,role2,...] TO user [,user2,...];

在上述语句中，role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。

示例：给kangshifu用户添加角色school_read权限。

（1）使用GRANT语句给zhang3添加school_read权限，SQL语句如下。

GRANT 'school_read'@'%' TO 'zhang3'@'%';

（2）添加完成后使用SHOW语句查看是否添加成功。

SHOW GRANTS FOR 'zhang3'@'%';

（3）使用zhang3用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE。

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.02 sec)

上面结果是NONE，说明用户未具备相应的角色。

或者你用赋予了角色的用户去登录、操作，你会发现，这个账号没有任何权限。这是因为，MySQL中创建了角色之后，默认都是没有被激活，也就是不能用，须要手动激活，激活以后用户才能拥有角色对应的权限。

蓝队云官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，蓝队云整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，蓝队云期待与你一起探索。