Some suggestions for ensuring MySQL data security

Data is the core asset of an enterprise and one of the most important tasks for an enterprise. If you are not careful, there is a high risk of data being leaked unintentionally or even stolen maliciously by hackers. Every year, there are several major incidents in the industry, such as a well-known or unknown company being hacked (a homonym for "拖库", which means that the entire database is stolen by hackers).

From the perspective of data security, it can also be divided into external network security and internal operation security, which will be discussed below.

Internal Operational Security Policy

1. Whether to revoke all DBA permissions

Imagine if the DBA has no authority, how can daily DB operation and maintenance work and emergency troubleshooting be carried out? Therefore, it is recommended that before a mature automated operation and maintenance platform is established, too many permissions of the DBA should not be roughly revoked, otherwise it may lead to reduced work efficiency and even the DBA may have a negative emotion of being distrusted.

2. MySQL layer security strategy

• Business accounts can only be logged in remotely through the intranet, but cannot be connected remotely through the public network.
• Add an operation and maintenance platform account, which allows remote connection from a dedicated management platform server. Of course, security measures and necessary security audit strategies must be implemented on the server where the management platform is deployed.
• It is recommended to enable the database audit function. This requires the use of MySQL Enterprise Edition or Percona/MariaDB branch version. MySQL Community Edition does not support this feature.
• Enable the safe-update option to prevent the entire table from being modified without a WHERE condition;
• Try not to delete data directly using DELETE in your application, but just set a flag. When deletion is really necessary, let the DBA back up the data first and then delete it physically to avoid deleting all the data by mistake.
• Triggers can also be used to perform some auxiliary functions, such as preventing hackers from maliciously tampering with data.
  

3. MySQL account permission rules

• For business accounts, permissions are minimized, and DROP and TRUNCATE permissions are strictly prohibited.
• By default, business accounts are granted only the permissions required for ordinary DML, such as select, update, insert, delete, and execute, and no other permissions are granted.
• After MySQL is initialized, delete useless accounts and the anonymous test database.

mysql> delete from mysql.user where user!='root' or host!='localhost'; flush privileges; mysql> drop database test;

• Create a dedicated backup account with only SELECT permissions and only allow login from the local computer.
• Set the password security policy for the MySQL account, including length and complexity.
  

4. About data backup

Remember, making a full data backup is the last straw when the system crashes and cannot be repaired.

Backup data can also be used for data auditing or to pull data sources from data warehouses.

Generally speaking, the backup strategy is as follows: perform a full backup once a day and regularly perform additional backups on the binlog, or directly use the binlog server mechanism to transfer the binlog to other remote hosts. With full backup + binlog, you can restore to any point in time as needed.

Special reminder: When using xtrabackup streaming backup, consider using encrypted transmission to prevent the backup data from being maliciously intercepted.

External network security strategy

In fact, operating system security and application security are more important than the security policy of the database itself. Similarly, the system security of the application and its server is also very important. Many data security incidents are caused by invading the application server through code vulnerabilities, then probing the database and finally successfully deleting the database.

1. Operating system security recommendations

• The Linux running MySQL must only run in the internal network and is not allowed to be directly exposed to the public network. If it is necessary to connect from the public network, port forwarding should be done through a jump server. And as mentioned above, the database account permission level must be strictly limited.
• All system accounts have been changed to ssh key-based authentication, remote password login is not allowed, and there are requirements for the algorithm and length of the ssh key to ensure relative security. This way there is no risk of password loss unless an individual's private key is stolen.
• Furthermore, you can even enable PAM authentication for all servers to achieve unified account management, which is more convenient and secure.
• Shut down unnecessary system services and only run necessary processes, such as mysqld, sshd, networking, crond, syslogd and other services, and shut down all others.
• Remote login by the root account is prohibited.
• It is forbidden to use the root account to start common business service processes such as mysqld.
• It is recommended to change the port number of the sshd service to above 10000.
• Without affecting performance, try to enable the firewall policy for the MySQL service port (when the concurrency is high, the use of iptables may affect the performance, it is recommended to use the ip route policy instead).
• A password must be set for GRUB, and the default passwords for accounts such as Idrac/imm/ilo on the physical server must also be changed.
• Every employee who needs to log into the system uses their own private account instead of a public account.
• System-level operation auditing should be enabled to record all ssh logs, or use bash to record corresponding operation commands and send them to the remote server, and then conduct corresponding security audits to detect unsafe operations in a timely manner.
• Correctly set the directory permissions for MySQL and other database services. Do not set all permissions to 755. Generally, 750 is sufficient.
• You can consider deploying a bastion host. All connections to remote servers must first go through the bastion host, and all operation records and audit functions can be implemented on the bastion host.
• Script encryption doesn't really help improve security. For experienced hackers, as long as they have system login permissions, they can easily gain root through methods such as escalating privileges.

2. Application security recommendations

• Disable the web server's autoindex configuration.
• From an institutional perspective, employees are prohibited from uploading code to external GitHub, as there is a high risk of internal IP, account and password leakage. If code must be uploaded, it must first undergo a security review.
• Try not to use open source cms, blogs, forums and other systems on the public Internet unless you have done a code security audit or have prepared a security strategy in advance. Such systems are usually the focus of hacker research and are easy to be hacked;
• At the web server layer, you can use some security modules, such as nginx's WAF module;
• At the app server layer, code security audits and security scans can be performed to prevent security vulnerabilities such as XSS attacks, CSRF attacks, SQL injections, file upload attacks, and bypassing cookie detection.
• Wherever account passwords are involved in the application, such as JDBC connection string configuration, try to store the plain text password in encrypted form, and then use the internal private decryption tool to decrypt it before use. Alternatively, you can let the application use an intermediate account to connect to the proxy layer first, and then use the proxy to connect to MySQL, to avoid the application layer directly connecting to MySQL;
  

Finally, we would like to say that any brilliant security strategy is not as important as the security awareness of internal employees. There was a case in the past where an employee's PC was accidentally infected, resulting in the theft of intranet data.

Safety is no small matter and everyone should keep it in mind. When it comes to data security, you can sacrifice some convenience, but not too much, otherwise the gain may not outweigh the loss.

The above are the details of some suggestions for ensuring MySQL data security. For more information about MySQL security suggestions, please pay attention to other related articles on 123WORDPRESS.COM!