认真学习MySQL中的角色权限控制

【1】 权限管理

关于MySQL的权限简单的理解就是MySQL运行你做你权力以内的事情，不可以越界。比如只允许你执行select操作，那么你就不能执行update操作。只允许你从某台机器上连接MySQL，那么你就不能从除那台机器以为的其他机器连接MySQL。

① 权限列表

可以使用如下命令查看MySQL到底有哪些权限

show privileges;

• create和drop权限，可以创建新的数据库和表，或删除(移掉)已有的数据库和表。如果将MySQL数据库中的drop权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
• select、insert、update和delete权限允许在一个数据库现有的表上实施操作。
• select权限只有在它们真正从一个表中检索行时才被用到。
• index权限允许创建或删除索引，index适用于已有的表。如果具有某个表的create权限，就可以在create table语句中包括索引定义。
• alter权限可以使用alter table来更改表的结构和重新命名表。
• create routine权限用来创建保存的程序(函数和程序)，alter routine权限用来更改和删除保存的程序，execute权限用来执行保存的程序。
• grant权限允许授权给其他用户，可用于数据库、表和保存的程序。
• file权限使用户可以使用load data infile 和 select…into outfile语句读或写服务器上的文件，任何被授予file权限的用户都能读或写MySQL服务器上的任何文件(说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件)。

② 授予权限

给用户授权的方式由两种，分别是通过把角色赋予用户给用户授权和直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。

授权命令：

grant 权限1,权限2,...权限n ON 数据库名称.表名称 TO 用户名@用户地址 [identified by '密码口令'] ;

该权限如果发现没有该用户，将会直接创建一个新用户。

赋予所有权限：

grant all privileges on *.* to 用户名@'%' identified by '密码' with grant option;

如果需要赋予包括grant的权限，添加参数 with grant option 这个选项即可，表示该用户可以将自己拥有的权限授权给别人。

可以使用grant重复给用户添加权限，权限将会叠加。比如先给用户赋予一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select 和 insert权限。

③ 查看权限

查看当前用户权限

show grants;

show GRANTS FOR CURRENT_USER;

SHOW GRANTS FOR  CURRENT_USER();

查看某用户的全部权限

SHOW GRANTS FOR 'username'@'主机地址' ;

④ 收回权限

收回权限就是取消已经赋予用户的某些权限，收回用户不必要的权限可以在一定程度上保证系统的安全性。

MySQL中使用revoke语句取消用户的某些权限。使用revoke收回权限之后，用户账户的记录将从db、host、tables_priv 和 columns_priv表中删除，但是用户账户记录仍然在user表中保存(删除user表中的账户记录使用drop user语句)。

# 将用户账户从user表删除之前，应该收回相应用户的所有权限
revoke 权限1,权限2,...权限n ON 数据库名称.表名称 from 用户名@用户地址 ;

收回全部权限

REVOKE ALL PRIVILEGES ON *.* FROM jane@'%' ;

修改完后，需用户重新登录才生效。

⑤ 权限的实现

MySQL服务器通过 权限表 来控制用户对数据库的访问，权限表存放在mysql数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有table_priv表、column_priv表和proc_priv表。

在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

• user：用户账号及权限信息
• global_grants：动态全局授权
• db：数据库层级的权限
• tables_priv：表层级的权限
• columns_priv：列层级的权限
• procs_priv：存储过程和函数权限
• proxies_priv：代理用户的权限

【2】角色管理

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

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

① 创建角色

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

创建角色语法

create role 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

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

② 给角色赋予权限

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

grant 权限1,权限2,....权限n on table_name to 'role_name'[@'host_name'] ;

# e.g:
grant select on demo.goods to 'manager' ;

grant select ,insert ,delete,update on demo.invcount to 'stocker'；

③ 查看角色的权限

语法如下：

show grants for 'role_name' ;

默认情况下，只要创建了一个角色，系统自动给赋予 ‘USAGE’ 权限，意思是连接登录数据库的权限。

④ 回收角色的权限

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

语法如下：

revoke 权限1,权限2,....权限n on tablename from 'rolename' ;

⑤ 删除角色

语法如下：

DROP ROLE role [,role2]....

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

⑥ 给用户赋予角色

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

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

# e.g:
grant 'goods_read' to 'jane'@'localhost' ;

role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。

添加完成后可以使用show语句查看是否添加成功：

show grants for 'jane'@'localhost';

使用用户jane登录，查询当前角色。如果角色为激活，结果将显示none：

select current_role();

MySQL中创建了角色之后，默认都是没有被激活，也就是不能用，必须手动激活，激活以后用户才能拥有角色对应的权限。

⑦ 激活角色

激活角色有两种方式：使用 set default role 命令 或者将activate_all_roles_on_login设置为 on。

# 第一种方式：用户需重新登录
set default role all to 'jane'@'localhost';

MySQL8下

show variables like 'activate_all_roles_on_login';

#设置开启
set global activate_all_roles_on_login=ON;

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

⑧ 撤销用户的角色

语法如下：

revoke role_name from user_name;

# e.g:
revoke 'goods_read' from 'jane'@'localhost' ;

⑨ 设置强制角色(mandatory role)

强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被revoke 或者 drop 。

方式1：服务启动设置

[mysqld]
mandatory_roles='role1,role2....'

方式2：运行时设置

SET PERSIST mandatory_roles='role1,role2...';
SET GLOBAL mandatory_roles='role1,role2...';