权限管理
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; # 刷新权限