Detailed explanation of permission _ MySQL in MySQL

This article mainly introduces the permissions in MySQL, including the transactions that can be operated by each permission and some common command statements for the operation permission. For more information, see I. Preface

In many articles, we will say that the database permission is based on the principle of least privilege. this statement is correct but empty. This is too abstract because of the minimum permission. you may not know what permissions are required. Currently, many mysql instances are operating with the root account. it is not because you do not know that the root permission is too big or insecure, but that many users do not know what permissions should be granted to ensure the security and normal operation. Therefore, this article focuses more on how to configure a secure mysql instance. Note: the test environment in this article is mysql-5.6.4
II. Mysql permissions

Mysql has four tables with control permissions: user table, db table, tables_priv table, and columns_priv table.

The verification process of the mysql permission table is as follows:

1. check whether the connected ip address, user name, and Password exist from the Host, User, and Password fields in the user table. if the connection exists, it passes verification.

2. after passing identity authentication, grant permissions in the order of user, db, tables_priv, and columns_priv. Check the global permission table user first. if the user has the corresponding permission Y, the user has the permission Y for all databases and does not check db, tables_priv, columns_priv; if the value is N, check the specific database corresponding to this user in the db table and obtain the permission of Y in the db. if the value is N in the db, check the specific table corresponding to this database in tables_priv, obtain the permission Y in the table, and so on.

III. what permissions does mysql have?

IV. Database-level (db table) permission analysis

V. mysql security configuration scheme

1. restrict the ip address used to access the mysql Port

Windows can be restricted through windows Firewall or ipsec, and linux can be restricted by iptables.

2. modify the mysql Port

In windows, you can modify the configuration file my. ini. in linux, you can modify the configuration file my. cnf.

3. set strong passwords for all users and strictly specify the access ip address of the corresponding account

In mysql, you can specify the accessible ip addresses of users in the user table.

4. handling of root privileged accounts

We recommend that you set a strong password for the root account and specify that only local logon is allowed.

5. log processing

If necessary, you can enable Log Query. log query records logon and query statements.

6. mysql process running account

You cannot use local system to run mysql accounts in windows. you can use network service or create an account by yourself, however, you must grant the read permission to the directory where the mysql program is located and the read and write permissions to the data directory. in linux, create a new mysql account, during installation, specify mysql to run with a mysql account, and grant the read permission to the directory where the program is located, and the read and write permissions to the directory where data is located.

7. disk permissions of the mysql running account

1) the mysql running account must grant the read permission to the directory where the program is located, as well as the read and write permissions to the data directory.
2) do not grant write and execution permissions to other directories, especially those with websites.
3) cancel the execution permissions of the mysql running account for cmd, sh, and other programs.

8. process the mysql account used by the website

Create an account and grant the account all permissions on the database in use. This not only ensures all operations on the corresponding database on the website, but also ensures that the account will not be affected by excessive permissions. Accounts that grant all permissions to a single database do not have management permissions such as super, process, and file. Of course, if you can clearly know what permissions are required for my website, do not give more permissions, because the publisher often does not know what permissions are required for the website, so I suggest the above configuration. In addition, I usually mean that only a few machines are available. in a few cases, I personally suggest giving only the required permissions. for details, refer to the suggestions in the above table.

9 delete useless databases

Database test has permissions on the newly created account by default.
VI. mysql intrusion elevation analysis and prevention measures

In general, there are several methods for mysql privilege escalation:

1. udf privilege escalation

The key to this method is to import a dll file. I personally think that as long as the process account's write permission to the directory is properly controlled, it will prevent the imported dll file from being broken, at this time, as long as the permissions of the process account are low enough, there are no high-risk operations, such as adding an account.

2. write the startup file

This method is the same as above. You still need to reasonably control the write permission of the process account to the directory.

3. when the root account is leaked

If the root account is not properly managed, and the root account is infiltrated, the database information cannot be guaranteed. However, if the process account's permissions are controlled and its permissions on the disk are controlled, the server can still be guaranteed not to be compromised.

4. normal account leakage (as mentioned above, only accounts with all permissions for a database)

The general account mentioned here refers to the account used by the website. a convenient suggestion is to directly grant all permissions to a specific database. Account leakage includes getting the database account password after injection and web server intrusion.

At this time, the corresponding database data is not guaranteed, but it does not threaten other databases. In addition, the general account has no file permission, and all files cannot be exported to the disk. of course, the permissions of the process account are strictly controlled.

For details about the permissions granted to an ordinary account, refer to the table above. In fact, it does not directly grant all permissions to a database.
7. common commands required for security configuration

1. create a new user and grant the corresponding 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. Remove authorization

revoke delete on *.* from 'jack'@'localhost';

5. delete a user

drop user 'jack'@'localhost';

6. rename the user

rename user 'jack'@'%' to 'jim'@'%';

7. change the password for the user

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

8. delete a database

drop database test;

9. export files from the database

select * from a into outfile "~/abc.sql"