MySQL Bugs: #97735: ALTER USER IF EXISTS ... WITH_MAX_USER_CONNECTIONS 9999 not applied correctly

Bug #97735	ALTER USER IF EXISTS ... WITH_MAX_USER_CONNECTIONS 9999 not applied correctly
Submitted:	21 Nov 2019 15:46	Modified:	4 Feb 2020 17:35
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
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
Tags:	ALTER USER

Description:
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.

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.

Hello Simon,

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

regards,
Umesh

Test results - 5.7.28

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

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.