| Bug #117174 | Provide secondary password usage in P_S table rather than logging on each connect | ||
|---|---|---|---|
| Submitted: | 10 Jan 6:20 | Modified: | 10 Jan 7:58 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S4 (Feature request) |
| Version: | 8.4.3 / 9.1.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | credentials rotation, second password | ||
[10 Jan 6:28]
MySQL Verification Team
Hello Simon, Thank you for the feature request! regards, Umesh
[10 Jan 6:34]
Simon Mudd
To be clear the type of log entry that I am talking about is this: 2025-01-10T06:23:28.153836Z 1516743 [Note] [MY-013300] [Server] Plugin mysql_native_password reported: 'Second password was used for login by user: 'username1'@'127.0.0.1'.' This is on 8.4.3 while upgrading and running 8.0 with 8.4 native_password has been enabled temporarily while credential cleanup is taking place. The same would apply once migrating to caching_sha2_password. For reference I'm aware of the option to adjust my configuration and add the error number above to my existing exclusion list of: log_error_suppression_list = 10559,10914,10926,10927,13360 For password rotation it's convenient to know which of the 2 passwords is actually being used to be sure that the user has adjusted credentials correctly. Legal PoliciesYour Privacy RightsTerms of UseContact Us
[10 Jan 7:58]
Simon Mudd
After talking with some other people it was also brought up that while handling MySQL password rotation as an administrator we want to see which of the 2 passwords is being used. That information is currently not provided in MySQL. The current logging shows if the new password is being used but logging is not the best way of showing that. So perhaps the original suggestion I made could be extended a little further for the requested performance_schema table to be extended a little further and have the following columns: username username logging in host hostname / ip address of username password_type primary / second first_seen timestamp when first login was seen last_seen timestamp when last login was seen count counter of successful login attempts using this user/host/password_type This would simplify password rotation as it would provide the administrator with the ability to determine if the password rotation from primary to secondary or secondary back to primary was completed or not. Note: if the host is provided this table may get to be very large. I often see connections from a large number of different application hosts and if the number of users is large then this table's size could become problematic. It is convenient to see the host part to be able to identify which user uses which password but if this causes a performance issue it it not entirely necessary.
Description: I notice on a server which runs 8.4.3 the following: 18:35 [user@host ~]$ sudo grep MY-013300 /var/log/mysqld.log | awk '{ print $17 }' | sort | uniq -c 5128 'username1'@'127.0.0.1'.' 19 'username2'@'127.0.0.1'.' 279 'username3'@'10.%'.' 2091 'username4'@'10.%'.' 29706 'username5'@'127.0.0.1'.' 18:35 [user@host ~]$ This is very noisy, even on this "not very busy server". I'm aware that with mysqld native filtering you can remove these log entries but I think that generating them for every connect is not helpful. Some people might like this on a low activity server but on a busy server this generates unnecessary noise. How to repeat: set a secondary password for a user and use it for connections to mysqld, then look in the error log. Suggested fix: I understand some people might want to see this in logging. - I would like to see this logging to be optional behaviour I would prefer to see a P_S table which contains the username / host / counter / first_seen / last_seen entries so that I can query such usage if I want to see these metrics.