MySQL Database security settings

As the platform of data management, the security of the database is determined by the internal security and network security of the system first. For system administrators, the first thing to ensure the security of the system itself, when installing the MySQL database, the basic environment needs to be better configured.

1, modify the root user password, delete the empty password
The default installed MySQL root user is a blank password, for security purposes, must be modified as a strong password, so-called strong password, at least 8 bits, composed of letters, numbers and symbols of irregular passwords. Use the MySQL command mysaladmin to modify the root password, you can also log in to the database, modify the database MySQL User table field content, modify the method as follows:

#/usr/local/mysql/bin/mysqladmin-u root Password "Upassword"//Use Mysqladmin
#mysql > Use MySQL;
#mysql > Update user set Password=password (' Upassword ') where user= ' root ';
#mysql > Flush Privileges; Forces the memory authorization table to be flushed, otherwise the memory-buffered password is used

2. Delete the default database and database users
In general, the MySQL database is installed locally and requires only local PHP scripts to read MySQL, so many users do not need to, especially the default installed users. MySQL initialization will automatically generate empty users and test libraries, to install the test, which will be a threat to the security of the database, it is necessary to delete all, the final state only a single root can be, of course, later on as needed to increase the user and database.

#mysql > show databases;
#mysql > DROP database test; Delete Database test
#use MySQL;
#delete from DB; Delete the table information that holds the database because there is no database information.
#mysql > Delete from the user where not (user= ' root '); Delete the initial non-root user
#mysql > Delete from user where user= ' root ' and password= '; Delete the root of the empty password and try to repeat the operation
Query OK, 2 rows Affected (0.00 sec)
#mysql > Flush Privileges; Force flush Memory Authorization table.

3. Change the default MySQL administrator account
The administrator name of the system MySQL is root, and in general, the database administrator has not been modified, which is to some extent to the user of the malicious behavior of the system to provide convenience, this time to modify the complex user name, please do not set in the form of admin or Administraror, Because they are also in the user Dictionary of Yiche.

mysql> Update user set user= "Newroot" where user= "root"; Change to a user name that is not easy to guess
mysql> flush Privileges;

4, about the management of the password
Password is a very important factor in database security management, do not save plaintext passwords to the database. If your computer is in danger of security, intruders can get all the passwords and use them. Instead, use the MD5 (), SHA1 (), or one-way hash functions. Also do not choose the password from the dictionary, there is a special program to crack them, please choose at least eight bits, composed of letters, numbers and symbols strong password. When accessing the password, use the SQL statement of MySQL's built-in function password () to encrypt and store the password. For example, add a new user to the Users table in the following ways.

#mysql > INSERT into users values (1,password (1234), ' Test ');

5. Run MSYQL with an independent user
Never run a MySQL server as a root user. This is dangerous because any user with file permissions can create a file with root (for example, ~ROOT/.BASHRC). Mysqld refuses to run with root unless it is clearly specified with the--user=root option. Mysqld should be run with ordinary non-privileged users. As in the previous installation process, create a separate MySQL account for the database that is used for managing and running MySQL.

To start mysqld with another UNIX user, increase the user option to specify the username of the [mysqld] group in the MY.CNF options file of the/ETC/MY.CNF options file or the server data directory.
#vi/etc/my.cnf
[Mysqld]
User=mysql
This command enables the server to be started with the specified user, regardless of whether you start manually or through Mysqld_safe or mysql.server, ensuring the identity of MySQL is used. You can also start the database by adding the user parameter.

#/usr/local/mysql/bin/mysqld_safe--user=mysql &
As other Linux users without root running mysqld, you do not need to change the root user name in the user table because the username of the MySQL account is not related to the username of the Linux account. Ensure that Mysqld runs only with Linux users who have read or write access to the database directory.

6. Prohibit remote connection to database
See under command line Netstat-ant, the default port of 3306 is open, at this time open the Mysqld network listens, allow the user to remote through the account password to connect the local data base, by default is allowed to connect remotely. To disable this feature, start skip-networking, do not listen for any TCP/IP connections to SQL, cut off remote access rights, and ensure security. If you need to manage the database remotely, you can do so by installing phpMyAdmin. If you do need to remotely connect to the database, at least modify the default listening port, and add firewall rules that allow only the data for the trusted network's MySQL listening port to pass.

# VI/ETC/MY.CF
Remove the #skip-networking comment.
#/usr/local/mysql/bin/mysqladmin-u Root-p shutdown//Stop database
#/usr/local/mysql/bin/mysqld_safe--user=mysql &//Background with MySQL user start MySQL

7. Limit the number of connected users
It is necessary to restrict a user of a database from multiple remote connections, which can degrade performance and affect other users ' operations. You can do this by restricting the number of connections allowed for a single account, setting the Max_user_connections variable in the mysqld of the my.cnf file. The grant statement can also support resource control options to limit the extent to which a server is allowed to use an account.

#vi/etc/my.cnf
[Mysqld]
Max_user_connections 2

8. User Directory permission limit
The default MySQL is installed in/usr/local/mysql, and the corresponding database file is in the/usr/local/mysql/var directory, therefore, it must be ensured that the directory cannot be accessed by unauthorized users after the database package copy away, so to restrict access to the directory. Ensure that Mysqld runs only with Linux users who have read or write access to the database directory.
# chown-r root/usr/local/mysql///mysql home directory to root
# Chown-r Mysql.mysql/usr/local/mysql/var//Ensure that the database directory permissions belong to the MySQL user

9. Command history protection
Database-related shell operations commands are recorded in. Bash_history, and if these files are inadvertently read, it can cause information disclosure such as database password and database structure, and the operation after logging into the database will be recorded in the. mysql_history file, If you use the Update table information to modify the database user password, you will also be read the password, so you need to delete the two files, while in the login or backup database and other password-related operations, should use the-p parameter to enter the password, implicitly enter the password, it is recommended to empty the above file.

# rm. bash_history. Mysql_history//Delete history
# ln-s/dev/null. Bash_history//Empty the shell record file
# ln-s/dev/null. Mysql_history//Empty the MySQL log file

10. Disable MySQL access to local files
In MySQL, you provide a read of the local file, using the Load data local infile command, By default in version 5.0, this option is turned on by default, which uses MySQL to read the local files to the database, then the user can illegally obtain sensitive information, if you do not need to read local files, be sure to close.

Test: First build the Sqlfile.txt file under the test database, separating the fields with commas
# VI Sqlfile.txt
1,sszng,111
2,sman,222
#mysql > Load Data local infile ' sqlfile.txt ' into table the users fields terminated by ', '; Read in Data
#mysql > select * from Users;
+--------+------------+----------+
| UserID | Username | password |
+--------+------------+----------+
| 1 | Sszng | 111 |
| 2 | Sman | 222 |
+--------+------------+----------+

When you successfully insert local data into your data, you should disable the "LOAD data local INFILE" command in MySQL. Some of the attacks that are circulating on the network are useful in its load DATA LOCAL infile, and it is also a means for many newly discovered SQL injection attack exploits! Hackers can also use the load datalocal infile to load "/etc/passwd" into a database table, and then can display it with SELECT, which is fatal to the security of the server. You can add local-infile=0 to the MY.CNF, or local-infile=0 start MySQL with a parameter.

#/usr/local/mysql/bin/mysqld_safe--user=mysql--local-infile=0 &
#mysql > Load Data local infile ' sqlfile.txt ' into table the users fields terminated by ', ';
#ERROR 1148 (42000): The used command isn't allowed with this MySQL version

The--local-infile=0 option starts mysqld Disable all the load DATA local commands from the server side, if you need to get local files, it needs to be opened, but it is recommended to close.

11, MySQL server permissions control
The main function of the MySQL privilege system is to verify the user connected to a given host and give the user the Select, INSERT, update, and delete permissions on the database (see the User Super User table). Its additional functionality includes the ability to have anonymous users and for MySQL-specific functions such as load DATA infile to authorize and manage operations.

Administrators can configure tables such as User,db,host to control user access, and user table permissions are superuser privileges. It is advisable to grant the user table permissions only to Superuser, such as the server or database supervisor. For other users, you should set the permissions in the user table to ' N ' and authorize only on a specific database basis. You can authorize specific databases, tables, or columns, and FILE permissions give you the load DATA infile and select ... into OutFile statement reads and writes files on the server, and any user granted file permission can read or write any file that the MySQL server can read or write. (indicates that the user can read files in any database directory because the server can access the files). The file permission allows the user to create a new file under a directory where the MySQL server has write permissions, but cannot overwrite the existing file in the user table's File_priv setting Y or N. , so when you don't need to read the server file, turn off the permission.

#mysql > Load Data infile ' sqlfile.txt ' into table loadfile.users fields terminated by ', ';
Query OK, 4 rows Affected (0.00 sec)//read local information sqlfile.txt '
Records:4 deleted:0 skipped:0 warnings:0
#mysql > Update user set file_priv= ' N ' where user= ' root '; Prohibit Read permission
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0
mysql> flush Privileges; Refresh Authorization Table
Query OK, 0 rows Affected (0.00 sec)
#mysql > Load Data infile ' sqlfile.txt ' into table users fields terminated by ', '; Re-login Read file
#ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using password:yes)//failure
# mysql> SELECT * from loadfile.users to outfile ' test.txt ' fields terminated by ', ';
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)
For security reasons, always check with the show grants statement to see who has accessed what. Then use the REVOKE statement to delete the permissions that are no longer needed.

12. Use Chroot mode to control the running directory of MySQL
Chroot is a high-level system protection in Linux that is almost completely isolated from the main system, that is, if there is any problem, it will not compromise the running Master System. This is a very effective approach, especially when configuring a network service program.

13. Turn off support for Web Access
If you do not intend to use the MySQL database for Web Access, do not provide suchWeb language like PHP, reset or compile your PHP, and cancel their default support for MySQL. If the server uses PHP and other web programs, try to test the Web form of illegal requests, if you get any form of MySQL error, immediately analyze the reasons, timely modification of the Web program, blocking the vulnerability, to prevent MySQL exposure in front of the web.
For web security checks, as suggested in the MySQL official documentation, for Web apps, check at least the following checklist:
Try the Web form to enter single and double quotation marks ("' and '"). If you get any form of MySQL error, analyze the cause immediately.
Try modifying the dynamic URL where you can add%22 (' "'),%23 (' # '), and%27 (").
Try modifying the data type in the dynamic URL, using the characters from the previous example, including numbers and character types. Your application should be secure enough to protect against such modifications and similar attacks.
Try entering characters, spaces, and special symbols, and do not enter numbers for numeric fields. Your application should delete or generate errors before passing them to MySQL. It is dangerous to pass the unchecked value to MySQL!
Check the size of the data before passing it to MySQL.
Connect the application to the database using a user name other than the administrative account. Do not give the application any unwanted access rights.

14. Database Backup Strategy
Can generally take the form of local backup and network backup, can take the mysqldump of MySQL itself and direct copy backup form,

Direct copying of data files is most straightforward, fast, and convenient, but the disadvantage is that incremental backups are largely not possible. To ensure data consistency, you need to execute the following SQL statement before backing up the file: Flush TABLES with READ lock, which is to flush the in-memory data to disk while locking the data table to ensure that no new data is written during the copy process. This method backs up the data recovery is also very simple, directly copied back to the original database directory.

Using mysqldump, you can load the entire database into a single text file. This file contains all the SQL commands needed to rebuild your database. This command obtains all the schemas (schema, which are explained later) and converts them into DDL syntax (create statement, i.e. database definition statement), obtains all the data, and creates insert statements from the data. This tool reverses all the designs in your database. Because everything is contained in a text file. This text file can be directed back to MySQL with a simple batch and a suitable SQL statement.

Backup with mysqldump is very simple, if you want to back up the database "Nagios_db_backup", using the command, while using the pipeline gzip command to compress the backup file, it is recommended to use the form of offsite backup, using rsync, etc. Mount the directory of the backup server to the database server, package the database file backup, and back up the data by crontab scheduled time:

#!/bin/sh
Time= ' date + ' ("%F") "%R"
$/usr/local/mysql/bin/mysqldump-u Nagios-pnagios Nagios | Gzip >/home/sszheng/nfs58/nagiosbackup/nagios_backup. $time. gz
# crontab-l
# m H Dom Mon Dow command
XX * * * */home/sszheng/shnagios/backup.sh

To recover data using commands:
gzip-d nagios_backup./(2008-01-24/) 00/:00.gz
Nagios_backup. (2008-01-24) 00:00
#mysql –u root-p Nagios

Iii. mysqld Security-related startup options
The following mysqld options affect security:
--allow-suspicious-udfs
This option controls whether the main function can be loaded with a user-defined function that has only the xxx character. By default, this option is turned off and only UDF with at least the auxiliary character can be loaded. This prevents shared object files from being loaded into functions that never contain legitimate UDFs.
--LOCAL-INFILE[={0|1}]
If you start the server with--local-infile=0, the client cannot use the local in LOAD data statement.
--old-passwords
Forces the server to generate a short (pre-4.1) password hash for the new password. This is useful in order to ensure compatibility when the server must support older versions of the client program.
(OBSOLETE)--safe-show-database
In previous versions of MySQL, this option enabled the show databases statement to show only the names of the databases with partial permissions for the user. In MySQL 5.1, this option is no longer used as the default behavior, and a show databases permission can be used to control each account's access to the database name.
--safe-user-create
If enabled, the user cannot create a new user with the GRANT statement unless the user has INSERT permission for the Mysql.user table. If you want the user to have permission to create a new user, you should grant the user the following permissions:
Mysql> GRANT INSERT (user) on Mysql.user to ' user_name ' @ ' host_name ';
This ensures that the user cannot directly change the permission column and must use the GRANT statement to grant the permission to other users.
--secure-auth
An account with an old (pre-4.1) password is not allowed to be authenticated.
--skip-grant-tables
This option causes the server to not use the permission system at all. This gives everyone the power to fully access all the databases! (by executing the mysqladmin flush-privileges or mysqladmin eload command, or by executing the flush privileges statement, you can tell a running server to start using the authorization table again.) ）
--skip-name-resolve
Host name is not parsed. All the column values for the host in the authorization table must be the IP number or localhost.
--skip-networking
TCP/IP connections are not allowed on the network. All connections to Mysqld must be made via a UNIX socket.
-skip-show-database
With this option, only users with show databases permissions are allowed to execute the show databases statement, which displays all database names. Without this option, all users are allowed to execute show DATABASES, but only the name of the database that the user has show DATABASES permissions or partial database permissions is displayed. Note that global permissions refer to the permissions of the database.

"Turn" http://blog.chinaunix.net/uid-25057421-id-3481345.html

MySQL Database security settings