Ещё раз про бесправных пользователей
Несколько дней назад я писала пост о том как MySQL пользователь с минимальными правами доступа может получить очень много ресурсов. Честно говоря, мотивацией для написания статьи было не столько желание вас попугать, сколько желание ещё раз показать memory tables в Performance Schema.
Однако читатели больше обращали внимание на результат теста и на мои ошибки. Поэтому сейчас я хочу немножко поговорить об этих ошибках и реальных опасностях.
Во-первых, Egor Rukhvadze справедливо отметил, что хотя пользователь-то у меня только с USAGE, но таблицу mysql.db я не почистила:
mysql> select host, db, user from mysql.db;
+------+---------+------+
| host | db | user |
+------+---------+------+
| % | test | |
| % | test\_% | |
+------+---------+------+
2 rows in set (0.01 sec)
Так что пример я привела неправильный. Для чистоты эксперимента давайте сделаем пример получше:
mysql> truncate db;
Query OK, 0 rows affected (0.01 sec)
mysql> select host, db, user from mysql.db;
Empty set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant usage on *.* to lj@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select user, host from mysql.user;
+------+--------------------------+
| user | host |
+------+--------------------------+
| lj | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | svetas-macbook-pro.local |
+------+--------------------------+
5 rows in set (0.00 sec)
И соединимся пользователем lj:
mysql> use test
ERROR 1044 (42000): Access denied for user 'lj'@'%' to database 'test'
Да, я перепутала привилегии с опцией read-only.
Однако доступ к Information Schema есть всегда, тем более что большинство SHOW команд теперь mapped to Information Schema. Для недобросовестного пользователя это даже лучше, потому что таблицы Information Schema позволяют сделать много больше, чем временные.
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Кстати, удалить этот доступ нельзя:
mysql> revoke all on information_schema from lj@'%';
ERROR 1147 (42000): There is no such grant defined for user 'lj' on host '%' on table 'information_schema'
Соответственно нам ничего не стоит сделать запрос типа
select sleep(1) from tables t1 join tables t2 join tables t3 join tables t4 join tables t5 join tables t6 join tables t7 join tables t8 join tables t9 join tables t10 ;
С тем, чтобы в параллельном клиенте получить те же 36G:
mysql> select thread_id from performance_schema.threads where processlist_id=7;
+-----------+
| thread_id |
+-----------+
| 25 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in (25)\G
*************************** 1. row ***************************
thread_id: 25
user: lj@localhost
current_count_used: 212
current_allocated: 36.01 GiB
current_avg_alloc: 173.92 MiB
current_max_alloc: 36.00 GiB
total_allocated: 36.06 GiB
1 row in set (0.25 sec)
Магическое число 7 - это connection_id() клиента, который запустил JOIN.
Пользователь dkrnl сделал справедливое замечание, что если бы всё было бы так просто, shared hosting-и не выжили бы. Тем более, что у их пользователей прав побольше, чем USAGE. В самом деле, на помощь shared hosting-ам приходит возможность ограничить верхние значения переменных. Для нашего случая - это maximum-join-buffer-size. Проблема только в том, что даже установив его в минимальное возможное значение мы ещё даём возможность недобросовестному пользователю запустить ресурсоёмкий запрос.
В соответствии с http://dev.mysql.com/doc/refman/5.7/en/joins-limits.html мы можем объединить только 61 табличку в одном запросе. Это не так уж и много:
echo "select sleep(1) from tables t1" >tmp.sql; for i in `seq 2 1 61`; do echo " join tables t$i" >>tmp.sql; done
$ cat tmp.sql
select sleep(1) from tables t1
join tables t2
[...]
join tables t3
join tables t4
join tables t5
join tables t6
join tables t7
join tables t8
join tables t9
join tables t10
join tables t11
join tables t12
join tables t13
join tables t14
join tables t15
join tables t16
join tables t17
join tables t18
join tables t19
join tables t20
join tables t21
join tables t22
join tables t23
join tables t24
join tables t25
join tables t26
join tables t27
join tables t28
join tables t29
join tables t30
join tables t31
join tables t32
join tables t33
join tables t34
join tables t35
join tables t36
join tables t37
join tables t38
join tables t39
join tables t40
join tables t41
join tables t42
join tables t43
join tables t44
join tables t45
join tables t46
join tables t47
join tables t48
join tables t49
join tables t50
join tables t51
join tables t52
join tables t53
join tables t54
join tables t55
join tables t56
join tables t57
join tables t58
join tables t59
join tables t60
join tables t61
И немного памяти будет использовано, если мы запустим этот запрос при минимально возможном join_buffer_size.
$ ../bin/mysql -ulj -h127.0.0.1 -P13000 information_schema select @@join_buffer_size, @@global.join_buffer_size\G
*************************** 1. row ***************************
@@join_buffer_size: 131072
@@global.join_buffer_size: 131072
1 row in set (0.00 sec)
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in (select thread_id from performance_schema.threads where processlist_id is not null and processlist_id != connection_id())\G
*************************** 1. row ***************************
thread_id: 31
user: lj@localhost
current_count_used: 1071
current_allocated: 47.37 MiB
current_avg_alloc: 45.29 KiB
current_max_alloc: 36.57 MiB
total_allocated: 49.77 MiB
1 row in set (0.11 sec)
Однако есть ещё UNION, которых может быть сколько угодно:
$ cat tmp.sql>tmp1.sql ; for i in `seq 1 1 611`; do echo " union " >> tmp1.sql; cat tmp.sql >> tmp1.sql ; done
$ ../bin/mysql -ulj -h127.0.0.1 -P13000 information_schema
И картина здесь совсем другая:
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in (select thread_id from performance_schema.threads where processlist_id is not null and processlist_id != connection_id())\G
*************************** 1. row ***************************
thread_id: 37
user: lj@localhost
current_count_used: 411075
current_allocated: 6.36 GiB
current_avg_alloc: 16.22 KiB
current_max_alloc: 4.48 GiB
total_allocated: 6.36 GiB
1 row in set (0.11 sec)
В моём тесте всего два пользовательских соединения: администратора и злоумышленника, в реальной жизни подзапрос в IN будет несколько другим.
И это при минимально возможном join_buffer_size! Не забудьте также, что мы редко устанавливаем ограничение, например, в max_connections_per_hour в единицу. Недобросовестный пользователь легко может запустить несколько параллельных соединений.
К чему всё это я. В реальной жизни пользователей только лишь с правами USAGE у вас, скорее всего, не будет. У тех же, что будут, будут бОльшие права, которые позволят им создать, при желании, сколь угодно ресурсоёмкие запросы. Также вы не будете устанавливать все возможные верхние границы для буферов в минимально возможное значение. Просто потому, что для некоторых запросов они должны быть высокими. И хорошая практика - изменять эти буферы на уровне сессии.
Поэтому я повторюсь: внимательно следите за тем, кому и зачем вы раздаёте доступ к базе!
Однако читатели больше обращали внимание на результат теста и на мои ошибки. Поэтому сейчас я хочу немножко поговорить об этих ошибках и реальных опасностях.
Во-первых, Egor Rukhvadze справедливо отметил, что хотя пользователь-то у меня только с USAGE, но таблицу mysql.db я не почистила:
mysql> select host, db, user from mysql.db;
+------+---------+------+
| host | db | user |
+------+---------+------+
| % | test | |
| % | test\_% | |
+------+---------+------+
2 rows in set (0.01 sec)
Так что пример я привела неправильный. Для чистоты эксперимента давайте сделаем пример получше:
mysql> truncate db;
Query OK, 0 rows affected (0.01 sec)
mysql> select host, db, user from mysql.db;
Empty set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant usage on *.* to lj@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select user, host from mysql.user;
+------+--------------------------+
| user | host |
+------+--------------------------+
| lj | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | svetas-macbook-pro.local |
+------+--------------------------+
5 rows in set (0.00 sec)
И соединимся пользователем lj:
mysql> use test
ERROR 1044 (42000): Access denied for user 'lj'@'%' to database 'test'
Да, я перепутала привилегии с опцией read-only.
Однако доступ к Information Schema есть всегда, тем более что большинство SHOW команд теперь mapped to Information Schema. Для недобросовестного пользователя это даже лучше, потому что таблицы Information Schema позволяют сделать много больше, чем временные.
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Кстати, удалить этот доступ нельзя:
mysql> revoke all on information_schema from lj@'%';
ERROR 1147 (42000): There is no such grant defined for user 'lj' on host '%' on table 'information_schema'
Соответственно нам ничего не стоит сделать запрос типа
select sleep(1) from tables t1 join tables t2 join tables t3 join tables t4 join tables t5 join tables t6 join tables t7 join tables t8 join tables t9 join tables t10 ;
С тем, чтобы в параллельном клиенте получить те же 36G:
mysql> select thread_id from performance_schema.threads where processlist_id=7;
+-----------+
| thread_id |
+-----------+
| 25 |
+-----------+
1 row in set (0.00 sec)
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in (25)\G
*************************** 1. row ***************************
thread_id: 25
user: lj@localhost
current_count_used: 212
current_allocated: 36.01 GiB
current_avg_alloc: 173.92 MiB
current_max_alloc: 36.00 GiB
total_allocated: 36.06 GiB
1 row in set (0.25 sec)
Магическое число 7 - это connection_id() клиента, который запустил JOIN.
Пользователь dkrnl сделал справедливое замечание, что если бы всё было бы так просто, shared hosting-и не выжили бы. Тем более, что у их пользователей прав побольше, чем USAGE. В самом деле, на помощь shared hosting-ам приходит возможность ограничить верхние значения переменных. Для нашего случая - это maximum-join-buffer-size. Проблема только в том, что даже установив его в минимальное возможное значение мы ещё даём возможность недобросовестному пользователю запустить ресурсоёмкий запрос.
В соответствии с http://dev.mysql.com/doc/refman/5.7/en/joins-limits.html мы можем объединить только 61 табличку в одном запросе. Это не так уж и много:
echo "select sleep(1) from tables t1" >tmp.sql; for i in `seq 2 1 61`; do echo " join tables t$i" >>tmp.sql; done
$ cat tmp.sql
select sleep(1) from tables t1
join tables t2
[...]
join tables t3
join tables t4
join tables t5
join tables t6
join tables t7
join tables t8
join tables t9
join tables t10
join tables t11
join tables t12
join tables t13
join tables t14
join tables t15
join tables t16
join tables t17
join tables t18
join tables t19
join tables t20
join tables t21
join tables t22
join tables t23
join tables t24
join tables t25
join tables t26
join tables t27
join tables t28
join tables t29
join tables t30
join tables t31
join tables t32
join tables t33
join tables t34
join tables t35
join tables t36
join tables t37
join tables t38
join tables t39
join tables t40
join tables t41
join tables t42
join tables t43
join tables t44
join tables t45
join tables t46
join tables t47
join tables t48
join tables t49
join tables t50
join tables t51
join tables t52
join tables t53
join tables t54
join tables t55
join tables t56
join tables t57
join tables t58
join tables t59
join tables t60
join tables t61
И немного памяти будет использовано, если мы запустим этот запрос при минимально возможном join_buffer_size.
$ ../bin/mysql -ulj -h127.0.0.1 -P13000 information_schema select @@join_buffer_size, @@global.join_buffer_size\G
*************************** 1. row ***************************
@@join_buffer_size: 131072
@@global.join_buffer_size: 131072
1 row in set (0.00 sec)
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in (select thread_id from performance_schema.threads where processlist_id is not null and processlist_id != connection_id())\G
*************************** 1. row ***************************
thread_id: 31
user: lj@localhost
current_count_used: 1071
current_allocated: 47.37 MiB
current_avg_alloc: 45.29 KiB
current_max_alloc: 36.57 MiB
total_allocated: 49.77 MiB
1 row in set (0.11 sec)
Однако есть ещё UNION, которых может быть сколько угодно:
$ cat tmp.sql>tmp1.sql ; for i in `seq 1 1 611`; do echo " union " >> tmp1.sql; cat tmp.sql >> tmp1.sql ; done
$ ../bin/mysql -ulj -h127.0.0.1 -P13000 information_schema
И картина здесь совсем другая:
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in (select thread_id from performance_schema.threads where processlist_id is not null and processlist_id != connection_id())\G
*************************** 1. row ***************************
thread_id: 37
user: lj@localhost
current_count_used: 411075
current_allocated: 6.36 GiB
current_avg_alloc: 16.22 KiB
current_max_alloc: 4.48 GiB
total_allocated: 6.36 GiB
1 row in set (0.11 sec)
В моём тесте всего два пользовательских соединения: администратора и злоумышленника, в реальной жизни подзапрос в IN будет несколько другим.
И это при минимально возможном join_buffer_size! Не забудьте также, что мы редко устанавливаем ограничение, например, в max_connections_per_hour в единицу. Недобросовестный пользователь легко может запустить несколько параллельных соединений.
К чему всё это я. В реальной жизни пользователей только лишь с правами USAGE у вас, скорее всего, не будет. У тех же, что будут, будут бОльшие права, которые позволят им создать, при желании, сколь угодно ресурсоёмкие запросы. Также вы не будете устанавливать все возможные верхние границы для буферов в минимально возможное значение. Просто потому, что для некоторых запросов они должны быть высокими. И хорошая практика - изменять эти буферы на уровне сессии.
Поэтому я повторюсь: внимательно следите за тем, кому и зачем вы раздаёте доступ к базе!