How to protect important data in MySQL

Unlike automated database backups, it is necessary for the system administrator to protect the data from unauthorized users and take certain actions. If you use MySQL, you can use a number of convenient features to protect the system, to greatly reduce the risk of confidential data access by unauthorized users.

The most valuable asset of an enterprise is usually the customer or product information in its database. Therefore, an important part of database management in these enterprises is to protect this data from external attacks and to repair soft/hardware failures.

In most cases, hardware and software failures are handled through a data backup mechanism. Most databases automate the entire process with built-in tools, so this is a relatively easy task and not a mistake. But the trouble comes from the other side: preventing foreign hackers from hacking or destroying information in the database. Unfortunately, there is generally no automated tool to solve this problem, and this requires the administrator to manually set up barriers to prevent hackers to ensure that the company's data security.

The common reason not to protect the database is because of the "trouble" and "complexity" of the job. This is true, but if you apply MySQL, you can use some handy features to dramatically reduce the risks that you face. Six features are listed below:

Step one: Remove wildcard characters from the authorization table

The MySQL access control system runs through a series of so-called authorization tables to define user access rights at the database, table, or column level. However, these tables allow an administrator to set a package license for a user, or a set of tables that apply wildcard characters. This can be potentially dangerous because hackers may use a restricted account to access other parts of the system. For this reason, be cautious when setting user privileges, always ensuring that users have access only to the content they need. Be especially careful when setting up super privileges for individual users, because this level allows ordinary users to modify the basic configuration of the server and access the entire database.

Recommendation: Apply display privilege commands to each user account to review the authorization table to see if it is appropriate to apply the wildcard license.

Step two: Require the use of a secure password

The security of user accounts is closely related to the password used to protect them. Therefore, the first thing to do when installing MySQL is to set the password for the MySQL root account (the default is null). After fixing this vulnerability, you should then require each user account to use a password and do not use the easily identifiable heuristic password, such as birthdays, user names, or words in the dictionary.

Recommendation: Apply the mysql-Security-authorization option to avoid using the old, less secure MySQL password format.

Step three: Check the configuration file license

In general, to make server connections more quickly and conveniently, individual users and server administrators must store their user account passwords in a single user MySQL options file. However, this password is stored as plain text in a file and is easily accessible. Therefore, it is important to ensure that such a single-user profile is not consulted by other users in the system and that it is stored in a non-public location. Ideally, you want a single user profile to be saved in the user's root directory, with a license of 0600.

Step Fourth: Encrypt data transfer between client and server

One important issue with MySQL (and other) client and server architectures is the security of data transfer over the network. If the interaction between the client and the server takes place in plain text, the hacker may "sniff" the transmitted packets to obtain confidential information. You can close this vulnerability by activating SSL in the MySQL configuration, or by applying a security such as OpenSSH, which should be used to establish a secure encrypted "channel" for the transmitted data. Encrypting client-server connections in this form can make it extremely difficult for unauthorized users to access the data that is exchanged.

Fifth step: Prohibit remote access

If the user does not need a remote access server, you can force all MySQL connections to be done through the Unix slot file, thereby greatly reducing the risk of network attack. This process can be done by skipping the network option to start the server. This prevents TCP/IP networks from connecting to MySQL, ensuring that no users can connect to the system remotely.

Recommendation: You can add the bundled address 127.0.0.1 directive to the MySQL server configuration to enhance this functionality, forcing MySQL to bundle the local machine's IP address to ensure that only users in the same system can connect to MySQL.

Sixth step: actively monitor MySQL access record

MySQL has a number of different log files that record client connections, queries, and server errors. Among them, the most important is the general query log, which records each customer's connection and interruption time with the time tag, and records each query executed by the customer. If you suspect an unusual behavior, such as a cyber intrusion, it is a good idea to monitor the log to understand the source of the behavior.

Protecting your MySQL database is a daily job. Therefore, even if you complete the above steps, you should not be lax, understand more security recommendations, actively monitor and update the system security.