MYSQL database reinforcement

Comprehensive MYSQL database consolidation

MYSQL database reinforcement Guide

1. Database storage location check

  • windows system: database cannot be placed on Disk C
  • linux system: database files cannot be stored in /, / var,/usr directory
#Connect mysql data, mysql-uroot-p -- enter password

select @@datadir;

show variables where variable_name='datadir';

Reinforcement method

#Set the path to specify security
set global datadir='route' ;
#Or modify the configuration file my.cnf (linux) or my.init datadir value in (Windows)

2. Run MYSQL database with minimum permission account

  • windows system: open Task Manager to check the permission account of running mysql. It cannot be administrator
  • linux system: it is not a high authority account (root) running mysql, nor can it be in a high authority user group
#ps Command view process, grep Filter results,Use the following command to view the run mysql Users of
ps -ef | grep mysql

Reinforcement method

# 1. Create low privilege accounts and groups
groupadd mysql
useradd -r -g mysql mysql
# 2. Add password to new account
passwd mysql
# 3. modify MYSQL Permission to install directory (close in advance mysql Services)
chown -R mysl.mysql  mysql Installation path
# 4. Minimize read and write execution permissions 755
chmod 755 mysql Install root path
chmod 755 mysql Execute directory after installation(bin)
chmod 755 mysql After installation lib library(libexec)
chmod -R go-rwx mysql Data storage directory(data)
# 5.restart mysql Just service
service mysqld start

3. Disable using mysql command line history

  • Check whether there is. MySQL in the system_ History file: find / -name ".mysql_history "indicates that reinforcement is needed

Reinforcement method

echo "export MYSQL_HISTORY=dev/null" >> /etc/profile
ln -s /dev/null  .mysql_history File path
rm -r .mysql_history File path

4. Control the permission of slow query log file

  • View the name and path of the slow query log file, and then view the permissions. The log file permissions should meet the following requirements: chmoed 660 and chmod mysq.mysql
  • If slow query is not enabled, it indicates security
### The first method
#1. View slow query log file name and path
select @@slow_query_log_file;
show variables like 'slow_query_log_file';
#2. Check whether slow query is enabled, slow_query_log Value: ON Indicates on, OFF Indicates off
show variables like '%query%';
 ### The second method is to directly view mysql configuration file / etc/my.cnf
 cat /etc/my.cnf
 #see[mysqld]Are the following configurations available:

 #3. Permission to view slow query log files
 ls -l Slow query log file path

Reinforcement method

#Modify slow query log permissions as follows
 chmod 660 slow query log file
 chmod  msyql.mysql  Slow query log file

5. Control permissions of general log files

  • View the general log file name and permissions. The log file permissions should meet the following requirements: chmoed 660 and chmod mysq.mysql
  • If slow query is not enabled, it indicates security
#1. Check whether to start general logging, if general_log by ON Indicates on, or off, where general_log_file It is the storage location of general logs
show variables like '%general%';

#2. According to the first step to view the location where the log file is obtained, view the permissions of the general log file
ls -l General log file

Reinforcement method

#Modify permissions and permissions
chmod 660 <log file>
chown mysql:mysql <log file>

6. Authority control of audit log file

  • View the audit log file name and permissions. The log file permissions should meet the following requirements: chmoed 660 and chmod mysq.mysql
  • If slow query is not enabled, it indicates security
#View file path
show variables like '%audit_log_file%';

# View permissions
ls -l Audit log file

Reinforcement method

#Modify permissions and permissions
chmod 660 <log file>
chown mysql:mysql <log file>

7. Check MYSQL version to see if it needs to be patched

show variables like 'version';

Reinforcement method

  • Install patches
  • Upgrade to the latest version of mysql

8. Delete test database

  • If the test default database exists, it needs to be deleted and consolidated
  • If not, no reinforcement is needed
#1. View system database
show databases;

Reinforcement method

drop database test;

9. Read local file to OFF

  • Confirm that mysql database turns OFF the reading function of local files_ Infile should be OFF
# Check whether the local read file function is enabled
show variables like 'local_infile'

Reinforcement method

#Temporary settings
set @@local_infile=0;
#Permanent settings, modify / etc/my.cnf  Add local infile = 0
 Add under [mysqld]

10. Confirm that only administrator authority can manage all tables, view all users and password hashes, and modify authority

# 1. View all database users
select user,host from mysql.user;

# 2. Execute the following two statements to ensure that the result intelligence is the database administrator
SELECT user, host FROM mysql.user WHERE (Select_priv = 'Y') OR (Insert_priv = 'Y') OR (Update_priv = 'Y')
OR (Delete_priv = 'Y')  OR (Create_priv = 'Y')  OR (Drop_priv = 'Y');
SELECT user, host FROM mysql.db WHERE db = 'mysql' AND ((Select_priv = 'Y') OR (Insert_priv = 'Y') OR (Update_priv = 'Y') OR (Delete_priv = 'Y') OR (Create_priv = 'Y') OR (Drop_priv = 'Y'));

Reinforcement method

# 1. Ensure that a database uses one user (as far as possible, each database uses a separate user). Each user can only have full permissions on his own database and table, and partial permissions on other database tables according to the situation
# 2. Ensure that rights management can only be an administrator
## Authorization operation: Grant privileges to the goods table of database test to xiaoming user
grant privileges on test.goods to xiaoming@localhost

privileges: indicates the authority to be granted, such as select, insert, delete, update, etc. if you want to grant ALL the authority, fill in ALL
atabasename.tablename : indicates which table of which database the user's permission can be used. If you want the user's permission to affect all tables of all databases, fill in the ", * is a wildcard, indicating all.
username@host : indicates which user is authorized.
GRANT select,insert ON zje.zje To 'aaa' @ '%'; / / indicates that aaa is authorized to insert and select zje tables in the zje library.

Grant all on. To 'aaa' @ '%; / / indicates that aaa is authorized to grant all the rights to all the tables in the library.

##Delete unrelated users
drop user 'test' @ '%'; (delete test user

###Revoke user rights
revoke privileges ON database.tablename FROM 'username'@'host';
privileges,database.tablename Same as above for 'username' @'host '

According to the actual situation of the database user rights recovery

11. Restrict non administrative authority

file_priv: indicates whether the user is allowed to read the local file of the host where the database is located;

Process: indicates whether users are allowed to query command execution information of all users;

Super_priv: indicates whether the user has high-level permissions such as setting global variables and administrator debugging;

Shutdown_priv: indicates whether the user can close the database;

Create_user_priv: indicates whether users can create or delete other users;

Grant_priv: indicates whether the user can modify the permissions of other users;

Ensure that only the database administrator has the above permissions. Use the following sql statements to view the database accounts with various permissions:

select user, host from mysql.user where File_priv = 'Y';

select user, host from mysql.user where Process_priv = 'Y';

select user, host from mysql.user where Process_priv = 'Y';

SELECT user, host FROM mysql.user WHERE Shutdown_priv = 'Y';

SELECT user, host FROM mysql.user WHERE Create_user_priv = 'Y';

SELECT user, host FROM mysql.user WHERE Grant_priv = 'Y';
SELECT user, host FROM mysql.db WHERE Grant_priv = 'Y';

Reinforcement method

# Reclaim permissions
REVOKE FILE ON *.* FROM '<user>';






Where user is the non administrator user queried above.
For example: Reclaim local file read permission of user xiaoming
revoke file on . from 'xiaoming';

12. Control DML/DDL operation authorization reasonably

  • DML/DDL statements include permissions to create or modify database structures, such as insert, update, delete, create, drop and alter statements. In any database, such permissions of users should be controlled to ensure that only non administrator users with business requirements are authorized. Execute the following command from the Mysql command line:
SELECT User,Host,Db FROM mysql.db WHERE Select_priv='Y' OR Insert_priv='Y' OR Update_priv='Y' OR Delete_priv='Y' OR Create_priv='Y'
OR Drop_priv='Y' OR Alter_priv='Y';

Reinforcement method

REVOKE SELECT ON <host>.<database> FROM <user>;
REVOKE INSERT ON <host>.<database> FROM <user>;
REVOKE UPDATE ON <host>.<database> FROM <user>;
REVOKE DELETE ON <host>.<database> FROM <user>;
REVOKE CREATE ON <host>.<database> FROM <user>;
REVOKE DROP ON <host>.<database> FROM <user>;
REVOKE ALTER ON <host>.<database> FROM <user>;
#among<user>For the unauthorized users found, host Is the relevant host, database Is the relevant database.

13. Enable the error log audit function

  • The error log includes a series of activity information in the process of database running and stopping, which is helpful to analyze some abnormal activities in the process of database running. In general, it is necessary to turn on the error log recording function, and use the following command to query:
show variables like 'log_error';
#Make sure the returned result is not empty. If it is empty, you need to mysql Add related configuration to database configuration file.

Reinforcement method

#stay/etc/my.cnf Join configuration
log-error=log File path

14. Ensure logs are stored in non system areas

  • The log file will increase with the operation of the database. If it is stored in the system area, it will affect the normal operation of the system. Use the following command to query:
show variables like 'log_error';
#Or go straight mysql File view /etc/my.cnf log-error Value of
#Make sure that the return result is not the following path:/,/var,/usr

Reinforcement method

#Modify mysql configuration file log error value is not /, / var /, / usr

15. Turn off the original log function

  • The original log option determines whether some sensitive information will be written into the log in clear text, such as query log, slow query log and binary log. Ensure that the following configuration items exist in the database configuration file:
#See if there is one in the configuration file log-raw=OFF perhapslog-raw=0
cat /etc/my.cnf

Reinforcement method

#modify mysql configuration file /etc/my.cnf join log-raw=0

16. Encryption strength of user password

  • old_ The value of passwords cannot be 0. If it is 0, the user password can be cracked
select @@old_passwpords;
#A value of 0 means reinforcement is required, otherwise reinforcement is not required

Reinforcement method

set global old_passwords=1;
set global old_passwords=2;
## Or modify / etc/my.cnf   Join old_passwords=1
old_passords=1 #Or equal to 2

17. secure_auth option settings

  • If the client uses old_ If security is set on the server side, the password initiates the connection request_ Auth, the client will reject the connection request. You can configure it in the configuration file according to the security requirements.
#See if there is one in the configuration file secure_auth And the value is not 0
cat /etc/my.cnf

Reinforcement method

## Or modify / etc/my.cnf   Join secure_auth=1
secure_auth=1 #Or equal to 2

18. Ensure that all users require a non empty password to log in

  • Execute the following statement to query whether a user can log in without a password:
SELECT User,host FROM mysql.user WHERE (plugin IN('mysql_native_password', 'mysql_old_password') AND (LENGTH(Password) = 0 OR Password IS NULL))OR (plugin='sha256_password' AND LENGTH(authentication_string) = 0);
#If no user is returned, it means it does not exist
#Or use the following statement to see if there is an empty password
select user,host from msyql.user;

Reinforcement method

#Add password to the user with null interface user The value of specifies the user, password('')Encrypt the user password, new password Specify the user's password
update mysql.user set password=password('new password') where user='root';

19. Scheduled database backup

  • Regular data backup, remote backup and full backup

Reinforcement method

#Create a scheduled task script to back up the data at a specified time interval

a. Create a new backup file and give it permission to execute

mkdir -p /home/mysql_backup/
touch /home/mysql_backup/
chmod 551 /home/mysql_backup/

b. Edit / home/mysql_backup/

vim /home/mysql_backup/

c. Write the following

time=` date +%Y_%m_%d_%H_%M_%S `
mysqldump --all-databases -u $db_user -p$db_pass | gzip > $backupdir/$time.sql.gz
find $backupdir -name "*.sql.gz" -type f -mtime +5 -exec rm -rf {} \; > /dev/null 2>&1

d. Edit crontab

crontab -e

e. Add on last line

* */1 * * * root /home/mysql_backup/

f, Restart crontab

service crond restart

Tags: MySQL Database Windows Linux

Posted on Sun, 07 Jun 2020 03:40:43 -0400 by ale1981