Mysql权限管理应用 mysql权限管理表

一、权限系统

  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;