Update:
Found a post here:
https://blogs.oracle.com/mysql/post/client-ip-address
but I'm a little bit suprised about the security argumentation.
If a user can login from a wrong IP address, this is not secure for me. It is then nice to see from which IP the intruder came but it is to late.
I tried the method described and created a new user with require=ssl to ensure that the connection was encrypted.
xxx@fedora:~$ mysql -u test_nutzer_5 -p -h 10.234.16.46 -P 6446
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3446990
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 |
+---------------+-----------------------------+
1 row in set (0,084 sec)
-> can login and ssl is used.
Then I tried the code snipped as admin user:
mysql> select program_name, last_statement, user,attr_value 'client ip'
-> from performance_schema.session_account_connect_attrs
-> join sys.processlist on conn_id=processlist_id
-> where attr_name='_client_ip' ;
Empty set (0,00 sec)
OK, after the login I can see my connection_id 3446990.
In sys.processlist I can find the user with the connection_id.
mysql> select * from sys.processlist where conn_id='3446990'\G;
*************************** 1. row ***************************
thd_id: 3447018
conn_id: 3446990
user: test_nutzer_5@router-01
db: NULL
command: Sleep
state: NULL
time: 102
current_statement: NULL
execution_engine: PRIMARY
statement_latency: NULL
progress: NULL
lock_latency: 9.00 us
cpu_latency: 0 ps
rows_examined: 17
rows_sent: 3
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: YES
last_statement: show databases
last_statement_latency: 1.93 ms
current_memory: 1.03 MiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: 998.48 us
trx_state: COMMITTED
trx_autocommit: YES
pid: 6441
program_name: mysql
1 row in set (0,31 sec)
as test_nutzer_5 I can not read the processlist table so it is not possible to use the snipped.
But i can read the performance_schema.session_account_connect_attrs and can see:
MySQL [(none)]> select * from performance_schema.session_account_connect_attrs;
+----------------+---------------------+-----------------------------+------------------+
| PROCESSLIST_ID | ATTR_NAME | ATTR_VALUE | ORDINAL_POSITION |
+----------------+---------------------+-----------------------------+------------------+
| 3446990 | _os | Linux | 0 |
| 3446990 | _client_name | libmariadb | 1 |
| 3446990 | _pid | 6441 | 2 |
| 3446990 | _client_version | 3.3.10 | 3 |
| 3446990 | _platform | x86_64 | 4 |
| 3446990 | program_name | mysql | 5 |
| 3446990 | _server_host | 10.234.16.46 | 6 |
| 3446990 | _client_ip | 192.168.2.1 | 7 |
| 3446990 | _client_port | 46184 | 8 |
| 3446990 | _client_ssl_cipher | ECDHE-RSA-AES256-GCM-SHA384 | 9 |
| 3446990 | _client_ssl_version | TLSv1.2 | 10 |
+----------------+---------------------+-----------------------------+------------------+
11 rows in set (0,083 sec)
Ok a result.
BUT:
the _client_ip is wrong.
My IP is:
sfl@fedora:~$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host noprefixroute
valid_lft forever preferred_lft forever
2: eno1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether e4:e7:49:81:1a:06 brd ff:ff:ff:ff:ff:ff
altname enp2s0
inet 192.168.1.32/24 brd 192.168.1.255 scope global dynamic noprefixroute eno1
valid_lft 82119sec preferred_lft 82119sec
inet6 2a01:c23:6115:7b00:c6d8:f3f4:daf4:63fe/64 scope global dynamic noprefixroute
valid_lft 7054sec preferred_lft 3454sec
inet6 fe80::918:a951:ea4c:86b0/64 scope link noprefixroute
valid_lft forever preferred_lft forever
My client is in a 192.168.1.xx network and I use a VPN connection. My VPN IP is
4: tun0: <POINTOPOINT,MULTICAST,NOARP,UP,LOWER_UP> mtu 1400 qdisc fq_codel state UNKNOWN group default qlen 500
link/none
inet 10.251.150.6 peer 10.251.150.5/32 scope global noprefixroute tun0
valid_lft forever preferred_lft forever
inet6 fe80::a9dc:1674:2325:a5e/64 scope link stable-privacy proto kernel_ll
valid_lft forever preferred_lft forever
The listed IP in the table is the IP of one router but not of the router in front of the db router.
-> the _client_ip info is not correct - not that I'm looking for
-> looking at connected and always authentificated and authorized IP addresses is not the security way I'm looking for
- I'm a little bit confused about this router implementation and the meaning of:
"For these reasons, as you know security is very important for us at Oracle, MySQL doesn’t support the Proxy Protocol."
Best
Silvio