MySQL Security Configuration

MySQL Security Configuration Zhangsan 2014/06/14 11:55

0x01 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

0x02 MySQL Permissions Introduction

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, first from the user table in the Host,user,password 3 fields to determine whether the connection IP, user name, password exists, the existence is verified. 2, through the identity authentication, the authority assignment, according to the Order of USER,DB,TABLES_PRIV,COLUMNS_PRIV verification. 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.

What permissions do 0x03 MySQL have?

Permissions	Permission levels	Permission Description
CREATE	Database, table, or index	Create database, table, or index permissions
DROP	Database or table	Delete database or table permissions
GRANT OPTION	A database, table, or saved program	Granting permission Options
REFERENCES	Database or table
Alter	Table	Change tables, such as adding fields, indexes, etc.
DELETE	Table	Delete Data permissions
INDEX	Table	Index permissions
INSERT	Table	Insert Permissions
SELECT	Table	Query permissions
UPDATE	Table	Update permissions
CREATE VIEW	View	CREATE VIEW Permissions
SHOW VIEW	View	View View Permissions
ALTER ROUTINE	Stored Procedures	Change stored procedure permissions
CREATE ROUTINE	Stored Procedures	Create Stored Procedure permissions
EXECUTE	Stored Procedures	Execute Stored Procedure permissions
FILE	File access on the server host	File access Permissions
CREATE Temporary TABLES	Server Management	Create temporary table permissions
LOCK TABLES	Server Management	Lock table Permissions
CREATE USER	Server Management	Create User Rights
PROCESS	Server Management	View Process Permissions
RELOAD	Server Management	Perform flush-hosts, Flush-logs, Flush-privileges, Flush-status, Flush-tables, flush-threads, refresh, reload, and so on command permissions
REPLICATION CLIENT	Server Management	Copy Permissions
REPLICATION SLAVE	Server Management	Copy Permissions
SHOW DATABASES	Server Management	View Database Permissions
SHUTDOWN	Server Management	Turn off database permissions
SUPER	Server Management	Execute Kill Thread Permissions

Permission analysis of the 0X04 database level (DB table)

Permissions	Description	Does the website use account give
Select	All tables under it can be queried	Recommend giving
Insert	All tables under it can be inserted	Recommend giving
Update	All tables under it can be updated	Recommend giving
Delete	All tables under it can be deleted	Recommend giving
Create	Tables or indexes can be created under this database	Recommend giving
Drop	You can delete this database, and the table under this database	Not recommended to give
Grant	Granting permission Options	Not recommended to give
References	Placeholders for future MySQL features	Not recommended to give
Index	All tables under it can be indexed	Recommend giving
Alter	You can make changes to all of the tables under it	Recommend giving
Create_tmp_table	Create a temporary table	Not recommended to give
Lock_tables	All tables under it can be locked	Not recommended to give
Create_view	You can create a view under this data	Recommend giving
Show_view	View can be viewed under this data	Recommend giving
Create_routine	You can create a stored procedure under this data	Not recommended to give
Alter_routine	Stored procedures can be changed under this data	Not recommended to give
Execute	Stored procedures can be executed under this data	Not recommended to give
Event	Event Scheduler can be created under this data	Not recommended to give
Trigger	Triggers can be created under this data	Not recommended to give

0x05 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 running account needs to give the directory Read permission to the program, 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 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

The analysis and prevention measures of the right of 0x06 MySQL intrusion

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

1 UDF the key to this method import a DLL file, personally think that as long as reasonable control of the process account write permissions to the directory to prevent the import of DLL files, and if the case is compromised, if the process account has low permissions, 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.

0X07 Common commands required for security configuration

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

Grant Select,insert,update,delete,create,drop privileges on database.* to [e-mail protected] identified by ' passwd '; grant All privileges in database.* to [e-mail protected] identified by ' passwd ';

2. Refresh Permissions

Flush privileges;

3. Show authorization

Show grants;

4. Remove authorization

Revoke Delete on * * from ' jack ' @ ' localhost ';

5. Delete a user

Drop user ' jack ' @ ' localhost ';

6. Renaming users

Rename user ' Jack ' @ '% ' to ' Jim ' at '% ';

7. Change the password for the user

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

8. Deleting a database

drop database test;

9. Exporting files from a database

SELECT * from A to outfile "D:\abc.vbs"

MySQL Security Configuration