Bug #29340 Possible DoS attack: any user can set sort_buffer_size/max_heap_size
Submitted: 25 Jun 2007 15:04 Modified: 16 Oct 2008 14:57
Reporter: Kai Voigt Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.19-beta/5.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[25 Jun 2007 15:04] Kai Voigt
Description:
Any user can set the session variables sort_buffer_size and max_heap_table_size to any value and so can (ab)use the server's memory.

How to repeat:
as root user:

mysql> CREATE USER foo;

as regular user:

$ mysql -u foo;
mysql> SET SESSION sort_buffer_size=100000000;
mysql> SET SESSION max_heap_table_size=100000000;
[25 Jun 2007 15:05] Kai Voigt
Changing the session values to something higher than the global values should only be allowed if you have SUPER privileges.
[26 Jun 2007 10:38] Shane Bester
simple testcase
----------------
set session max_heap_table_size=1024*1024*1024*10;
drop table if exists g;
create table g(a char(255))engine=memory;
insert into g values ('memory eater');
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
insert into g select * from g;
[26 Jun 2007 12:33] Miguel Solorzano
Thank you for the bug report.
[10 Jul 2007 8:25] Konstantin Osipov
Suggest to disallow setting of a session variable to a value higher than the global variable, regardless of privileges.
[11 Jul 2007 12:13] Alexander Keremidarski
SET syntax was introduced in early 4.0 and since the it proved to be very useful tool. 

The danger here is exaggerated simply because *any* user with access to MySQL with can do DoS attack without need to send SET commands. SELECT_Priv alone is enough for DoS attack.
[11 Jul 2007 12:30] Konstantin Osipov
If we fix this bug as suggested, we will destroy a lot of current usage scenarios 
of these tuning parameters.
It's often suggested that the global default is kept moderate, and values of these optimization parameters are adjusted for critical user sessions only.
It's unrealistic to require that all such sessions are run under SUPER account.

It's also incorrect to introduce dual semantics of a tuning parameter, which is designed to address performance problems, and extend it with semantics of a resource limit.

A resource limit is an attribute of the resource - i.e. a correct fix would be
to introduce MAX_MEMORY parameter in USER RESOURCES that would apply to all memory used by a given user across all connections.

A fix for this DoS attack will not prevent other opportunities, since not all memory is accounted for or limited by a variable. Besides, one could still open multiple connections or run a statement that uses multiple stored functions and thus have multiple applications of the same tuning parameter in scope of the same statement.

A correct fix for this and numbers of other DoS attacks that are related to Out of Memory is to introduce a global per-user limit.

Note, that if the server crashes when it runs out of memory, it's a separate bug and the crash itself should be fixed.
[26 Aug 2007 16:18] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=30606 has been marked as duplicate
of this one.
[15 Apr 2008 11:09] Shane Bester
All, there is a simple workaround for this.  You can limit session sizes of these variables.  Just run mysqld with these:

--maximum-sort_buffer_size=4M --maximum_read_buffer_size=2M --maximum_tmp_table_size=32M

Now nobody can set a session value higher than that ;-0

mysql> SET SESSION sort_buffer_size=100000000;
Query OK, 0 rows affected, 1 warning (0.25 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect sort_buffer_size value: '100000000' |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sort_buffer_size;
+----------------------------+
| @@session.sort_buffer_size |
+----------------------------+
|                    4194304 |
+----------------------------+
[26 Dec 2009 18:15] Baron Schwartz
I don't think it's a good idea to make the changes suggested by the OP, because it won't really prevent a DoS attack and will break various things.

1) Given the right hardware (especially IO system) and workload, I could imagine a DoS attack with too-small buffers, not just too-large.
2) If I want to run the server out of memory, I'll just create a bunch of large user variables, or prepared statements, or...

There are lots of ways to run the server out of memory and most of them have no safeguard.
[13 Oct 2010 15:09] Konstantin Osipov
+1 to Baron's comment, WL#3824