MySQL remote logon permission settings

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.