Skip to content

Password complexity verification

MatrixOne provides a range of system variables for configuring password complexity verification to ensure password security. These variables support dynamic modification, with the core variable validate_password, and the remaining settings only take effect when validate_password is turned on.

  • validate_password: The switch that controls the password complexity verification function, value range: ON | OFF (default: OFF).

  • validate_password.changed_characters_percentage: Specifies the character ratio required by the new password to the old password, the value range is: [0-100] (default: 0).

  • Password Policy (validate_password.policy): used to define global password complexity policies, supporting two modes: 0/low and 1/medium:

    Policy Effective parameters
    0/LOw validate_password.length
    1/MEDIUM validate_password.length
    validate_password.mixed_case_count
    validate_password.number_count
    validate_password.special_char_count

    • validate_password.length: Specify the minimum character length of the password, value range: >= 0 (default: 8).

    • validate_password.mixed_case_count: Requires the minimum number of upper and lower case characters included in the password, with a value range: >= 0 (default: 1).

    • validate_password.number_count: Specifies the minimum number of numeric characters that must be included in the password, with a value range: >= 0 (default: 1).

    • validate_password.special_char_count: Specify the minimum number of special characters to contain in the password, the value range is >= 0 (default: 1).

Check

select @@global.validate_password;
select @@global.validate_password.changed_characters_percentage;
select @@global.validate_password.check_user_name;
select @@global.validate_password.length;
select @@global.validate_password.mixed_case_count;
select @@global.validate_password.number_count;
select @@global.validate_password.special_char_count;

set up

After setting, you need to exit the reconnection before it can take effect.

set global validate_password=xx; --Default is 0
set global validate_password.changed_characters_percentage=xx; --Default is 0
set global validate_password.check_user_name=xx;---default is 1
set global validate_password.policy=xx;--Default is 0
set global validate_password.length=xx;--Default is 8
set global validate_password.mixed_case_count=xx;---Default is 1
set global validate_password.number_count=xx;---default is 1
set global validate_password.special_char_count==xx;---default is 1

Example

validate_password

mysql> select @@global.validate_password;
+--------------------------+
| @@validate_password |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> set global validate_password=1;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@global.validate_password; --Reconnection takes effect
+--------------------------+
| @@validate_password |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)

validate_password.changed_characters_percentage

mysql> select @@global.validate_password.changed_characters_percentage;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| @@validate_password.changed_characters_percentage |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

# Create user u1, characters account for 0%, creation is successful
mysql> create user u1 identified by '12345678';
Query OK, 0 rows affected (0.02 sec)

mysql>set global validate_password.changed_characters_percentage=80;--Set the character proportion to 80%:

mysql> select @@global.validate_password.changed_characters_percentage; --Reconnection takes effect
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| @@validate_password.changed_characters_percentage |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 80 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

# Create user u2, characters account for 0%, creation failed
mysql> create user u2 identified by '12345678';
ERROR 20301 (HY000): invalid input: Password '12345678' does not contain enough changed characters

# Create user u2, characters account for 20%, creation failed
mysql> create user u2 identified by '12345678ab';
ERROR 20301 (HY000): invalid input: Password '12345678ab' does not contain enough changed characters

# Create user u2, with 80% of characters, and the creation is successful
mysql> create user u4 identified by '12abdefhij';
Query OK, 0 rows affected (0.01 sec)

The following parameters need to be enabled to validate_password.policy.

mysql> select @@global.validate_password.policy;
+----------------------------------+
| @@validate_password.policy |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.00 sec)
set global validate_password.policy=1;

mysql> select @@global.validate_password.policy;--Reconnection takes effect
+----------------------------------+
| @@validate_password.policy |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)

validate_password.length

mysql> select @@global.validate_password.length;
+----------------------------------+
| @@validate_password.length |
+----------------------------------+
| 8 |
+----------------------------------+
1 row in set (0.00 sec)# Create user u3, password length is 8, creation is successful
mysql> create user u3 identified by 'Pass123!';
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.length=9;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.validate_password.length;
+----------------------------------+
| @@validate_password.length |
+----------------------------------+
| 9 |
+----------------------------------+
1 row in set (0.00 sec)

# Create user u4, password length is 8, creation failed
mysql> create user u4 identified by 'Pass123!';
ERROR 20301 (HY000): invalid input: Password 'Pass123!' is too short, require at least 9 characters

# Create user u4, password length is 9, creation is successful
mysql> create user u4 identified by 'Pass1234!';
Query OK, 0 rows affected (0.02 sec)

validate_password.mixed_case_count

mysql> select @@global.validate_password.mixed_case_count;
+---------------------------------------------+
| @@validate_password.mixed_case_count |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)

--Create user u4, password contains an uppercase letter and a lowercase letter, created successfully
mysql> create user u4 identified by 'Pa12345!';
Query OK, 0 rows affected (0.01 sec)

--Set validate_password.mixed_case_count to 2
mysql> set global validate_password.mixed_case_count=2;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.validate_password.mixed_case_count; --Reconnection takes effect
+---------------------------------------------+
| @@validate_password.mixed_case_count |
+---------------------------------------------+
| 2 |
+---------------------------------------------+
1 row in set (0.00 sec)

--Create user u5, password contains an uppercase letter and a lowercase letter, creation failed
mysql> create user u5 identified by 'Pa12345!';
ERROR 20301 (HY000): invalid input: Password 'Pa12345!' does not meet the Lowercase requirements

--Create user u5, password contains two uppercase letters and two lowercase letters, creation failed
mysql> create user u5 identified by 'PPaa123!';
Query OK, 0 rows affected (0.01 sec)

validate_password.number_count

mysql> select @@global.validate_password.number_count;
+---------------------------------------+
| @@validate_password.number_count |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)

--Create user u6, password contains 1 number, creation is successful
mysql> create user u6 identified by 'Password1!';
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.number_count=2;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.validate_password.number_count;
+---------------------------------------+
| @@validate_password.number_count |
+---------------------------------------+
| 2 |
+---------------------------------------+
1 row in set (0.00 sec)

--Create user u7, password contains a number, creation failed
mysql> create user u7 identified by 'Password1!';
ERROR 20301 (HY000): invalid input: Password 'Password1!' does not meet the Number requirements

--Create user u7, password contains two numbers, creation is successful
mysql> create user u7 identified by 'Password12!';
Query OK, 0 rows affected (0.01 sec)

validate_password.special_char_count

mysql> select @@global.validate_password.special_char_count;
+-------------------------------------------------+
| @@validate_password.special_char_count |
+-------------------------------------------------+
| 1 |
+-------------------------------------------------+
1 row in set (0.00 sec)

--Create user u8, password contains a special character, creation is successful
mysql> create user u8 identified by 'Password123!';
Query OK, 0 rows affected (0.01 sec)

mysql> set global validate_password.special_char_count=2;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@global.validate_password.special_char_count; --Effect after reconnection
+-------------------------------------------------+
| @@validate_password.special_char_count |
+-------------------------------------------------+
| 2 |
+-------------------------------------------------+
1 row in set (0.00 sec)

--Create user u9, password contains a special character, creation failed
mysql> create user u9 identified by 'Password123!';
ERROR 20301 (HY000): invalid input: Password 'Password123!' does not meet the Special Char requirements

--Create user u9, password contains two special characters, creation is successful
mysql> create user u9 identified by 'Password123!!';
Query OK, 0 rows affected (0.01 sec)