mysql database security policy

MySQL system itself can set password complexity and automatic expiration policy, which may be less used. Recently, I was informed to adjust the password and began to understand it

mysql password complexity policy

The MySQL system comes with validate_password plug-in, which can verify the password strength. Passwords that do not reach the specified strength are not allowed to be set. MySQL versions 5.7 and 8.0 do not seem to enable the plug-in by default, which also allows us to set the password at will, such as 123, 123456, etc. If we want to standardize the password strength from the root, we can enable the plug-in

Check to see if this plug-in is installed

Enter the MySQL command line and click show plugins or view validate_password related parameters can determine whether the plug-in has been installed. If there are no relevant parameters, it means that the plug-in is not installed
```
# If the pre installation check is empty, the plug-in is not installed
mysql> show variables like 'validate%';
Empty set (0.00 sec)
```

Install validate_password plug-in

Enter the MySQL command line and click show plugins or view validate_password related parameters can determine whether the plug-in has been installed. If there are no relevant parameters, it means that the plug-in is not installed

# This plug-in can be installed through the install plug in command
# The file name suffix of each platform is different. For Unix and Unix like systems, it is so, for Windows dll

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.28 sec)
# View validate_password related parameters
mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

Explanation of password strength related parameters

Install validate_ After the password plug-in, some parameters related to password strength are added
1. validate_password_policy
  The default password policy is MEDIUM. The configurable values are as follows:
  0 or LOW only needs to comply with the password length (specified by the parameter validate_password_length)
  1 or MEDIUM meets the LOW policy and at least one number, lowercase letter, uppercase letter and special character
  2 or strongmeet the MEDIUM policy, and the password cannot exist in the dictionary file
2. validate_password_length
  Used to set the minimum length of the password. The default value is 8
3. validate_password_mixed_case_count
  When validate_ password_ When the policy is set to MEDIUM or STRONG, the number of lowercase and uppercase letters in the password is at least 1 by default and 0 by minimum; The default is to have at least one lowercase and one uppercase letter.
4. validate_password_number_count
  When validate_ password_ When the policy is set to MEDIUM or STRONG, the minimum number of digits in the password is 1 by default and 0 by minimum
5. validate_password_special_char_count
  When validate_ password_ When the policy is set to MEDIUM or STRONG, the minimum number of special characters in the password is 1 by default and 0 by minimum

Specific settings of password complexity policy

After learning the above parameters, we can set the password complexity strategy according to our own situation. For example, if I want the password to be at least 15 characters and contain uppercase and lowercase letters, numbers and special characters, we can do so.

# Set the password length to at least 15 digits
mysql> set global validate_password_length = 15;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate%';                                                                                   
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 15     |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

Test password complexity

The password complexity policy is only valid for operations after it takes effect. For example, if you have an account before and the password is 123456, the account can still be used. However, if you change the password again, you need to meet the complexity policy. Let's test the specific effect of the password complexity policy.

mysql> create user 'test-password'@'%' identified by '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'test-password'@'%' identified by 'ab123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'test-password'@'%' identified by 'password123456!';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'test-password'@'%' identified by 'password123456!A';
Query OK, 0 rows affected (0.00 sec)

Automatic password expiration policy

View user password status

# View the user password status, which is N (no) not expired and Y (expired)
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+---------------+-----------+------------------+-------------------+-----------------------+----------------+
| user          | host      | password_expired | password_lifetime | password_last_changed | account_locked |
+---------------+-----------+------------------+-------------------+-----------------------+----------------+
| root          | localhost | N                |              NULL | 2020-10-27 13:21:25   | N              |
| mysql.session | localhost | N                |              NULL | 2020-10-27 13:14:58   | Y              |
| mysql.sys     | localhost | N                |              NULL | 2020-10-27 13:14:58   | Y              |
| root          | %         | N                |              NULL | 2020-10-27 13:22:52   | N              |
| cmf           | %         | Y                |              NULL | 2021-06-08 15:39:36   | N              |
| amon          | %         | N                |              NULL | 2020-10-28 13:11:34   | N              |
| hue           | %         | N                |              NULL | 2020-10-28 15:03:35   | N              |
| hive          | %         | N                |              NULL | 2021-06-08 18:35:08   | N              |
| oozie         | %         | N                |              NULL | 2020-10-28 15:05:40   | N              |
| nacos         | %         | N                |              NULL | 2020-10-29 14:58:15   | N              |
+---------------+-----------+------------------+-------------------+-----------------------+----------------+

Set individual user passwords to expire immediately

mysql> ALTER USER 'oozie'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user where user = 'oozie';
+---------------+-----------+------------------+-------------------+-----------------------+----------------+
| user          | host      | password_expired | password_lifetime | password_last_changed | account_locked |
+---------------+-----------+------------------+-------------------+-----------------------+----------------+
| oozie         | %         | Y                |              NULL | 2020-10-28 15:05:40   | N              |
+---------------+-----------+------------------+-------------------+-----------------------+----------------+
1 rows in set (0.00 sec)

The password has expired and the user cannot do anything

mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Set individual user password expiration time

# Set the user password expiration time to 60 days
mysql> ALTER USER 'oozie'@'%' PASSWORD EXPIRE INTERVAL 60 DAY;
Query OK, 0 rows affected (0.00 sec)

Set global user password expiration time

Manually change before adding a profile

mysql> SET GLOBAL default_password_lifetime = 60;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 60    |
+---------------------------+-------+
1 row in set (0.02 sec)

Write configuration file and restart to take effect

[mysqld]
# Password expiration policy
default_password_lifetime = 60
# Password verification policy
plugin-load = validate_password.so
validate_password_length = 15
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT
validate_password_mixed_case_count = 1
validate_password_number_count  = 1
validate_password_special_char_count = 1

Restart mysql

[root@node90 mysql]# systemctl stop mysqld.service
[root@node90 mysql]# systemctl start mysqld.service

Restrict IP remote access

Configure users to restrict access to ip fields only

# Now users can access all hosts
mysql> select user, host from mysql.user where user='LimitIP';
+---------+-----------+
| user    | host      |
+---------+-----------+
| LimitIP | * |
+---------+-----------+
1 row in set (0.00 sec)

mysql> update mysql.user set host='192.168.103.17' where user='LimitIP';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user, host from user where user='LimitIP';
ERROR 1046 (3D000): No database selected
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
# Now the user only allows this IP address 192.168 103.17 access
mysql> select user, host from user where user='LimitIP';
+---------+----------------+
| user    | host           |
+---------+----------------+
| LimitIP | 192.168.103.17 |
+---------+----------------+
1 row in set (0.00 sec)

The above test found that if this is the case, only mysql.xml is modified User table, then the previous permissions are no longer available, as shown below. If you query mysql db, mysql.tables_priv found that the Host field value is still*

mysql>  select * from mysql.db where user='LimitIP'\G;
*************************** 1. row ***************************
				 Host: *
				   Db: MyDB
				 User: LimitIP
		  Select_priv: Y
		  Insert_priv: N
		  Update_priv: N
		  Delete_priv: N
		  Create_priv: N
			Drop_priv: N
		   Grant_priv: N
	  References_priv: N
		   Index_priv: N
		   Alter_priv: N
Create_tmp_table_priv: N
	 Lock_tables_priv: N
	 Create_view_priv: N
	   Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
		 Execute_priv: N
		   Event_priv: N
		 Trigger_priv: N
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from mysql.tables_priv where user='LimitIP'\G;
*************************** 1. row ***************************
	   Host: *
		 Db: MyDB
	   User: LimitIP
 Table_name: kkk
	Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Insert,Update,Delete
Column_priv: 
1 row in set (0.00 sec)

ERROR: 
No query specified

So continue to modify mysql db, mysql.tables_priv table. Of course, if the permissions of the account are more than these levels, you may have to modify it, such as mysql columns_ priv,mysql.procs_priv et al
Reference blog: Related articles

Keywords: Database MySQL Big Data

Added by jscix on Sat, 18 Dec 2021 03:14:44 +0200

Programming VIP

mysql database security policy

mysql database security policy

mysql password complexity policy

Check to see if this plug-in is installed

Install validate_password plug-in

Explanation of password strength related parameters

Specific settings of password complexity policy

Test password complexity

Automatic password expiration policy

Set individual user password expiration time

Set global user password expiration time

Write configuration file and restart to take effect

Restart mysql

Restrict IP remote access

Popular Keywords