一、用户管理
1、查询当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
2、创建用户
mysql> create user cp@'10.0.0.%' identified by '000000';
注：用户的格式为：用户名@'白名单'。
白名单一般有以下几种写法：
'localhost'
'10.0.0.52'
'10.0.0.%'
'10.0.0.5%'
'10.0.0.0/255.255.255.0'
'%'
3、查询用户信息
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| cp | 10.0.0.% | *032197AE5731D4664921A6CCAC7CFCE6A0698693 |
+---------------+-----------+-------------------------------------------+
4、修改用户密码
mysql> alter user cp@'10.0.0.%' identified by '123456';
mysql> select user,host,authentication_string from mysql.user where user='cp';
+------+----------+-------------------------------------------+
| user | host | authentication_string |
+------+----------+-------------------------------------------+
| cp | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+----------+-------------------------------------------+
5、删除用户
mysql> drop user cp@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from mysql.user where user='cp';
Empty set (0.00 sec)
二、权限管理
1、查看所有权限
mysql> show privileges;
+----------------+--------------------------+-----------------------------------------------+
| Privilege | Context | Comment |
+----------------+--------------------------+-----------------------------------------------+
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Select | Tables | To retrieve rows from table |
| Insert | Tables | To insert data into tables |
| Update | Tables | To update existing rows |
| Delete | Tables | To delete existing rows |
| Usage | Server Admin | No privileges - allow connect only |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
......其余权限省略
注：展示的几乎所有权限都包含在ALL中，除了Usage 和 Grant option权限
2、授权
# 以下命令表示授予cp@'10.0.0.%'用户对所有库的所有表拥有ALL包含的所有权限
mysql> grant all on *.* to cp@'10.0.0.%';
格式：GRANT 权限 ON 库.表 TO 用户
# 以下命令表示给cp@'localhost'用户授予mysql数据库的所有表的查询、插入、更新、删除权限，如果用户不存在会自动创建。
mysql> grant select,insert,update,delete on mysql.* to cp@'localhost' identified by '000000';
3、查看用户权限信息
mysql> show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
mysql> show grants for cp@'10.0.0.%';
+------------------------------------------------+
| Grants for cp@10.0.0.% |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'cp'@'10.0.0.%' |
+------------------------------------------------+
通过以上我们可以发现，前面我们给cp用户赋予了ALL权限，而它对比root用户，没有Grant option权限（即给其它人赋予权限的权限）。
4、移除授权
mysql> revoke delete on mysql.* from cp@'localhost';
mysql> show grants for cp@'localhost';
+---------------------------------------------------------------+
| Grants for cp@localhost |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cp'@'localhost' |
| GRANT SELECT, INSERT, UPDATE ON `mysql`.* TO 'cp'@'localhost' |
+---------------------------------------------------------------+
三、8.0版本后用户和权限管理相关的变化
1、用户加密套件的变化
8.0后加密套件不在采用mysql_native_password，而是使用caching_sha2_password 。
# 5.7.28
mysql> select user,host,plugin from mysql.user;
+---------------+-----------+-----------------------+
| user | host | plugin |
+---------------+-----------+-----------------------+
| root | localhost | mysql_native_password |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| cp | localhost | mysql_native_password |
+---------------+-----------+-----------------------+
# 8.0.20
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| cp | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
# 更改加密套件
mysql> alter user cp@'localhost' identified with mysql_native_password by '000000';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,authentication_string,plugin from mysql.user where user='cp';
+------+-----------+-------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-----------+-------------------------------------------+-----------------------+
| cp | localhost | *032197AE5731D4664921A6CCAC7CFCE6A0698693 | mysql_native_password |
+------+-----------+-------------------------------------------+-----------------------+
2、grant命令不再支持自动创建用户
必须先创建好用户，再授予权限。
mysql> grant all on mysql.* to cp@'localhost' identified by '000000';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '000000'' at line 1
mysql> create user cp@'%' identified by '000000';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on *.* to cp@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for cp@'%';
+---------------------------------+
| Grants for cp@% |
+---------------------------------+
| GRANT SELECT ON *.* TO `cp`@`%` |
+---------------------------------+