Hello, everyone. Meet again. I'm Jun Quan.
Most developers may not pay much attention to this aspect, but it comes to operation and maintenance. Then we have to pay attention. As we all know, root has too much authority. It's not easy to use. We'd better use some users with lower permissions. This will greatly improve our safety and prevent unnecessary losses caused by misoperation in our ordinary work.
First, we need to view all accounts in mysql. We can view the information in the user table in mysql database. But what. Because if we directly select * from user, a lot of permission related information will be displayed, which will greatly affect our reading. Therefore, we only look at three important fields here. The following is the operation demonstration 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 specify the username and the address of the connection, that is, the same username (user). Different host. It will also be treated as different objects. We can use wildcards. Where% means to match any multiple characters, while - means to match one character. Below I create a user test. And the password is xin, and can log in from any host. See my operation:
mysql> create user 'test'@'%' identified by 'xin'; Query OK, 0 rows affected (0.23 sec)
Here we use the login information of the user to show the demonstration example, as follows:
C:\Users\Administrator>mysql -u test -pxin 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 14 Server 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 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>
For example, we want to check the user information at this time. 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 don't need the permission to select the user table here. Then it will involve permission management. Here we can use show grants to check 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 very small. We still use the root user to log in. Then we 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 supreme privileges. Therefore, he can change the 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, for example, the following commands:
mysql> set password for 'test'@'%' = password('qian');
Query OK, 0 rows affected (0.00 sec)At this point, we can use the user test to log in. Here is my code:
C:\Users\Administrator>mysql -u test -pqian 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 17 Server 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 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>
In fact, we can directly use the update statement to change the user table, but we need to use flush privileges; To make it work 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: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)Then we can log in with the 'nan' password. I won't give the demonstration code here. After all, it's very easy to operate. I'm Xin Xing. Ask for support.
Publisher: full stack programmer, stack length, please indicate the source for Reprint: https://javaforall.cn/115765.html Original link: https://javaforall.cn