mysql 创建用户并授予权限_mysql创建用户并赋予数据库访问权限

命令客户端

/opt/mysql-8.0.32/bin/mysql -h127.0.0.1 -P3306 -uroot -pMyPassword21

一、创建用户

命令：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例子：CREATE USER 'user'@'%' IDENTIFIED BY 'password';

二、身份验证插件

mysql 5.7 默认是mysql_native_password
mysql 8.0 默认是caching_sha2_password

指定身份验证插件
CREATE USER 'replicator'@'%' IDENTIFIED WITH caching_sha2_password BY 'myPassword21';
CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'myPassword21';
 
修改身份验证插件
ALTER USER 'replicator'@'%' IDENTIFIED WITH caching_sha2_password BY 'myPassword21';
ALTER USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'myPassword21';

mysql8.0 主从复制 Authentication plugin ‘caching_sha2_password‘ Error_code: MY-002061解决办法

三、修改密码允许远程登录

use mysql
select user,authentication_string,host from user;
alter user 'root'@'localhost' IDENTIFIED BY 'root';
update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;

四、删除用户

命令：DROP USER 'username'@'host';
例子：DROP USER 'user'@'%';

五、授予权限

命令：GRANT privileges ON databasename.tablename TO 'username'@'host'
例子：GRANT ALL ON test.* TO 'user'@'%';
刷新权限：FLUSH PRIVILEGES;
 
# 用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

六、收回权限

命令：REVOKE privilege ON databasename.tablename FROM 'username'@'host';
例子：REVOKE ALL ON test.* FROM 'user'@'%';
刷新权限：FLUSH PRIVILEGES;

七、查看用户权限

命令：SHOW GRANTS FOR 'username'@'hostname';
例子：SHOW GRANTS FOR 'root'@'%';

八、文档

13.7.1 Account Management Statements

13.7.1.1 ALTER USER Statement

13.7.1.2 CREATE ROLE Statement

13.7.1.3 CREATE USER Statement

13.7.1.4 DROP ROLE Statement

13.7.1.5 DROP USER Statement

13.7.1.6 GRANT Statement

13.7.1.7 RENAME USER Statement

13.7.1.8 REVOKE Statement

13.7.1.9 SET DEFAULT ROLE Statement

13.7.1.10 SET PASSWORD Statement

13.7.1.11 SET ROLE Statement