七: 角色管理

目录

• 1. 创建角色
• 2. 给角色赋予权限
• 3. 查看角色的权限
• 4. 回收角色的权限
• 5. 删除角色
• 6. 给用户赋予角色
• 7. 激活角色
• 8. 撤销用户的角色
• 9. 设置强制角色(mandatory role)
• 10. 小结

 

---

# 角色管理

角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限．用户可以被赋予角色，同时也被授予角色包含的权限．

对角色进行操作需要较高的权限,并且像用户账户一样，角色可以拥有授予和撤销的权限引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

回到顶部

1. 创建角色

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

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

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

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

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

CREATE ROLE 'manager'@'localhost';

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

如果不写主机名MySQL默认是通配符“％''，意思是这个账号可以．从任何一台主机上登录数据库。
同样道理，如果我们要创建库管的角色，就可以用下面的代码．

CREATE ROLE 'stocker' ;

还可以使用如下命令, 一次创建3 个角色

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

回到顶部

2. 给角色赋予权限

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

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 创建三个角色，分别拥有全部权限、查询权限和读写权限, 步骤如下所示·

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

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

给每个角色授予对应的权限，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'; 

回到顶部

3. 查看角色的权限

赋予角色权限之后，我们可以通过 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”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权 限。

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

回到顶部

4. 回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用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';

回到顶部

5. 删除角色

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

DROP ROLE role [,role2]...

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

示例：执行如下SQL删除角色school_read。

DROP ROLE 'school_read';

回到顶部

6. 给用户赋予角色

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

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

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

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

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

GRANT 'school_read' TO 'kangshifu'@'localhost';

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

SHOW GRANTS FOR 'kangshifu'@'localhost';

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

SELECT CURRENT_ROLE();

此时我们使用kangshifu 用户登录, 并没有获取到school_read 的角色的功能, 查看角色,显示为空

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

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

回到顶部

7. 激活角色

激活角色有两种方式:

方式1：使用set default role 命令激活角色

举例：

SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';

举例：使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下：

SET DEFAULT ROLE ALL TO
'dev1'@'localhost',
'read_user1'@'localhost',
'read_user2'@'localhost',
'rw_user1'@'localhost';

注意: 用户需要重新登录,才能看到赋予的角色

方式2：将activate_all_roles_on_login设置为ON

默认情况：

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)

设置：

SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有 权限。

回到顶部

8. 撤销用户的角色

撤销用户角色的SQL语法如下：

REVOKE role FROM user;

示例：撤销kangshifu用户的school_read角色。

（1）撤销的SQL语句如下

REVOKE 'school_read' FROM 'kangshifu'@'localhost';

（2）撤销后，执行如下查询语句，查看kangshifu用户的角色信息

SHOW GRANTS FOR 'kangshifu'@'localhost';

执行发现，用户kangshifu之前的school_read角色已被撤销。

回到顶部

9. 设置强制角色(mandatory role)

方式1：服务启动前设置

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式2：运行时设置

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然
有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效

回到顶部

10. 小结

mysql 主要管理角色的语句如下