A detailed interpretation of the permissions in Mysql _mysql

First, the preface

Many articles will say that the permissions of the database according to the principle of minimum authority, this sentence itself is not wrong, but it is an empty words. Because of the least privilege, this thing is too abstract, and many times you don't know exactly what permissions he needs. Now a lot of MySQL with the root account in operation, not everyone does not know that the root permission is too large insecurity, but many people do not know what kind of authority to give both security and ensure normal operation. So, this article is more about how we can simply configure a secure MySQL in this case. Note: This test environment is mysql-5.6.4
second, the MySQL authority introduction

MySQL has 4 control permissions in the table, respectively, the user table, DB table, Tables_priv table, Columns_priv table.

The validation process for the MySQL permission table is:

1. First of all, from the User table in the Host,user,password these 3 fields to determine whether the IP, user name, password exists, the existence is validated.

2. After the identity authentication, carries on the authority assignment, according to the User,db,tables_priv,columns_priv order carries on the verification. That is, the Global Permissions table user is checked first, if the corresponding permission in user is Y, then the user has y for all databases, will no longer check db, Tables_priv,columns_priv, and if n, check the database for the user in the DB table. And get the permissions for Y in db, and if DB is N, check the specific table in Tables_priv for this database, get permission y in the table, and so on.

Third, MySQL has what permissions

Four, database level (DB table) of the rights analysis

Five, MySQL security configuration scheme

1 Restrict access to the MySQL port IP

Windows can be limited by Windows Firewall or IPSec, which can be restricted by Iptables under Linux.

2 Modify the MySQL port

Windows can modify the configuration file My.ini to implement, Linux can modify the configuration file my.cnf to implement.

3 to all users to set strong password and strictly specify the corresponding account access IP

In MySQL, you can specify the access of users in the user table to access IP

4 Root Privileged account processing

We recommend that you set a strong password for the root account and specify that you only allow local logins

5th Records of the processing

If you need to open the query log, the query log will record the login and query statements.

6 MySQL process running account

Under Windows prohibit the use of local system to run the MySQL account, you can consider using the Network service or create a new account, but you must give the MySQL program in the directory with read access and the data directory read and write permissions; Under Linux, create a new MySQL account, and at the time of installation to specify MySQL with MySQL account to run, to the program in the directory of Read access, data directory read and write permissions.

7 MySQL Run account disk permissions

1 MySQL running account needs to give the program's directory read access, and the data directory read and Write permissions
2 Do not allow other directories to write and execute permissions, especially the site.
3 Cancel the MySQL running account for Cmd,sh and other programs, such as the execution of permissions.

8 web site Use of the MySQL account processing

Create a new account and give the account all the permissions on the database you are using. This will ensure that the site to the corresponding database of all operations, but also to ensure that the account will not be too high to affect security. Accounts that give all permissions to a single database do not have administrative privileges such as super, process, file, and so on. Of course, if you can be very clear yes know, my site needs what permissions, or do not give more permissions, because many times the publisher does not know what permissions the site needs, I recommend the above configuration. And I refer to the general, specific to only a few machines, not many cases, I personally suggest or give only the necessary permissions, specific reference to the above table recommendations.

9 Deleting useless databases

The test database has default permissions for new accounts
The analysis and prevention measures of MySQL intrusion right

In general, there are several ways in which MySQL can be put right:

1 UDF claim

The key to this approach is to import a DLL file that the individual believes can prevent the DLL file from being imported as long as the process account is properly controlled for the write permission of the directory; then if it is breached, then if the process account is low enough, it does not run high-risk operations, such as adding accounts.

2 Write to startup file

This way, or to reasonably control the process account write permission to the directory.

3 When the root account is compromised

If the root account is not properly managed and the root accounts are compromised, the database information must not be guaranteed. However, if the permissions on the process account are controlled and the permissions on the disk are controlled, the server can guarantee that it will not be lost.

4 General account leakage (above mentioned, only a library has all the permissions of the account)

The ordinary account mentioned here refers to the account used by the website, I give a more convenient suggestion is to give the specific library directly all the permissions. Account leaks include the presence of injection and the direct access to the database account password after the Web server has been hacked.

At this point, the corresponding database data is not guaranteed, but it does not threaten other databases. And the ordinary account here without file permissions, all can not export files to disk, of course, at this time will be the process of the account of strict control of the permissions.

The general account given what permissions can be seen on the table, it is not directly to give a library of all the permissions.
Vii. Common commands required for security configuration

1. Create a new user and give the appropriate database permissions

 Grant Select,insert,update,delete,create,drop privileges on database.* to user@localhost identified by ' passwd ';

 Grant all privileges on database.* to user@localhost identified by ' passwd ';

2. Refresh Permissions

Flush privileges;

3. Display authorization

Show grants;

4. Removal of authorization

Revoke delete on *.* from ' Jack ' @ ' localhost ';

5. Delete User

Drop user ' jack ' @ ' localhost ';

6. Renaming the user

Rename user ' Jack ' @ '% ' to ' jim ' @ '% ';

7. Change the password to the user

SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' 123456 ');

8. Delete Database

drop database test;

9. Export files from the database

SELECT * from A to outfile "~/abc.sql"