mysql> SELECT VERSION();                     -- 查看MySQL版本 
+-----------+
| VERSION() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql> select user,host from mysql.user;      -- 查詢MySQL的所有用戶信息
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';    -- 查看'root'@'localhost'對應的權(quán)限
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

其中'root'@'localhost'對應的權(quán)限解釋

這些是 MySQL 數(shù)據(jù)庫中的授權(quán)語句，用于授予用戶權(quán)限。下面是這些語句的解釋：

1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION:
   1.GRANT ALL PRIVILEGES：授予所有權(quán)限給指定的用戶。
   2.ON *.*：作用于所有的數(shù)據(jù)庫和表。
   3.TO 'root'@'localhost'：將權(quán)限授予用戶名為 'root'、主機名為 'localhost' 的用戶。
   4.WITH GRANT OPTION：授予該用戶授權(quán)其他用戶的權(quán)限。
2. GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION:
   1.GRANT PROXY：授予用戶代理權(quán)限，允許用戶以其他用戶的身份進行數(shù)據(jù)庫操作。
   2.ON ''@''：代理用戶為空，即允許任何用戶代理任何其他用戶。
  3.TO 'root'@'localhost'：將代理權(quán)限授予用戶名為 'root'、主機名為 'localhost' 的用戶。
  4.WITH GRANT OPTION：授予該用戶授權(quán)其他用戶的權(quán)限。
總結(jié)起來，這些語句的作用是授予用戶 'root'@'localhost' 所有的數(shù)據(jù)庫和表的權(quán)限，并允許該用戶授權(quán)其他用戶。同時，還授予了用戶 'root'@'localhost' 代理任何用戶進行數(shù)據(jù)庫操作的權(quán)限。

SHOW GRANTS FOR ‘root'@‘%'; 

1.創(chuàng)建用戶
   create user 'wmlc_dev'@'%' identified by '123456';
   創(chuàng)建用戶名為wmlc_dev，密碼是123456的用戶（wmlc_dev，123456可指定），'wmlc_dev'@'%' 里的%是指允許從任何主機（'%'）連接；
   如果要是指定主機的話可以把%換為要指定的IP即可
2.授予權(quán)限
   grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%';
   授予用戶名為wmlc_dev，數(shù)據(jù)庫為` wmlc `的查詢、插入、更新和刪除操作的權(quán)限。（wmlc 是數(shù)據(jù)庫名稱，可指定）;
   如果想授予新用戶在所有數(shù)據(jù)庫和所有表上選擇和鎖定表的權(quán)限的話可改、 wmlc.* 為  *.* 即可
3.刷新權(quán)限緩存
   flush privileges;
4.做連接測試。

mysql> CREATE DATABASE wmlc CHARACTER SET utf8;     -- 創(chuàng)建我們需要的數(shù)據(jù)庫
Query OK, 1 row affected (0.00 sec)

mysql> show databases;                              -- 查詢數(shù)據(jù)庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wmlc               |
| yeb                |
+--------------------+
6 rows in set (0.02 sec)

mysql> create user 'wmlc_dev'@'%' identified by '123456';  -- 創(chuàng)建新用戶
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;                 
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| wmlc_dev  | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%';    -- 給新用戶授權(quán)
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';          --查詢新用戶的權(quán)限
+--------------------------------------------------------------------+
| Grants for wmlc_dev@%                                              |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wmlc_dev'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wmlc`.* TO 'wmlc_dev'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  flush privileges;                          -- 刷新
Query OK, 0 rows affected (0.02 sec)

mysql> exit;
Bye
root@43e97fa6b64f:/# mysql -uwmlc_dev -p123456      -- 測試創(chuàng)建的新賬號是否可以登錄
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wmlc               |
+--------------------+
2 rows in set (0.01 sec)

mysql> use wmlc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE TABLE `t_sys_user`  (
    ->   `id` int(11) NOT NULL,
    ->   `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '',
    ->   PRIMARY KEY (`id`) USING BTREE
    -> ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 當我們創(chuàng)建表時會發(fā)現(xiàn)沒有權(quán)限創(chuàng)建
ERROR 1142 (42000): CREATE command denied to user 'wmlc_dev'@'localhost' for table 't_sys_user'

-- 在添加兩個權(quán)限 CREATE：允許創(chuàng)建新的數(shù)據(jù)庫和表。DROP：允許刪除數(shù)據(jù)庫和表。（添加權(quán)限需要切換回可以添加權(quán)限的賬號，這里我就不做過多介紹了，我是又切換回root賬號進行授權(quán)的
mysql> grant CREATE, DROP on wmlc.* to 'wmlc_dev'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%'; 
+----------------------------------------------------------------------------------+
| Grants for wmlc_dev@%                                                            |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wmlc_dev'@'%'                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `wmlc`.* TO 'wmlc_dev'@'%' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.02 sec)
-- 然后在重新登錄到我們新建的wmlc_dev賬號登錄操作就可以了
mysql> DROP TABLE IF EXISTS `t_sys_user`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t_sys_user`  (
    ->   `id` int(11) NOT NULL,
    ->   `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '',
    ->   PRIMARY KEY (`id`) USING BTREE
    -> ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Query OK, 0 rows affected (0.01 sec)

要刪除MySQL用戶的某些權(quán)限，您可以使用以下方法：

 1.登錄到MySQL服務器，使用root用戶身份打開命令行終端窗口(有其它授權(quán)賬號也可以)。
 2.確定要刪除權(quán)限的用戶。如果要刪除多個用戶的權(quán)限，請在每個用戶名下重復以下步驟。
 3.運行以下命令以撤銷用戶在特定數(shù)據(jù)庫上的特定權(quán)限
   REVOKE <privilege> ON <database_name>.<table_name> FROM '<user_name>@<host>';
 其中，<privilege>是要撤銷的權(quán)限，如SELECT、INSERT、UPDATE、DELETE等。
      <database_name>是要撤銷權(quán)限的數(shù)據(jù)庫名稱，
      <table_name>是要撤銷權(quán)限的表名稱，
      <user_name>是要刪除權(quán)限的用戶名，
      <host>是用戶的主機名。
      
例如，如果要刪除名為 wmlc_dev 的用戶在名為 wmlc 的數(shù)據(jù)庫中所有表的CREATE和DROP 權(quán)限，可以運行以下命令：
REVOKE  CREATE, DROP ON wmlc.* FROM 'wmlc_dev'@'%';
運行以下命令以刷新權(quán)限，以便更改生效：
FLUSH PRIVILEGES;
完成上述步驟后，指定用戶的特定權(quán)限應該已被刪除。
請注意，如果刪除了一個用戶在所有數(shù)據(jù)庫和表上的所有權(quán)限，該用戶將無法連接到MySQL服務器。

-- 我們這里刪除了剛才我們添加兩個權(quán)限 CREATE：允許創(chuàng)建新的數(shù)據(jù)庫和表。DROP：允許刪除數(shù)據(jù)庫和表
mysql> REVOKE  CREATE, DROP ON wmlc.* FROM 'wmlc_dev'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';
+--------------------------------------------------------------------+
| Grants for wmlc_dev@%                                              |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wmlc_dev'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wmlc`.* TO 'wmlc_dev'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

-- 假設新用戶名為'wmlc_dev'@'%'
mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';
-- 添加權(quán)限（創(chuàng)建數(shù)據(jù)庫/表,刪除數(shù)據(jù)庫/表,查詢,添加,修改,刪除）
mysql> grant CREATE, DROP, select, insert, update, delete on *.* to 'develop'@'%';
-- 撤銷用戶的所有權(quán)限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'wmlc_dev'@'%';
mysql> FLUSH PRIVILEGES;
 
-- 刪除用戶在授權(quán)表中的記錄（如果直接在授權(quán)表中添加了記錄）
mysql> DELETE FROM mysql.user WHERE User='wmlc_dev' AND Host='%';
mysql> FLUSH PRIVILEGES;