MySQL 用户管理

1 用户的作用
Linux用户：
登录Linux系统
管理Linux对象： 文件（一切皆文件）

MySQL用户：
登录MySQL数据库
管理MySQL对象： 表（一切皆表）

2 用户的定义（长成啥样？）
Linux用户： 用户名
MySQL用户： 用户名@'白名单'
白名单？
地址列表，允许白名单的IP登录MySQL，管理MySQL

oldguo@'localhost'    : oldguo用户能够通过本地登录MySQL（socket）
oldguo@'10.0.0.10'    :    oldguo用户能够通过10.0.0.10远程登录MySQL服务器
oldguo@'10.0.0.%'    :    oldguo用户能够通过10.0.0.xx/24远程登录MySQL服务器
oldguo@'10.0.0.5%'    :    .....50-59.....
oldguo@'10.0.0.0/255.255.254.0'
oldguo@'%'
oldguo@'db01'
oldguo@'db01.oldguo.com'

3 用户管理
查：

# 5.7
mysql> select user,host,plugin,authentication_string from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+
| user          | host      | plugin                | authentication_string                     |
+---------------+-----------+-----------------------+-------------------------------------------+
| root          | localhost | mysql_native_password |                                           |
| mysql.session | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-----------------------+-------------------------------------------+
3 rows in set (0.01 sec)

# 8.0
mysql> select user,host,plugin,authentication_string from mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost | caching_sha2_password |                                                                        |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.04 sec)

增：

# 5.7
mysql> create user olding@localhost identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,plugin,authentication_string from mysql.user;
+---------------+-----------+-----------------------+-------------------------------------------+
| user          | host      | plugin                | authentication_string                     |
+---------------+-----------+-----------------------+-------------------------------------------+
| root          | localhost | mysql_native_password |                                           |
| mysql.session | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| olding        | localhost | mysql_native_password | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+---------------+-----------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)


# 8.0
mysql> create user olding@localhost identified by 'mysql';
Query OK, 0 rows affected (0.38 sec)

mysql> create user olding2@localhost identified with mysql_native_password by 'mysql';
Query OK, 0 rows affected (0.12 sec)

mysql> select user,host,plugin,authentication_string from mysql.user;
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| user             | host      | plugin                | authentication_string                                                  |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| olding           | localhost | caching_sha2_password | $A$005$?_\* 
                                                                    roM|Tps%wxrdOJk/mQ54NEmFCEdIixaou9GFxC9GesznfKTSJ/5E0       |
| olding2          | localhost | mysql_native_password | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA                              |
| root             | localhost | caching_sha2_password |                                                                        |
+------------------+-----------+-----------------------+------------------------------------------------------------------------+
7 rows in set (0.00 sec)

[root@master /data/3306/mysql]# strings user.MYD
    localhost
root
mysql_native_password
    localhost
mysql.session
mysql_native_password)
*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
    localhost    mysql.sys
mysql_native_password)
*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
    localhost
olding
mysql_native_password)
*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA

改：

mysql> alter user olding@localhost identified by 'mysql';

删：

mysql> drop user oldding@localhost;

注意：8.0版本以前，是可以通过grant命令 建立用户+授权