Read more about permissions in MySQL

First, preface

Many articles will say that the permissions of the database according to the principle of minimum permissions, the sentence itself is not wrong, but it is an empty word. Because of the least privilege, this thing is too abstract, and many times you can't figure out exactly what permissions he needs. Now many MySQL use the root account in the operation, not everyone does not know that the root authority is too large and insecure, but many people do not know what permissions to give is safe and can guarantee normal operation. So, this article is more about considering this scenario, how we can simply configure a secure MySQL. Note: This test environment is mysql-5.6.4
Ii. Description of MySQL privileges

There are 4 control permissions tables in MySQL, the user table, the DB table, the Tables_priv table, and the Columns_priv table.

The validation process for the MySQL permissions table is:

1. Verify that the IP, username, and password for the connection are present from the 3 fields in the user table Host,user,password.

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

Iii. What are the rights of MySQL?

Iv. permission Analysis of database level (DB table)

V. MySQL Security Configuration scheme

1 restricting access to the MySQL port's IP

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

2 Modifying the port of MySQL

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

3 Set strong password for all users and strictly specify the access IP of the corresponding account

MySQL can specify the user's access to the IP in the Users table

4 Processing of root privileged accounts

It is recommended to set a strong password for the root account and specify that only local logins are allowed

5th the processing of records

If you need to open the query log, the query log logs logins and query statements.

6 MySQL Process run account

Under Windows prohibit the use of the local system to run the MySQL account, you may consider using the Network service or create a new account, but must give the MySQL program in the directory of the Read permission and the data directory read and write permissions; Under Linux, create a new MySQL account and, when installed, specify MySQL to run as a MySQL account, giving read access to the directory where the program resides, and read and write access to the directory where the data resides.

7 disk permissions for MySQL run account

1) MySQL run account needs to give the directory Read permission to the program, and the data directory read and Write permissions
2) do not allow permission to write and execute other directories, especially if there is a website.
3) Cancel the execution rights of some programs such as Cmd,sh for MySQL running account.

8 handling of MySQL account used by the website

Create a new account and give all the permissions to the account in the database you are using. This will not only ensure that the site to the corresponding database of all operations, but also to ensure that the account is not too high authority 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 it is clear that you know, what permissions my site needs, or do not give more permissions, because many times the publisher does not know what permissions the site requires, I recommend the above configuration. And I mean the general, specific to only a few machines, not many cases, I personally suggest or give only the required permissions, specific reference to the above table recommendations.

9 Deleting a useless database

The test database has permissions to the newly created account by default
Vi. the analysis and prevention measures of MySQL intrusion right

In general, there are several ways in which MySQL has the right to lift:

1 UDF right to extract

The key to this approach to import a DLL file, the personal think that as long as reasonable control of the process account write permissions to the directory can prevent the import of DLL files, and if the case is compromised, at this time as long as the process account permissions low enough, do not do high-risk operations, such as adding accounts.

2 Writing the startup file

In this way, it is still reasonable to control the process account write permissions to the directory.

3 When the root account is compromised

If the root account is compromised without proper management of the root account, the database information must not be guaranteed. However, if the permissions of the process account are controlled and the permissions on the disk are controlled, the server is guaranteed not to be compromised.

4 General Account disclosure (as mentioned above, only accounts that have all permissions on a library)

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

At this point, the corresponding database data is not insured, but no other database is compromised. And the ordinary account here does not have file permissions, all can not export files to disk, of course, this time will still be strict control of the account permissions of the process.

What permissions are given to a general account can be seen in the table, and it is not possible to give all permissions directly to a library.
Vii. Common commands required for security configuration

1. Create a new user and give permissions to the corresponding database

?

1	grantselect,insert,update,delete,create,drop privileges on database.* to user@localhost identified by‘passwd‘;

?

1	grantall privileges on database.* to user@localhost identified by‘passwd‘;

2. Refresh Permissions

?

1	flush privileges;

3. Show authorization

?

1	show grants;

4. Remove authorization

?

1	revokedelete on *.* from‘jack‘@‘localhost‘;

5. Delete a user

?

1	dropuser‘jack‘@‘localhost‘;

6. Renaming users

?

1	rename user‘jack‘@‘%‘ to‘jim‘@‘%‘;

7. Change the password for the user

?

1	SETPASSWORD FOR ‘root‘@‘localhost‘= PASSWORD(‘123456‘);

8. Deleting a database

?

1	dropdatabasetest;

9. Exporting files from a database

?

1	select* from a intooutfile "~/abc.sql"

Read more about permissions in MySQL