mysql8创建用户，授权

进入MySQL命令行

1. 黑窗口：进到mysql的安装目录bin的目录下

C:\mysql\mysql-8.0.16-winx64\bin>mysql -u root -p
Enter password:

2. Navicat：F6进入命令窗口

使用mysql库

mysql> use mysql

创建用户

mysql> create user 'test2'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

# 创建用户，ip填'%'表示允许所有ip
create user 'username'@'ip' IDENTIFIED by 'password';

# 允许远程客户端通过密码访问
alter user 'username'@'ip' IDENTIFIED WITH mysql_native_password by 'password';
 
# 直接创建能够通过远程客户端访问的账户
create user 'username'@'ip' IDENTIFIED WITH mysql_native_password by 'password';

授权用户

## 授权
mysql> GRANT SELECT ON *.* TO `test2`@`%`;
Query OK, 0 rows affected (0.01 sec)
## 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 授予部分权限，db和table填*表示所有
grant SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, CREATE VIEW, SHOW VIEW, CREATE TEMPORARY TABLES on dbname.tablename to 'username'@'ip';
 # 授予所有权限，
grant ALL PRIVILEGES on dbname.tablename to 'username'@'ip';

查看权限

mysql> show grants for 'test2'@'%';
+-----------------------------------+
| Grants for test2@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `test2`@`%` |
+-----------------------------------+
1 row in set (0.04 sec)

撤权用户

revoke select on *.* from 'test2'@'%';

# 撤销所有授权
revoke all on *.* from 'username'@'ip';

## 刷新权限
flush privileges;

修改密码

set password for 'username'@'ip'='new_password';

修改用户

 rename user 'username'@'ip' to 'new_username'@'new_ip';

删除用户

drop user 'username'@'ip';

drop user 'test2'@'localhost';

MySQL提供的特权