MySQL Account and Rights Management overview

MySQL Initial account management

MySQL's initial account is as follows:

[root@lx16 ~]# mysql-u root  
mysql> select Host,user,password from Mysql.user;  
+-----------+------+----------+  
| host      | user | password |  
+-----------+------+----------+  
| lx16      | root | | |  
127.0.0.1 | root |          |  
|: 1       | root |< c13/>|  
| localhost | | | |  
lx16 | | | |      localhost |  
root  
| | +-----------+------+----------+

MySQL has two types of initial users:

Root Super Account: Has all the privileges, can do anything.

Anonymous account: How people can connect to the server through it, but it has very little permissions.

By default, these accounts do not have a password, so for security reasons, we first have to set the password for all the root accounts.
The first way to set a password is to use the Set Password statement, if we now want to give ' root ' @ ' localhost ' a password, just execute:

mysql> set password for ' root ' @ ' localhost ' =password (' * * * ');

The second way to set up a password is to update the user permission table directly, which has the advantage that you can set a password for multiple accounts at the same time, such as the following statement to modify all root account passwords at once:

mysql> Update Mysql.user Set Password=password (' * * *) where user= ' root ';

mysql> flush Privileges;

If you modify with update, you must explicitly tell the server to reload the permissions table (flush privileges)

For anonymous accounts, it is strongly recommended that they be deleted and the deletion statement as follows:

mysql> drop user ' @ ' localhost ';

mysql> drop user ' @ ' lx16 ';

After you perform these actions, the data in the User permission table is as follows:

Mysql> select Host,user,password from Mysql.user;  
+-----------+------+-------------------------------------------+  
| host      | user | password                                  |  
+-----------+------+-------------------------------------------+  
| lx16      | root | 578ec7851088ac1f2a67b100540344b03bd2ba99 |  
| 127.0.0.1 | Root | *578ec7851088ac1f2a67b100540344b03bd2ba99 |  
| :: 1       | root | *578ec7851088ac1f2a67b100540344b03bd2ba99 |  
| localhost | root | * 578ec7851088ac1f2a67b100540344b03bd2ba99 |  
+-----------+------+-------------------------------------------+

Create a new account

MySQL not only requires you to know who (user_name) can connect, you must also specify where to connect (HOST_NAME), that is, even if two accounts have the same name, if they will connect from different clients, you will also create an account for them.
You can use the following two wildcard characters to flexibly configure the host name restrictions:

'% '-matches any number of characters

'-'-Configure a character

Test account can be from any IP connection  
create user ' test ' @ '% ' identified by ' * *;  
Test account can only be connected from the local  
create user ' test ' @ ' localhost ' identified by ' * *;  
The test account can only be connected from the ' 192.168.2.% ' segment to  
create user ' test ' @ ' 192.168.2.% ' identified by ' * *;  
You can also use the IP mask  
create user ' test ' @ ' 192.168.2.2