MySQL添加用户以及分配权限等命令

MySQL5.7

授予权限并更新密码（ 账号不存在会创建账号 ）

1
GRANT ALL PRIVILEGES ON *.* TO 'ddd'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

撤销用户权限

1
REVOKE ALL PRIVILEGES ON *.* FROM 'ddd'@'%';

使得更改生效

1
FLUSH PRIVILEGES;

权限列表

1
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, Index, Insert, Lock Tables, Process, References, Reload, Replication Client, Replication Slave, Select, Show Databases, Show View, Shutdown, Super, Trigger, Update ON *.* TO `ddd`@`%`;

MySQL8.0

8.0 不能通过 GRANT 创建账号，所以需要先创建账号

1
CREATE USER 'ddd'@'%' IDENTIFIED BY '123456';

默认是 caching_sha2_password 验证方式的账号，如果客户端不支持 caching_sha2_password 认证，可以在创建账号或者更新账号的时候加上 WITH mysql_native_password

创建

1
CREATE USER 'ddd'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

更新

1
ALTER USER 'ddd'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

改回来

1
ALTER USER 'ddd'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';

授予权限

8.0版本不能授予自己的权限

1
GRANT ALL PRIVILEGES ON *.* TO 'ddd'@'%' WITH GRANT OPTION;

撤销用户权限

1
REVOKE ALL PRIVILEGES ON *.* FROM 'ddd'@'%';

使得更改生效

1
FLUSH PRIVILEGES;

权限列表

1
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, Index, Insert, Lock Tables, Process, References, Reload, Replication Client, Replication Slave, Select, Show Databases, Show View, Shutdown, Super, Trigger, Update ON *.* TO `ddd`@`%`;