用户管理

• 创建用户

mysql> create user root@'%' identified by '123';

#8.0以前，下面语句,可以自动创建用户并授权
mysql> grant all on *.* to root@'%' identified by '123';


#8.0以后, 需要先创建用户在授权
#8.0使用navicat连接
mysql> create USER root@'%' IDENTIFIED WITH mysql_native_password BY 'TST-cccaaa1!';
mysql> grant all on *.* to root@'%' with grant option;

• 查询用户

mysql> select user,host from mysql.user;

• 修改用户密码

mysql> alter user root@'localhost' identified by '123456';

• 删除用户

mysql> drop user oldguo@'10.0.0.%' ;

• 授权

#权限列表
#SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

# 赋予所有权限,不包括授权
mysql> grant all on *.* to root@'%' identified by '123';

# 赋予授权权限
mysql> grant all on *.* to root@'%' identified by '123' with grant option;

• 查看权限

mysql> show  grants for wordpress@'10.0.0.%';

• 回收权限

mysql> revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';