[MDEV-17852] Altered connection limits for user have no effect - Jira

Staffan Olsson created issue - 2018-11-27 15:41

Staffan Olsson made changes - 2018-11-27 15:42

Field	Original Value	New Value
Description	Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice. Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image. To reproduce: {{docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0.13@sha256:b7f7479f0a2e7a3f4ce008329572f3497075dc000d8b89bac3134b0fb0288de8 docker exec -ti mysql bash # in the container mysql -e "CREATE USER IF NOT EXISTS 'testuser'@'%' IDENTIFIED BY 'testuser' WITH MAX_CONNECTIONS_PER_HOUR 3;" mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null # = "ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)" mysql -e "ALTER USER 'testuser'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10;" mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null # = works again exit # outside the container docker kill mysql}} Now try the same experiment with MariaDB {{docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mariadb:10.2.19@sha256:8263e0a4a69ee6defdea16c48ed2c2243f086a5fed3febb2d062e6e938dc7c96}} {{root@82eff92299fd:/# mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3) root@82eff92299fd:/# mysql -e "USE mysql; SELECT host, user, max_user_connections, max_connections FROM user;" +-----------+----------+----------------------+-----------------+ \| host \| user \| max_user_connections \| max_connections \| +-----------+----------+----------------------+-----------------+ \| localhost \| root \| 0 \| 0 \| \| % \| testuser \| 0 \| 10 \| +-----------+----------+----------------------+-----------------+}}	Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice. Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image. To reproduce: ``` docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0.13@sha256:b7f7479f0a2e7a3f4ce008329572f3497075dc000d8b89bac3134b0fb0288de8 docker exec -ti mysql bash # in the container mysql -e "CREATE USER IF NOT EXISTS 'testuser'@'%' IDENTIFIED BY 'testuser' WITH MAX_CONNECTIONS_PER_HOUR 3;" mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null # = "ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)" mysql -e "ALTER USER 'testuser'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10;" mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null # = works again exit # outside the container docker kill mysql ``` Now try the same experiment with MariaDB {{docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mariadb:10.2.19@sha256:8263e0a4a69ee6defdea16c48ed2c2243f086a5fed3febb2d062e6e938dc7c96}} {{root@82eff92299fd:/# mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3) root@82eff92299fd:/# mysql -e "USE mysql; SELECT host, user, max_user_connections, max_connections FROM user;" +-----------+----------+----------------------+-----------------+ \| host \| user \| max_user_connections \| max_connections \| +-----------+----------+----------------------+-----------------+ \| localhost \| root \| 0 \| 0 \| \| % \| testuser \| 0 \| 10 \| +-----------+----------+----------------------+-----------------+}}

Staffan Olsson made changes - 2018-11-27 15:43

Attachment

MDEV-17852-repro.txt [ 46766 ]

Staffan Olsson made changes - 2018-11-27 15:43

Description

Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image.

To reproduce:

```
docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0.13@sha256:b7f7479f0a2e7a3f4ce008329572f3497075dc000d8b89bac3134b0fb0288de8
docker exec -ti mysql bash
# in the container
mysql -e "CREATE USER IF NOT EXISTS 'testuser'@'%' IDENTIFIED BY 'testuser' WITH MAX_CONNECTIONS_PER_HOUR 3;"
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
# = "ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)"
mysql -e "ALTER USER 'testuser'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10;"
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
# = works again
exit
# outside the container
docker kill mysql
```

Now try the same experiment with MariaDB

{{docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mariadb:10.2.19@sha256:8263e0a4a69ee6defdea16c48ed2c2243f086a5fed3febb2d062e6e938dc7c96}}

{{root@82eff92299fd:/# mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)
root@82eff92299fd:/# mysql -e "USE mysql; SELECT host, user, max_user_connections, max_connections FROM user;"
+-----------+----------+----------------------+-----------------+
| host | user | max_user_connections | max_connections |
+-----------+----------+----------------------+-----------------+
| localhost | root | 0 | 0 |
| % | testuser | 0 | 10 |
+-----------+----------+----------------------+-----------------+}}

Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image.

To reproduce:

docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0.13@sha256:b7f7479f0a2e7a3f4ce008329572f3497075dc000d8b89bac3134b0fb0288de8
docker exec -ti mysql bash
# in the container
mysql -e "CREATE USER IF NOT EXISTS 'testuser'@'%' IDENTIFIED BY 'testuser' WITH MAX_CONNECTIONS_PER_HOUR 3;"
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
# = "ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)"
mysql -e "ALTER USER 'testuser'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10;"
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
# = works again
exit
# outside the container
docker kill mysql

Now try the same experiment with MariaDB

docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mariadb:10.2.19@sha256:8263e0a4a69ee6defdea16c48ed2c2243f086a5fed3febb2d062e6e938dc7c96

root@82eff92299fd:/# mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)
root@82eff92299fd:/# mysql -e "USE mysql; SELECT host, user, max_user_connections, max_connections FROM user;"
+-----------+----------+----------------------+-----------------+
| host | user | max_user_connections | max_connections |
+-----------+----------+----------------------+-----------------+
| localhost | root | 0 | 0 |
| % | testuser | 0 | 10 |
+-----------+----------+----------------------+-----------------+ [^~~MDEV-17852~~-repro.txt]

Staffan Olsson made changes - 2018-11-27 15:44

Description

Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image.

To reproduce:

docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.0.13@sha256:b7f7479f0a2e7a3f4ce008329572f3497075dc000d8b89bac3134b0fb0288de8
docker exec -ti mysql bash
# in the container
mysql -e "CREATE USER IF NOT EXISTS 'testuser'@'%' IDENTIFIED BY 'testuser' WITH MAX_CONNECTIONS_PER_HOUR 3;"
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
# = "ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)"
mysql -e "ALTER USER 'testuser'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10;"
mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
# = works again
exit
# outside the container
docker kill mysql

Now try the same experiment with MariaDB

docker run -d --rm --name mysql -e MYSQL_ROOT_HOST=localhost -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mariadb:10.2.19@sha256:8263e0a4a69ee6defdea16c48ed2c2243f086a5fed3febb2d062e6e938dc7c96

root@82eff92299fd:/# mysql -utestuser -ptestuser -e "SHOW DATABASES" > /dev/null
ERROR 1226 (42000): User 'testuser' has exceeded the 'max_connections_per_hour' resource (current value: 3)
root@82eff92299fd:/# mysql -e "USE mysql; SELECT host, user, max_user_connections, max_connections FROM user;"
+-----------+----------+----------------------+-----------------+
| host | user | max_user_connections | max_connections |
+-----------+----------+----------------------+-----------------+
| localhost | root | 0 | 0 |
| % | testuser | 0 | 10 |
+-----------+----------+----------------------+-----------------+ [^~~MDEV-17852~~-repro.txt]

Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image.

Edit: moved the repro case to an attached text file because I couldn't get proper preformatted in JIRA.

[^~~MDEV-17852~~-repro.txt]

Staffan Olsson made changes - 2018-11-27 19:30

Description

Updating MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

Works on MySQL 8.0.13 but not MariaDB 10.2.19. Tested both as standalone using the respective official docker image.

Edit: moved the repro case to an attached text file because I couldn't get proper preformatted in JIRA.

[^~~MDEV-17852~~-repro.txt]

When you update MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

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;

Staffan Olsson made changes - 2018-11-27 19:35

Description

When you update MAX_CONNECTIONS_PER_HOUR has effect on the users table, but not in practice.

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;

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.

Staffan Olsson made changes - 2018-11-28 08:45

Attachment

test-max-user-connections.txt [ 46776 ]

Staffan Olsson added a comment - 2018-11-28 08:46

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

test-max-user-connections.txt

Staffan Olsson added a comment - 2018-11-28 08:46 We have the same issue for MAX_USER_CONNECTIONS as well. Attaching another repro case txt. test-max-user-connections.txt

Staffan Olsson made changes - 2018-11-28 08:46

Summary

ALTER USER WITH MAX_CONNECTIONS_PER_HOUR has no effect

Altered connection limits for user have no effect

Staffan Olsson added a comment - 2018-11-28 08:59

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

Staffan Olsson added a comment - 2018-11-28 08:59 It makes no difference if you ALTER before the limit is reached. The original limit is still in effect.

Elena Stepanova made changes - 2018-11-28 17:47

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2018-11-28 17:53 - edited

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@'%';

Elena Stepanova added a comment - 2018-11-28 17:53 - edited 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@ '%' ;

Elena Stepanova made changes - 2018-11-28 17:53

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Elena Stepanova made changes - 2018-11-28 17:54

Labels

upstream-fixed

Elena Stepanova made changes - 2018-11-28 17:54

Assignee

Sergei Golubchik [ serg ]

Staffan Olsson made changes - 2018-11-29 23:24

Attachment

MDEV-17852-repro-docker-with-workaround.txt [ 46787 ]

Staffan Olsson added a comment - 2018-11-29 23:25

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

Staffan Olsson added a comment - 2018-11-29 23:25 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

Sergei Golubchik made changes - 2021-01-09 16:54

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2021-01-09 16:56

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-01-12 09:23

Fix Version/s		10.2.37 [ 25112 ]
Fix Version/s		10.3.28 [ 25111 ]
Fix Version/s		10.4.18 [ 25110 ]
Fix Version/s		10.5.9 [ 25109 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 90934 ]

MariaDB v4 [ 155282 ]

MariaDB Server

Altered connection limits for user have no effect

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration