MySQL database has always been known for its high performance, high availability and ease of use, and it has become the most popular open source database in the world. A large number of individuals, web developers, large companies and so on their websites, critical systems, software packages widely used MySQL database. often, when deploying a product, many enterprises often do not have the proper attention to security. The most concern of enterprises is to enable them to run as soon as possible, the enterprise can also profit as soon as possible. Some enterprises use the default option when installing MySQL, resulting in unsafe data, and the risk of the server being compromised, and the possibility of performance problems in a short period of time. The best way to ensure MySQL security is provided below.
1. Avoid accessing the MySQL database from the Internet to ensure that specific hosts have access privileges It is dangerous to change the database in the production environment directly from a computer outside the local network. Occasionally, the administrator opens the host access to the database: > GRANT All on * * to ' root ' ('% '); This is actually a complete release of access to root. Therefore, it is important to restrict important operations to specific hosts: > GRANT All on * * to ' root ' @ ' localhost '; > GRANT All on * * to ' root ' @ ' myip.athome ' > FLUSH Privileges at this point, you still have full access, but only the specified IP, regardless of whether it is static, can be accessed.
2. Backup Database regularly any system is likely to have a disaster. Server, MySQL will also crash, it may be compromised, data may be deleted. It is only by preparing for the worst that it is possible to recover quickly from the disaster after the event. It is best to use the backup process as a daily routine for the server.
3. Disabling or restricting remote access as mentioned earlier, if you are using remote access, make sure that only the defined hosts can access the server. This is typically achieved through TCP wrappers, iptables, or any other firewall software or hardware. to restrict opening a network socket, the administrator should add the following parameter to the [mysqld] section of MY.CNF or My.ini: skip-networking These files are located in the Windows C:\Program files\mysql\mysql Server 5.1 folder, or in Linux, My.cnf is located in/etc/, or is located in/etc/mysql/. This line of command disables the initialization of the network connection during MySQL startup. Please note that you can still establish a local connection to the MySQL server here. Another possible option is to force MySQL to listen only to the native by adding the following line in the [mysqld] section of MY.CNF: bind-address=127.0.0.1 you may be reluctant to disable network access if the users of your enterprise connect to the server from their own machine or to a Web server that is installed on another machine. At this point, consider the following limited license access: mysql> GRANT SELECT, INSERT on mydb.* to ' someuser ' @ ' somehost '; here, you want to replace the Someuser with the user name, the somehost into the corresponding host.
4. Set the root user's password and change their login name in Linux, the root user has full access to all databases. Therefore, you must set the root password during the installation of Linux. Of course, to change the default empty password, the method is as follows: Access MySQL console: $ mysql-u root-p execute in MySQL console: > SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' New_password '); in the actual operation, just replace the new_password of the above line with the actual password. another way to change the root password in the Linux console is to use the Mysqladmin tool: $ mysqladmin-u root password new_password at this point, the new_password of the above line is replaced by the actual password. Of course, this is the need to use strong passwords to avoid brute force attacks. another good way to improve the security of the root user more effectively is to rename it. To do this, you must update the MySQL database in the table user. To do this in the MySQL console: > Use MySQL; > UPDATE user SET user= "another_username" WHERE user= "root"; > FLUSH privileges; then, accessing the MySQL console via Linux will use the new username: $ mysql-u another_username-p
5. Test Database Removal in MySQL, which is installed by default, anonymous users can access the test database. We can remove any useless database to avoid having access to the database in unforeseen circumstances. Thus, in the MySQL console, execute: > DROP DATABASE test;
6. Disable local INFILE Another change is to disable the "LOAD DATA local INFILE" command, which helps prevent unauthorized users from accessing local files. This is especially important when new SQL injection vulnerabilities are found in PHP applications. In addition, in some cases, the LOCAL infile command can be used to access other files on the operating system (such as/etc/passwd) and should use the following command: mysql> LOAD DATA LOCAL INFILE '/etc/passwd ' into TABLE table1 A simpler approach is to: mysql> Select Load_file ("/etc/passwd") to disable the local infile command, the following parameters should be added to the [mysqld] section of the MySQL configuration file: set-variable=local-infile=0
7. Removal of anonymous accounts and abandoned accounts some MySQL databases have an empty password for anonymous users. As a result, anyone can connect to these databases. You can use the following command to check: mysql> SELECT * from Mysql.user where user= ""; in a secure system, no information is returned. Another approach is to: mysql> SHOW GRANTS for ' @ ' localhost '; mysql> SHOW GRANTS for ' @ ' myhost '; if grants exists, then anyone can access the database, or at least the default database "Test" can be used. The check method is as follows: shell> mysql-u Blablabla If you want to remove an account, execute the command: mysql> DROP USER ""; support for the drop user command starting with MySQL version 5.0. If you are using an older version of MySQL, you can remove the account as follows: mysql> use MySQL; mysql> DELETE from user WHERE user= ""; mysql> flush Privileges;
8. Reduce System Privileges Common Database Security recommendations have the notion of "reducing privileges to all parties". The same is true for MySQL. In general, developers use the maximum license and do not consider the licensing principle as security management, which exposes the database to a significant risk. to protect the database, it is important to ensure that the file directory where the MySQL database is actually stored is owned by the "MySQL" User and the "MySQL" group. shell>ls-l/var/lib/mysql also, make sure that only the user "MySQL" and the root user can access the/var/lib/mysql directory. MySQL binaries exist in the/usr/bin/directory and should be owned by the root user or a specific "MySQL" user. For these files, other users should not have "write" Access rights: shell>ls-l/usr/bin/my*
9, reduce the user's database privileges Some applications are connected to MySQL through a user name and password for a particular database table, and security personnel should not give the user full access. If an attacker obtains this user with full access, he will have all of the databases. The way to view a user license is to use the command in the MySQL console show GRANT >show GRANTS for ' user ' @ ' localhost '; to define the user's access rights, use the grant command. In the following example, User1 can only be selected from the billing table in the Dianshang database: > GRANT SELECT on Billing.dianshang to ' user1 ' @ ' localhost '; > FLUSH privileges; As a result , User1 users cannot change any data in the table and other tables in the database. on the other hand, if you want to remove access from a user, you should use a revoke command similar to the GRANT command: > REVOKE SELECT on Billing.ecommerce from ' user1 ' @ ' localhost '; > FLUSH privileges;
10. Remove and Disable. mysql_history file all command histories are recorded in ~/.mysql_history when the user accesses the MySQL console. If an attacker accesses the file, he can know the structure of the database. $ cat ~/.mysql_history in order to remove and disable this file, the log should be sent to/dev/null. $export Mysql_histfile=/dev/null The above command causes all log files to be directed to/dev/null, which you should remove from the home folder. mysql_history:$ rm ~/.mysql_history, and create a symbolic link to/dev/null.
11. Security Patches Be sure to keep the database as the latest version. Because an attacker could exploit a known vulnerability in the previous version to access the enterprise's database.
12. Enable Logging If your database server does not perform any queries, it is recommended that you enable trace logging, which you can add by adding: Log =/var/log/mylogfile in the [Mysql] section of the/etc/my.cnf file. for a heavy-duty MySQL database in a production environment, this can cause a high cost to the server. also, make sure that only root and MySQL can access these log files. error Log It is important to ensure that only root and MySQL can access the Hostname.err log files. This file is stored in MySQL data history. The file contains very sensitive information, such as a password, address, table name, stored procedure name, code, and so on, which can be used for information collection and, in some cases, can also provide an attacker with information that exploits a database vulnerability. Attackers can also know the machine or internal data that is installed on the database. MySQL Log make sure that only root and MySQL can access the Logfilexy log file, which is stored in the MySQL history directory.
13. Change the root directory chroot in the UNIX operating system can change the root directory of the currently running process and its child processes. A program that re-obtains the root permission of another directory cannot access or name files outside this directory, which is known as "Chroot prison". by leveraging the chroot environment, you can restrict the write operation of the MySQL process and its child processes and increase the security of the server. you need to ensure a dedicated directory for the chroot environment, such as/chroot/mysql. In addition, to facilitate the use of database management tools, you can change the following parameters in the [Client] section of the MySQL configuration file: [Client] socket =/chroot/mysql/tmp/mysql.sock
14. Disable the local infile command The LOAD DATA LOCAL infile can read files from the file system and appear on the screen or saved in the database. If an attacker is able to find a SQL injection vulnerability from an application, this command is quite dangerous. The following commands can be operated from the MySQL console: > select Load_file ("/etc/passwd"); the command lists all the users. The best way to solve this problem is to disable it in the MySQL configuration, find/etc/my.cnf in CentOS or find/etc/mysql/my.cnf in Ubuntu, and add the following line in the [mysqld] section: set-variable= Local-infile=0. Get. Of course, Chunwangchihan, protecting the security of the server is also critical to securing the MySQL database. Server security is a matter of life and death for a database.
14 best ways to ensure MySQL security
