Mysql Security Configuration

Starting remote services with the root user has always been a security taboo, because if the service program encounters problems, remote attackers are very likely to gain full control of the host. MySQL from version 3.23.15
A small change was made at the beginning of this article. After the installation by default, the service should be started by mysql users, and root users are not allowed to start the service. If you have to use the root user for startup, you must add -- user = root.

(./Safe_mysqld -- user = root &). Because MySQL contains SQL statements for LOAD DATA INFILE and SELECT... INTO OUTFILE, if the root user starts

MySQL server, the database user has the write permission of the root user. However, MySQL still imposes some restrictions. For example, load data infile can only read globally readable files.

, SELECT... into outfile cannot overwrite existing files.

Local log files cannot be ignored, including shell logs and MySQL logs. Some users log on to or back up the database locally for the convenience of the diagram, sometimes in the command line parameters

The Database Password is directly included in the data, for example:

Shell>/usr/local/mysql/bin/mysqldump-uroot-ptest test> test. SQL
Shell>/usr/local/mysql/bin/mysql-uroot-ptest

These commands will be recorded by shell in history files. For example, bash will write the. bash_history file in the user directory. If these files are accidentally read, the database password will be leaked.

. The SQL commands executed after you log on to the database are also recorded in the. mysql_history file in the user directory by MySQL. If the database user uses an SQL statement to modify the Database Password

Because of the. mysql_history file. Therefore, do not add a password after-p during shell login and backup. Instead, enter the database password after prompt.
In addition, we should not allow these two files to record our operations, just in case.

Shell> rm. bash_history. mysql_history
Shell> ln-s/dev/null. bash_history
Shell> ln-s/dev/null. mysql_history

These two commands link these two files to/dev/null, so our operations will not be recorded in these two files.
Notes for programming

No matter which programming language is used to write programs connected to the MySQL database, there is a rule that never trust the data submitted by users!
For numeric fields, we need to use the query statement: SELECT * FROM table where id = '000000'. Do not use a query statement like SELECT * FROM table where id = 234.

. MySQL automatically converts a string to a numeric character unless it is a numeric character. If the data submitted by the user is processed by mysql_escape_string, we can completely eliminate it.

SQL inject attacks. For SQL inject attacks, see the following link:
Http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
Http://www.ngssoftware.com/papers/advanced_ SQL _injection.pdf
Notes for various programming languages:

1) All Web programs:
A) try to enter single quotation marks and double quotation marks in the Web form to test possible errors and find out the cause.
B) modify the URL parameter % 22 ('"'), % 23 ('#'), and % 27 (''').
C) For numeric field variables, our application must perform strict checks, otherwise it is very dangerous.
D) check whether the data submitted by the user exceeds the length of the field.
E) do not grant excessive access permissions to users who connect their programs to the database.

2) PHP:
A) check whether the data submitted by the user is processed by addslashes before the query. After PHP 4.0.3, the MySQL c api-based function mysql_escape_string () is provided ().

3) MySQL c api:
A) check whether the query string is called using the mysql_escape_string () API.

4) MySQL ++:
A) check whether the query string is processed using escape and quote.

5) Perl DBI:
A) check whether the quote () method is used for the query string.

6) Java JDBC:
A) check whether the PreparedStatement object is used for the query string.

4. Tips

1) if you accidentally forget the MySQL root Password, you can add the -- skip-grant-tables parameter when starting the MySQL server to skip the authentication of the authorization table (./safe_mysqld

-- Skip-grant-tables &) so that we can directly log on to the MySQL server, modify the password of the root user, and restart MySQL to log on with the new password.

2) When the MySQL server is started, add -- skip-show-database so that general database users cannot browse other databases.

3) Add the -- chroot = path parameter when starting the MySQL server to run the mysqld daemon in the chroot environment. In this way, the SQL statements LOAD DATA INFILE and SELECT...

OUTFILE is limited to read and write files under chroot_path. Note that a MySQL. sock file will be created after mysql is started, which is in the/tmp directory by default. Used

After chroot, MySQL will create the mysql. sock file in chroot_path/tmp. If you do not have the chroot_path/tmp directory or do not have the write permission to this directory, you cannot

Create the mysql. sock file and MySQL will fail to start. For example, if we add the -- chroot =/usr/local/mysql/startup parameter, it is best to create a user that can start MySQL to write
/Usr/local/mysql/tmp directory. Of course, we can also use -- socket = path to specify the path of the mysql. sock file, but this path must be in chroot_path.

4) when the MySQL server is started, add the -- log-slow-queries [= file] parameter, so that mysqld will write the SQL command execution time beyond long_query_time to the file. If no

With the Specified = file, mysqld writes to the hostname-slow.log under the data directory by default. If only filename is specified and no path is specified, mysqld also writes filename

Data Directory. We can use this log file to find query statements that have been executed for a long time, and optimize the statements as much as possible to relieve the burden on the MySQL server.

5) if we only need to use the MySQL service on the local machine, we can add the -- skip-networking startup parameter so that MySQL does not listen to any TCP/IP connection, increasing security.