Sinsing interpretation of MySQL user management

Probably do not pay much attention to the development of this aspect, but to the operation of the maintenance. That's a concern. As we all know, Root has too much authority. It's not easy to use. We usually better use some of the less privileged users. This will make our security greatly improved, but also can prevent us from the ordinary due to misoperation caused unnecessary losses.

First we need to look at all the accounts in MySQL, we can view the information in the user table in the MySQL database. But then. Because we directly select * from user will show a lot of information related to the permissions, greatly affect our reading. So we're just looking at three more important fields here, and here's a demo of the operation in 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 make username and connect the address, that is, the same username (user). Not the same host. will also be treated as a different object, and we can use wildcard characters. The% indicates that the match no matter what multiple characters, and-represents a match of one character. Below I create a user test. and make password for Xin, and be able to log in from whatever host. Watch me operate:

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

Here we use the user login information to display demo samples such as the following:

C:\users\administrator>mysql-u test-pxinmysql: [Warning] Using a password on the command line interface can be INSECU Re. 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) +, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

For example, we want to look at what the user information has. It turns out 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 have the right to select the user table. Then it will involve permission management, and we can use show grants to check our permissions:

Mysql> Show grants;+----------------------------------------------------------------------------------------- ------------+| Grants for [email protected]%                                                                                   |+----------------------------------------------------------------------------- ------------------------+| GRANT USAGE on * * to ' test ' @ '% ' identified by PASSWORD ' *76995602b7611fa37648852f235d6ecb29d844e2 ' |+------------------ -----------------------------------------------------------------------------------+1 Row in Set (0.00 sec)

Because the test user's permissions here are still very small. We still logged in with the root user, and then we looked at the user table and found 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 overriding privileges. So he can change password, the first way is to use the Set password statement, for example, we set a password for the new user just now. We can use the following commands, for example:

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

At this point we will be able to use test this user login, the following see my code:

C:\users\administrator>mysql-u test-pqianmysql: [Warning] Using a password on the command line interface can be insec Ure. 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) +, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

In fact we can also use the UPDATE statement to change the user's table directly, but we need to use the flush privileges to get it into 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 ' This password login, I do not give the demo code here, after all, very easy operation, I am Sinsing. Support.

Sinsing interpretation of MySQL user management