MySQL Bugs: #29340: Possible DoS attack: any user can set sort_buffer_size/max_heap

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

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;

Changing the session values to something higher than the global values should only be allowed if you have SUPER privileges.

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;

Thank you for the bug report.

Suggest to disallow setting of a session variable to a value higher than the global variable, regardless of privileges.

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.

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.

Bug: http://bugs.mysql.com/bug.php?id=30606 has been marked as duplicate
of this one.

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 |
+----------------------------+

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.

+1 to Baron's comment, WL#3824