角色的理解
角色是MySQL8.0中引入的新功能,在MySQL8.0中,角色是权限的集合,可以为角色添加或移除权限,用户可以被赋予角色,同时也被授予角色包含的权限,对角色进行操作需要较高的权限,并且像账户一样,角色可以拥有授予和撤销的权限。
引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定,可以确保数据的安全性,这是至关重要的。
创建角色
创建角色使用 CREATE ROLE 语句,语法如下:
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
角色名称的命名规则和用户名类似。如果 host_name省略,默认为% , role_name不可省略 ,不可为空。
练习:我们现在需要创建一个经理的角色,就可以用下面的代码:
CREATE ROLE 'manager'@'localhost';
给角色赋予权限
创建角色之后,默认这个角色是没有任何权限的,我们需要给角色授权。给角色授权的语法结构是:
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';
查看角色的权限
赋予角色权限之后,我们可以通过 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”赋予的权限,也就是对商品信息表、盘点表和应付账款表的只读权限。
结果显示,库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。
回收角色的权限
角色授权后,可以对角色的权限进行维护,对权限进行添加或撤销。添加权限使用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';
删除角色
当我们需要对业务重新整合的时候,可能就需要对之前创建的角色进行清理,删除一些不会再使用的角色。删除角色的操作很简单,你只要掌握语法结构就行了。
DROP ROLE role [,role2]...
注意, 如果你删除了角色,那么用户也就失去了通过这个角色所获得的所有权限 。
练习:执行如下SQL删除角色school_read。
DROP ROLE 'school_read';
给用户赋予角色
角色创建并授权后,要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用GRANT语句,语法形式如下:
GRANT role [,role2,...] TO user [,user2,...];
在上述语句中,role代表角色,user代表用户。可将多个角色同时赋予多个用户,用逗号隔开即可。
练习:给huan用户添加角色school_read权限。
(1)使用GRANT语句给huan添加school_read权限,SQL语句如下。
GRANT 'school_read' TO 'huan'@'localhost';
(2)添加完成后使用SHOW语句查看是否添加成功,SQL语句如下。
SHOW GRANTS FOR 'huan'@'localhost';
(3)使用huan用户登录,然后查询当前角色,如果角色未激活,结果将显示NONE。
SQL语句如下。
SELECT CURRENT_ROLE();
一般情况下,需要赋予角色的用户去登陆,操作,你会发现这个账号没有任何权限,这是因为 MySQL中创建角色之后,默认都是没有激活,也就是不能用的状态,必须要手动激活,激活以后用户才能拥有角色对应的权限
激活角色
方式1:使用set default role 命令激活角色
举例:
SET DEFAULT ROLE ALL TO 'huan'@'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 语句的意思是,对 所有角色永久激活 。运行这条语句之后,用户才真正拥有了赋予角色的所有权限。
查看当前会话已激活的角色
SELECT CURRENT_ROLE();
撤销用户的角色
撤销用户角色的SQL语法如下:
REVOKE role FROM user;
练习:撤销huan用户的school_read角色。
(1)撤销的SQL语句如下
REVOKE 'school_read' FROM 'huan'@'localhost';
(2)撤销后,执行如下查询语句,查看huan用户的角色信息
SHOW GRANTS FOR 'huan'@'localhost';
执行发现,用户huan之前的school_read角色已被撤销。
设置强制角色(mandatory role)
强制角色是给每个创建账户的默认角色,不需要手动设置,强制角色无法被 REVOKE 或者 DROP
方式1:服务启动前设置
[mysqld] mandatory_roles='role1,role2@localhost,r3@%.example.com'
方式2:运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; --系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; --系统重启后失效
小结
MySQL主要管理角色的语句如下:
CREATE ROLE and DROP ROLE | 创建和删除角色 |
---|---|
GRANT and REVOKE | 给角色或者账户分配权限 |
SHOW GRANTS | 显示 账户/角色 所拥有的 权限或角色 |
SET DEFAULT ROLE | 设置账户默认使用什么角色 |
SET ROLE | 改变当前会话的角色 |
CURRNET_ROLE()函数 | 显示当前会话的角色 |
mandatory_roles 和 activate_all_roles_on_login 系统变量 | 允许定义用户登录时强制的或者激活授权的角色 |