Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17852

Altered connection limits for user have no effect

Details

    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.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          solsson Staffan Olsson added a comment -

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

          test-max-user-connections.txt

          solsson Staffan Olsson added a comment - We have the same issue for MAX_USER_CONNECTIONS as well. Attaching another repro case txt. test-max-user-connections.txt
          solsson Staffan Olsson added a comment -

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

          solsson Staffan Olsson added a comment - It makes no difference if you ALTER before the limit is reached. The original limit is still in effect.
          elenst Elena Stepanova added a comment - - 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@'%';

          elenst Elena Stepanova added a comment - - 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@ '%' ;
          solsson Staffan Olsson added a comment -

          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

          solsson Staffan Olsson added a comment - 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

          People

            serg Sergei Golubchik
            solsson Staffan Olsson
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.