Bug #97735 ALTER USER IF EXISTS ... WITH_MAX_USER_CONNECTIONS 9999 not applied correctly
Submitted: 21 Nov 2019 15:46 Modified: 4 Feb 17:35
Reporter: Simon Mudd (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.27, 5.7.28 OS:Any (CentOS 7)
Assigned to: CPU Architecture:Any

[21 Nov 2019 15:46] Simon Mudd
Seen on 5.7.27. This may be an incomplete interaction with ALTER USER IF EXISTS but I'm not 100% sure yet.

I limit user connections per user with CREATE USER ... WITH_MAX_USER_CONNECTIONS 99;
Sometimes I need to change the limit so I apply that with ALTER USER ... WITH MAX_USER_CONNECTIONS 888; and expect the change to be applied immediately.

It should not be necessary with an ALTER USER command to run FLUSH PRIVILEGES as I am not touching the privilege tables myself.

How to repeat:
1. Create a user with a MAX_USER_CONNECTIONS limit
2. exceed the value and get an "expected" error
3. Run ALTER USER IF EXISTS xxxx WITH MAX_USER_CONNECTIONS 999  -- a higher value
4. Expect to see the connections work now.

I see this doesn't happen.

Workaround: FLUSH PRIVILEGES but this shouldn't be necessary.
I'm not sure if [IF EXISTS] makes a difference but the code I was using uses this syntax so it's mentioned explicitly.

Suggested fix:
Apply the privileges immediately after executing the ALTER USER statement.
[21 Nov 2019 17:26] Simon Mudd
A follow on comment. I was looking at https://dev.mysql.com/doc/refman/8.0/en/alter-user.html to double check intended behaviour and notice no reference in this document to "when" the changes apply and what happens to existing connections.

So clarifying what happens to existing connections for a user when ALTER USER is executed would be good, as well as what happens to new users after the ALTER USER has been executed.

Again I was looking at 5.7 and am not sure if 8.0 behaves differently, but ideally we'd like "user changes" to be visible as soon as possible after they are executed for existing connected users.
[22 Nov 2019 8:45] MySQL Verification Team
Hello Simon,

Thank you for the report.
Observed reported behavior with 5.7.28 build.

[22 Nov 2019 8:46] MySQL Verification Team
Test results - 5.7.28

Attachment: 97735_5.7.28.results (application/octet-stream, text), 6.34 KiB.

[4 Feb 17:35] Paul Dubois
Posted by developer:
Fixed in 5.7.30, 8.0.20.

Using ALTER USER to reset an account MAX_USER_CONNECTIONS value did
not take effect until all current account connections terminated,
if there were any.