How to create a new user and authorize MySQL

mysql> SELECT VERSION();                     -- CheckMySQLVersion 
+-----------+
| VERSION() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql> select user,host from ;      -- QueryMySQLAll user information
+-----------+-----------+
| user      | host      |
+-----------+-----------+
|  | localhost |
| root      | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'root'@'localhost';    -- Check'root'@'localhost'Corresponding permissions
+---------------------------------------------------------------------+
| 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> 

in'root'@'localhost'Corresponding permission explanation

These are MySQL Authorization statements in the database，Used to grant user permissions。The following are explanations of these statements：

1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION:
    ALL PRIVILEGES：Grant all permissions to the specified user。
    *.*：Acts on all databases and tables。
    'root'@'localhost'：Grant permissions to the username 'root', the host name is 'localhost' Users。
    GRANT OPTION：Grant this user permissions to authorize other users。
2. GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION:
     PROXY: Grants user agent permissions to allow users to perform database operations as other users.
     ''@'': The proxy user is empty, which means that any user is allowed to proxy any other user.
    'root'@'localhost': Grant the proxy permissions to the username 'root', the host name is 'localhost' user.
    GRANT OPTION: Grants permissions to authorize other users to other users.
 To sum up, the function of these statements is to grant users 'root'@'localhost' All databases and table permissions and allow that user to authorize other users.  At the same time, the user was grantedroot'@'localhost' Permission to proxy any user to perform database operations。

SHOW GRANTS FOR ‘root'@‘%'; 

1.Create a user
   create user 'wmlc_dev'@'%' identified by '123456';
   Create a user名forwmlc_dev，The password is123456Users（wmlc_dev，123456Can be specified），'wmlc_dev'@'%' Inside%means allowing from any host（'%'）connect；
   If you specify a host, you can%Change to the one you want to specifyIPJust
2.Grant permissions
   grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%';
   Grant usernamewmlc_dev，The database is` wmlc `Query、insert、Permissions to update and delete operations。（wmlc It's the database name，Can be specified）;
   If you want to grant new users permission to select and lock tables on all databases and all tables, you can change it、 wmlc.* for  *.* Just
3.Refresh permission cache
   flush privileges;
4.做connect测试。

mysql> CREATE DATABASE wmlc CHARACTER SET utf8;     -- Create the database we need
Query OK, 1 row affected (0.00 sec)

mysql> show databases;                              -- Query the database
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wmlc               |
| yeb                |
+--------------------+
6 rows in set (0.02 sec)

mysql> create user 'wmlc_dev'@'%' identified by '123456';  -- Create a new user
Query OK, 0 rows affected (0.01 sec)

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

mysql> grant select, insert, update, delete on wmlc.* to 'wmlc_dev'@'%';    -- Authorize new users
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';          --Query the permissions of new users
+--------------------------------------------------------------------+
| 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;                          -- refresh
Query OK, 0 rows affected (0.02 sec)

mysql> exit;
Bye
root@43e97fa6b64f:/# mysql -uwmlc_dev -p123456 -- Test whether the created new account can be logged inmysql: [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;
-- When we create the table, we find that we do not have permission to create it
ERROR 1142 (42000): CREATE command denied to user 'wmlc_dev'@'localhost' for table 't_sys_user'

-- Adding two permissions CREATE：Allows creation of new databases and tables。DROP：Allows deletion of databases and tables。（Add permissions requires switching back to an account that can add permissions，I won't give too much introduction here，I'm switching backrootAuthorized by account
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)
-- Then log in to our newly createdwmlc_devJust log in to the account
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)

To deleteMySQLSome permissions of users，You can use the following methods：

 1.Log inMySQLserver，userootOpen the command line terminal window with user identity(You can also have other authorized accounts)。
 2.确定To deletePermissions的用户。like果To delete多个用户的Permissions，Please repeat the following steps under each username。
 3.Run the following command to revoke specific permissions for users on specific databases
   REVOKE <privilege> ON <database_name>.<table_name> FROM '<user_name>@<host>';
 in，<privilege>It's the permission to be revoked，likeSELECT、INSERT、UPDATE、DELETEwait。
      <database_name>It is the database name to revoke permissions，
      <table_name>The name of the table to revoke permissions，
      <user_name>是To deletePermissions的用户名，
      <host>It is the user's host name。
      
例like，like果To delete名为 wmlc_dev The user is named wmlc all tables in the databaseCREATEandDROP Permissions，You can run the following command：
REVOKE  CREATE, DROP ON wmlc.* FROM 'wmlc_dev'@'%';
运行以下命令以刷新Permissions，For the change to take effect：
FLUSH PRIVILEGES;
After completing the above steps，指定用户的特定Permissions应该已被删除。
Please note，like果删除了一个用户在所有数据库and表上的所有Permissions，The user will not be able to connect toMySQLserver。

-- We deleted here we just added two permissions CREATE：Allows creation of new databases and tables。DROP：Allows deletion of databases and tables
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)

-- Assume that the new username is'wmlc_dev'@'%'
mysql> SHOW GRANTS FOR 'wmlc_dev'@'%';
-- Add permissions（Create a database/surface,Delete the database/surface,Query,Add to,Revise,delete）
mysql> grant CREATE, DROP, select, insert, update, delete on *.* to 'develop'@'%';
-- Revoke all user permissions
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'wmlc_dev'@'%';
mysql> FLUSH PRIVILEGES;
 
-- delete用户在授权surface中的记录（如果直接在授权surface中Add to了记录）
mysql> DELETE FROM  WHERE User='wmlc_dev' AND Host='%';
mysql> FLUSH PRIVILEGES;