Description:
Migration from an old MyDSQL installation to LTS (8.4.3 and now 8.4.4) version.
Followed the documentation which said sha256_password is deprecated and created all users with caching_sha2_password.
Created proxy user accounts as documented in:
https://dev.mysql.com/doc/refman/8.4/en/proxy-users.html
Result proxying isn't working.
User can login but has no rights as expected.
Same procedure with a user with sah256_password -> user receives the correct rights.
Could not found any information in the documentation about a problem with cachching_sha2_password.
How to repeat:
Create 3 users:
1. with sha256_password
2. with caching_sha2_password
3. proxy user with no_login.
Grants rights to user 1 and 2.
| test_user_6 | sha256_password |
| test_user_7 | caching_sha2_password |
mysql> show grants for 'test_user_6'@'10.234.16.0/255.255.255.0';
+---------------------------------------------------------------------------------------+
| Grants for test_user_6@10.234.16.0/255.255.255.0 |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user_6`@`10.234.16.0/255.255.255.0` |
| GRANT PROXY ON `bc_monitoring_proxy`@`%` TO `test_user_6`@`10.234.16.0/255.255.255.0` |
+---------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)
mysql> show grants for 'test_user_7'@'10.234.16.0/255.255.255.0';
+---------------------------------------------------------------------------------------+
| Grants for test_user_7@10.234.16.0/255.255.255.0 |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user_7`@`10.234.16.0/255.255.255.0` |
| GRANT PROXY ON `bc_monitoring_proxy`@`%` TO `test_user_7`@`10.234.16.0/255.255.255.0` |
+---------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)
Login and check proxy:
-with sha256_password
mysql> SELECT USER(), CURRENT_USER();
+-------------------+-----------------------+
| USER() | CURRENT_USER() |
+-------------------+-----------------------+
| test_user_6@db-01 | bc_monitoring_proxy@% |
+-------------------+-----------------------+
1 row in set (0,00 sec)
mysql> select @@proxy_user;
+-------------------------------------------+
| @@proxy_user |
+-------------------------------------------+
| 'test_user_6'@'10.234.16.0/255.255.255.0' |
+-------------------------------------------+
1 row in set (0,00 sec)
- with caching _sha2_password:
mysql> SELECT USER(), CURRENT_USER();
+-------------------+---------------------------------------+
| USER() | CURRENT_USER() |
+-------------------+---------------------------------------+
| test_user_7@db-01 | test_user_7@10.234.16.0/255.255.255.0 |
+-------------------+---------------------------------------+
1 row in set (0,01 sec)
mysql> select @@proxy_user;
+--------------+
| @@proxy_user |
+--------------+
| NULL |
+--------------+
1 row in set (0,00 sec)
Suggested fix:
- make documentation clear -> native proxying isn't working with caching_sha2_password at the moment
- add info to deprecation message at
https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/sha256-pluggable-authentication.ht...
- add feature to caching_sha2_password
