Skip to content

Illegal login restrictions

In today's increasingly important data security, reasonable connection control and password management strategies are the key to database protection. MatrixOne provides a range of global parameters designed to enhance connection security and password management to prevent malicious attacks and unauthorized access.

  • Connection control parameters

    • connection_control_failed_connections_threshold: This parameter sets the maximum number of failed connections allowed in a short period of time. When the threshold is exceeded, MatrixOne will reject further connection attempts from the client, effectively preventing brute-force and malicious attacks.
    • connection_control_max_connection_delay: This parameter specifies the maximum delay time to wait after the client connection fails. This delay will be applied after the number of connection failures reaches the threshold to prevent further connection attempts and increase the cost of malicious attacks.

  • Password management parameters

    • default_password_lifetime: This parameter specifies the validity period of the user password, the unit is days, and the default value is 0, indicating that the password never expires. When the password expires, the user can still log in to the database, but cannot perform SQL operations unless the password is modified through ALTER USER.
    • password_history: This parameter restricts users from using the most recent historical password when changing their password. If set to 5, the user will not be able to reuse the most recent 5 passwords. This configuration can effectively avoid security risks caused by password reuse.
    • password_reuse_interval: This parameter controls that the user cannot reuse the historical password within the specified time range after the password expires. The unit is days, and the default value is 0, indicating that no reuse check of historical passwords is performed.

Check

SELECT @@global.connection_control_failed_connections_threshold; --The default value is 3
SELECT @@global.connection_control_max_connection_delay; --The default value is 0
SELECT @@global.default_password_lifetime; --The default value is 0
SELECT @@global.password_history; --The default value is 0
SELECT @@global.password_reuse_interval; --The default value is 0

set up

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

set global connection_control_failed_connections_threshold=xx;
set global connection_control_max_connection_delay=xx;--Unit:ms
set global default_password_lifetime=xx;---Unit is day
set global password_history=xx;
set global password_reuse_interval=xx;---Unit is day

Example

connection_control_failed_connections_threshold & connection_control_max_connection_delay

mysql> SELECT @@global.connection_control_failed_connections_threshold;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| @@connection_control_failed_connections_threshold |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

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

set global connection_control_failed_connections_threshold=2;
set global connection_control_max_connection_delay=10000;

--exit, reconnect after exit
mysql> SELECT @@global.connection_control_failed_connections_threshold;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| @@connection_control_failed_connections_threshold |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT @@global.connection_control_max_connection_delay;
+-------------------------------------------------------+
| @@connection_control_max_connection_delay |
+-------------------------------------------------------+
| 10000 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

--Create ordinary users and empower them
create user user1 identified by '111';
create role role role1;
grant create database on account * to role1;
grant alter user on account * to role1;
grant role1 to user1;

Try to log in to user1 user with the wrong password

#First time: Log in with the wrong password
(base) admin@admindeMacBook-Pro matrixorigin.io.cn % mysql -u user1 -h 127.0.0.1 -P 6001 -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user user1. internal error: check password failed

#Second time: Log in with the wrong password
(base) admin@admindeMacBook-Pro matrixorigin.io.cn % mysql -u user1 -h 127.0.0.1 -P 6001 -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user user1. internal error: check password failed

#Third time: Log in with the correct password
(base) admin@admindeMacBook-Pro matrixorigin.io.cn % mysql -u user1 -h 127.0.0.1 -P 6001 -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 20101 (HY000): internal error: user is locked, please try again later

#Wait for about ten seconds, log in again, login successfully
(base) admin@admindeMacBook-Pro matrixorigin.io.cn % mysql -u user1 -h 127.0.0.1 -P 6001 -p111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2662
Server version: 8.0.30-MatrixOne-v MatrixOne

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

default_password_lifetime

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

set global default_password_lifetime=1;

mysql> SELECT @@global.default_password_lifetime; --Effect after reconnection
+----------------------------------+
| @@default_password_lifetime |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)

Stop mo and modify the system time to 1 month later

# Stop mo
>mo_ctl stop

# The system modification time is 1 month later
> sudo date "122518302024"
Wed Dec 25 18:30:00 CST 2024

#View the modified time
> date
Wed Dec 25 18:30:02 CST 2024

#Start mo
>mo_ctl start

#root user connects to mo and confirms the current time
>mo_ctl connect

mysql> select current_timestamp;
+----------------------------------+
| current_timestamp() |
+----------------------------------+
| 2024-12-25 18:32:30.664877 |
+----------------------------------+
1 row in set (0.00 sec)

#Try to log in as an ordinary user, expect to log in, but cannot execute sql statements other than alter user xxx identified by 'xxxx'; and dump users are not subject to this restriction.
>mysql -u sys:user1:role1 -h 127.0.0.1 -P 6001 -p123

mysql> create database db1;
ERROR 20101 (HY000): internal error: password has expired, please change the password
mysql> alter user user1 identified by '123';
Query OK, 0 rows affected (0.01 sec)

#Login with the modified password
>mysql -u sys:user1:role1 -h 127.0.0.1 -P 6001 -p123

mysql> create database db1;
Query OK, 1 row affected (0.03 sec)

password_history

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

set global password_history=2;

mysql> SELECT @@global.password_history;
+-------------------------+
| @@password_history |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.01 sec)

mysql> create user user2 identified by '111';
Query OK, 0 rows affected (0.03 sec)
--Modify the password to '123', and it is successful
mysql> alter user user2 identified by '123';
Query OK, 0 rows affected (0.02 sec)

--Modify the password to 111, fails because password_history=2, MatrixOne will keep the history of the last 2 passwords
mysql> alter user user2 identified by '111';
ERROR 20301 (HY000): invalid input: The password has been used before, please change another one.

--Modify the password to '123', and it is successful
mysql> alter user user2 identified by '234';
Query OK, 0 rows affected (0.02 sec)

--Change the password again to '111', and it will be successful
mysql> alter user user2 identified by '111';
Query OK, 0 rows affected (0.01 sec)

password_reuse_interval

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

mysql> set global password_reuse_interval=30;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.password_reuse_interval; --Effect after reconnection
+--------------------------------+
| @@password_reuse_interval |
+--------------------------------+
| 30 |
+--------------------------------+
1 row in set (0.02 sec)

--Create user user3
create user user3 identified by '111';

--Change user password, successfully
mysql> alter user user3 identified by '123';
Query OK, 0 rows affected (0.02 sec)

--Change the system time to ten days and restart mo, modify the user3 password to ‘111’, fail
mysql> alter user user3 identified by '111';
ERROR 20301 (HY000): invalid input: The password has been used before, please change another one

--Change the system time to two months and restart mo, modify the user3 password to ‘111’, successfully
mysql> alter user user3 identified by '111';
Query OK, 0 rows affected (0.01 sec)