MySQL disables the remote logon permission by default. The following operations allow users to log on to any location: 1. go to MySQL and grant all privileges on *. * To 'root' @ '%' identified by ''with grant option; identified by is followed by a password, which can be left blank. 2. Flush pridrop
MySQL disables the remote logon permission by default. The following operations allow users to log on to any location:
1. Go to MySQL and grant all privileges on *. * To 'root' @ '% 'identified by'' with grant option;
Identified by is followed by a password, which can be left blank.
2. Flush privileges; Update
To ensure security, MySQL allows users to log on only locally by default. However, in this case, you still need to use the user for remote connection. Therefore, you need to perform the following operations remotely:
1. Allow the root user to log on Remotely Anywhere and have any operation permissions on all databases. The specific operations are as follows:
Log on to MYSQL as a root user on the local machine:
Mysql-u root-P "youpassword"
Perform authorization:
Mysql> grant all privileges on *. * To 'root' @ '%' identified by 'youpassword' with grant option;
Reload authorization table:
Flush privileges;
Exit MySQL database:
Exit
2. Allow the root user to log on remotely from a specific IP address and have any operation permissions on all databases. The specific operations are as follows:
Log on to MYSQL as a root user on the local machine:
Mysql-u root-P "youpassword"
Perform authorization:
Grant all privileges on *. * to root @ "172.16.16.152" identified by "youpassword" with grant option;
Reload authorization table:
Flush privileges;
Exit MySQL database:
Exit
3. Allow the root user to log on remotely from a specific IP address and have specific database operation permissions. The specific operations are as follows:
Log on to MYSQL as a root user on the local machine:
Mysql-u root-P "youpassword"
Perform authorization:
Grant select, insert, update, delete on *. * to root @ "172.16.16.152" identified by "youpassword ";
Reload authorization table:
Flush privileges;
Exit MySQL database:
Exit
4. Use the revoke command to delete user authorization. The specific command format is:
Revoke privileges on database [. Table name] from user-name;
For specific instances, log on to MySQL on the local machine:
Mysql-u root-P "youpassword"
Perform authorization:
Grant select, insert, update, delete on TEST-DB to test-user @ "172.16.16.152" identified by "youpassword ";
Then perform the delete authorization operation:
Revoke all on TEST-DB from test-user;
* *** Note: this operation only removes the user's related permissions to the TEST-DB, but this "test-user" user still exists.
Finally, clear the user from the user table:
Delete from user where user = "test-user ";
Reload authorization table:
Flush privileges;
Exit MySQL database:
Exit
V. detailed classification of MySQL permissions:
Global Management permissions:
File: read and write files on the MySQL server.
Process: displays or kills service threads of other users.
Reload: Reload Access Control tables and refresh logs.
Shutdown: Shut down the MySQL service.
Database/data table/data column permissions:
Alter: Modify existing data tables (such as adding/deleting columns) and indexes.
Create: Create a new database or data table.
Delete: Delete table records.
Drop: delete a data table or database.
Index: Create or delete an index.
Insert: Add Table records.
Select: displays/searches for table records.
Update: Modify existing records in the table.
Special permissions:
ALL: allow anything (same as root ).
Usage: Only logon is allowed. Other operations are not allowed.