MySQL Security Configuration

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

Modify the root user password and delete the empty password

The root user of MySQL installed by default has an empty password. For security reasons, it must be changed to a strong password. The so-called strong password is at least 8 characters, and an irregular password composed of letters, numbers and symbols. Use the command mysaladmin that comes with MySQL to modify the root password, and you can also log in to the database and modify the field content of the user table under the database mysql. The modification method is 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; //Forcibly refresh the memory authorization table, otherwise the password buffered in memory is still used

Delete the default database and database user

Under normal circumstances, the MySQL database is installed locally, and only the local php script is required to read mysql, so many users do not need it, especially the users who are installed by default. After MySQL is initialized, an empty user and a test library will be automatically generated for installation testing, which will pose a threat to the security of the database. It is necessary to delete all of them. The final state can only retain a single root. Of course, users and databases will be added later as needed.

#mysql> show databases;

#mysql> drop database test; //Delete the database named as test

#use mysql;

#delete from db; //Delete the table information that stores the database, because there is no database information yet.

#mysql> delete from user where not (user=’root’) ; // Delete the initial user which are not root

#mysql> delete from user where user=’root’ and password=”; //Delete the root user which password is empty

Query OK, 2 rows affected (0.00 sec)

#mysql> flush privileges;

Change the default mysql administrator account

The administrator name of the system mysql is root, and under normal circumstances, the database administrator does not modify it, which facilitates the exhaustive malicious behavior of system users to a certain extent. Set to the form of admin or administrator, because they are also in the easy-to-guess user dictionary.

#mysql> update user set user=”newroot” where user=”root”; //Change to a username that is not easy to guess

#mysql> flush privileges;

Password management

Passwords are a very important factor in database security management. Do not save plain text passwords into the database. If your computer is at risk, an intruder can get all your passwords and use them. Instead, MD5(), SHA1(), or a one-way hash function should be used. Also don’t choose passwords from dictionaries, there are special programs that can crack them, choose strong passwords with at least eight characters, consisting of letters, numbers and symbols. When accessing the password, use the sql statement of the built-in function password() of mysql to encrypt the password and store it. For example, the following way to add a new user to the users table.

#mysql> insert into users values (1,password(1234),’test’);

Running msyql as a separate user

Never run the MySQL server as the root user. This is very dangerous because any user with FILE privileges can create files as root (eg, ~root/.bashrc). mysqld refuses to run as root unless explicitly specified with the –user=root option. mysqld should be run as a normal unprivileged user. As in the previous installation process, create a separate mysql account in linux for the database, which is used only for managing and running MySQL.

To start mysqld as another Unix user, add the user option to specify the user name of the `[mysqld]` group in the `/etc/my.cnf` options file or the `my.cnf` options file in the server data directory.

#vim /etc/my.cnf

[mysqld]

user=mysql

# /usr/local/mysql/bin/mysqld_safe –user=mysql &

To run mysqld as another linux user without root, you do not need to change the root username in the user table, because the username of the MySQL account has nothing to do with the username of the linux account. Make sure that mysqld runs only as a linux user with read or write permissions to the database directory.

Disable remote connection to database

From the command line netstat -ant, you can see that the default port 3306 is open. At this time, the network monitoring of mysqld is turned on, allowing users to remotely connect to the local database through the account and password. By default, remote data connection is allowed. In order to prohibit this function, start skip-networking, do not monitor any TCP/IP connection of sql, cut off the right of remote access, and ensure security. If you need to manage the database remotely, it can be achieved by installing PhpMyadmin. If you really need to connect to the database remotely, at least modify the default listening port and add firewall rules to only allow data from the mysql listening port of the trusted network to pass.

# vim /etc/my.cf

remove the comment of “#skip-networking”

# /usr/local/mysql/bin/mysqladmin -u root -p shutdown //Stop database

#/usr/local/mysql/bin/mysqld_safe –user=mysql & //run mysql by user “mysql” at background

Limit the number of connected users

A user of the database connects remotely for many times, which will cause performance degradation and affect the operations of other users, so it is necessary to limit it. This can be done by limiting the number of connections allowed for a single account, by setting the `max_user_connections` variable in mysqld in the `my.cnf` file. The GRANT statement can also support resource control options to limit the scope of usage that the server is allowed to use for an account.

#vim /etc/my.cnf

[mysqld]

max_user_connections 2

User directory permission restrictions

The default mysql is installed in /usr/local/mysql, and the corresponding database files are in the /usr/local/mysql/var directory. Therefore, it must be ensured that the directory cannot be accessed by unauthorized users and the database is packaged and copied. , so restrict access to that directory. Make sure that mysqld runs only as a linux user with read or write permissions to the database directory.

# chown -R root /usr/local/mysql/ //give the main index of mysql to root

# chown -R mysql.mysql /usr/local/mysql/var //ensure the permission of database folder belongs to mysql user

Command history protection

The shell operation commands related to the database will be recorded in .bash_history respectively. If these files are accidentally read, information such as database password and database structure will be leaked, and the operations after logging in to the database will be recorded in the .mysql_history file. If you use update If you modify the database user password with table information, the password will also be read. Therefore, you need to delete these two files. At the same time, when performing password-related operations such as logging in or backing up the database, you should use the -p parameter to add a prompt to enter the password. Enter the password in the format, it is recommended to leave the above file blank.

# rm .bash_history .mysql_history //Delete history record

# ln -s /dev/null .bash_history //empty the history file of shell

# ln -s /dev/null .mysql_history //empty the history file of mysql

Disable MySQL access to local files

In mysql, to provide reading of local files, the load data local infile command is used. By default in version 5.0, this option is enabled by default. This operation command will use MySQL to read local files into the database, and then the user You can illegally obtain sensitive information. If you don’t need to read local files, be sure to close it.

Test: First create a sqlfile.txt file under the test database, and separate fields with commas

# vi sqlfile.txt

1,scott,123

2,damien,456

#mysql> load data local infile ‘sqlfile.txt’ into table users fields terminated by ‘,’; //reading data

#mysql> select * from users;

+——–+————+———-+

| userid | username | password |

+——–+————+———-+

| 1 | scott | 123 |

| 2 | damien | 456 |

+——–+————+———-+

#/usr/local/mysql/bin/mysqld_safe –user=mysql –local-infile=0 &

#mysql> load data local infile ‘sqlfile.txt’ into table users fields terminated by ‘,’;

#ERROR 1148 (42000): The used command is not allowed with this MySQL version

MySQL Server Privilege Control

The main function of the MySQL privilege system is to authenticate a user connected to a given host, and to grant that user SELECT, INSERT, UPDATE, and DELETE privileges on the database (see the user superuser table for details). Additional features include anonymous users and the ability to authorize and manage MySQL-specific functions such as LOAD DATA INFILE.

Administrators can configure user, db, host and other tables to control user access rights, and user table rights are super user rights. It is wise to only grant privileges to the user table to superusers such as the server or database supervisor. For other users, you should set permissions in the user table to ‘N’ and grant permissions only on a database-specific basis. You can authorize specific databases, tables, or columns. FILE privileges allow you to use LOAD DATA INFILE and SELECT … INTO OUTFILE statements to read and write files on the server. Any user who is granted FILE privileges can read or write. The MySQL server can read and write them. or any document written. (Indicates that the user can read files in any database directory, because the server can access these files). The FILE privilege allows users to create new files in a directory that the MySQL server has write access to, but cannot overwrite the File_priv setting of Y or N in the user table for existing files. , so when you don’t need to read the server file, please turn off this permission.

#mysql> load data infile ‘sqlfile.txt’ into table loadfile.users fields terminated by ‘,’;

Query OK, 4 rows affected (0.00 sec) //Read info from ‘sqlfile.txt’ locally

Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

#mysql> update user set File_priv=’N’ where user=’root’; //forbidden read permission

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

#mysql> load data infile ‘sqlfile.txt’ into table users fields terminated by ‘,’; // Re-signin and read file

#ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) //Failed

# mysql> select * from loadfile.users into outfile ‘test.txt’ fields terminated by ‘,’;

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

To be on the safe side, use the SHOW GRANTS statement to check to see who has accessed what at any time. Then use the REVOKE statement to remove permissions that are no longer needed.

If you don’t plan to use MySQL database for Web access, when no Web language such as PHP is provided, reset or compile your PHP to remove their default support for MySQL. If a web program such as php is used in the server, try using an illegal request in the form of web. If you get any form of MySQL error, immediately analyze the cause, modify the web program in time, plug the loophole, and prevent MySQL from being exposed to the web.
For web security checks, as suggested in MySQL official documentation, for web applications, at least check the following list:

Generally, the form of local backup and network backup can be used, and the mysqldump method that comes with MySQL itself and the form of direct copy backup can be used.
Copying data files directly is the most direct, fast, and convenient method, but the disadvantage is that incremental backup is basically impossible. In order to ensure the consistency of data, it is necessary to execute the following SQL statement before backing up the file: FLUSH TABLES WITH READ LOCK; that is, flush the data in the memory to the disk, and lock the data table at the same time to ensure that there will be no data in the copy process. New data is written. The data backed up by this method is also very simple to restore, just copy it back to the original database directory.
Use mysqldump to load the entire database into a single text file. This file contains all the SQL commands needed to rebuild your database. This command takes all the schema (Schema, explained later) and converts it into DDL syntax (CREATE statement, that is, the database definition statement), takes all the data, and creates an INSERT statement from this data. This tool reverses all designs in your database. Because everything is included into a text file. This text file can be imported back into MySQL with a simple batch process and an appropriate SQL statement.
Using mysqldump to back up is very simple. If you want to back up the database “nagios_db_backup”, use the command and use the pipeline gzip command to compress the backup file. It is recommended to use the form of remote backup. You can use Rsync and other methods to mount the directory of the backup server to On the database server, the database file backup is packaged, and the data is regularly backed up through crontab:

#!/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

00 00 * * * /home/sszheng/shnagios/backup.sh

Restore：

gzip -d nagios_backup.\(2008-01-24\)00\:00.gz

nagios_backup.(2008-01-24)00:00

#mysql –u root -p nagios < /home/sszheng/nfs58/nagiosbackup/nagios_backup.\(2008-01-24\)12\:00

Enable SSL connection

MySQL does not enable SSL connection by default. You can use wireshakr to capture packets to view the executed SQL statements and execution results. Under complex networks such as station library separation, master-slave replication, and master-slave synchronization, the database execution process may be sniffed.

Enable SSL on installation

In the MySQL5.7 installation initialization stage, there is one more operation than the previous version, and this operation is to install SSL.

shell> bin/mysqld –initialize –user=mysql # MySQL 5.7.6 and up

shell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up

[root mysql_data]# ll *.pem

-rw——- 1 mysql mysql 1675 Jun 12 17:22 ca-key.pem #CA private key
-rw-r–r– 1 mysql mysql 1074 Jun 12 17:22 ca.pem #self-signed CA certificate, the client connection also needs to provide
-rw-r–r– 1 mysql mysql 1078 Jun 12 17:22 client-cert.pem #The certificate file required by the client to connect to the server
-rw——- 1 mysql mysql 1675 Jun 12 17:22 client-key.pem #The private key file required by the client to connect to the server
-rw——- 1 mysql mysql 1675 Jun 12 17:22 private_key.pem #private member of private key/public key pair
-rw-r–r– 1 mysql mysql 451 Jun 12 17:22 public_key.pem #Common members of the private/public key pair
-rw-r–r– 1 mysql mysql 1078 Jun 12 17:22 server-cert.pem #Server certificate file
-rw——- 1 mysql mysql 1675 Jun 12 17:22 server-key.pem #Server-side private key file

root> mysql -h 10.126.xxx.xxx -udba -p

dba:(none)> show global variables like ‘%ssl%’;

+—————+—————–+

| Variable_name | Value |

+—————+—————–+

| have_openssl | YES |

| have_ssl | YES | #已经开启了SSL

| ssl_ca | ca.pem |

| ssl_capath | |

| ssl_cert | server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | server-key.pem |

+—————+—————–+

dba:(none)> \s

————–

/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 2973

Current database:

Current user: [email protected]

SSL: Cipher in use is DHE-RSA-AES256-SHA #Indicates that the dba user uses SSL to connect to the mysql server. If it is not ssl, “Not in use” will be displayed.

Current pager: more

Using outfile: ”

Using delimiter: ;

Server version: 5.7.18-log MySQL Community Server (GPL)

Protocol version: 10

Connection: 10.126.126.160 via TCP/IP

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 2 hours 35 min 48 sec

* If the user uses the local localhost or sock to connect to the database, then the SSL method will not be used.

Enable SSL after installation

* Shut down the MySQL service
* Run the mysql_ssl_rsa_setup command
* Go to the data_dir directory to modify the ownership of the .pem file and the user is mysql

chown -R mysql.mysql *.pem

* Start the MySQL service

Force a user to use SSL to connect to the database

Modify an existing user

ALTER USER ‘dba’@’%’ REQUIRE SSL;

#Create a new user that must use SSL

grant select on *.* to ‘dba’@’%’ identified by ‘xxx’ REQUIRE SSL;

For users who are forced to use ssl connection above, if they do not use ssl connection, an error will be reported, like the following:

[root]# /usr/local/mysql/bin/mysql -udba -p -h10.126.xxx.xxx –ssl=0
Enter password:
ERROR 1045 (28000): Access denied for user ‘dba’@’10.126.xxx.xxx’ (using password: YES)

Mysqld security related startup options

The following mysqld options affect security:

• * –allow-suspicious-udfs
  • * * This option controls whether user-defined functions whose main function has only xxx characters can be loaded. By default, this option is turned off, and only UDFs with at least helper characters can be loaded. This prevents functions from being loaded from shared object files that do not contain valid UDFs.
• * `–local-infile[={0|1}]`
  • * * If the server is started with –local-infile=0, the client cannot use the LOCAL in LOAD DATA statement.
• * –old-passwords
  • * * Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful to ensure compatibility when the server must support older versions of client programs.
• * (OBSOLETE) –safe-show-database
  • * * In previous versions of MySQL, this option caused the SHOW DATABASES statement to display only database names for which the user has partial privileges. In MySQL 5.1, this option is no longer used as the current default behavior, and there is a SHOW DATABASES privilege that can be used to control per-account access to the database name.
• * –safe-user-create
  • * * If enabled, users cannot create new users with the GRANT statement unless the user has INSERT privileges on the mysql.user table. If you want the user to have authorization rights to create new users, you should grant the user the following rights:
  • * * mysql> GRANT INSERT(user) ON mysql.user TO ‘user_name’@’host_name’;
  • * * This ensures that the user cannot directly change the privilege column, and the privilege must be granted to other users using the GRANT statement.
• * –secure-auth
  • ** Do not allow authentication of accounts with old (pre-4.1) passwords.
• * –skip-grant-tables
  • * * This option causes the server to not use the permissions system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing the mysqladmin flush-privileges or mysqladmin eload commands, or by executing the FLUSH PRIVILEGES statement.)
• * –skip-name-resolve
  • ** The hostname is not resolved. All Host column values ​​in the authorization table must be IP numbers or localhost.
• * –skip-networking
  • * * TCP/IP connections are not allowed on the network. All connections to mysqld must be made via Unix sockets.
• * –skip-show-database
  • * * With this option, only users with SHOW DATABASES authority 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 database names for which the user has SHOW DATABASES authority or partial database authority are displayed. Note that global permissions refer to database permissions.