MySQL system security management and optimization

1. Disable MySQL from running with Administrator account privileges

MySQL should be run with a non-administrator account and run securely with a normal account mysqld

Hardening Method: Configure the User=mysql in the MY.CNF configuration file

2. Set the root user password and modify the login name, and there is no empty password account

• To modify the root user password, execute it in the MySQL console:

> Set password for ' root ' @ ' localhost ' =password (' new_password '); #实际操作中, just replace the new_password above with the actual password

• In order to improve the security of the root user more effectively, it is renamed. The MySQL database in the table user needs to be updated. Execute in console:

> Use mysql;> Update user set user= "Another_username" where user= "root";> flush privileges;

Then, you can access the MySQL console through $ mysql-u another_username-p.

• All users in the database should be configured with a password, and the following statements can be used to query for a blank password account:

> select * from Mysql.user where user= "";

3. Configure the appropriate password strength, with a maximum age of less than 90 days

• Database user Miami complexity includes length, casing, and special characters.

Hardening method: Add the following configuration line to the global configuration

Plugin-load = validate_password.so #加载密码强度验证插件validate_password_length = #密码长度最小为14, default is 8 Valida Te_password_mixed_case_count = 1 #至少包含的小写字母个数和大写字母个数validate_password_number_count = 1 #至少包含的数字个数validate_passwo Rd_special_char_count = 1 #至少包含的特殊字符个数validate_password_policy = MEDIUM #密码强度等级, three kinds: 0/low, 1/medium, 2/strong, default to Me Dium

Three levels of password strength are required as follows:

Policy Tests Performed0 or Low Length1 or MEDIUM Length; Numeric, lowercase/uppercase, and special characters2 or strong Length; Numeric, lowercase/uppercase, and special characters; Dictionary file

• User password expiration time is less than 90 days in the console execution:

> Set global default_password_lifetime=90;

4. Reduce the user's database privileges, only the administrator has full database access rights

• The Mysql.user and mysql.db tables in the MySQL database list the various permissions that can be granted (or denied) to MySQL users, which are generally not available to every MySQL user and are usually reserved only to administrator users.

Hardening method: Audit Each privilege granted to the user, and for non-administrative users, use the REVOKE statement to remove the permissions appropriately.

# The privileges in the Mysql.user table are: File_priv: Indicates whether the user is allowed to read the local file of the host on which the database resides; Process: Indicates whether the user is allowed to query command execution information for all users; Super_priv: Indicates whether the user has set global variables. High-level permissions such as Administrator debugging; Shutdown_priv: Indicates whether the user can close the database, Create_user_priv: Indicates whether the user can create or delete another user, Grant_priv: Indicates whether the user can modify other user rights

Query the SQL statement being executed:

> Show processlist;# or > Use information_schema;> select * from Processlist where info was not null;

Use the following command to view the database account with the appropriate permissions:

Select Host,user from Mysql.user where file_priv= ' Y ';

If a non-administrator user exists, use the following command to recycle permissions:

Revoke file on *. * from ' MySQL ';

5. Prohibit or restrict remote access to ensure that specific hosts have access rights

> Grant All on * * to ' root ' ('% ');

• The above authorization allows root to be used on all hosts for all execution permissions on the database, to restrict the use of a specific host:

> Grant All on * * to ' root ' @ ' localhost ';> grant all on * * to ' root ' @ ' hostname_ip '; #可以是ip或者主机名

If you want to cancel access on a host, you can use:

> Revoke all on * * from ' root ' @ ' hostname_ip ';

If, only partial permissions are granted, you can use:

> Grant Select on mydb.* to ' someuser ' @ ' hostname_ip ';

6. Configure MySQL logs for easy auditing

MySQL system security management and optimization