Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the MySQL security blind spots". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the MySQL security blind spots"?

MySQL server security

The installation of MySQL server in self-built computer room will go through network configuration, storage planning, server installation and MySQL installation.

Storage security

1) self-built server

For self-built MySQL servers, redundant arrays of RAID disks are generally used in order to avoid a single point of disk failure.

There are two modes: hardware RAID and software RAID, hardware RAID needs to buy raid card support, software RAID can be generated through the software package mdadm, production environment is recommended to choose hardware RAID, generally need to do RAID for multiple disks first, for database servers, usually choose RAID10 (also known as RAID1 + 0, mirror RAID1 first, and then stripe RAID0 storage), read and write performance is better.

In the actual production environment, there are usually 8 disks of 800G each. When the server starts, the disk RAID is divided into two volume groups (volume group). One is the operating system built-in directory that uses rootvg, and the other is the main steps of the MySQL data-related volume group datavg,LVM operation as shown in the figure:

The data generated by the above storage partition and configuration RAID10,MySQL will be written to the / data directory, synchronized to the logical volume lvdata, and then synchronized to the physical volume, and finally synchronized to the physical disk for mirrored storage, effectively avoiding the data unavailable caused by single disk damage.

2) Public Cloud RDS

For RDS, the online environment generally chooses the high available version, that is, one master and one slave mode. By default, Slave only does failover (failover), mainly because the disks of Master and Slave servers do not do RAID.

network security

1) set whitelist

For self-built computer rooms, hardware firewalls are generally used for network isolation and IP whitelist restrictions. Only specified application servers are allowed to connect to MySQL Server through port 3306. Other malicious requests should be blocked at the firewall level. For example, specified IP can be added to the data security of RDS console.

2) access to public network

The database server only needs to be accessed through the internal network, and it is forbidden to open public network addresses or public network addresses.

Operating system security

After installing the operating system (such as CentOS 7.4), you should pay attention to the following aspects to scan for security vulnerabilities and conduct regular inspections:

1) root starts MySQL

After MySQL Server installation, if you start MySQL with root, any user with FILE system permissions can read and write files under root users, which will cause serious security risks. Software directory owners should be set to root users, data directory owners should be set to mysql users, and mysql users should be used to start MySQL instances.

2) weak password for account

All accounts and passwords of the Linux server cannot be weak passwords (for example, passwords are pure numbers, pure letters, part of the account, etc., and the length is too short). It is recommended that all accounts have a length of 20 digits, including numbers, uppercase letters, lowercase letters and special characters.

3) Open ports to the outside world

For MySQL database servers, all ports should be disabled except port 3306 developed for specified application server access, such as ssh, ftp, and telent service ports.

4) Service vulnerabilities

When using Green Alliance or other tools for security scanning, you will find some system vulnerabilities, such as ntp vulnerabilities, vsftpd vulnerabilities and so on. At this point, you need to stop to play patch repair, scan again, until all passed before you can install MySQL.

5) password login

For production servers, it is strictly forbidden to log in directly through the password. It is recommended to use the jumping machine jumpserver. Each user logs in with his or her own jumpserver account, and then selects the server to be accessed (you need to inform the administrator in advance to add it). The default login users (such as server users) only have read-only permissions and cannot make any modifications.

6) Audit function

All operations that users log in to the production server through jumpserver should be audited and screenshot, and these actions should be played back regularly to ensure that each step is optimal and that there are no unnecessary multiple actions.

7) Operation Command History

All operation commands logged on to the Linux server by the user will be recorded in history, setting the history a little larger, and saving more operation command records.

8) Resource restrictions

Limit the use of user resources by configuring / etc/security/limits.conf files, such as number of open processes, number of files, file size and memory, etc.

9) Server backup

Back up the server regularly, including Linux built-in directory (/ usr / var / lib) and MySQL data directory (data file, binlog,undolog, redolog). At the same time, check the validity of backup and recovery exercise regularly. The following is a snapshot of Aliyun ECS. It is recommended to keep one snapshot per day for 7 days.

MySQL database security

Account security

1) account password

It is recommended that all accounts have a length of 20 digits, including numbers, uppercase letters, lowercase letters and special characters, such as ^ N9UxOR&ydQWCBvIwqql

2) account separation

For a distributed system, there will be multiple services, and there are invocation relationships between the services, for example, the transaction trade-service invokes the payment payment-service.

For security reasons, create a database for each service and assign its own account to prohibit cross-database access.

3) account permissions

According to the principle of minimizing account permissions, for business accounts, you only need to grant permissions to connect, query, add, and modify.

4) use SSL to connect

For businesses with high data security requirements, it is recommended to enable SSL connection, so that even if you use the packet capture tool to analyze, you can not see the specific SQL text. However, from the perspective of SSL implementation, handshake, encryption, decryption and other operations are needed to establish a connection, and the connection establishment phase is time-consuming. If you use connection pooling or long connections, it will not have much impact, if the short connection performance loss is relatively large.

System permissions

1) mysql.user permission

In addition to the MySQL root account, any other account has permission to modify the mysql.user table, which brings security risks: the account password is changed and the authentication plug-in is modified.

2) change the account password

3) modify the password authentication method

MySQL is the local password authentication plug-in mysql_native_password by default. If you change it to auth_socket, you don't need a password, just check whether the user uses UNIX sockets to connect, and then compare the user names.

4) permission risk

Generally, ordinary developers are allowed to log in to the transit machine through jumpserver, and then use mysql-h-u-p to access the database as a read-only account. Sometimes they also want to see relevant information such as innodb_trx under information_schema. It is necessary to grant administrative rights to the process system, which will bring some security risks.

In this way, read-only accounts can also view the SQL statements being executed by all business accounts in the innodb_ trx table. SQL may contain sensitive information.

In previous versions of MySQL, users with process system privileges could also lock system tables, such as lock table mysql.user read, causing the user's actions to be modified normally.

5) FILE permission

With FILE permission, you can transfer data through SELECT. .. INTO OUTFILE... .. Write to a directory with write permission on the server and store it as a text format, or through LOAD DATA INFILE. Import text file data into the data table.

6) Audit function

After installing MySQL Server, Audit Plugin should be configured and enabled so that all SQL executed by MySQL Server will be recorded, so that problems can be eliminated on the one hand, and performance monitoring analysis can be done on the other (such as the QPS,TPS of a SQL over a period of time).

7) data backup

You need to back up data files and binlog regularly. For self-built MySQL servers, you can use xtrabackup to do physical integrity every day. For RDS, you need to configure backup policies and check backup integrity and validity regularly.

Remote disaster recovery

For the core business systems, such as transactions, payments, etc., it is necessary to do a good job in remote disaster recovery of the database to avoid unrecoverable data caused by earthquakes and other natural disasters.

Data security

Data is a valuable resource for enterprises, and the data generated and needed by each business domain are different. How to ensure secure access to data is very important. Generally large companies have their own data management platform, such as Ali's iDB, which is now a service DMS (data management service), which can manage various types of databases.

Data access normalization

All data changes should go through the approval process. Initiators and approvers cannot be the same. Important and sensitive data operations need to submit work orders for multi-level approval and confirm that there are no problems before they can be carried out. Backup should be made before execution, so that it can be used in rollback.

It is recommended to use the database management platform to manage the database. Generally, you need to register an account, apply for permissions (data export, structure change, data change), serve Owner approval, submit a ticket to enter the approval process, and other operations. All operations will record the operation log.

Data desensitization

Desensitize the sensitive fields of the core business table, such as name, mailbox, phone number and ID number, which can be processed by an intermediate platform. For example, Aliyun's DMS can configure data desensitization and support database, table and field levels. By default, queries on the platform are displayed with a * sign. If you want to view plaintext, you need to apply for sensitive data permissions and submit a work order. You can view it only after the approval has been approved. The permission period is one day by default, with a maximum of 7 days.

Before doing marketing activities, you will do database performance stress testing. You need to import all the PROD database into UAT environment, which will involve some sensitive data. Generally, you need to desensitize through the middle layer. For example, the mobile phone number can be coded in the first 3 digits and the last 3 digits in 15222210123.

data encryption

Since MySQL 5.7, InnoDB supports encryption of data at rest in independent tablespaces, which is a means of encryption at the data page level within the engine. It is encrypted when data pages are written to the file system, decrypted when read from data files to memory, and works based on rotating key files, which is very useful for data protection.

The master key file is stored on disk and should be backed up at the same time. For encrypted tables, the tablespace key is stored by the master key and stored in the header of the tablespace file. The encryption algorithm is AES and the encryption mode is block encryption.

Let's take a look at the difference between encrypted ENCRYPTION='Y' and unencrypted ENCRYPTION='N'**.

It can be seen here that through the encryption method of Keyring, the master key is in the header information of the storage table space file, which enhances the security of the data file. For chat messages generated by some social software, the data file can be encrypted in this way to prevent the data file from being stolen and recovered.

Delayed slave library

Oracle database has two features: recycle bin (recyclebin) and flashback (flashback) function. If drop table does not add the purge option, Oracle will store the table rename a new table name in the Recycle Bin, and execute flashback table when you need to restore.

What if delete can recover?

It is also possible to use flashback query to get the data before the deletion time in Undo Log and insert it back into the original table. These two functions are great, but they are not available in MySQL.

Usually, the master-slave replication of MySQL is real-time consistent replication, and the changes performed by Master are immediately synchronized to Slave through binlog replication.

If you don't want Slave to perform Master changes so quickly, you can configure a deferred slave library, which can be used to quickly recover data from erroneous deletions, such as a delay of one hour.

Thank you for your reading, the above is the content of "what are the MySQL safety blind spots". After the study of this article, I believe you have a deeper understanding of what the MySQL safety blind spots have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.