MySQL的用户授权

MySQL的用户授权

什么是用户授权

在数据库服务器上添加新的连接用户，在添加用户时可以设置访问权限及连接密码，默认数据库管理员root用户本机登录有授权权限

grant授权

授权：添加用户并设置权限

授权命令格式

grant 权限列表 on 库名.表名 to 用户名@"客户端地址"

 identified by "密码"  #授权用户密码

with grant option;  #有授权权限，可选项

权限列表：(有多个权限时，用’ , '分隔)

• all #所有权限
• usage #无权限
• select、update、insert #个别权限
• select、update(字段1…字段N) #指定字段

库名：

• *. *#所有库列表
• 库名.* #一个库
• 库名.表名 #一张表

用户名：

• 授权时自定义要有表示性
• 存储在mysql库的user表里

客户端地址

• % #所有主机
• 192.168.4.% #网段内的所有主机
• 192.168.4.1 #一台主机
• localhost #数据库服务器本机

授权例子:

添加用户mydba，对所有库、表有完全权限

允许从任何客户端连接，密码123456

且有授权权限

MySQL服务器：192.168.4.3
测试机器：192.168.4.133
grant all on *.* to mydba@"%" identified by "123456" with grand option;

#在测试机器上测试：（测试机器上要有MySQL的客户端）
#也可以使用mariadb代替
mysql -umydba -p123456 -h192.168.4.3
show databases;    #显示所有数据库
drop database db1;   #删除db1数据库，测试权限
create database db1;  #创建db1数据库，测试权限
create table db1.user(id int);   #创建表测试权限
desc db1.user;
insert into db1.user values(1); #插入数据，测试权限
select * from db1.user;    #查找数据测试权限
grant select,insert,update(password,uid)on db3.user to test@'localhost' identified by '123456';  #测试授权权限

#192.168.4.133(test主机）
#测试刚刚的授权用户
mysql -u test -p123456 -h 192.168.4.3
#因为刚才创建这个用户只能本机登录，所以我们在test主机不能登录
#192.168.4.3（数据库服务器）
 #测试刚刚的授权用户 
 mysql -u test -p123456 -hlocalhost
 #测试test用户的权限
# grant select,insert,update(password,uid)on db3.user to test@'localhost' identified by '123456'; 
show databases;    #显示所有数据库
#这时我们还能看到一个库，这个库是个虚拟库，他使内存映射出的一个库
delete from db3.user;   #删除数据，测试权限
select * from db3.user;    #查找数据测试权限
insert into db3.user values(30,"test",20,"A",20,20,"test","/test","/bash"); #插入数据，测试权限
update db3.user set password='' where id=0;  #修改数据查看权限

相关命令

登录用户使用

命令	作用
select user();	显示登录用户名及客户端地址
show grants;	用户显示自身访问权限
show grants for 用户名@“客户端地址”;	管理员查看已有授权用户权限
set password=password(“密码”);	授权用户连接后修改修改连接密码
set password for 用户名@“客户端地址”=password(“密码”);	管理员重置授权用户连接密码
drop user 用户名@“客户端地址”;	删除授权用户（必须有管理员权限）

授权库mysql（记录授权信息mysql库的管理）

mysql库记录授权信息，主要表如下

• user表 #记录已有的授权用户及权限
• db表 #记录已有授权用户对数据库的访问权限
• tables_priv表 #记录已有授权用户对表的访问权限
• columns_priv 表 #记录已有授权用户对字段的访问权限

可以查看表记录，查看到授权用户的权限信息，也可以通过修改记录，修改用户的权限

show tables;
#user表
desc mysql.user;
select host,user from mysql.user ;  #查看当前数据库的授权用户
select * from mysql.user where host='%' and user='mydba' \G; #查看mydba用户的详细权限

#db表
desc mysql.db;
select host,db,user from mysql.db;
select * from mysql.db where host="%" and db="school" and user="tom" \G;   #查看登录本机的test用户对db3
update mysql.db set Update_priv="N",Delete_priv="N"  where host="%" and db="school" and user="tom"  ;
#通过修改记录来修改权限
FLUSH PRIVILEGES;  #修改完之后需要刷新权限表
select * from mysql.db where host="%" and db="school" and user="tom" \G;  #再次查看

#tables_priv表
desc mysql.tables_priv;
select * from mysql.tables_priv; 
select * from mysql.tables_priv where db='db3' and table_name="user" \G;
show grants for test@"localhost";
 
 #columns_priv 表
 desc mysql.columns_priv;
 select * from mysql.columns_priv;

撤销权限

删除已有用户的权限

命令格式：

revoke 权限列表 on 库名.表名 from 用户名@“客户端地址” ;

select user,host from mysql.user;  #查看目前数据库有哪些授权用户
show grants for mydba@'%';  #查看他的权限
revoke grant option on *.* from mydba@'%';   #撤销他的授权权限
show grants for mydba@'%';  #查看他的权限
revoke delete on *.* from mydba@'%'; #撤销他的删除权限
show grants for mydba@'%';  #查看他的权限
revoke all on *.* from mydba@'%';   #删除他的所有权限
show grants for mydba@'%';  #查看他的权限

删除授权用户

drop user 用户名@“客户端”;

drop user mydba@"%";
select host,user from mysql.user ;

管理root密码恢复密码

root密码忘记了怎么办

1. 停止MySQL服务程序
2. 跳过授权表启动MySQL服务程序
3. 修改root密码
4. 以正常方式启动MySQL服务程序

更多恢复root密码的方法请点击：MySQL修改密码或者重置密码

systemctl stop mysqld
vim /etc/my.cnf
[mysqld]
skip-grant-tables    #跳过权限表
#有在配置文件中修改密码策略的需要注释掉
#validate_password_policy=0
#validate_password_length=6

systemctl start mysqld
mysql
select host,user,authentication_string from mysql.user;
#authentication_string 存放的为密码加密之后的样子
update mysql.user set authentication_string=password("123456")
where host='localhost' and user='root' ;
flush privileges;
\q

vim /etc/my.cnf
#skip-grant-tables
validate_password_policy=0
validate_password_length=6
systemctl restart mysqld
#正常登录就行

修改密码：

mysqladmin  -h localhost -u root -p  password ”123456“