MySQL Bugs: #32071: Account limits are not enforced per account

Bug #32071	Account limits are not enforced per account
Submitted:	3 Nov 2007 3:48	Modified:	28 Nov 2007 16:29
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.0,4.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	account, limit, resource

Description:
The manual at http://dev.mysql.com/doc/refman/4.1/en/user-resources.html states that limits (max_questions, max_updates, max_connections) in MySQL 4.0 and 4.1 are enforced per account. They are not. They are enforce per (account name, client IP) pair because the hash key for the structure used to enforce the limits uses THD::host_or_ip.

And the comment below from mysqld for 5.0 confirms this is the case:
 {"old-style-user-limits", OPT_OLD_STYLE_USER_LIMITS,
   "Enable old-style user limits (before 5.0.3 user resources were counted per each user+host vs. per account)",
   (gptr*) &opt_old_style_user_limits, (gptr*) &opt_old_style_user_limits,
   0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},

When you test this using clients from one host, the behavior appears to match the docs, but only because all clients use the same IP.

The feature has little use as implemented in 4.0, but I suppose that 4.0 and 4.1 are not getting fixes.

How to repeat:
Set a limit, confirm the limit is enforced when all clients connect from one host, confirm the limit is not enforced when clients connect from different hosts.

Suggested fix:
Update the docs.

Thank you for the bug report.

The page in question says:

     An account in this context is a single row in the user table. Each account is 
     uniquely identified by its User and Host column values.

I think this makes it pretty clear that it's for this *combination* that it's determined when a given limit is reached, and not for just a username.

And as my bug report states, it doesn't enforce resource limits at that level (Host, User). If (User, Host) is ('foo', '10.%') and I connect as 'foo' from 10.0.0.0 and 10.1.1.1, then the hash table internal to mysqld that tracks resource limits will have two entries. One uses ('foo':'10.0.0.0') as the key. The other uses ('foo':'10.1.1.1') as the key. And resources used by these two connections are limited separately rather than together.

Jon,

This is a bug in the documentation, as Mark stated in his last comment on this bug. 

A single row in the mysql.user can result in innumerable entries within the hash table that tracks users/privileges etc., given wild cards included in the host - and each entry in the hash table has their resources tracked individually. 

Please clarify this in the documentation.

Mark

I'll take this. It has to do with a change made in 5.0.3 in the way that clients are tracked. Prior to 5.0.3, the actual host from which the account connected was used. As of 5.0.3, the Host value in the user table row is used. So for a Host value such as %.example.com, clients with a given username connecting from different example.com hosts will be tracked as separate "accounts" prior to 5.0.3, but as a single "account" as of 5.0.3.

At least, that's how I understand it. Mark C and Mark L, does that match your understanding?

Hi Paul, 

You are correct in your understanding yes (I too have tested this in the past).

Thanks,

Mark

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Text for 4.1 manual:

An account in this context is assessed against the actual host from
which a user connects. Suppose that there is a row in the user table
that has User and Host values of usera and %.example.com, to allow
usera to connect from any host in the example.com domain. If usera
connects simultaneously from host1.example.com and host2.example.com,
the server applies the account resource limits separately to each
connection. If usera connects again from host1.example.com, the
server applies the limits for that connection together with the
existing connection from that host.

Text for 5.0 manual:

Before MySQL 5.0.3, an account in this context is assessed against
the actual host from which a user connects. Suppose that there is a
row in the user table that has User and Host values of usera and
%.example.com, to allow usera to connect from any host in the
example.com domain. If usera connects simultaneously from
host1.example.com and host2.example.com, the server applies the
account resource limits separately to each connection. If usera
connects again from host1.example.com, the server applies the limits
for that connection together with the existing connection from that
host.

As of MySQL 5.0.3, an account is assessed as a single row in the user
table. That is, connections are assessed against the Host value in
the user table row that applies to the connection. In this case, the
server applies resource limits collectively to all connections by
usera from any host in the example.com domain. The pre-5.0.3 method
of accounting may be selected by starting the server with the
--old-style-user-limits option.