"MYSQL" Insufficient permissions caused by inability to connect to the database and permissions granted and revoked

"Environment Parameters"

1, Host os:win7 64bit

2. Host ip:192.168.10.1

3, Vm:vmware 11.1.0

4, Client Os:centos 6

5, Client ip:192.168.10.1

6, mysql:5.1.73

"Barrier Reproduction"

Host is not allowed to connect to this MySql Server  

After installing the " first " on the CentOS system (installed in the virtual machine) and simply configuring the MySQL server, you want to access the MySQL database on the virtual machine locally, and when you enter the MySQL login command locally, the error appears as shown.

In short, host (192.168.10.1) is not allowed to connect to the MySQL database.

　　

"Cause of the obstacle"

The host side has insufficient permissions. On the MySQL database server-side MySQL database, there is a user table that records information about the accounts that can log in to the MySQL server, as shown in.

At this point, the Host field in the user table only has a record of "localhost", which means that the MySQL server only allows logging in to the database from "localhost (where localhost is a virtual machine)".

Therefore, if you want to allow other non-localhost users access, you need to modify the value of the host field. "solution : The Table method" Modify the data in the Host field in the user table to "%" 　　Notice that when you're done, remember " Refresh PermissionsOtherwise, the host side still cannot connect to MySQL. "solution : Authorization Law" The following examples can be used as valuable references: (self-tested, can be effective) (1)If you want to myDbUser01 the database user, use the password MyPassword, from the any host"If you connect to a MySQL server, you can use the following command:mysql> GRANT all privileges on *. * to ' MyDbUser01 ' @ '% 'Identified by ' MyPassword ' with GRANT option;mysql> FLUSH privileges; (2)If you want to myDbUser01 the database user, use the password MyPassword, from the host with IP 192.168.10.1"To connect to the MySQL server, you can use the following command:mysql> GRANT all privileges on *. * to ' MyDbUser01 ' @ ' 192.168.10.1 'Identified by ' MyPassword ' with GRANT option;mysql> FLUSH privileges; (3)If you want the database user to MyDbUser01, use the password mypassword, connect the host from the IP 192.168.10.1 to the MySQL server " DK Database, you can use the following command:mysql> GRANT all privileges on dk.*To ' MyDbUser01 ' @ ' 192.168.10.1 ' identified by ' MyPassword ' with GRANT option;mysql> FLUSH privileges; "Expand: Revoke User Rights"Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';
Description: Privilege, DatabaseName, TableName-With the authorization section.
Example: REVOKE SELECT on * * from ' pig ' @ '% ';
Note:
The REVOKE statement can only cancel the user's permissions and not delete the user. Even if all permissions are canceled, the user can still connect to the server.
To completely delete a user, you must use the DELETE statement to remove the user's records from the users table in the MySQL database. The syntax format for this statement is as follows:
Delete from user where user = "user_name" and host = "HOST_NAME";
Use Delete to delete the user SSS with the following code:
mysql> use MySQL
Database changed
mysql> Delete from user where user= ' sss ' and host= ' localhost ';
Mysql>flush privileges;
Query OK, 1 row affected (0.02 sec)
Where delete is used to remove the user, flush tells the server to reload the authorization table.

If the reader wants to reprint, please indicate the source and the author's name, thank you.

Address 01:http://space.itpub.net/25851087

Address 02:http://www.cnblogs.com/zjrodger

Author Name: Zjrodger

"MYSQL" Insufficient permissions caused by inability to connect to the database and permissions granted and revoked