MySQL Bugs: #60266: Limit Account Resources not enforcing properly

Bug #60266	Limit Account Resources not enforcing properly
Submitted:	26 Feb 2011 18:08	Modified:	2 May 2011 23:35
Reporter:	Sean Jenkins	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.47,5.5.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Limit Account Resources

Description:
MySQL's user accounting/resource limiting does not enforce on the max_queries_per_hour or max_updates_per_hour when performed via a script, but does when ran through command-line.

How to repeat:
create database limituser;

grant all privileges on limituser.* to 'usera'@'localhost' identified by 'test' with max_queries_per_hour 10 max_updates_per_hour 10;

flush privileges;

use mysql;

select * from user where User="usera"\G (Note that max_questions and max_updates are properly set, and the others are at 0)

use limituser;

create table test (id int);

Connect as the user via command line and run a series of selects/updates.  After a few queries, you should get messages of:

ERROR 1226 (42000): User 'usera' has exceeded the 'max_questions' resource (current value: 10)

Now, write a script in your preferred language similar to this:

#!/usr/bin/perl

use strict;
use DBI;

my $dbistring = 'DBI:mysql:host=localhost:database=limituser:user=usera:password=test';

foreach my $n (1..10) {
  my $dbh = DBI->connect( "$dbistring" );
  if ($dbh) {
    my $sth = $dbh->prepare(qq{SELECT SQL_NO_CACHE id FROM test WHERE id = $n LIMIT 1});
    $sth->execute();
    my $result = $sth->fetchrow;
    $sth->finish();
    print "$result\n";
    $dbh->disconnect();
  } else {
    print "$DBI::errstr\n";
  }
}

Notice that even though according to the command line, I was blocked, the script connected just fine and returned the results.  The same holds true even if I'm doing strictly updates or a combination of the two in the script.  If I issue a "flush user_resources" and then run the script to the point I would otherwise have been blocked, I am blocked via the command line, but the script can still go through indefinitely.

Here's hoping that it's something all too obvious that I'm missing :-/

Suggested fix:
MySQL user accounting/resource limiting needs to enforce regardless of the method that it connects on.  Seeing as the internal counters are working and blocking the user via command line if the only way they've connected is via the script, it needs to enforce there too.

I tested this again trying to block from the script, and then login via the command line, and the internal counter was still at 0 despite numerous selects/updates triggered via a script.  This would seem that the internal counter isn't updating, hence not blocking, unless connecting via the command line.

I believe I found the culprit, however this still needs fixing despite finding a work-around for the time being.  In the my.cnf, I had set max_user_connections=15.  Despite the grant being set to 0 (unlimited) for all the user connect stuff, it was this part that was being enforced, not the max_questions and max_updates.  This same behaviour was mentioned here: http://www.devcomments.com/q521104/Per-user-resource-limits-MySQL-Problem-with-PHP

I experience another problem witk a C-API program (SQLyog). I tried with SQLyog (connecting as user 'testa' - server is 5.5.9). I am not even able to SHOW:

SHOW FULL TABLE FROM `limituser` WHERE table_type = 'BASE TABLE'

returns "User 'usera' has exceeded the 'max_questions' resource (current value: 10)"

This is the complete HISTORY from SQLyog for this connection (there are a few more configuration option executed as parameters to mysql_options() but nothing that accesses data):

/*[11:28:28][ 0 ms]*/ SHOW VARIABLES LIKE 'lower_case_table_names'; 
/*[11:28:28][ 0 ms]*/ SET NAMES 'utf8'; 
/*[11:28:28][ 0 ms]*/ SHOW DATABASES; 
/*[11:28:33][ 0 ms]*/ USE `limituser`; 
/*[11:28:34][ 0 ms]*/ SHOW FULL TABLES FROM `limituser` WHERE table_type = 'BASE TABLE'; 

Can supporters here please tell if this requires a new report or not?

Peter,

I think your case is different. Original report says that limits are NOT enforced at all for C API program. In your case you suspect that C API program is allowed to execute fewer queries than you expected. At least this is how I understand your comment...

So, please, open another bug report with complete test case.

I did as root: "FLUSH USER_RESOURCES;"

Now as usera I can "SHOW FULL TABLES FROM `limituser` WHERE table_type = 'BASE TABLE';" exactly 10 times before I get the error.  So it seems that SHOW-queries sent as parameters to mysql_real_connect()/mysql_options() 'count' too. So it is likely not an issue.

I will create a separate report if further debugging reveals something.

.. but I still do not exclude the possibility that after GRANT the counter is 'in mess' and FLUSH is required to reset it properly.

Thank you for the report.

Please provide content of $dbistring  with real password and username screened by ***: I want to check host.

my $dbistring = 'DBI:mysql:host=box231.bluehost.com:database=limituser:user=*****:password=*****';

Thank you for the feedback.

So host is box231.bluehost.com, but you granted privilege to usera@localhost. Please run query SELECT USER(), CURRENT_USER() from Perl program and send us its output.

Both results returned 'usera@localhost'.  The script is running at localhost (ie, on box231.bluehost.com).  You had said you wanted to check host as part of your last message, hence providing you the FQDN.  Grants are applied to usera@locahost, as that is where the script is being ran from.  Although I can apply grants to usera@box231.bluehost.com and change the script to access it that way too, the end result is the same on not enforcing resource limits.  The only difference is that "SELECT USER()" and "SELECT CURRENT_USER()" return usera@localhost or usera@box231.bluehost.com, based on hostname specified as determined through $dbistring.

Thank you for the feedback.

I can not repeatt described behavior. Which version of DBD::MySQL do you use?

root(sean)@box231 [root]# perl -MDBD::mysql -le 'print $DBD::mysql::VERSION'
4.018

And I can duplicate this problem across over 800 servers, so I'd be curious how you're testing it and not recreating the problem.

Thank you for the feedback.

Do you have same problem if specify localhost in perl program?

The original method was using localhost, so yes, I can duplicate it both ways, as I'd mentioned previously in this thread.

In case it wasn't clear, the enforcing of the max_questions/updates only happens when all max_connections and max_user_connections are 0.  Entries of max_user_connections in your my.cnf will also make enforcement on max_questions/updates not work.  This may be why you can't duplicate the issue, cause you have these at 0.

As mentioned by Peter Laursen, I can duplicate this behavior too, and it typically takes 3-4 flush user_resources for it to properly work.  But I believe that needs to be addressed in a different report.

Thank you for the feedback.

I still can not repeat described behavior. Please provide output of SELECT user, host FROM mysql.user.

The only entry pertaining to usera: 

| usera            | localhost           |

I've re-tested this several times over and am unable to reproduce the problem any longer.  Part of the delayed response was largely in part to seeing if 5.5.10 had any impact whatsoever, despite the changelog not indicating anything about this.

Thank you for the feedback. So isn't repeatable anymore?. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".