Sing Xing interprets mysql User Management _ MySQL

Most of the developers may not pay much attention to this aspect, but when it comes to O & M, we should not be concerned about it. As we all know, the root permission is too large to be used at will. we 'd better use some users with lower permissions at ordinary times, which will greatly improve our security, most of the developers may not pay much attention to this aspect, but when it comes to O & M, we should not be concerned about it. As we all know, the root permission is too large to be used at will. we 'd better use some users with lower permissions at ordinary times, which will greatly improve our security, it can also prevent unnecessary losses due to misoperations.

First, we need to view all the accounts in mysql. we can view the information in the user table of mysql database. However, because we directly select * from user, a lot of permission-related information will be displayed, which will greatly affect our reading. Therefore, we can only view three important fields here, the following is an operation demonstration on my machine:

mysql>   select host,user,password from mysql.user;+-----------+------+-------------------------------------------+| host      | user | password                                  |+-----------+------+-------------------------------------------+| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || ::1       | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |+-----------+------+-------------------------------------------+3 rows in set (0.00 sec)

Then we create a user. here we need to specify the user name and connection address, that is, the same user name (user) and different host will also be treated as different objects, we can use wildcards, where % indicates matching any multiple characters, and-indicates matching one character. Next, I will create a user test with the password "xin". I can log on to any host and check my operations:

mysql> create user 'test'@'%' identified by 'xin';Query OK, 0 rows affected (0.23 sec)

Here, the user login information is shown as follows:

C:\Users\Administrator>mysql -u test -pxinmysql: [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 14Server version: 5.7.3-m13 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

For example, if we want to check what the user information has, we can find that:

mysql> select host,user,password from mysql.user;ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user'mysql>

That is, we do not use the permission to select the user table here, so permission management is involved. here we can use show grants to view our permissions:

mysql> show grants;+-----------------------------------------------------------------------------------------------------+| Grants for test@%                                                                                   |+-----------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*76995602B7611FA37648852F235D6ECB29D844E2' |+-----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Because the permissions of the test user here are still small, we still use the root user to log on. then we can look at the user table and find a new user:

mysql> select host,user,password from mysql.user;+-----------+------+-------------------------------------------+| host      | user | password                                  |+-----------+------+-------------------------------------------+| localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || 127.0.0.1 | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || ::1       | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || %         | test | *76995602B7611FA37648852F235D6ECB29D844E2 |+-----------+------+-------------------------------------------+4 rows in set (0.00 sec)

The root user has the highest permission, so he can change the password. The first method is to use the set password statement. for example, if we set a password for the new user, we can use the following command:

mysql> set  password for 'test'@'%' = password('qian');Query OK, 0 rows affected (0.00 sec)

Now we can use the user test to log on. The following shows my code:

C:\Users\Administrator>mysql -u test -pqianmysql: [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 17Server version: 5.7.3-m13 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

In fact, we can also directly use the update statement to modify the user table, but we need to use flush privileges; to make it take effect immediately:

mysql> update mysql.user set password = password('nan') where user = 'test';Query OK, 1 row affected (0.05 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.03 sec)

Then we can use the 'Nan 'password to log on. I will not provide the demo code here. after all, it is a very simple operation. I am xin xing. please support it.