[MDEV-17852] Altered connection limits for user have no effect Created: 2018-11-27  Updated: 2021-01-12  Resolved: 2021-01-12

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Bug Priority: Major
Reporter: Staffan Olsson Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: upstream-fixed

Attachments: Text File MDEV-17852-repro-docker-with-workaround.txt     Text File MDEV-17852-repro.txt     Text File test-max-user-connections.txt    

 Description   

This issue points out a difference between MySQL and MariaDB, where MySQL seems to have the expected behavior. When you update MAX_CONNECTIONS_PER_HOUR for a user the change can be seen in the users table, but in MariaDB it has no effect in practice. A blocked user will still not be able to connect. This can have serious consequences in production.

With MySQL 8.0.13 the ALTER actually lets new clients connect with the account, but with MariaDB 10.2.19 it does not. See the attached text file for a simple repro case with Docker.

MDEV-17852-repro.txt

I've also tested to change the limit using the following statements, but with the same result:

  • UPDATE mysql.user SET max_connections = 0 WHERE user='testuser'; FLUSH PRIVILEGES;
  • GRANT USAGE ON . TO 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0;
  • Delete and re-create the user with a higher limit.

 Comments   
Comment by Staffan Olsson [ 2018-11-28 ]

We have the same issue for MAX_USER_CONNECTIONS as well. Attaching another repro case txt.

test-max-user-connections.txt

Comment by Staffan Olsson [ 2018-11-28 ]

It makes no difference if you ALTER before the limit is reached. The original limit is still in effect.

Comment by Elena Stepanova [ 2018-11-28 ]

Thanks for the report. It appears that you have to run FLUSH PRIVILEGES after ALTER USER. I'm not sure it's meant to be so, but you can use it as a workaround.
Same problem exists in MySQL 5.7, but apparently fixed in 8.0.

The test case below fails on connecting con3 with

mysqltest: At line 15: query 'connect  con3,localhost,foo,,' failed: 1226: User 'foo' has exceeded the 'max_user_connections' resource (current value: 1)

If FLUSH PRIVILEGES is uncommented, it works okay.

Unfortunately, the test case, when it fails, retries con3 500 times before giving up,as it's a default value for mysqltest. If possible, adjust it before adding to the regression suite.

CREATE USER foo WITH MAX_USER_CONNECTIONS 1;
 
 
 
--connect (con1,localhost,foo,,)
 
SELECT CURRENT_USER();
 
 
 
--error ER_USER_LIMIT_REACHED
 
--connect (con2,localhost,foo,,)
 
 
 
--connection default
 
ALTER USER foo WITH MAX_USER_CONNECTIONS 2;
 
 
 
#FLUSH PRIVILEGES;
 
 
 
--connect (con3,localhost,foo,,)
 
SELECT CURRENT_USER();
 
 
 
# Cleanup
 
--disconnect con3
 
--disconnect con1
 
--connection default
 
DROP USER foo@'%';

Comment by Staffan Olsson [ 2018-11-29 ]

Thanks for the workaround. It makes this issue non-urgent. I was quite certain that I had tested with FLUSH PRIVILEGES, but apparently not good enough, or maybe when other limits were in play too.

Didn't get the connect commands to work in the sql prompt so I made another docker based repro case. Attaching that one too but it doesn't add any information.

MDEV-17852-repro-docker-with-workaround.txt

Generated at Thu Feb 08 08:39:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.