Detailed explanation of permissions in MySQL

This article mainly introduces the permissions in MySQL, including the transactions that each permission can operate and some common command statements for operating permissions. Friends in need can refer to it. I hope it can help everyone.

1. Foreword

Many articles will say that the permissions of the database are based on the principle of least permissions. This sentence itself is not wrong, but it is an empty talk. Because of the minimum permissions, this thing is too abstract, and many times you don't know exactly what permissions it requires. Nowadays, many MySQL operations use root accounts. It's not that everyone doesn't know that using root permissions is too unsafe, but that many people don't know what kind of permissions should be given that are both safe and ensure normal operation. Therefore, this article is more about considering how we can simply configure a secure mysql in this case. Note: The test environment of this article is mysql-5.6.4
2. Introduction to Mysql permissions

There are 4 tables that control permissions in mysql, namely user table, db table, tables_priv Table, columns_priv table.

The verification process of mysql permission table is:

1. First, determine whether the connected ip, user name, and password exist from the three fields of Host, User, and Password in the user table. If it exists, it is verified.

2. After passing the identity authentication, assign permissions and verify in the order of user, db, tables_priv, columns_priv. That is, first check the global permission table user. If the corresponding permission in user is Y, then this user's permission on all databases is Y, and db, tables_priv, columns_priv will no longer be checked; if it is N, check this in the db table. The specific database corresponding to the user, and obtains the permission Y in the db; if the permission in the db is N, check the specific table corresponding to the database in tables_priv, and obtain the permission Y in the table, and so on.

3. What permissions does mysql have?

##4. Permission analysis at the database level (db table)

##5. MySQL security configuration scheme

1 Limitation IP access to the mysql port

Windows can be restricted through Windows Firewall or ipsec, and Linux can be restricted through iptables.

2 Modify the port of mysql

Under Windows, you can modify the configuration file my.ini to achieve this. In Linux, you can modify the configuration file my.cnf to achieve this.

3 Set strong passwords for all users and strictly specify the access ip of the corresponding account

In mysql, the user's access ip can be specified in the user table

4 root privileges Account handling

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

5 Log handling

If necessary, you can enable the query log, which will be recorded Login and query statements.

6 mysql process running account

It is forbidden to use local system to run mysql account under windows. You can consider using network service or creating a new account yourself, but you must give read access to the directory where the mysql program is located. Take permissions and read and write permissions of the data directory; Under Linux, create a new mysql account, and specify mysql to run as the mysql account during installation, and give read permissions to the directory where the program is located, and permissions to the directory where the data is located. Read and write permissions.

7 Disk permissions of the mysql running account

1) The mysql running account needs to be given read permissions to the directory where the program is located, as well as read and write permissions to the data directory

2) No Allows writing and executing permissions to be given to other directories, especially those with websites.

3) Cancel the execution permissions of the mysql running account for cmd, sh and other programs.

8 How to handle the mysql account used by the website

Create a new account and give the account all permissions in the database used. This not only ensures that the website can fully operate the corresponding database, but also ensures that the account will not affect security due to excessive permissions. An account given all permissions to a single database will not have super, process, file and other management permissions. Of course, if I can clearly know what permissions my website requires, it is better not to give more permissions, because many times the publisher does not know what permissions the website requires, so I only recommend the above configuration. And what I mean is general. Specifically, when there are only a few machines, and not many, I personally recommend giving only the necessary permissions. For details, please refer to the suggestions in the table above.

9 Delete useless database

The test database has default permissions for the newly created account

6. Analysis and prevention measures of mysql intrusion and privilege escalation
Generally speaking, there are several ways to escalate mysql privileges:

1 UDF privilege escalation

The key to this method is to import a dll file. Personally, I think that as long as the process account's write permissions on the directory are reasonably controlled, the dll file can be prevented from being imported; and if it is breached, as long as the process account's permissions are low enough , and it is impossible to perform high-risk operations, such as adding accounts, etc.

2 Write startup file

This method is the same as above, but it is still necessary 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 invaded, the database information will definitely not be guaranteed. However, if the permissions of the process account and its permissions on the disk are controlled, the server can still ensure that it will not be compromised.

4 Ordinary account leaks (as mentioned above, accounts that only have all permissions for a certain library)

The ordinary account mentioned here refers to the account used by the website. I will give you a comparison A convenient suggestion is to simply give all permissions to a specific library. Account leakage includes injection and direct access to database account passwords after the web server is compromised.

At this time, the corresponding database data is not protected, but it will not threaten other databases. Moreover, the ordinary account here does not have file permissions and cannot export files to disk. Of course, the permissions of the process account will still be strictly controlled at this time.

What kind of permissions are given to ordinary accounts can be seen in the table above. It is really not possible to directly give all permissions to a library.
7. Common commands required for security configuration

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

 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. Move Remove authorization

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

5. Delete user

drop user 'jack'@'localhost';

6. Change user name

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

7. Change the user’s password

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

8. Delete the database

##

drop database test;

9. Export files from the database

##

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

Related recommendations:

Detailed explanation of mysql permissions and security

Mysql permission system working principle

MySQL permission management

The above is the detailed content of Detailed explanation of permissions in MySQL. For more information, please follow other related articles on the PHP Chinese website!