Description:
Error "ERROR 1045 (28000): Access denied for user 'subnet1'@'10.10.10.3' (using password: YES)" when connect from other host using a user created with host subnet definition '255.255.255.248'; if create the same user with '255.255.255.0', the error not occours.
The main subnet to be restrited would be CIDR 10.10.10.0/29.
How to repeat:
### 1) Create logins at destination mysql server ( 10.10.10.1 )
mysql> status;
--------------
mysql Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 55
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.25 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysql.sock
Binary data as: Hexadecimal
Uptime: 1 hour 35 min 28 sec
Threads: 2 Questions: 148 Slow queries: 0 Opens: 532 Flush tables: 3 Open tables: 451 Queries per second avg: 0.025
--------------
mysql> create user 'user1'@'10.10.10.0/255.255.255.248' identified by 'test123';;
Query OK, 0 rows affected (0.02 sec)
mysql> create user 'user2'@'10.10.10.0/255.255.255.0' identified by 'test123';
Query OK, 0 rows affected (0.02 sec)
mysql> select user,host from mysql.user where user like 'subnet%';
+-------+----------------------------+
| user | host |
+-------+----------------------------+
| user1 | 10.10.10.0/255.255.255.248 |
| user2 | 10.10.10.0/255.255.255.0 |
+-------+----------------------------+
2 rows in set (0.00 sec)
mysql>
### Client Error ( 10.10.10.3 )
$> mysql -uuser1 -h10.10.10.1 -ptest123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'10.10.10.3' (using password: YES)
=======================================================================
Additional: mysqld error log contents
2021-06-21T17:31:09.272128Z 0 [System] [MY-010116] [Server] /opt/mysql/server/bin/mysqld (mysqld 8.0.25) starting as process 17754
2021-06-21T17:31:09.569822Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-06-21T17:31:13.921879Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-06-21T17:31:15.000676Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '0.0.0.0' port: 33060, socket: /tmp/mysqlx.sock
2021-06-21T17:31:15.283372Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-06-21T17:31:15.341268Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-06-21T17:31:15.365677Z 0 [ERROR] [MY-011947] [InnoDB] Cannot open '/var/mysql/db/ib_buffer_pool' for reading: No such file or directory
2021-06-21T17:31:15.613520Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-06-21T17:31:15.905462Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003031 - slave_preserve_commit_order is not supported when slave_parallel_type is DATABASE.
2021-06-21T17:31:15.922373Z 0 [System] [MY-010931] [Server] /opt/mysql/server/bin/mysqld: ready for connections. Version: '8.0.25' socket: '/var/run/mysql.sock' port: 3306 MySQL Community Server - GPL.
