数据库---权限管理

权限管理
1、创建账号

# 本地账号
create user 'egon1'@'localhost' identified by '123'; # mysql -uegon1 -p123

 

# 查看所有用户

select host ,user,authentication_string from user;

 

# 修改用户的账户的密码

update user set authentication_string=password('新密码') where user='用户名';

 

# 远程账号
create user 'egon2'@'192.168.31.10' identified by '123'; # mysql -uegon1 -p123 -h 服务端ip
create user 'egon3'@'192.168.31.%' identified by '123'; # mysql -uegon1 -p123 -h 服务端ip
create user 'egon3'@'%' identified by '123'; # mysql -uegon1 -p123 -h 服务端ip

 

# 删除账号

语法：drop user 用户名@主机地址 drop user 'testuser'@'localhost'; 或 delete from user where user='用户名';

2、授权
user:*.*
db:db1.*
table_priv:db1.t1
columns_priv:id,name

设置权限
grant all on *.* to 'egon1'@'localhost';

设置egon1用户下所有库的权限
grant select on *.* to 'egon1'@'localhost';
revoke select on *.* from 'egon1'@'localhost';

设置egon1用户下db3库的权限
grant select on db3.* to 'egon1'@'localhost';
revoke select on db3.* from 'egon1'@'localhost';

设置egon1用户下db3库中t4表的权限
grant select on db3.t4 to 'egon1'@'localhost';
revoke select on db3.t4 from 'egon1'@'localhost';

设置egon1用户下db3库中t4表下的字段的权限
grant select (id,name),update(age) on db3.t4 to 'egon1'@'localhost';
revoke select on db3.t4 from 'egon1'@'localhost';

 

# 修改权限

语法：grant 权限名称 on 数据库 to 账户@主机 with grant option;

-- 将dgon1权限改为对所有库有所有权限 

grant all privileges on *.* to 'egon1'@'localhost' with grant option;

flush privileges; # 刷新权限