MySQL关于用户的操作

• 创建一个用户并为该用户设置密码

create user lijiahui@localhost identified by '123456';

创建了一个用户，用户名是lijiahui，密码是123456；但是只能在本地登录！

如果想创建一个用户可以远程访问，怎么创建呢？

create user lijiahui2 identified by '123456';

去掉@localhost的范围限制，任何机器都可以用lijiahui访问MySQL服务器了。

• 查看一下用户

select user, host from mysql.user;

user	host
lijiahui2	%
lijiahui	localhost
mysql.session	localhost
mysql.sys	localhost
root	localhost

用户lijiahui2可以在远程访问MySQL服务器，而其他用户只能在本地访问。

• 查看用户权限

show grants for 'zhangpeining'@'%';

• 授予 用户 ['zhangpeining'@'%'] 对 数据库['test'] 所有表['*'] 权限[select]

grant select on test.* to 'zhangpeining'@'%';

• 收回 用户 ['zhangpeining'@'%'] 对 数据库['test'] 所有表['*'] 权限[select]

revoke select on test.* from 'zhangpeining'@'%';

• 授予 用户 ['zhangpeining'@'%'] 对 所有数据库['*'] 所有表['*'] 所有权限[all privileges]

grant all privileges on *.* to 'zhangpeining'@'%';

• 授予 用户 ['zhangpeining'@'%'] 对 所有数据库['*'] 所有表['*'] 所有权限[all privileges] 并且还可以授权

grant all privileges on *.* to 'zhangpeining'@'%' with grant option;

• 删除用户

drop user 'zhangpeining'@'%';

• 刷新权限

flush privileges;

• 为用户['zhangpn'@'%']修改密码

set password for 'zhangpn'@'%' = password('888888');

如果是为当前用户修改密码，就可以省略[for 用户名]！

set password = password('666666');