一、权限系统
MySQL数据库中使用3种不同类型的安全检查：登录验证、授权、访问控制。
二、权限表
MySQL权限表存储在名为mysql的数据库中，常用的表有user、db、tables_priv、columns_priv、procs_priv。进行身份验证时也按照上述的顺序依次查找验证。
1. user表
字段 字段名 说明 用户字段 host、user、password、… 权限字段 …_priv 包含几十个 安全字段 ssl_type
ssl_cipher
x509_issuer
x509_subject ssl用于加密
x509用户标识用户
plugin是用户验证用户身份的插件 资源控制列 max_questions
max_updates
max_connections
max_user_connections 用户每小时允许的查询操作次数
用户每小时允许的更新操作次数
用户每小时允许执行的连接操作次数
单个用户可以同时具有的连接次数。
以上默认值都为0，表示无限制次数
MySQL5.7 版本的mysql.user表中，password字段改成authentication_string。
2. db表和host表
- db表中存储用户对某个数据库的操作权限，决定用户能从那个主机存取哪个数据库。
- host表中存储了某个主机对数据库的操作权限，配合db表对给定主机上数据库级操作权限有更细致的控制。
这两个表不受grant、revoke语句的影响。
user表中的权限是针对所有数据库的。如果user表中的select_ priv字段取值为y,那么该用户可以查询所有数据库中的表。如果为某个用户只设置了查询test表的权限，那么user表的select_ priv字段的取值为n。而这个select权限则记录在db表中，db 表中select_ priv 字段的取值将会是y。 由此可知，用户先根据user表的内容获取权限，然后再根据db表的内容获取权限。
3. tables_priv表
tables_priv表对单个表进行权限设置，用来指定表级权限，权限用于一个表的所有列。
4. columns_priv表
columns_priv表对表的某一列进行权限设置。
5. procs_priv表
procs_priv表对存储过程和函数进行权限设置。
三、用户管理
1. 添加用户
1.1 create user语句
create user user_name[@host_name] [identified by passwd] [login policy policy_name] [force password change {on | off}]
参数 说明 user_name 用户名 host_name 主机名，省略时默认为%，表示一组主机 passwd 密码 policy_name 用户登录的策略名称 force password change 指定用户登录时是否需重新设置新密码
注意点 ：
- user_name、passwd必须是有效的标识符：不能以空格、单引号、双引号开头，不能以空格结尾，不能含有分号。
- 使用create user语句必须具有 对mysql数据库的INSERT权限或全局的CREATE USER权限。
- 新创建的用户不能访问相关的数据库或表。
1.2 insert语句
可以直接使用insert语句将用户信息插入到mysql.user表中，但需要具有 对user表的INSERT权限。用于user表中字段较多，要保证没有默认值的字段一定要给出值，即至少插入6个字段的值： host、user、password、ssl_cipher、x509_issuer、x509_subject。插入数据之后需要执行下面语句来使用户生效，这个命令需要 RELOAD权限。
-- 插入数据到mysql.user表中
insert into mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values('host_aame', 'user_name', password('passwd'), '', '', '');
-- 使新用户生效
flush privileges;
2. 查看用户
select * from mysql.user [where host='host_name' and user='user_name'];
3. 修改用户信息
3.1 修改用户账户
修改用户账户需要具有 对mysql数据库的UPDATE权限或全局的CREATE USER权限。
rename user user_name@host_name to user_name@new_host [, ...];
3.2 修改用户密码
（1）mysqladmin命令
mysqladmin -uuser_name -p password
输入上述命令之后，根据提示输入旧密码和新密码即可。
例子：将kate密码由kate90改为1234。
（2）set语句
set password [for 'user_name'@'host_name'] = password('new_passwd');
可选参数省略时为修改当前用户的密码。
（3）update语句
-- 更新数据到mysql.user表中
update mysql.user set authentication_string = password('new_passwd')
where user = 'user_name' and host = 'host_name';
-- 使修改生效
flush privileges;
此方法需要具有 对mysql数据库的UPDATE权限和RELOAD权限。
4. 删除用户
4.1 drop语句
drop user user_name@host_name;
4.2 delete语句
-- 删除mysql.user表的数据
delete from mysql.user where user = 'user_name' and host = 'host_name';
-- 时删除生效
flush privileges;
四、账户权限管理
1. 权限赋予
1.1 语法格式
grant priv_type[(column_list)] [, priv_type[(column_list)]]
on [object_type] priv_level
to user_specification
[with with_option...];
1.2 参数说明
参数 参数值 说明 priv_type 见下表 权限类型 column_list string 列名 object_type table、function、procedure 对象类型 priv_level 见下表 权限级别 user_specification ‘user_name’@’host_name’ 用户信息 with_option – 用于实现权限转移或限制
priv_type参数值 说明 select 赋予使用select语句的权限 insert 赋予使用insert语句的权限 update 赋予使用update语句的权限 delete 赋予使用delete语句的权限 references 赋予创建外键约束的权限 create 赋予创建数据表的权限 alter 赋予使用alter table语句修改数据库的权限 index 赋予定义索引的权限 drop 赋予删除数据表的权限 all all privileges
赋予所有的权限
priv_level参数值 说明 *
当前数据库的所有表 *.*
所有数据库的所有表 tb_name 当前数据库的某个表或视图 db_name.* 某个数据库的所有表 db_name.tb_name 某个数据库的某个表或视图 db_name.routine_name 某个数据库的某个存储过程或函数
1.3 例子
grant select(sno, sname)
on table student
to 'lili'@'localhost';
这个是赋予用户lili有使用select语句查询student数据表中sno和sname字段的权限。
grant select, update
on studentinfo.student
to 'liming'@'localhost'
identified by '123';
liming这个用户在系统中是还不存在的，这段代码是创建用户liming有使用select、update语句查询和修改studentinfo数据库中student表的权限，同时设置登录密码为123。
2. 权限转移
将with_option改为 GRANT OPTION可将to指定的用户拥有把自己的权限授予给其他用户的权力。
3. 权限限制
with_option也可以使用下面的参数+参数值的方式限制用户权限。
参数名 说明 max_queries_per_hour count 限制每小时可以查询数据库的次数 max_updates_per_hour count 限制每小时可以修改数据库的次数 max_connections_per_hour count 限制每小时可以连接数据库的次数 max_user_connections count 限制同时连接MySQL的最大用户数
上述中，count为非负整数值，0为默认值，表示无穷大。
4. 权限撤销
使用revoke语句，必须要有 MySQL数据库的全局create user权限或update权限。
-- 收回某些特定权限
revoke priv_type[(column_list)] [, priv_type[(column_list)]]
on [object_type] priv_level
from user_specification;
-- 收回特定用户的所有权限
revoke all privileges, grant option from user_specification;
Original: https://www.cnblogs.com/bpf-1024/p/14060100.html
Author: 步平凡
Title: NoteOfMySQL-11-权限管理
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/612021/
转载文章受原作者版权保护。转载请注明原作者出处！